如何在 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中文网其他相关文章！