Postgres多列场景下lead()函数获取次年同月值的正确用法

我有如下查询语句：

select
calendar,
column1,
column2,
...
columnN,
count(some_value)/30 as some_value
from some_table
group by 1,2,...,N

其中calendar是通过date_trunc('month')得到的月份值。我想使用lead()函数获取当前记录次年同月（12个月后）对应的值，但尝试的写法：

lead(count(some_value)/30,12) OVER (PARTITION BY column1, column2, ..., columnN ORDER BY calendar ASC)

没有达到预期效果——单列分区时有效，多列分区时失效。我的需求是针对calendar、column1、column2的特定组合，获取次年同月的统计值，示例输出如下：

lead(...,12)失效的核心原因是：分区内可能存在月份缺失，导致偏移12行对应的不是恰好12个月后的数据。比如某个column1+column2组合下只有6个月的数据，偏移12行会直接返回空；或者中间跳过了几个月，偏移12行对应的是超过12个月后的记录，完全不符合需求。

针对这种“匹配固定时间偏移”的需求，有两种可靠的实现方式：

方案1：使用窗口函数匹配精确日期偏移（PostgreSQL等支持场景） #

如果你的数据库支持窗口函数的RANGE INTERVAL语法，可以直接通过日期匹配定位目标行：

WITH grouped_data AS (
    select
        calendar,
        column1,
        column2,
        count(some_value)/30 as some_value
    from some_table
    group by 1,2,3 -- 根据实际分组列调整
)
SELECT
    calendar,
    column1,
    column2,
    some_value as count,
    -- 精确匹配当前日期加12个月的记录
    MAX(some_value) OVER (
        PARTITION BY column1, column2
        ORDER BY calendar ASC
        RANGE BETWEEN INTERVAL '12 months' FOLLOWING AND INTERVAL '12 months' FOLLOWING
    ) as lead
FROM grouped_data
ORDER BY calendar, column1, column2;

这种写法直接基于日期区间筛选，完全避免了行数偏移带来的误差。

方案2：自连接（通用SQL写法，兼容所有数据库） #

如果你的数据库不支持RANGE INTERVAL窗口函数，用自连接的方式直接匹配同组合、日期晚12个月的记录：

WITH grouped_data AS (
    select
        calendar,
        column1,
        column2,
        count(some_value)/30 as some_value
    from some_table
    group by 1,2,3
)
SELECT
    g1.calendar,
    g1.column1,
    g1.column2,
    g1.some_value as count,
    g2.some_value as lead
FROM grouped_data g1
LEFT JOIN grouped_data g2
    ON g1.column1 = g2.column1
    AND g1.column2 = g2.column2
    -- 根据数据库语法调整日期运算，以下是不同数据库示例：
    -- PostgreSQL: g2.calendar = g1.calendar + INTERVAL '12 months'
    -- MySQL: g2.calendar = DATE_ADD(g1.calendar, INTERVAL 12 MONTH)
    -- SQL Server: g2.calendar = DATEADD(month, 12, g1.calendar)
ORDER BY g1.calendar, g1.column1, g1.column2;

这种写法逻辑直观，兼容性强，适合所有支持日期运算的数据库。

• 确保calendar是精确的月份起始日期（比如date_trunc('month')返回的每月1号），否则日期匹配会出错。
• 如果分组后仍存在同一column1+column2+calendar组合有多条记录的情况，需要在自连接时用MAX(g2.some_value)或MIN(g2.some_value)聚合，避免返回多行。

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