MySQL中基于INCLUDE字段划分连续有效组并计算时间跨度的SQL查询实现

要解决这个问题，核心是区分开不连续的include='yes'行组——普通的GROUP BY include会把所有yes行归为一组，显然无法满足“遇到no就重置分组”的需求。我们可以借助MySQL的窗口函数来生成每个yes行的分组标识，再进行分组计算。

完整SQL查询 #

SELECT 
    ROUND(MAX(`end`) - MIN(`start`), 3) AS time_span
FROM (
    SELECT 
        `start`, `end`, include,
        -- 计算截至当前行的`include='no'`累计数量，作为分组ID
        SUM(CASE WHEN include = 'no' THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM mytable
) AS grouped_data
WHERE include = 'yes'
GROUP BY group_id
ORDER BY MIN(`start`);

逻辑解释 #

1. 生成分组ID：
   子查询里的SUM(CASE...) OVER (ORDER BY id)是关键——它会按id顺序遍历每行，累计统计前面所有include='no'的行数。这样：

   • 第一组yes行（id=1）前面没有no，group_id为0；
   • 遇到id=2的no行后，后面的yes行（id=3、4、5）的group_id变为1；
   • 再遇到id=6的no行，后续yes行（id=7、8、9）的group_id变为2；
     以此类推，连续的yes行会被分配相同的group_id，实现分组重置。

2. 计算时间跨度：
   外层查询过滤掉include='no'的行，按group_id分组后，取每组的最小start（组内第一行的开始时间）和最大end（组内最后一行的结束时间），两者的差值就是该组的时间跨度。用ROUND(...,3)是为了和原数据的小数位数对齐。

3. 排序结果：
   ORDER BY MIN(start)保证结果按时间顺序输出，和预期的分组顺序一致。

验证结果 #

执行后会得到：

time_span
---------
2.000
7.499
18.800

注：你预期里的7.3应该是笔误，根据实际数据计算，第三行到第五行的跨度是12.5 - 5.001 = 7.499，第三组的34.5 - 15.7 = 18.8和预期完全一致。

原SQL的问题 #

你之前的GROUP BY include会把所有include='yes'的行合并为一个组，自然只能得到单个结果，无法区分不连续的yes行块。

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