如何计算去重重叠时间范围总和，编写SQL UDF返回时间重叠占比

需求概述 #

需要创建一个用户定义函数（UDF），接收指定日期范围的两个参数，查询dbo.Shifts表，返回表内所有班次与输入时间范围的唯一重叠时长，占输入范围总时长的百分比。

依赖表结构 #

CREATE TABLE dbo.Shifts (
    Id INT IDENTITY(1,1) NOT NULL,
    StartTime DATETIME2(0) NOT NULL,
    EndTime DATETIME2(0) NOT NULL
    CONSTRAINT [PK_Shifts] PRIMARY KEY CLUSTERED ([Id] ASC)
)

实现要求 #

• 入参为两个时间参数：@Start（查询范围起始时间）、@End（查询范围结束时间）
• 所有参与计算的时间（输入参数、表中的StartTime和EndTime）需先四舍五入到最近的15分钟刻度，再进行后续计算
• 多个班次的重叠部分需要去重统计，避免重复计算时长
• 返回值为百分比数值，保留两位小数

完整UDF代码 #

CREATE OR ALTER FUNCTION dbo.CalculateShiftOverlapPercentage
(
    @Start DATETIME2(0),
    @End DATETIME2(0)
)
RETURNS DECIMAL(5,2)
AS
BEGIN
    -- 边界处理：如果起始时间大于等于结束时间，直接返回0
    IF @Start >= @End
        RETURN 0.00;

    -- 1. 把输入时间四舍五入到15分钟刻度
    DECLARE 
        @RoundedStart DATETIME2(0) = DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '1900-01-01', @Start) / 15.0, 0) * 15, '1900-01-01'),
        @RoundedEnd DATETIME2(0) = DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '1900-01-01', @End) / 15.0, 0) * 15, '1900-01-01'),
        @TotalRangeMinutes INT = DATEDIFF(MINUTE, @RoundedStart, @RoundedEnd);

    -- 边界处理：四舍五入后范围长度为0，返回0
    IF @TotalRangeMinutes <= 0
        RETURN 0.00;

    DECLARE @OverlapMinutes INT;

    -- 2. 处理班次时间，合并重叠区间后统计总重叠时长
    WITH RoundedShifts AS (
        -- 先把所有班次时间四舍五入到15分钟，预过滤无重叠班次减少计算量
        SELECT
            DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '1900-01-01', StartTime) / 15.0, 0) * 15, '1900-01-01') AS RoundedShiftStart,
            DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, '1900-01-01', EndTime) / 15.0, 0) * 15, '1900-01-01') AS RoundedShiftEnd
        FROM dbo.Shifts
        WHERE EndTime >= @Start AND StartTime <= @End
    ),
    ShiftBoundaries AS (
        -- 标记新的不重叠区间起始点
        SELECT
            RoundedShiftStart,
            RoundedShiftEnd,
            SUM(IsNewGroup) OVER (ORDER BY RoundedShiftStart ROWS UNBOUNDED PRECEDING) AS GroupId
        FROM (
            SELECT
                RoundedShiftStart,
                RoundedShiftEnd,
                CASE WHEN LAG(RoundedShiftEnd) OVER (ORDER BY RoundedShiftStart) >= RoundedShiftStart THEN 0 ELSE 1 END AS IsNewGroup
            FROM RoundedShifts
        ) t
    ),
    MergedShifts AS (
        -- 合并重叠/相邻的班次区间
        SELECT
            MIN(RoundedShiftStart) AS MergedStart,
            MAX(RoundedShiftEnd) AS MergedEnd
        FROM ShiftBoundaries
        GROUP BY GroupId
    ),
    OverlapCalculation AS (
        -- 计算每个合并区间和输入范围的实际重叠时长
        SELECT
            DATEDIFF(
                MINUTE,
                CASE WHEN MergedStart > @RoundedStart THEN MergedStart ELSE @RoundedStart END,
                CASE WHEN MergedEnd < @RoundedEnd THEN MergedEnd ELSE @RoundedEnd END
            ) AS SingleOverlap
        FROM MergedShifts
        WHERE MergedEnd > @RoundedStart AND MergedStart < @RoundedEnd
    )
    -- 汇总总重叠时长计算百分比
    SELECT @OverlapMinutes = ISNULL(SUM(SingleOverlap), 0)
    FROM OverlapCalculation;

    RETURN CAST((@OverlapMinutes * 100.0) / @TotalRangeMinutes AS DECIMAL(5,2));
END

调用示例 #

-- 计算2024-05-01 08:00到2024-05-01 18:00的班次重叠占比
SELECT dbo.CalculateShiftOverlapPercentage('2024-05-01 08:00:00', '2024-05-01 18:00:00') AS OverlapPercentage;

如果输入范围内总时长为10小时，去重后的重叠总时长为5.8小时，上述调用将返回58.00的结果，和示例预期一致。

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