SQL按系统分组统计加载起止时间及各子系统耗时求助

数据表存储多系统下各子系统对应文件的加载日志，共包含4个字段：System、Subsystem & Filename、File Load Start Time、File Load End Time，样例数据如下：

按System维度分组汇总，返回以下指标：

• 每个系统的整体最早加载开始时间
• 每个系统的整体最晚加载结束时间
• A1、A2、A3三个子系统各自的实际加载耗时：同一子系统下任务加载时间段的重叠部分不重复累加，仅统计不重叠的有效加载总时长，格式为时分秒

期望返回结果如下：

最初尝试在SELECT子句中搭配CASE语句与聚合函数编写SQL，仅按System字段分组，参考写法如下：

SELECT System, 
min(StartTime) as 'File Load Start Time',
max(EndTime) as 'File Load End Time', 
CASE WHEN SubSystem LIKE 'A1%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A1 Time Taken',
CASE WHEN SubSystem LIKE 'A2%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A2 Time Taken',
CASE WHEN SubSystem LIKE 'A3%' THEN SUM(DATEDIFF(s, min(StartTime), max(EndTime))) Else 0 END AS 'A3 Time Taken'
FROM TABLE GROUP BY SYSTEM

该写法存在两个核心问题：

1. SELECT中未聚合的Subsystem字段按SQL语法要求必须加入GROUP BY，无法实现仅按System单字段分组的需求
2. 直接对单条记录的时长做SUM会重复计算并行任务的重叠时长，不符合去重统计有效时长的逻辑

解决重叠时间段求和的核心思路是先对每个(系统,子系统)分组下的所有时间段做区间合并，再对合并后的无重叠区间计算总时长，最后关联系统维度的整体开始/结束时间即可。以下代码兼容支持窗口函数的主流SQL引擎（以SQL Server为例，MySQL 8.0+可将时长格式化部分替换为SEC_TO_TIME()函数）：

WITH base_data AS (
    -- 提取子系统编码，标准化基础数据
    SELECT 
        System,
        LEFT(`Subsystem & Filename`, 2) AS sub_sys,
        `File Load Start Time` AS start_t,
        `File Load End Time` AS end_t
    FROM file_load_log -- 替换为实际表名
),
sys_overall AS (
    -- 计算每个系统的整体起止时间
    SELECT
        System,
        MIN(start_t) AS overall_start,
        MAX(end_t) AS overall_end
    FROM base_data
    GROUP BY System
),
interval_merge_pre AS (
    -- 排序后标记每个不重叠区间的起始点
    SELECT
        System,
        sub_sys,
        start_t,
        end_t,
        CASE WHEN start_t > MAX(end_t) OVER (
            PARTITION BY System, sub_sys 
            ORDER BY start_t 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) THEN 1 ELSE 0 END AS new_interval_flag
    FROM base_data
),
interval_merge_group AS (
    -- 累加标记生成重叠区间的统一分组ID
    SELECT
        System,
        sub_sys,
        start_t,
        end_t,
        SUM(new_interval_flag) OVER (
            PARTITION BY System, sub_sys 
            ORDER BY start_t
        ) AS interval_gid
    FROM interval_merge_pre
),
sub_sys_duration AS (
    -- 合并同组重叠区间，计算每个子系统的总有效时长（秒）
    SELECT
        System,
        sub_sys,
        SUM(DATEDIFF(second, MIN(start_t), MAX(end_t))) AS total_seconds
    FROM interval_merge_group
    GROUP BY System, sub_sys, interval_gid
)
-- 最终聚合关联，格式化输出
SELECT
    so.System,
    so.overall_start AS `Overall System Load Start Time`,
    so.overall_end AS `Overall System Load End Time`,
    CONVERT(varchar, DATEADD(second, SUM(CASE WHEN ssd.sub_sys='A1' THEN ssd.total_seconds ELSE 0 END), 0), 108) AS `A1 Time Taken`,
    CONVERT(varchar, DATEADD(second, SUM(CASE WHEN ssd.sub_sys='A2' THEN ssd.total_seconds ELSE 0 END), 0), 108) AS `A2 Time Taken`,
    CONVERT(varchar, DATEADD(second, SUM(CASE WHEN ssd.sub_sys='A3' THEN ssd.total_seconds ELSE 0 END), 0), 108) AS `A3 Time Taken`
FROM sys_overall so
LEFT JOIN sub_sys_duration ssd ON so.System = ssd.System
GROUP BY so.System, so.overall_start, so.overall_end

逻辑说明 #

• 通过窗口函数识别同(系统,子系统)下的重叠/连续时间段，归为同一组后合并计算时长，从根源避免重叠部分重复计算
• 系统维度的整体起止时间单独计算，和子系统时长计算逻辑解耦，避免GROUP BY字段不符合语法要求的问题
• 最后通过条件聚合将三个子系统的时长转为独立列，实现单系统一行的输出效果，完全匹配预期结果

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