如何在Snowflake中实现无间隙日期生成与两表关联查询？

问题描述 #

现有两张表：

• 表1（假设表名为transaction_principal）：含字段Transactions_Date（交易日期）、Principal balance（可用本金余额）
• 表2（假设表名为interest_rates）：含字段Effective_Date（生效日期）、Interest（利率）

需生成满足以下要求的结果集：

• Result_Date：包含表1所有交易日期，并填充日期间隙生成连续日期序列
• Principal Balance：将Result_Date匹配到表1，把对应本金余额填充至下一个交易日期
• Interest：将Result_Date匹配到表2的生效日期，填充对应日期区间的利率

要求：仅使用CTE，不创建物理表

实现SQL #

WITH date_range AS (
    -- 生成表1交易日期范围内的连续日期序列
    SELECT DATEADD(day, seq4(), MIN(Transactions_Date)) AS Result_Date
    FROM transaction_principal
    GROUP BY MIN(Transactions_Date), MAX(Transactions_Date)
    CONNECT BY DATEADD(day, seq4(), MIN(Transactions_Date)) <= MAX(Transactions_Date)
),
transaction_with_next_date AS (
    -- 标记每条本金记录的生效结束日期（下一个交易日期）
    SELECT 
        Transactions_Date,
        "Principal balance",
        LEAD(Transactions_Date) OVER (ORDER BY Transactions_Date) AS next_transaction_date
    FROM transaction_principal
),
rate_with_next_effective_date AS (
    -- 标记每条利率记录的生效结束日期（下一个利率生效日期）
    SELECT 
        Effective_Date,
        Interest,
        LEAD(Effective_Date) OVER (ORDER BY Effective_Date) AS next_effective_date
    FROM interest_rates
)
-- 关联连续日期与本金、利率的生效区间
SELECT 
    dr.Result_Date,
    tp."Principal balance",
    r.Interest
FROM date_range dr
LEFT JOIN transaction_with_next_date tp 
    ON dr.Result_Date >= tp.Transactions_Date 
    AND (dr.Result_Date < tp.next_transaction_date OR tp.next_transaction_date IS NULL)
LEFT JOIN rate_with_next_effective_date r 
    ON dr.Result_Date >= r.Effective_Date 
    AND (dr.Result_Date < r.next_effective_date OR r.next_effective_date IS NULL)
ORDER BY dr.Result_Date;

逻辑说明 #

1. date_range CTE：利用Snowflake的seq4()生成连续整数序列，结合DATEADD将整数转为连续日期，覆盖表1交易日期的最小到最大值区间。
2. transaction_with_next_date CTE：通过LEAD()窗口函数获取每条交易记录的下一个交易日期，以此确定当前本金的生效区间——从当前交易日期到下一个交易日期前一天，最后一条本金记录生效到序列的最后一天。
3. rate_with_next_effective_date CTE：同理，用LEAD()获取每条利率的下一个生效日期，确定利率的生效区间，最后一条利率生效到序列的最后一天。
4. 最终关联：将连续日期分别与本金、利率的生效区间做范围匹配，得到每个日期对应的本金和利率值。

注意事项 #

• 若表1存在重复交易日期，需在transaction_with_next_date中添加DISTINCT或通过窗口函数去重，避免本金值重复匹配。
• 若利率生效日期超出表1的交易日期范围，可根据业务需求调整区间判断逻辑（比如保留最早/最晚利率）。

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