Say, I have 150 records spanned over 2 hours period, eg, 1am to 3pm. There are 60 records timestamped at the end of 1pm and 70 records timestamped at the beginning of the 2am.
I need to produce a report to indicate if the number of records exceeded 100 within 60 minutes, this 60 minutes period can be any 60 minutes between 1am to 2am, eg. 1:01 - 2:01, 1:13 - 2:13, 1:59-2:59 etc
id, created_at
...
17376889,2023-06-07 01:00:50
17376890,2023-06-07 01:00:50
17376891,2023-06-07 01:09:50
17376892,2023-06-07 01:09:50
17376901,2023-06-07 01:33:28
17376902,2023-06-07 01:33:07
17376905,2023-06-07 01:42:54
... 100+ records here
17376943,2023-06-07 02:20:57
17376944,2023-06-07 02:20:24
17376948,2023-06-07 02:20:50
17376952,2023-06-07 02:23:37
17376953,2023-06-07 02:23:37
17376954,2023-06-07 02:23:37
17376955,2023-06-07 02:23:37
17376956,2023-06-07 02:59:37
...
eg. in this case (1.30am - 2:30am) is it possible to get result by simply running SQL queries without involving coding? (Especially MySQL), something like this imaginary query
SELECT max(*) as c FROM records GROUP BY PERIOD(created_at, 3600) HAVING c > 100
If I slice by the hour to count like this answer, eg. 1am - 2am, 2am - 3am, I won't get any results. If I slice by the minute then I will need to aggregate by code I presume?
This is untested…
SELECT created_at ,
COUNT(*) OVER w AS cnt
FROM records
WINDOW w AS (
ORDER BY created_at
RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW);
Sources:
https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html
https://docs.sqlstream.com/glossary/sliding-window-or-rolling-wind/