我有一個表，用于存盤我使用 PHP 從 API 獲取的資料，該表如下所示：
| ID | 數數 | 時間戳 |
|---|---|---|
| 6285 | 123 | 21.11 18:54 |
| 6284 | 122 | 21.11 18:53 |
| 6283 | 121 | 21.11 18:52 |
| 6282 | 120 | 21.11 18:51 |
我想要實作的是：在過去的 24 小時內，這個數字增加了 X
要從資料庫中檢索資料，我使用：
while($row = mysqli_fetch_array($result))
{
// generating HTML table etc
// echo-ign data with $row['timestamp']
}
我的想法是在 24 小時內獲得第一次寫入和最后一次寫入并獲得差異，但我無法將這個想法轉移到 PHP
uj5u.com熱心網友回復：
自 v.8.0 以來的現代 MySQL 版本提供了window functions這樣的“FIRST_VALUE”：
SELECT DISTINCT
DATE(`timestamp`) as day,
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
FROM t;
PHP 解決方案可以是下一個（使用 PDO 模塊）：
<?php
$query = "
SELECT DISTINCT
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
FROM t WHERE DATE(`timestamp`) = '2021-11-21';";
$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
在這里你可以用 MySQL 視窗函式測驗 PHP
或使用 mysqli：
<?php
$query = "
SELECT DISTINCT
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp`) as start_day_count,
FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) as end_day_count
FROM t WHERE DATE(`timestamp`) = '2021-11-21';";
$result = mysqli_query($mysqli, $query);
$row = mysqli_fetch_array($result);
print_r($row);
PHP mysqli 演示
uj5u.com熱心網友回復：
我們可以說“我想知道過去 24 小時的最大和最小計數之間的差異”嗎？計數總是在增加嗎？
建議：
SELECT MAX(count) - MIN(count) AS up_24h
FROM your_table
WHERE timestamp >= NOW() - INTERVAL (24 * 60) MINUTE
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/362930.html