分组多日期区间年度前100天累计统计（重叠日期计数修复）

背景与需求 #

这是此前日期区间年度天数统计问题的延续，当前业务需求升级：

• 按Group字段分组，统计每组内多个日期区间的年度前100天，年度起始日为每组首个区间的StartDate
• 需保留每个原区间对应的Location和Amount字段
• 当前痛点：部分区间存在单日重叠（前一区间EndDate与后一区间StartDate相同），导致重复计数，最终累计天数超过100的阈值

问题根源 #

原逻辑未处理日期重叠场景，将同一天重复计入累计计数。比如2021-02-01同时属于前一个区间的结束日和后一个区间的开始日，被两次统计，导致累计值错误超标。

修复后的SQL代码 #

WITH GroupYearStart AS (
    -- 获取每组的年度起始日（取该组首个区间的StartDate）
    SELECT 
        [Group],
        MIN(StartDate) AS YearStart
    FROM YourTable  -- 替换为你的实际表名
    GROUP BY [Group]
),
-- 标记每组内连续/重叠的区间组，用于后续合并
MergedIntervalGroups AS (
    SELECT 
        t.[Group],
        t.StartDate,
        t.EndDate,
        t.Location,
        t.Amount,
        gys.YearStart,
        -- 若当前区间与前一区间重叠/连续，归为同一组
        SUM(CASE WHEN LAG(t.EndDate) OVER (PARTITION BY t.[Group] ORDER BY t.StartDate) >= DATEADD(DAY, -1, t.StartDate) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY t.[Group] ORDER BY t.StartDate) AS IntervalGroupId
    FROM YourTable t  -- 替换为你的实际表名
    JOIN GroupYearStart gys ON t.[Group] = gys.[Group]
),
-- 合并同一组内的重叠/连续区间，消除日期重复
CombinedIntervals AS (
    SELECT 
        [Group],
        MIN(StartDate) AS MergedStart,
        MAX(EndDate) AS MergedEnd,
        YearStart,
        -- 保留原区间的Location和Amount（多区间合并时可根据业务调整聚合方式）
        STRING_AGG(Location, ', ') AS LinkedLocations,
        STRING_AGG(CAST(Amount AS VARCHAR), ', ') AS LinkedAmounts
    FROM MergedIntervalGroups
    GROUP BY [Group], IntervalGroupId, YearStart
),
-- 计算合并后区间的有效天数，控制累计不超过100天
CalculatedValidDays AS (
    SELECT 
        [Group],
        MergedStart,
        MergedEnd,
        YearStart,
        LinkedLocations,
        LinkedAmounts,
        -- 计算该区间在年度内的起始/结束天数
        DATEDIFF(DAY, YearStart, MergedStart) + 1 AS StartDayOfYear,
        DATEDIFF(DAY, YearStart, MergedEnd) + 1 AS EndDayOfYear,
        -- 计算累计有效天数，不超过100
        LEAST(
            SUM(
                CASE 
                    WHEN EndDayOfYear <= 100 THEN DATEDIFF(DAY, MergedStart, MergedEnd) + 1
                    WHEN StartDayOfYear > 100 THEN 0
                    ELSE 100 - (StartDayOfYear - 1)
                END
            ) OVER (PARTITION BY [Group] ORDER BY MergedStart),
            100
        ) AS CumulativeDays
    FROM CombinedIntervals
)
-- 关联原表，输出每个原区间的对应数据及有效天数
SELECT 
    t.[Group],
    t.StartDate,
    t.EndDate,
    t.Location,
    t.Amount,
    -- 计算当前原区间在年度前100天内的有效天数
    CASE
        WHEN DATEDIFF(DAY, gys.YearStart, t.EndDate) + 1 <= 100 THEN DATEDIFF(DAY, t.StartDate, t.EndDate) + 1
        WHEN DATEDIFF(DAY, gys.YearStart, t.StartDate) + 1 > 100 THEN 0
        ELSE 100 - DATEDIFF(DAY, gys.YearStart, t.StartDate)
    END AS ValidDays,
    -- 输出累计有效天数（不超过100）
    LEAST(
        SUM(
            CASE
                WHEN DATEDIFF(DAY, gys.YearStart, t2.EndDate) + 1 <= 100 THEN DATEDIFF(DAY, t2.StartDate, t2.EndDate) + 1
                WHEN DATEDIFF(DAY, gys.YearStart, t2.StartDate) + 1 > 100 THEN 0
                ELSE 100 - DATEDIFF(DAY, gys.YearStart, t2.StartDate)
            END
        ) OVER (PARTITION BY t.[Group] ORDER BY t.StartDate),
        100
    ) AS CumulativeValidDays
FROM YourTable t  -- 替换为你的实际表名
JOIN GroupYearStart gys ON t.[Group] = gys.[Group]
LEFT JOIN YourTable t2 ON t.[Group] = t2.[Group] AND t2.StartDate <= t.StartDate
GROUP BY t.[Group], t.StartDate, t.EndDate, t.Location, t.Amount, gys.YearStart
ORDER BY t.[Group], t.StartDate;

关键修复点 #

1. 合并重叠区间：通过LAG()函数标记连续/重叠的区间，合并后彻底避免同一天被多次统计
2. 强制阈值控制：用LEAST()函数确保累计天数始终不超过100，同时精准计算每个区间的有效天数范围
3. 保留原始字段：即使区间被合并，仍能通过聚合或关联逻辑保留每个原区间的Location和Amount信息，符合业务输出要求

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