MySQL 5.6环境下运动检测时间序列的活动阶段统计查询需求

嘿，针对你在MySQL 5.6环境下的这个运动检测活动阶段统计需求，我整理了一个适配该版本的SQL方案——毕竟5.6确实没有后续版本的窗口函数，咱们用用户变量就能实现分组逻辑：

核心思路 #

我们的目标是把**连续的活动记录（两条记录间隔小于30分钟）**归为同一个活动阶段，当两条记录间隔≥30分钟时，就开启一个新的阶段。具体步骤是：

• 用用户变量记录上一条记录的时间，计算当前记录与前一条的时间差
• 根据时间差判断是否需要创建新的活动分组ID
• 按分组ID聚合，得到每个阶段的起止时间
• 最后给分组重新生成连续的序号

完整SQL查询 #

假设你的表名为motion_detection，请替换成实际表名：

SELECT
    @row_num := @row_num + 1 AS Id,
    MIN(`Date Time`) AS `Activity starts`,
    MAX(`Date Time`) AS `Activity ends`
FROM (
    SELECT
        `Date Time`,
        @group_id := IF(TIMESTAMPDIFF(MINUTE, @prev_time, `Date Time`) >= 30, @group_id + 1, @group_id) AS group_id,
        @prev_time := `Date Time` AS prev_time
    FROM
        motion_detection,
        (SELECT @prev_time := NULL, @group_id := 0) AS init_vars
    ORDER BY
        `Date Time`
) AS grouped_data
GROUP BY
    group_id
ORDER BY
    `Activity starts`;

代码细节解释 #

1. 变量初始化：子查询init_vars初始化两个用户变量：@prev_time用来存储上一条记录的时间，@group_id用来标记当前活动阶段的分组ID
2. 分组判断逻辑：通过TIMESTAMPDIFF(MINUTE, @prev_time, Date Time)计算当前记录和前一条的时间差（分钟数），如果差≥30，就把@group_id加1（开启新阶段），否则保持原分组ID
3. 聚合统计：外层查询按group_id分组，取每组的最小时间作为活动开始时间，最大时间作为活动结束时间
4. 生成结果序号：用@row_num变量给每个活动阶段生成连续的序号，完全匹配你期望的结果格式

测试验证 #

用你提供的示例数据测试，这个查询会输出：

Id | Activity starts       | Activity ends
---|-----------------------|-----------------------
1  | 2018-01-01 15:00:01   | 2018-01-01 15:00:06
2  | 2018-01-01 17:01:06   | 2018-01-01 17:02:09
3  | 2018-01-10 19:40:06   | 2018-01-10 19:41:06

完全符合你的需求。

注意事项 #

• 确保Date Time字段是DATETIME类型，这样TIMESTAMPDIFF才能正确计算时间差
• 如果需要筛选特定时间段的数据，可以在子查询的FROM后面添加WHERE子句
• MySQL 5.6中用户变量的赋值顺序是可靠的，但务必保证子查询里的ORDER BY存在，确保记录按时间顺序处理

内容的提问来源于stack exchange，提问作者hallo02