Question 270971 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL how to get max count of group by time window

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?

Tags:

3

Answers


  1. 0

    There might be a way to do this with window functions, but you can do it with a self-join.

    SELECT r1.id, r1.created_at, COUNT(*) AS count
FROM records AS r1
JOIN records AS r2 ON r2.created_at BETWEEN r1.created_at AND DATE_ADD(r1.created_at, INTERVAL 1 HOUR)
GROUP BY r1.id
HAVING count > 100
    Login or Signup to reply.
  2. 0

    Looks like you need in something close to:

    WITH cte AS (
    SELECT *, 
           COUNT(*) OVER (ORDER BY created_at 
                          RANGE BETWEEN CURRENT ROW 
                                    AND INTERVAL 1 HOUR FOLLOWING) cnt
    FROM tablename 
    )
SELECT *
FROM cte
ORDER BY cnt DESC LIMIT 1;
    Login or Signup to reply.
  3. 0

    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/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search