如何使首组NULL值分组的天数匹配后续非NULL值分组的天数？

核心思路 #

先通过Gaps and Islands完成分组聚合，再借助窗口函数标记分组顺序、定位后续第一个非NULL折扣分组的天数，最后用CASE语句仅替换首组NULL折扣分组的num_of_days。

分步实现SQL #

假设你的原始表名为sales_data，以下是完整的SQL代码：

-- 步骤1：用Gaps and Islands划分连续的折扣分组
WITH island_groups AS (
    SELECT 
        item,
        discount,
        MIN(date) AS start_date,
        MAX(date) AS end_date,
        DATEDIFF(DAY, MIN(date), MAX(date)) + 1 AS num_of_days,
        -- 核心分组逻辑：通过行号差值识别连续相同折扣状态的分组
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) 
        - ROW_NUMBER() OVER (PARTITION BY item, CASE WHEN discount IS NULL THEN 'null_disc' ELSE 'non_null_disc' END ORDER BY date) AS group_id
    FROM sales_data
    WHERE item = 'Chair'
    GROUP BY item, discount, 
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY date) 
        - ROW_NUMBER() OVER (PARTITION BY item, CASE WHEN discount IS NULL THEN 'null_disc' ELSE 'non_null_disc' END ORDER BY date)
),
-- 步骤2：标记分组顺序并获取后续第一个非NULL折扣分组的天数
group_ordered AS (
    SELECT 
        *,
        -- 按时间顺序给每个分组编号
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY start_date) AS group_seq,
        -- 标记当前分组是否为NULL折扣组
        CASE WHEN discount IS NULL THEN 1 ELSE 0 END AS is_null_discount,
        -- 获取当前分组及之后第一个非NULL折扣分组的天数
        FIRST_VALUE(CASE WHEN is_null_discount = 0 THEN num_of_days END) OVER (
            PARTITION BY item 
            ORDER BY start_date 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS first_non_null_days
    FROM island_groups
)
-- 步骤3：替换首组NULL折扣分组的天数
SELECT 
    item,
    discount,
    start_date,
    end_date,
    CASE 
        -- 仅对首组且为NULL折扣的分组替换天数，若后续无有效分组则保留原天数
        WHEN group_seq = 1 AND is_null_discount = 1 AND first_non_null_days IS NOT NULL THEN first_non_null_days
        ELSE num_of_days
    END AS num_of_days
FROM group_ordered
ORDER BY start_date;

关键逻辑说明 #

1. Gaps and Islands分组：通过两次ROW_NUMBER()的差值，将连续的NULL/非NULL折扣记录划分为独立分组，确保每组内的折扣状态一致。
2. 分组顺序与目标天数定位：用ROW_NUMBER()生成分组的时间顺序号，再通过FIRST_VALUE()窗口函数，从当前组往后查找第一个非NULL折扣分组的num_of_days。
3. 条件替换：仅当分组是首组且为NULL折扣时，才用找到的目标天数替换原天数，其他分组保持原样。

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