Teradata中12个月周期费用计算及历史缴费日期补全需求

现有代码 #

静态日历表创建语句 #

CREATE VOLATILE TABLE calender AS (
    SELECT DISTINCT Add_Months(Last_Day(calendar_date) + 1, - 12) AS Period_Start
        ,Last_Day(calendar_date) AS Period_End
    FROM sys_calendar.CALENDAR
    WHERE calendar_date BETWEEN DATE '2024-01-01' AND DATE '2024-06-30'

) WITH DATA PRIMARY INDEX (Period_End)
 ON COMMIT PRESERVE ROWS;

关联交易表查询语句 #

SELECT m.Period_Start
        ,m.Period_End
        ,t.Account_Id
        ,Sum(t.Amount) AS Fees
        ,Max(t.process_Date) AS Last_Fee_Paid
FROM calender m 
LEFT JOIN Trans t
    ON t.process_Date BETWEEN m.Period_Start AND m.Period_End
    AND t.Transaction_Type IN (
            'One-off Fee'
            ,'On-going Fee'
            )
GROUP BY 1,2,3

需求说明 #

• 获取每个周期内账户的最后缴费日期；
• 若账户在该周期内未缴纳任何费用，则Fees字段显示0，Last_Fee_Paid字段沿用该账户上次的缴费日期。

当前输出问题 #

无缴费记录的周期中，Account_ID、Fees、Last_Fee_Paid字段为空：

期望输出 #

无缴费记录的周期中，Account_ID保留原有账户值，Fees显示0，Last_Fee_Paid沿用上次缴费日期：

解决方案 #

要实现需求，需先生成账户与日历周期的全量组合，再结合交易数据计算，同时用窗口函数填充历史缴费日期：

-- 生成所有账户与日历周期的组合
WITH account_periods AS (
    SELECT 
        m.Period_Start,
        m.Period_End,
        a.Account_Id
    FROM calender m
    CROSS JOIN (SELECT DISTINCT Account_Id FROM Trans WHERE Transaction_Type IN ('One-off Fee','On-going Fee')) a
),
-- 计算每个账户每个周期的费用和当期最后缴费日期
period_fees AS (
    SELECT 
        ap.Period_Start,
        ap.Period_End,
        ap.Account_Id,
        COALESCE(SUM(t.Amount), 0) AS Fees,
        MAX(t.process_Date) AS Current_Last_Fee
    FROM account_periods ap
    LEFT JOIN Trans t
        ON ap.Account_Id = t.Account_Id
        AND t.process_Date BETWEEN ap.Period_Start AND ap.Period_End
        AND t.Transaction_Type IN ('One-off Fee','On-going Fee')
    GROUP BY ap.Period_Start, ap.Period_End, ap.Account_Id
)
-- 用LAST_VALUE填充历史缴费日期
SELECT 
    Period_Start,
    Period_End,
    Account_Id,
    Fees,
    LAST_VALUE(Current_Last_Fee IGNORE NULLS) OVER (
        PARTITION BY Account_Id 
        ORDER BY Period_End 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Last_Fee_Paid
FROM period_fees
ORDER BY Account_Id, Period_End;

逻辑说明 #

1. account_periods：通过CROSS JOIN生成每个账户与所有日历周期的组合，确保每个周期都有账户记录，避免无交易时账户信息缺失。
2. period_fees：将组合表与交易表关联，用COALESCE把空的费用值替换为0，同时计算当期最后缴费日期。
3. 窗口函数填充：使用LAST_VALUE(IGNORE NULLS)按账户分组、周期排序，向前填充最近的非空缴费日期，实现无交易时沿用上次日期的需求。

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