Redshift中如何关联dim_date表并补全缺失日期的有效值

我有两张需要关联的表，需求是为空行补全上方的有效值。但关联dim_date表后，原查询里的缺失日期并未显示——比如指定具体ID时能拿到对应行，但流程里实现不了。使用的数据库是Redshift，当前执行的SQL如下：

select first_day_of_month, 
       last_value(c_loan_agreement_id) IGNORE NULLS OVER (ORDER BY first_day_of_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  c_loan_agreement_id 
from business_core.dim_date dd
left join business_core.fact_receivable_repayment frr  
    on date_trunc('month',frr.repayment_date) = dd.first_day_of_month 
where dd.first_day_of_month <= '2023-12-01' and dd.first_day_of_month >= '2022-12-01' 
group by c_loan_agreement_id, first_day_of_month, frr.repayment_date;

问题分析 #

原SQL的核心问题：

• GROUP BY过滤了无匹配的日期行：LEFT JOIN后，dim_date中没有对应还款记录的行，其c_loan_agreement_id和repayment_date为NULL，分组后会丢失这些行，导致缺失日期不显示。
• 窗口函数未按ID分区：LAST_VALUE的窗口是全局的，多个ID的补全逻辑混在一起，单个ID场景能正常工作，但多ID场景下逻辑混乱。
• 关联条件仅匹配月份，未绑定ID：LEFT JOIN只按月份关联，不同ID的还款记录会和同一日期匹配，数据逻辑混乱。

修正后的SQL #

要实现每个ID对应完整日期序列并补全空值，需要先生成日期与ID的全量组合，再关联数据、补全空值：

-- 生成所有目标日期与贷款ID的全量组合，确保每个ID都有完整日期序列
WITH date_id_cross AS (
    SELECT 
        dd.first_day_of_month,
        loan_ids.c_loan_agreement_id
    FROM business_core.dim_date dd
    -- 关联所有需要处理的贷款ID集合（这里取有还款记录的ID，可替换为贷款维度表）
    CROSS JOIN (
        SELECT DISTINCT c_loan_agreement_id 
        FROM business_core.fact_receivable_repayment
    ) loan_ids
    WHERE dd.first_day_of_month BETWEEN '2022-12-01' AND '2023-12-01'
),
-- 关联还款表，获取对应月份的还款数据
joined_data AS (
    SELECT 
        dic.first_day_of_month,
        dic.c_loan_agreement_id,
        frr.repayment_date
    FROM date_id_cross dic
    LEFT JOIN business_core.fact_receivable_repayment frr
        ON dic.c_loan_agreement_id = frr.c_loan_agreement_id
        AND date_trunc('month', frr.repayment_date) = dic.first_day_of_month
)
-- 按贷款ID分区，用LAST_VALUE补全空值
SELECT 
    first_day_of_month,
    -- 示例：补全贷款ID（实际不会为空，可替换为需要补全的其他字段）
    LAST_VALUE(c_loan_agreement_id IGNORE NULLS) OVER (
        PARTITION BY c_loan_agreement_id 
        ORDER BY first_day_of_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS c_loan_agreement_id,
    -- 示例：补全还款日期，空值取上一个非空值
    LAST_VALUE(frr.repayment_date IGNORE NULLS) OVER (
        PARTITION BY c_loan_agreement_id 
        ORDER BY first_day_of_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS repayment_date
FROM joined_data
ORDER BY c_loan_agreement_id, first_day_of_month;

关键说明 #

1. 全量日期-ID组合：用CROSS JOIN生成每个贷款ID与所有目标日期的匹配，确保不会丢失任何日期行。如果需要处理所有贷款ID（包括无还款记录的），把子查询换成贷款维度表（比如business_core.dim_loan_agreement）的ID集合即可。
2. 按ID分区的窗口函数：添加PARTITION BY c_loan_agreement_id，保证每个ID的补全逻辑独立，不会和其他ID的数据混淆。
3. 去掉GROUP BY：我们需要保留所有日期行，分组会过滤掉无匹配的空值行，因此直接用窗口函数处理即可。

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