如何合并多行中连续的商品缺货时间区间？

针对你提到的HistoricalStockStatus表中连续缺货区间被拆分的问题，以下是基于窗口函数的具体实现步骤，完全解决Gaps and Islands场景的合并需求：

实现思路 #

1. 先筛选出所有**缺货（Out of Stock）**的记录，只处理目标数据；
2. 对每个商品的缺货记录按日期排序，用窗口函数识别连续的区间，生成分组标识PartitionBlock；
3. 按商品编号和分组标识聚合，取每组的最小开始日期和最大结束日期，得到合并后的完整缺货区间。

具体SQL代码（适配SQL Server语法） #

WITH FilteredStock AS (
    -- 第一步：筛选仅缺货的记录
    SELECT 
        [Item Number],
        [Start Date],
        [End Date]
    FROM HistoricalStockStatus
    WHERE [Stock Status] = 'Out of Stock'
),
GroupedBlocks AS (
    -- 第二步：生成连续区间的分组标识
    SELECT
        [Item Number],
        [Start Date],
        [End Date],
        -- 计算分组：若当前记录的开始日期与上一条的结束日期连续/重叠，则归为同一组
        SUM(CASE 
            WHEN LAG([End Date]) OVER (PARTITION BY [Item Number] ORDER BY [Start Date]) >= DATEADD(DAY, -1, [Start Date])
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY [Item Number] ORDER BY [Start Date]) AS PartitionBlock
    FROM FilteredStock
)
-- 第三步：聚合得到合并后的区间
SELECT
    [Item Number],
    MIN([Start Date]) AS [Merged Start Date],
    MAX([End Date]) AS [Merged End Date],
    'Out of Stock' AS [Stock Status]
FROM GroupedBlocks
GROUP BY [Item Number], PartitionBlock
ORDER BY [Item Number], [Merged Start Date];

代码细节解释 #

• FilteredStock：过滤掉非缺货记录，减少后续计算量，只聚焦需要合并的目标数据；
• GroupedBlocks：
  • LAG([End Date]) OVER (...)：获取同一商品的上一条缺货记录的结束日期；
  • CASE判断逻辑：如果上一条的结束日期 >= 当前开始日期的前一天，说明两个区间是连续（或重叠）的，归为同一分组（加0）；否则新建分组（加1）；
  • SUM(...) OVER (...)：累计分组标识，让同一连续区间的记录拥有相同的PartitionBlock值；
• 最终聚合：按商品和分组标识分组，取最小开始日期和最大结束日期，得到合并后的完整缺货区间。

特殊情况处理 #

如果你的区间定义是左闭右开，或者存在日期间隙（比如上一条结束是2024-01-03，当前开始是2024-01-05，中间隔了一天），只需要调整CASE里的判断条件即可。比如要严格连续（无间隙），可以把条件改成：

WHEN LAG([End Date]) OVER (PARTITION BY [Item Number] ORDER BY [Start Date]) = DATEADD(DAY, -1, [Start Date])

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