SQL Server 统计跨天干预任务每日总分钟数的最优方案问询

核心实现思路 #

通过递归CTE生成每个干预任务覆盖的所有日期序列，再逐天计算当日有效时长，最后按日期聚合求和，可同时兼容单日任务、跨1天、跨多天的全场景需求。

具体实现步骤 #

前置说明 #

假设你的任务存储表名为InterventionTasks，包含核心字段如下：

• 任务唯一标识 TaskID
• 干预开始时间 InterventionStart（datetime 类型）
• 干预结束时间 InterventionEnd（datetime 类型）

完整可运行SQL代码 #

WITH DateSeries AS (
    -- 递归锚点：取每个任务的起始日期
    SELECT 
        TaskID,
        InterventionStart,
        InterventionEnd,
        CAST(InterventionStart AS DATE) AS CurrentDate
    FROM InterventionTasks
    UNION ALL
    -- 递归生成任务覆盖的所有后续日期
    SELECT 
        TaskID,
        InterventionStart,
        InterventionEnd,
        DATEADD(DAY, 1, CurrentDate) AS CurrentDate
    FROM DateSeries
    WHERE DATEADD(DAY, 1, CurrentDate) <= CAST(InterventionEnd AS DATE)
),
DailyDuration AS (
    -- 逐天计算当日有效时长（示例单位为分钟，可按需调整）
    SELECT 
        CurrentDate,
        TaskID,
        DATEDIFF(MINUTE,
            -- 当日统计起始时间取任务开始时间和当日0点的较大值
            CASE WHEN InterventionStart >= CAST(CurrentDate AS DATETIME) THEN InterventionStart ELSE CAST(CurrentDate AS DATETIME) END,
            -- 当日统计结束时间取任务结束时间和次日0点的较小值
            CASE WHEN InterventionEnd <= DATEADD(DAY, 1, CAST(CurrentDate AS DATETIME)) THEN InterventionEnd ELSE DATEADD(DAY, 1, CAST(CurrentDate AS DATETIME)) END
        ) AS DurationMin
    FROM DateSeries
)
-- 按日期聚合得到每日总干预时长
SELECT 
    CurrentDate AS 统计日期,
    SUM(DurationMin) AS 当日总干预时长_分钟,
    SUM(DurationMin)/60.0 AS 当日总干预时长_小时
FROM DailyDuration
GROUP BY CurrentDate
ORDER BY CurrentDate
OPTION (MAXRECURSION 0); -- 取消默认100层递归限制，兼容跨超过100天的超长任务

方案优势 #

• 全场景兼容：不管是单日内完成的任务，还是跨1天、跨N天的任务都能正确拆分统计
• 计算无偏差：边界逻辑覆盖了任务开始/结束时间落在任意时刻的场景，不会出现时长多算/漏算的问题
• 性能可控：递归CTE的开销远低于关联日历表的方案，数据量较大时提前建立InterventionStart和InterventionEnd的联合索引即可进一步优化查询速度
• 灵活度高：修改DATEDIFF的第一个参数即可直接输出秒、小时等不同粒度的时长单位，适配不同统计需求

扩展适配：如果业务中存在未结束的干预任务（即InterventionEnd为NULL），可在锚点查询中新增判断，将InterventionEnd替换为当前时间GETDATE()即可正常统计。

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