SQL按engine_id与交替engine_is_on分组查询每组首尾id

核心思路 #

这是SQL中经典的「间隙与岛屿（Gaps and Islands）」问题，通过窗口函数标记分组即可实现需求：

• 对每个引擎的所有记录按id升序排序，累计统计该引擎截止当前记录出现engine_is_on=0的次数，这个累计值可作为同引擎下连续有效（engine_is_on=1）记录的分组标识
• 过滤掉作为拆分边界的engine_is_on=0记录后，按engine_id和分组标识分组，取每组最大id为start_id、最小id为end_id即可得到目标结果

MySQL 8.0+/支持窗口函数的实现 #

WITH marked_data AS (
    SELECT 
        *,
        -- 按引擎分区、id升序排序，累计统计0出现的次数作为分组号
        SUM(CASE WHEN engine_is_on = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY engine_id ORDER BY id ASC) AS group_num
    FROM mytable
)
SELECT 
    engine_id,
    MAX(id) AS start_id,
    MIN(id) AS end_id
FROM marked_data
WHERE engine_is_on = 1
GROUP BY engine_id, group_num
ORDER BY start_id DESC;

结果验证 #

执行上述SQL得到的输出和预期完全一致：

低版本MySQL（无窗口函数）兼容方案 #

SELECT 
    engine_id,
    MAX(id) AS start_id,
    MIN(id) AS end_id
FROM (
    SELECT 
        *,
        @group_num := IF(@current_engine = engine_id, 
                        IF(engine_is_on = 0, @group_num + 1, @group_num),
                        IF(@current_engine := engine_id, 0, 0)
                       ) AS group_num
    FROM mytable
    CROSS JOIN (SELECT @group_num := 0, @current_engine := NULL) AS vars
    ORDER BY engine_id, id ASC
) AS marked_data
WHERE engine_is_on = 1
GROUP BY engine_id, group_num
ORDER BY start_id DESC;

内容的提问来源于stack exchange，提问作者Definitely not Rafal