具有时间间隔的行的滚动计数 [英] 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:
这篇关于具有时间间隔的行的滚动计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!