Get max value of binned time-interval-CodePudding

I have a 'requests' table with a 'time_request' column which has a timestamp for each request. I want to know the maximum amount of requests that i had in a single minute.

So im guessing i need to somehow 'group by' a 1m time interval, and then do some sort of MAX(COUNT(request_id))? Although nested aggregations are not allowed.

Will appreciate any help.

Table example:

     request_id   |    time_request
------------------ ---------------------
 ab1              | 2021-03-29 16:20:05
 ab2              | 2021-03-29 16:20:20
 bc3              | 2021-03-31 20:34:07
 fw3              | 2021-03-31 20:38:53
 fe4              | 2021-03-31 20:39:53

Expected result: 2 (There were a maximum of 2 requests in a single minute)

Thanks!

CodePudding user response：

You may use window function count and specify logical interval of one minute as the window boundary. It will calculate the count for each row and will account all the rows that are within one minute before.

Code for Postgres is below:

with a as (
select
  id
  , cast(ts as timestamp) as ts
from(values
 ('ab1', '2021-03-29 16:20:05'),
 ('ab2', '2021-03-29 16:20:20'),
 ('bc3', '2021-03-31 20:34:07'),
 ('fw3', '2021-03-31 20:38:53'),
 ('fe4', '2021-03-31 20:39:53')
) as t(id, ts)
)
, count_per_interval as (
select
  a.*
  , count(id) over (
    order by ts asc
    range between
      interval '1' minute preceding
      and current row
  ) as cnt_per_min
from a
)
select max(cnt_per_min)
from count_per_interval

| max |
| --: |
|   2 |

db<>fiddle here