CODEWITHSUNDEEP
mysql
Simon
Simon

Reputation: 394

MSQL group by interval?

I need to write a MYSQL select query to do the following, possibly using Interval, but am not sure how to achieve it.

To retrieve groups of results where the date time value of neighbouring rows is within 5 minutes. In the below simplified example I need to retrieve Bob, Ted and Fred in one group and Andy, Mike and Bert in another.

I don't know how many groups there will be. I need to be able to establish when each group starts, when it ends and collect the data in between.

Once I have these groups I need to be able to access each value from every row within a group in order to perform further calculations 

**DB Table**

DateTime          Value
2018-10-17 12:50  Bob
2018-10-17 12:55  Ted
2018-10-17 13:00  Fred
2018-10-17 15:00  Andy
2018-10-17 15:05  Mike
2018-10-17 15:10  Bert

Upvotes: 1

Views: 84

Answers (1)

LukStorms
LukStorms

Reputation: 29647

In MySql 5.x you can calculate a rank by using variables.

Example: 

SELECT `DateTime`, `Value`,
CASE
WHEN @prev_dt >= `DateTime` - INTERVAL 5 MINUTE AND @prev_dt := `DateTime` THEN @rank
WHEN @prev_dt := `DateTime` THEN @rank := @rank + 1 -- this WHEN criteria is always true
END AS `Rank`
FROM YourTable t
CROSS JOIN (SELECT @prev_dt := NULL, @rank := 0) vars
ORDER BY `DateTime`;

You'll find a test on SQL Fiddle here

Result: 

DateTime             Value Rank
-------------------- ---   ----
2018-10-17T12:50:00Z Bob      1 
2018-10-17T12:55:00Z Ted      1 
2018-10-17T13:00:00Z Fred     1 
2018-10-17T15:00:00Z Andy     2 
2018-10-17T15:05:00Z Mike     2 
2018-10-17T15:10:00Z Bert     2

In MySql 8.x you could also use the window function LAG to get the previous DateTime.

Upvotes: 2

Related Questions