在Snowflake中拆分日期范围并按年度生成记录

需求说明 #

将跨年度的员工日期范围记录，拆分为每个年度对应的子记录，每个子记录的起止日期匹配所在年度的实际覆盖区间。

输入数据 #

期望输出 #

Snowflake实现方案 #

通过生成年度序列结合日期范围计算实现，核心逻辑为：为每条原始记录生成覆盖其日期区间的所有年度，再计算每个年度对应的实际起止日期：

WITH employee_data AS (
    -- 替换为你的实际业务表
    SELECT 
        EMP_KEY,
        TO_DATE(START_DT, 'MM-DD-YYYY') AS START_DT,
        TO_DATE(END_DT, 'MM-DD-YYYY') AS END_DT
    VALUES
        (1, '12-1-2020', '05-06-2023'),
        (2, '1-1-2022', '12-21-2023')
),
year_series AS (
    -- 生成每条记录对应的年度序列
    SELECT 
        ed.EMP_KEY,
        ed.START_DT,
        ed.END_DT,
        YEAR(ed.START_DT) + seq.n AS current_year
    FROM employee_data ed
    JOIN TABLE(GENERATOR(ROWCOUNT => 10)) seq  -- 设为足够大的数值，覆盖最大年度跨度
        ON YEAR(ed.START_DT) + seq.n <= YEAR(ed.END_DT)
)
SELECT 
    EMP_KEY,
    -- 取原始起始日期与年度第一天的较大值
    TO_CHAR(GREATEST(START_DT, DATE_FROM_PARTS(current_year, 1, 1)), 'MM-DD-YYYY') AS START_DT,
    -- 取原始结束日期与年度最后一天的较小值
    TO_CHAR(LEAST(END_DT, DATE_FROM_PARTS(current_year, 12, 31)), 'MM-DD-YYYY') AS END_DT
FROM year_series
ORDER BY EMP_KEY, current_year;

代码说明 #

• employee_data CTE：将原始日期字符串转换为Snowflake日期类型，确保日期计算精度。
• year_series CTE：利用GENERATOR生成连续行，结合原始记录的起止年份，得到需要拆分的所有年度。
• 最终查询：通过GREATEST和LEAST函数精准计算每个年度的实际起止边界，避免日期越界。

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