时间戳

首页 > 解决方案 > MSQL按间隔分组？

mysql - MSQL按间隔分组？

问题描述

我需要编写一个 MYSQL 选择查询来执行以下操作，可能使用 Interval，但不知道如何实现它。

检索相邻行的日期时间值在 5 分钟内的结果组。在下面的简化示例中，我需要检索一组中的 Bob、Ted 和 Fred，以及另一组中的 Andy、Mike 和 Bert。

不知道会有多少组。我需要能够确定每个组何时开始、何时结束并收集其间的数据。

拥有这些组后，我需要能够访问组内每一行的每个值，以便执行进一步的计算

**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

标签： mysql

解决方案

在 MySql 5.x 中，您可以使用变量来计算排名。

例子： 

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`;

您将在此处找到有关 SQL Fiddle 的测试

结果： 

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

在 MySql 8.x 中，您还可以使用窗口函数LAG来获取以前的 DateTime。

推荐阅读