如何在日期范围连续时按WidgetId和Price分组（SQL Server）

需按WidgetId和Price分组，但仅当日期范围连续时合并记录。连续判定规则为：上一条记录的EndEffectiveWhen次日等于当前记录的StartEffectiveWhen；若存在日期间隔（如示例中2023-1-31至2023-3-5无数据），同一价格的记录需分开保留。

测试数据 #

DECLARE @WidgetPrice TABLE (WidgetPriceId BIGINT IDENTITY(1,1), WidgitId INT, Price MONEY, 
    StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO @WidgetPrice(WidgitId, Price, StartEffectiveWhen, EndEffectiveWhen)
VALUES
(100,      21.48, '2020-1-1',         '2021-8-5'),
(100,      19.34, '2021-8-6',         '2021-12-31'),
(100,      19.34, '2022-1-1',         '2022-12-31'),
(100,      19.34, '2023-1-1',         '2023-1-31'),
-- 此处存在日期间隔（2023-1-31至2023-3-5无价格数据）
(100,      19.34, '2023-3-5',         '2023-12-31'),
(100,      12.87, '2024-1-1',         '2024-1-31'),
(100,      12.87, '2024-2-1',         '2100-12-31'),
-- 下一个Widget的价格数据          
(200,      728.25, '2020-1-1',         '2021-12-31'),
(200,      728.25, '2022-1-1',         '2022-12-31'),
(200,      861.58, '2023-1-1',         '2024-5-21'),
(200,      601.19, '2024-5-22',        '2100-12-31')

解决方案 #

针对SQL Server 2017及1.13亿行的大数据量，推荐使用窗口函数+分组聚合方案，避开递归CTE的性能瓶颈。核心逻辑是通过标记连续日期段的边界，生成分组ID后聚合：

实现代码 #

WITH PriceSegments AS (
    SELECT 
        WidgitId,
        Price,
        StartEffectiveWhen,
        EndEffectiveWhen,
        -- 标记当前记录是否为新连续段起点：上一条结束日期+1不等于当前开始日期则标记为1
        CASE 
            WHEN LAG(EndEffectiveWhen) OVER (PARTITION BY WidgitId, Price ORDER BY StartEffectiveWhen) + 1 = StartEffectiveWhen 
            THEN 0 
            ELSE 1 
        END AS IsNewSegment
    FROM @WidgetPrice
),
SegmentGroups AS (
    SELECT 
        WidgitId,
        Price,
        StartEffectiveWhen,
        EndEffectiveWhen,
        -- 累加标记值，生成每个连续段的唯一分组ID
        SUM(IsNewSegment) OVER (PARTITION BY WidgitId, Price ORDER BY StartEffectiveWhen ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SegmentGroupId
    FROM PriceSegments
)
SELECT 
    WidgitId,
    Price,
    MIN(StartEffectiveWhen) AS MergedStartEffectiveWhen,
    MAX(EndEffectiveWhen) AS MergedEndEffectiveWhen
FROM SegmentGroups
GROUP BY WidgitId, Price, SegmentGroupId
ORDER BY WidgitId, MergedStartEffectiveWhen;

代码说明 #

1. PriceSegments阶段：用LAG函数对比同WidgetId、同Price组内的相邻记录日期连续性，标记新分段的起点。
2. SegmentGroups阶段：通过窗口累加IsNewSegment值，为每个连续日期段生成唯一SegmentGroupId，同一连续段内的记录共享此ID。
3. 最终聚合：按WidgetId、Price和SegmentGroupId分组，取每组的最小开始日期和最大结束日期，得到合并后的结果。

性能优化建议 #

针对超大规模数据，需注意：

• 创建覆盖索引：CREATE NONCLUSTERED INDEX IX_WidgetPrice_WidgitId_Price_StartEnd ON WidgetPrice(WidgitId, Price, StartEffectiveWhen) INCLUDE (EndEffectiveWhen);，避免全表扫描。
• 若服务器资源允许，可设置MAXDOP启用并行查询，提升处理速度。
• 仅保留必要列，减少CTE中的数据传输量。

测试结果 #

执行代码后，示例数据输出如下：

可见WidgetId=100、Price=19.34的记录因日期间隔被分成两组，完全符合需求。

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