Improve query to calculate number of rows matching 20 minute window for every row

Apad 0
Viewed 50
CREATE PROCEDURE windowPeriod (IN BEGIN_TIME DATETIME)
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE minuteCount INTEGER DEFAULT 0;
DECLARE END_TIME DATETIME;
DECLARE eachMinute CURSOR FOR 
    select  count(*) as C
        from  
        (
            SELECT  *
                from  DATA timestamp >= BEGIN_TIME
                  and  timestamp <= 
                    ( SELECT  addtime(BEGIN_TIME, "00:20:00") )
                group by  user,host,timestamp
        ) as X;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN eachMinute;
addMinute: LOOP
   FETCH eachMinute INTO minuteCount;
   IF finished = 1 THEN 
      LEAVE addMinute;
   END IF;
select CONCAT (BEGIN_TIME," ", minuteCount);
END LOOP addMinute;
CLOSE eachMinute;

I have a table DATA that contains several entries for a minute. For each row in the table, i want to calculate "how many rows within a 20 minute period starting that minute"? I write the above query, but this gets tremendously slow as the number of rows increases. Whats a better way to achieve this?

Sample entries in the table :

2022-09-10 23:22:05 Linux1
2022-09-10 23:22:05 Linux2
2022-09-10 23:26:04 Linux3
2022-09-10 23:26:04 Linux4
2022-09-10 23:33:04 Linux5
2022-09-10 23:33:04 Linux5
2022-09-10 23:35:04 Linux7

2022-09-10 23:48:04 Linux7
2022-09-10 23:52:05 Linux1
2022-09-10 23:55:05 Linux2
2022-09-10 23:56:04 Linux3
2022-09-10 23:57:04 Linux4

So if 23:22:05 is the first minute of the window, then there are 2 rows that are within the 20 minute window (Linux1 and Linux3 rows). If 23:35:04 is the first minute of the window, then there is just 1 row in the 20 minute window (Linux3 only).

mysql
1 Answers

SQL likes things to be done en masse, not in loops.

This does most of the work, and does it for the entire table, without looping:

SELECT  FLOOR(TO_SECONDS(begin_time) / (20*60)) AS chunk
        COUNT(*) AS ct
    FROM tbl
    GROUP BY chunk;

The idea is to convert the time to a number, then convert that to integers... 0 for the first 20 minutes, 1 for the next 20, etc. That way, all items in the first 20 minutes will be lumped together and counted.

Since you may want the 'start' of each chunk showing:

SELECT  FROM_SECONDS(chunk * 20*60) AS start_at.
        ct
    FROM ((the above query)) AS x
    ORDER BY start_at;

It gets messier if you need to show "0" for intervals with no action.

Rick James0
Related