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

Question

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

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

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

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

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

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

这里的最佳实践是什么，因为我可以根据需要修改模型和预存储间隔数据(尽管代价是将表格宽度加倍)？

Answer 1

幸运的是，您使用的是 PostgreSQL。窗口函数generate_series()是你的 friend 。

测试用例

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

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。

一定要正确处理上限和下限。见:

• Unexpected results from SQL query with BETWEEN timestamps

窗口函数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 <b>to_char(start_time, 'YYYY-MM-DD HH24:MI')</b>, count(e.ts) AS events
FROM   generate_series(<b>date_trunc('day', localtimestamp - interval '7 days')
                     , localtimestamp
                     , interval '15 min'</b>) 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<> fiddle here

在时间范围内生成运行计数的相关答案:

• PostgreSQL: running count of rows for a query 'by minute'