如何用Snowflake SQL处理商品价格表的重叠日期区间并拆分为新行？

背景：现有一张存储ITEM_ID、PRICE、START_DATE、END_DATE及LOADED_DATETIME的基础表，用户通过文件导入更新指定日期区间的价格后，会出现同一ITEM_ID下日期区间重叠的行。需要按以下规则处理：

• 保留LOADED_DATETIME最新的重叠行
• 将旧行在重叠区间的前后部分拆分为新行，保留非重叠的日期段
• 无重叠的行保持不变

比如ITEM_ID为A的两行重叠时，处理后会保留最新的重叠行，同时拆分出原行的2023-01-01至2023-01-09和2023-01-16至2023-01-31两个非重叠区间；ITEM_ID为B的无重叠行则直接保留。

思路拆解 #

1. 标记最新行：给每个ITEM_ID的行按LOADED_DATETIME降序排序，标记出最新加载的行（这类行直接完整保留）。
2. 识别重叠区间：针对非最新行，找出它们与同ITEM_ID下所有最新行的重叠日期范围。
3. 拆分旧行片段：把旧行中未被最新行覆盖的前后部分拆成独立行。
4. 合并结果：将保留的最新行和拆分后的旧行片段合并，得到无重叠的最终数据集。

Snowflake SQL 实现代码 #

WITH ranked_data AS (
    -- 给每个ITEM_ID的行按加载时间排序，标记最新行
    SELECT
        ITEM_ID,
        PRICE,
        START_DATE,
        END_DATE,
        LOADED_DATETIME,
        ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY LOADED_DATETIME DESC) AS rn
    FROM your_base_table
),
latest_rows AS (
    -- 提取每个ITEM_ID的最新行（支持同一时间加载多行同ITEM_ID的情况）
    SELECT * FROM ranked_data WHERE rn = 1
),
old_rows AS (
    -- 提取所有非最新行
    SELECT * FROM ranked_data WHERE rn > 1
),
overlap_details AS (
    -- 计算旧行与最新行的重叠区间
    SELECT
        o.ITEM_ID,
        o.PRICE AS old_price,
        o.START_DATE AS old_start,
        o.END_DATE AS old_end,
        GREATEST(o.START_DATE, l.START_DATE) AS overlap_start,
        LEAST(o.END_DATE, l.END_DATE) AS overlap_end
    FROM old_rows o
    JOIN latest_rows l 
        ON o.ITEM_ID = l.ITEM_ID
        -- 判断是否存在日期重叠
        AND o.START_DATE <= l.END_DATE
        AND o.END_DATE >= l.START_DATE
),
split_old_records AS (
    -- 生成重叠前的有效片段
    SELECT
        ITEM_ID,
        old_price AS PRICE,
        old_start AS START_DATE,
        DATEADD(DAY, -1, overlap_start) AS END_DATE,
        (SELECT MAX(LOADED_DATETIME) FROM old_rows WHERE ITEM_ID = overlap_details.ITEM_ID) AS LOADED_DATETIME
    FROM overlap_details
    WHERE old_start < overlap_start
    
    UNION ALL
    
    -- 生成重叠后的有效片段
    SELECT
        ITEM_ID,
        old_price AS PRICE,
        DATEADD(DAY, 1, overlap_end) AS START_DATE,
        old_end AS END_DATE,
        (SELECT MAX(LOADED_DATETIME) FROM old_rows WHERE ITEM_ID = overlap_details.ITEM_ID) AS LOADED_DATETIME
    FROM overlap_details
    WHERE old_end > overlap_end
    
    UNION ALL
    
    -- 添加无重叠的旧行（未被最新行覆盖的非最新行）
    SELECT
        ITEM_ID,
        PRICE,
        START_DATE,
        END_DATE,
        LOADED_DATETIME
    FROM old_rows o
    WHERE NOT EXISTS (
        SELECT 1 FROM latest_rows l
        WHERE o.ITEM_ID = l.ITEM_ID
        AND o.START_DATE <= l.END_DATE
        AND o.END_DATE >= l.START_DATE
    )
)
-- 合并最新行和拆分后的旧行片段，输出最终结果
SELECT ITEM_ID, PRICE, START_DATE, END_DATE, LOADED_DATETIME FROM latest_rows
UNION ALL
SELECT ITEM_ID, PRICE, START_DATE, END_DATE, LOADED_DATETIME FROM split_old_records
ORDER BY ITEM_ID, START_DATE;

关键说明 #

• ranked_data：使用ROW_NUMBER()窗口函数为每个ITEM_ID的行按加载时间降序排名，最新行的rn值为1。
• overlap_details：通过JOIN关联旧行和最新行，利用GREATEST和LEAST计算两者的重叠起止日期。
• split_old_records：拆分旧行时，用DATEADD调整日期，确保拆分后的区间与最新行完全不重叠；同时保留没有重叠的旧行。
• 替换代码中的your_base_table为实际表名即可运行。

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