按任意时间间隔计算行数的最佳方法

Question

我的应用程序有一个带有时间戳事件的Events表。

我需要报告在每个最近的N时间间隔内的事件计数。对于不同的报告，间隔可以是"每周"或"每天"或"每小时"或"每15分钟间隔"。

例如，用户可以显示他们每周、每天、每小时或每25小时收到的订单数量。

1)我的偏好是动态地执行单个SQL查询(我使用Postgres)，按任意时间间隔分组。有办法做到吗?

2)一个简单但丑陋的蛮力方法是对按时间戳排序的开始/结束时间框架内的所有记录进行单个查询，然后有一个方法手动构建一个计数间隔。

3)另一种方法是为每个间隔向事件表添加单独的字段，并静态存储the_week the_day, the_hour和the_quarter_hour字段，因此我在创建记录时(一次)采取"命中"，而不是每次报告该字段。

如果需要的话，我可以修改模型和预存储间隔数据，那么这里的最佳实践是什么(尽管代价是表宽度增加一倍)?

Answer 1

幸运的是，你正在使用PostgreSQL。窗口函数 generate_series() 是您的朋友。

测试用例

给定以下测试表(您应该提供):

CREATE TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp '2018-05-01'
                     , timestamp '2018-05-08'
                     , interval '7 min') + random() * interval '7 min';

每7分钟发生一次事件(随机加0 ~ 7分钟)

基本解决方案

此查询对任意时间间隔内的事件进行计数。示例中的17分钟:

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;

查询从基表中检索最小和最大ts以覆盖整个时间范围。您可以使用任意的时间范围。

根据需要提供任意时间间隔

为每个时隙生成一行。如果在此时间间隔内没有发生任何事件，则计数为0。

确保正确处理上界和下界。看到:

• 使用BETWEEN时间戳的SQL查询的意外结果

窗口函数lead()有一个经常被忽略的特性:当不存在前导行时，它可以提供一个默认值。在示例中提供'infinity'。否则最后一个间隔将被截断，上限为NULL。

最小等效

上面的查询使用CTE、lead()和详细语法。优雅，也许更容易理解，但有点贵。下面是一个更短、更快、最小的版本:

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

<过去一周每隔15分钟>

示例

用to_char()格式化。

SELECT to_char(start_time, 'YYYY-MM-DD HH24:MI'), count(e.ts) AS events
FROM   generate_series(date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval '15 min'
GROUP  BY start_time
ORDER  BY start_time;

ORDER BY和GROUP BY仍然在底层时间戳值上，而不是在格式化字符串上。这样更快更可靠。

db<此处小提琴>

在时间框架内产生运行计数的相关答案:

• PostgreSQL:查询的行数'按分钟计算'