如何在Snowflake中用LAG函数实现本年与去年营收同列展示？

问题说明 #

需要按地区和类型，将当年营收与去年同期营收展示在同一行，例如：

• 2022-04-01、美国、Type 1 → 营收: 2456，去年同期营收: 4000
• 2021-04-01、美国、Type 1 → 营收: 4000，去年同期营收: 0

原SQL使用LAG函数返回错误值，以下是问题排查和修正方案。

原错误SQL #

WITH
indata(dt,region,type,revenue) AS (
          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-08-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-10-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-12-01','US','Type 2',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2022-04-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2022-06-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2022-08-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-10-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-12-01','Europe','Type 2',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2023-02-01','Europe','Type 2',7755
)

SELECT indata.*, 
lag(REVENUE, 1, 0)  over (partition by region,type,revenue order by year(dt)) REVENUE_last_year
FROM indata
order by year(dt)

错误原因分析 #

• 分区字段错误：PARTITION BY中包含了revenue，会将相同营收的记录强制分组，破坏按地区、类型分组的逻辑，导致LAG无法正确关联同地区同类型的历史数据。
• 排序逻辑错误：ORDER BY year(dt)仅按年份排序，会把同一年的所有记录集中排列，LAG(1)会取同一年的上一条记录，而非去年同期的对应月份数据。
• LAG函数局限性：LAG(1)是取分组内的前一条记录，仅适用于连续按时间排列的序列，但测试数据中部分地区类型的月份不连续（如美国Type1无2021年5、7月数据），无法通过行偏移准确获取去年同月营收。

修正方案：使用LEFT JOIN关联去年同期数据 #

这种方法直接匹配去年同月的记录，不受数据连续性影响，逻辑更准确：

WITH
indata(dt,region,type,revenue) AS (
          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-08-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-10-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-12-01','US','Type 2',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2022-04-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2022-06-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2022-08-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-10-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-12-01','Europe','Type 2',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2023-02-01','Europe','Type 2',7755
)
SELECT 
    curr.dt,
    curr.region,
    curr.type,
    curr.revenue AS REVENUE,
    COALESCE(last.revenue, 0) AS REVENUE_LAST_YEAR
FROM indata curr
LEFT JOIN indata last 
    ON curr.region = last.region
    AND curr.type = last.type
    AND curr.dt = DATEADD(year, 1, last.dt)
ORDER BY curr.dt;

可选方案：数据连续时使用LAG(12) #

如果确保每个地区类型的每月数据都存在（无缺失），可以修正LAG函数的分区和排序逻辑，使用LAG(12)获取去年同月数据：

WITH
indata(dt,region,type,revenue) AS (
          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-08-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-10-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-12-01','US','Type 2',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2022-04-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2022-06-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2022-08-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-10-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-12-01','Europe','Type 2',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2023-02-01','Europe','Type 2',7755
)
SELECT 
    indata.*,
    LAG(revenue, 12, 0) OVER (PARTITION BY region, type ORDER BY dt) AS REVENUE_LAST_YEAR
FROM indata
ORDER BY dt;

注意：此方案仅在数据无缺失月份时有效，否则会返回错误的历史数据。

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