如何在 PostgreSQL 中按时间间隔高效统计事件？

按时间间隔有效计数事件

问题陈述

考虑一个包含时间戳事件的事件表。目标是报告特定时间间隔内发生的事件计数，例如天、小时或用户定义的时间间隔。目标是确定通过 Postgres 中的单个 SQL 查询动态生成此信息的最有效方法。

解决方案

使用 Date_bin() (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;

生成完整的行集（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中文网其他相关文章！