如何在 PostgreSQL 中按時間間隔高效統計事件？

按時間間隔有效計數事件

問題陳述

考慮一個包含時間戳事件的事件表。目標是報告特定時間間隔內發生的事件計數，例如天、小時或使用者定義的時間間隔。目標是確定透過 Postgres 中的單一 SQL 查詢動態產生此資訊的最有效方法。

解決方案

使用Date_bin() (Postgres 14 或更新版本）

Postgres 14 引入了'date )'函數，為這個問題提供了一個簡單的解決方案：

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 或更早版本）

對於更早的版本Postgres 版本中，以下查詢產生完整的時隙集並執行LEFT JOIN來對每個時隙內的事件進行計數間隔：

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;

注意事項

• 「generate_series()」需要Postgres 9.6 或更高版本。
• 對於大型資料集，在表中建立單獨的「間隔」欄位並預先儲存時間間隔值可以改善效能，但需要額外的儲存空間。
• 指定時間間隔時，請使用適當的 Postgres 語法，例如 'interval '17 min''。
• 可以使用 'BETWEEN' 運算子過濾特定時間範圍內的事件，但確保正確處理上限和下限。
• 使用 'to_char()' 格式化start_time 欄位用於顯示目的，而不影響 ORDER BY 操作。

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