如何在SQL中按時間間隔高效統計行數？

按時間間隔計數行：揭示最佳方法

應用程式經常處理帶有時間戳的事件，需要根據特定時間分析數據間隔。為了有效計算每個時間間隔內的事件數量，開發人員可以考慮各種方法。

1.任意時間間隔的動態 SQL 查詢

使用 SQL 查詢以任意時間間隔對事件進行分組是一種高效且首選的方法。然而，Postgres 本身並不支援此功能。

2.時間排序的暴力方法

查詢開始/結束時間範圍內的所有記錄並按所需的時間間隔手動統計它們是一種簡單但麻煩的方法。它需要按時間戳對結果進行排序，並且缺乏動態間隔自訂。

3.預先儲存間隔資料

在事件表中為每個間隔維護單獨的欄位可以避免執行時間計算並簡化報表。然而，這種方法會使表大小加倍，這可能不是最佳的。

最佳實踐

最佳實踐取決於應用程式的具體要求和資料大小：

• 對於大量間隔或任意間隔自訂，動態SQL 查詢是適當的。雖然 Postgres 缺乏內建支持，但存在使用 Lead() 等視窗函數的解決方法。
• 對於少量固定間隔（例如，天或週）， 預先儲存間隔資料可以簡化查詢執行，但會增加表格大小。
• 對於即時解決方案或表格成長是一個問題關注，帶有時間排序的暴力方法可能更合適。

帶有date_bin() 函數的Postgres 14

對於Postgres 14 及更新版本，date_bin() 函數允許將時間間隔戳為間隔時間。這簡化了查詢並提供高效的結果。

範例：對每 15 分鐘間隔的事件進行計數

對給定時間範圍內每 15 分鐘間隔的事件進行計數使用date_bin()函數：

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

注意：

對於 Postgres 13 及更早版本，可以使用使用generate_series() 和視窗函數的替代方法。

以上是如何在SQL中按時間間隔高效統計行數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！