SQL Server 2016日期区间分组：合并行生成最长匹配日期范围组

测试数据 #

DECLARE @A TABLE (Col varchar(20), DateIn date)
INSERT @A SELECT 'DEF', '10/27/2023'
INSERT @A SELECT 'DEF', '10/28/2023'
INSERT @A SELECT 'DEF', '10/29/2023'
INSERT @A SELECT 'DEF', '10/30/2023'
INSERT @A SELECT 'DEF', '10/31/2023'

INSERT @A SELECT 'ABC', '10/27/2023'
INSERT @A SELECT 'ABC', '10/28/2023'
INSERT @A SELECT 'ABC', '10/29/2023'
INSERT @A SELECT 'ABC', '10/30/2023'
INSERT @A SELECT 'ABC', '10/31/2023'
INSERT @A SELECT 'ABC', '11/01/2023'
INSERT @A SELECT 'ABC', '11/02/2023'
INSERT @A SELECT 'ABC', '11/03/2023'
INSERT @A SELECT 'ABC', '11/04/2023'

INSERT @A SELECT 'XXX', '10/31/2023'
INSERT @A SELECT 'XXX', '11/01/2023'
INSERT @A SELECT 'XXX', '11/02/2023'

期望输出 #

DECLARE @B TABLE (Col varchar(20), DateIn date, DateOut date, DaysIn int)
INSERT @B Select 'DEF', '10/27/2023', '10/30/2023', 4
INSERT @B Select 'ABC', '10/27/2023', '10/30/2023', 4
INSERT @B Select 'DEF', '10/31/2023', '10/31/2023', 1
INSERT @B Select 'ABC', '10/31/2023', '10/31/2023', 1
INSERT @B Select 'XXX', '10/31/2023', '10/31/2023', 1
INSERT @B Select 'ABC', '11/01/2023', '11/02/2023', 2
INSERT @B Select 'XXX', '11/01/2023', '11/02/2023', 2
INSERT @B Select 'ABC', '11/03/2023', '11/04/2023', 2

需求说明 #

需要按Col分组，提取所有同步的连续日期区间：即某个区间内，所有包含该区间全部日期的Col，都能覆盖区间内每一天；若Col缺失区间内任意日期，则无法纳入该区间。例如XXX无10/27-10/30的记录，因此该区间仅包含DEF和ABC。

解决方案（基于集合操作，替代WHILE循环） #

使用窗口函数和XML拼接拆分实现，性能远优于循环方案：

WITH DateColGroups AS (
    -- 生成每个日期对应的Col列表（按排序拼接，保证相同集合的字符串一致）
    SELECT 
        DateIn,
        ColList = STUFF((
            SELECT ',' + Col 
            FROM @A a2 
            WHERE a2.DateIn = a1.DateIn 
            ORDER BY Col 
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 1, 1, ''),
        -- 标记当前日期与前一天的Col集合是否变化
        GroupFlag = CASE 
            WHEN LAG(
                STUFF((
                    SELECT ',' + Col 
                    FROM @A a2 
                    WHERE a2.DateIn = a1.DateIn 
                    ORDER BY Col 
                    FOR XML PATH(''), TYPE
                ).value('.', 'varchar(max)'), 1, 1, '')
            ) OVER (ORDER BY DateIn) != 
            STUFF((
                SELECT ',' + Col 
                FROM @A a2 
                WHERE a2.DateIn = a1.DateIn 
                ORDER BY Col 
                FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'), 1, 1, '')
            THEN 1 
            ELSE 0 
        END
    FROM @A a1
    GROUP BY DateIn
),
IntervalGroups AS (
    -- 累计变化点生成区间分组ID
    SELECT 
        DateIn,
        ColList,
        GroupId = SUM(GroupFlag) OVER (ORDER BY DateIn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM DateColGroups
),
Intervals AS (
    -- 聚合得到每个连续区间的起止日期
    SELECT 
        StartDate = MIN(DateIn),
        EndDate = MAX(DateIn),
        ColList
    FROM IntervalGroups
    GROUP BY GroupId, ColList
)
-- 拆分Col列表，生成最终结果
SELECT 
    Col = LTRIM(RTRIM(m.n.value('.[1]','varchar(20)'))),
    DateIn = StartDate,
    DateOut = EndDate,
    DaysIn = DATEDIFF(day, StartDate, EndDate) + 1
FROM Intervals
CROSS APPLY (
    SELECT CAST('<XMLRoot><RowData>' + REPLACE(ColList, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
) t
CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
ORDER BY StartDate, Col;

代码逻辑说明 #

1. 日期-Col集合映射：通过分组和XML拼接，为每个日期生成对应的Col列表，确保相同Col集合的日期有一致的字符串标识。
2. 区间分组标记：用LAG函数对比相邻日期的Col集合，标记变化点；通过累计变化点得到区间分组ID，将连续且Col集合相同的日期归为同一区间。
3. 聚合区间：按分组ID聚合，得到每个连续区间的起止日期和对应的Col列表。
4. 拆分输出：将Col列表拆分为单个Col，计算区间天数，输出符合要求的结果。

内容的提问来源于stack exchange，提问作者T-Rex