求助：基于父子表日期范围生成连续时间序列的Snowflake SQL实现

我有Parent和Child两张表，需要通过parentid关联两表，基于两者的起止日期构建连续时间序列：在父表的日期范围内，匹配对应时间段的Child值；超出Child日期范围的父记录，Child字段填充Null。

Parent表结构及数据 #

Child表结构及数据 #

预期输出 #

我尝试的代码 #

WITH RECURSIVE DateRanges AS 
(
    -- Anchor member: Starting with the date ranges from table_one
    SELECT 
        parentid, start_date, end_date, NULL AS child, parentnm
    FROM 
        table_one
    UNION ALL
    -- Recursive member: Add date ranges from table_two
    SELECT 
        t1.parentid,
        CASE 
            WHEN t2.start_date <= t1.end_date THEN DATEADD(DAY, 1, t1.end_date)
            ELSE t2.start_date
        END AS start_date,
        CASE 
            WHEN t2.end_date <= t1.end_date THEN t2.end_date
            ELSE t1.end_date
        END AS end_date,
        t2.child, parentnm
    FROM 
        DateRanges t1
    JOIN 
        table_two t2 ON t1.parentid = t2.parentid
                     AND DATEADD(DAY, 1, t1.end_date) <= t2.start_date
)
-- Selecting the final result
SELECT 
    idnumber, start_date, end_date, child, parentnm
FROM 
    DateRanges
ORDER BY 
    idnumber, start_date;

但这段递归代码导致数据膨胀，求解决方案。

解决方案 #

递归CTE在这里容易产生笛卡尔积导致数据膨胀，我们可以通过收集所有关键日期点，再将相邻日期点组合成区间的方式来实现，效率更高：

WITH all_dates AS (
    -- 收集父表的起止日期
    SELECT parentid, start_date AS dt, parentnm FROM Parent
    UNION ALL
    SELECT parentid, DATEADD(DAY, 1, end_date) AS dt, parentnm FROM Parent
    -- 收集子表的起止日期
    UNION ALL
    SELECT parentid, start_date AS dt, NULL FROM Child
    UNION ALL
    SELECT parentid, DATEADD(DAY, 1, end_date) AS dt, NULL FROM Child
),
-- 按parentid和日期排序，生成相邻日期对
date_pairs AS (
    SELECT 
        parentid,
        dt AS start_date,
        LEAD(dt) OVER (PARTITION BY parentid ORDER BY dt) AS end_date,
        -- 继承父表的parentnm（取区间内对应的父表名称）
        FIRST_VALUE(parentnm) IGNORE NULLS OVER (
            PARTITION BY parentid ORDER BY dt
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS parentnm
    FROM all_dates
    -- 过滤掉超出父表最大日期的无效点
    WHERE dt <= (SELECT MAX(end_date) FROM Parent WHERE parentid = all_dates.parentid)
),
-- 匹配子表的child值
final_ranges AS (
    SELECT 
        dp.parentid,
        dp.start_date,
        DATEADD(DAY, -1, dp.end_date) AS end_date,
        c.child,
        dp.parentnm
    FROM date_pairs dp
    LEFT JOIN Child c 
        ON dp.parentid = c.parentid
        AND dp.start_date <= c.end_date
        AND DATEADD(DAY, -1, dp.end_date) >= c.start_date
    WHERE dp.end_date IS NOT NULL
)
SELECT * FROM final_ranges ORDER BY parentid, start_date;

代码说明： #

1. all_dates：收集所有父表和子表的起止日期，以及父表日期的次日（作为区间分割点），确保所有需要分割的节点都被覆盖。
2. date_pairs：通过窗口函数LEAD将相邻日期组合成区间，同时用FIRST_VALUE确保每个区间对应正确的parentnm。
3. final_ranges：将日期对转换为有效区间（结束日期减1天），并通过左连接匹配对应的child值，没有匹配到的自动为Null。

这种方法避免了递归的膨胀问题，通过日期点分割的方式精准生成所需的连续区间，在Snowflake中执行效率更优。

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