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 certa

Solution 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.

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
LEFTJOIN tbl t1 ON t1.time_created BETWEEN t0.time_created -interval'3 sec'AND t0.time_created
GROUPBY1ORDERBY1;

db<>fiddle here

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.

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.)

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
LEFTJOINLATERAL (
   SELECTcount(*) AS count_within_3_sec
   FROM   tbl t1
   WHERE  t1.time_created BETWEEN t0.time_created -interval'3 sec'AND t0.time_created
   ) t1 ONtrueORDERBY1;

Related:

• Rolling sum / count / average over date interval

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

• Window Functions or Common Table Expressions: count previous rows within range
• Alternatives to broken PL/ruby: convert a warehouse journal table
• GROUP BY and aggregate sequential numeric values