MySQL: 相邻1分钟时间间隔的数据分组

需求

把data_time相邻1分钟的分为一组，输出组数及该组对应的数据条数。

输入：

输出：

解决

WITH ranked_data AS (
  SELECT 
    vin,
    data_time,
    -- 计算当前行与上一行的时间差（秒）
    CASE 
      WHEN LAG(data_time) OVER (PARTITION BY vin ORDER BY data_time) IS NULL THEN NULL
      ELSE timestampdiff(minute, LAG(data_time) OVER (PARTITION BY vin ORDER BY data_time), data_time)
    END AS time_diff
  FROM t
), group_markers AS (
  SELECT 
    vin,
    data_time,
    -- 当时间差>1分钟或没有前一行时，标记为新组的开始
    SUM(CASE WHEN time_diff IS NULL OR time_diff > 1 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY vin ORDER BY data_time) AS group_id
  FROM ranked_data
)

SELECT 
  group_id,
  COUNT(*) AS group_cnt
FROM group_markers
GROUP BY group_id
ORDER BY group_id;