在Snowflake中基于发票年与审计年使用LAG()获取历史金额

实现思路 #

• 放弃依赖连续行排序的LAG()函数，改用CTE+自连接的方式精准匹配跨年记录，避免数据缺失导致的无效0值
• 按vendor、site维度分组，分别匹配前1年（发票/审计年份减1、月份不变）和前2年（发票/审计年份减2、月份不变）的对应金额

SQL代码实现 #

WITH base_data AS (
    SELECT
        invoice_year,
        invoice_month,
        audit_year,
        audit_month,
        vendor,
        site,
        amount
    FROM invoice_audit_data
)
SELECT
    bd.invoice_year,
    bd.invoice_month,
    bd.audit_year,
    bd.audit_month,
    bd.vendor,
    bd.site,
    bd.amount AS current_amount,
    -- 前1年对应金额，无数据则显示0
    COALESCE(bd_prev1.amount, 0) AS prev1_year_amount,
    -- 前2年对应金额，无数据则显示0
    COALESCE(bd_prev2.amount, 0) AS prev2_year_amount
FROM base_data bd
-- 自连接匹配前1年的同维度同月份记录
LEFT JOIN base_data bd_prev1
    ON bd.vendor = bd_prev1.vendor
    AND bd.site = bd_prev1.site
    AND bd.invoice_year = bd_prev1.invoice_year + 1
    AND bd.invoice_month = bd_prev1.invoice_month
    AND bd.audit_year = bd_prev1.audit_year + 1
    AND bd.audit_month = bd_prev1.audit_month
-- 自连接匹配前2年的同维度同月份记录
LEFT JOIN base_data bd_prev2
    ON bd.vendor = bd_prev2.vendor
    AND bd.site = bd_prev2.site
    AND bd.invoice_year = bd_prev2.invoice_year + 2
    AND bd.invoice_month = bd_prev2.invoice_month
    AND bd.audit_year = bd_prev2.audit_year + 2
    AND bd.audit_month = bd_prev2.audit_month
ORDER BY bd.vendor, bd.site, bd.invoice_year, bd.invoice_month;

关键说明 #

• base_data CTE：提取原始表核心字段，作为自连接的基础数据集，简化后续逻辑
• LEFT JOIN：确保无跨年匹配数据时，当前记录仍会保留，不会丢失原始数据
• COALESCE：将缺失的匹配结果转为0，满足需求中“数据缺失时结果为0”的要求（若不需要转0，可直接保留NULL）

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