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