我想使用帶有 group by 的聚合函式來獲得兩個不同行之間的平均差異（例如以秒為單位）。
假設我有一張這樣的桌子：
id | ts | data
1123 | 2022-01-19 07:15:23 | ...
1124 | 2022-01-19 07:17:28 | ...
1125 | 2022-01-19 07:21:19 | ...
1126 | 2022-01-19 07:21:23 | ...
.
.
.
1227 | 2022-01-19 09:01:04 | ...
起初我想對它進行分組，并想計算我們每天有多少行。這很簡單：
SELECT DATE(ts), COUNT(data)
FROM table
GROUP BY DATE(ts)
結果如下所示：
DATE(ts) | COUNT(data)
2022-01-18 | 519
2022-01-19 | 104
但除此之外，我還想知道前一行之間的平均距離（以秒為單位），以便了解插入新資料的頻率。所以結果應該是這樣的：
DATE(ts) | Average Difference | COUNT(data)
2022-01-18 | 12 | 519
2022-01-19 | 14 | 104
有沒有辦法使用 SQL 獲取這樣的資訊？
uj5u.com熱心網友回復：
假設您使用的是 MySQL 8 或更高版本，您可以使用lag函式查找以前的日期時間以計算差異。休息是直截了當的：
with cte as (
select cast(ts as date) as dt
, ts
, lag(ts) over(partition by cast(ts as date) order by ts) as prev_ts
from t
)
select dt
, count(*) as count
, avg(timestampdiff(second, prev_ts, ts)) as avg_diff
from cte
group by dt
uj5u.com熱心網友回復：
您可以通過將最新時間戳和最早時間戳之間的差除以計數來估算給定日期所有記錄之間的平均距離（以分鐘為單位）：
SELECT DATE(ts),
TIMESTAMPDIFF(MINUTE, MIN(ts), MAX(ts)) / (COUNT(data) - 1) AS avg_distance,
COUNT(data)
FROM yourTable
GROUP BY DATE(ts);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/417187.html
標籤：