具有时间间隔的行的滚动计数 [英] Rolling count of rows withing time interval

问题描述

为了进行分析，我需要根据单个表的创建时间来汇总它们.基本上，我想知道在当前订单之前的一定时间内创建的订单数量.似乎找不到解决方案.

For an analysis I need to aggregate the rows of a single table depending on their creation time. Basically, I want to know the count of orders that have been created within a certain period of time before the current order. Can't seem to find the solution to this.

表结构:

<身体>

order_id	time_created
1	00:00
2	00:01
3	00:03
4	00:05
5	00:10

预期结果:

<身体>

order_id	在3秒内计数
1	1
2	2
3	3
4	2
5	1

推荐答案

声音就像

Sounds like an application for window functions. But, sadly, that's not the case. Window frames can only be based on row counts, not on actual column values.

使用 LEFT JOIN 进行简单查询即可完成此工作:

A simple query with LEFT JOIN can do the job:

SELECT t0.order_id
     , count(t1.time_created) AS count_within_3_sec
FROM   tbl      t0
LEFT   JOIN tbl t1 ON t1.time_created BETWEEN t0.time_created - interval '3 sec'
                                          AND t0.time_created
GROUP  BY 1
ORDER  BY 1;

db<>小提琴此处

不能像您的最小演示中那样在 time 上使用，因为它不会环绕.我认为假设 timestamp 或 timestamptz 是合理的.

Does not work with time like in your minimal demo, as that does not wrap around. I suppose it's reasonable to assume timestamp or timestamptz.

由于您在计数中包括了每一行，所以 INNER JOIN 也将起作用.(面对可能的NULL值，仍然更可靠.)

Since you include each row itself in the count, an INNER JOIN would work, too. (LEFT JOIN is still more reliable in the face of possible NULL values.)

或使用 LATERAL 子查询，您无需在外部查询级别进行汇总:

Or use a LATERAL subquery and you don't need to aggregate on the outer query level:

SELECT t0.order_id
     , t1.count_within_3_sec
FROM   tbl t0
LEFT   JOIN LATERAL (
   SELECT count(*) AS count_within_3_sec
   FROM   tbl t1
   WHERE  t1.time_created BETWEEN t0.time_created - interval '3 sec'
                              AND t0.time_created
   ) t1 ON true
ORDER  BY 1;

相关:

• 滚动总和/计数/日期间隔内的平均值

对于大型表和时间范围内的许多行，遍历表一次的过程解决方案将更好.喜欢:

For big tables and many rows in the time frame, a procedural solution that walks through the table once will perform better. Like:

• 窗口函数或常用表格表达式:计算范围内的前几行
• 替代已损坏的PL/红宝石:转换仓库日记帐表
• GROUP BY和合计连续数值

这篇关于具有时间间隔的行的滚动计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！