如何在SQL中用IF-ELSE逻辑合并两种活动中断时长计算场景？

你需要计算活动实例的total_uninterrupted时长，存在两种中断场景：

• 场景1：当前活动实例i被其他实例ac中断，且ac早于i结束，中断时长为ac.end - ac.start
• 场景2：当前活动实例i被其他实例ac中断，且i早于ac结束，中断时长为i.end - ac.start

目标是将两种场景的计算合并到一个查询中，得到interruptions（总中断时长）和timeWithoutInterruptions（无中断时长）列，预期结果分别为[21,22,0]和[4,6,22]。

核心解决方案：用CASE WHEN合并场景判断 #

可以通过CASE WHEN语句在同一查询中区分两种场景，计算每个中断实例的贡献，再聚合得到总中断时长，最后推导无中断时长。假设你的活动实例表名为activity_instances，包含id、start、end字段，核心查询如下：

SELECT
    i.id,
    -- 计算总中断时长：聚合所有符合条件的中断实例贡献
    COALESCE(SUM(
        CASE
            -- 匹配场景1：ac在i的时间范围内启动且早于i结束
            WHEN ac.start BETWEEN i.start AND i.end AND ac.end <= i.end
                THEN (ac.end - ac.start)
            -- 匹配场景2：ac在i的时间范围内启动且晚于i结束
            WHEN ac.start BETWEEN i.start AND i.end AND ac.end >= i.end
                THEN (i.end - ac.start)
            ELSE 0
        END
    ), 0) AS interruptions,
    -- 计算无中断时长：总时长减去总中断时长
    (i.end - i.start) - COALESCE(SUM(
        CASE
            WHEN ac.start BETWEEN i.start AND i.end AND ac.end <= i.end
                THEN (ac.end - ac.start)
            WHEN ac.start BETWEEN i.start AND i.end AND ac.end >= i.end
                THEN (i.end - ac.start)
            ELSE 0
        END
    ), 0) AS timeWithoutInterruptions
FROM
    activity_instances i
LEFT JOIN
    activity_instances ac ON ac.id != i.id -- 关联当前实例外的其他实例
GROUP BY
    i.id, i.start, i.end;

逻辑说明 #

1. LEFT JOIN：保证无中断的活动实例（如预期结果第三行）不会被过滤，确保全量数据输出
2. CASE WHEN：精准匹配两种中断场景，分别计算对应中断时长，不符合场景的实例贡献为0
3. COALESCE：处理无中断实例的SUM结果为NULL的情况，将其转为0，避免计算报错
4. GROUP BY：按单个活动实例聚合，得到该实例的总中断时长和无中断时长

内容的提问来源于stack exchange，提问作者Adeyinka Mariam Abiola