如何基于startdate、enddate、effective date清洗SQL表并计算有效日期区间

你已经完成了startdate_valid的计算，enddate_valid可以通过窗口偏移函数LEAD实现，核心逻辑如下：

• 同一个id下的所有记录按startdate_valid升序排序
• 当前记录的有效期结束时间默认取同id下下一条记录的startdate_valid
• 若当前是id下的最后一条记录，再判断原始表的enddate是否大于当前startdate_valid：如果是则用原始enddate，否则说明原始enddate无效，留空即可

WITH mid_table AS (
    SELECT 
        id,
        value,
        startdate,
        enddate,
        `effective date`,
        -- 你原有计算startdate_valid的逻辑
        MAX(`effective date`) OVER(PARTITION BY id, YEAR(`effective date`), MONTH(`effective date`) ORDER BY `effective date`) AS startdate_valid
    FROM 你的原始表名
)
SELECT 
    id,
    value,
    startdate_valid,
    CASE
        -- 优先取下一条记录的startdate_valid作为当前结束时间
        WHEN LEAD(startdate_valid) OVER(PARTITION BY id ORDER BY startdate_valid) IS NOT NULL
        THEN LEAD(startdate_valid) OVER(PARTITION BY id ORDER BY startdate_valid)
        -- 最后一条记录判断原始enddate是否有效
        WHEN enddate > startdate_valid THEN enddate
        ELSE NULL
    END AS enddate_valid
FROM mid_table
ORDER BY id, startdate_valid;

如果同一个id下存在多条startdate_valid相同的重复记录，可以先对mid_table按id、startdate_valid去重，保留最新的effective date对应的value即可，避免生成错误的时间区间。

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