如何在 PostgreSQL 中有效率地統計動態時間間隔內的事件？

如何按時間間隔高效地計數事件

簡介

應用程式通常需要從基於事件的資料中提取見解，例如對特定範圍內的事件進行計數時間間隔。當間隔大小動態變化時，此任務會帶來挑戰。

問題陳述

包含時間戳事件的資料表需要動態 SQL 查詢，以任意時間間隔將事件分組，包括每週、每天、每小時，甚至 15 分鐘間隔。

解決方案

Postgres 14 或較新

對於 Postgres 版本 14 及以上版本，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;

要取得自特定日期以來的所有時間段，請按如下方式調整查詢：

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 或更早

對於較舊的Postgres版本，可以使用以下查詢使用：

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

此查詢為每個時間段提供一行，沒有事件的時間段計數為 0。

最佳化

預儲存間隔資料事件表可以提高效能。然而，這種方法使表大小加倍。如果權衡可以接受，它可以顯著提高速度。

其他注意事項

• 正確處理時間上限和下限。
• 使用 to_char () 函數格式化時間戳以供顯示。
• 利用generate_series() 函數產生完整的一組時間段。

以上是如何在 PostgreSQL 中有效率地統計動態時間間隔內的事件？的詳細內容。更多資訊請關注PHP中文網其他相關文章！