PostgreSQL窗口函数实现上年同期累计金额计算的问题

你的窗口函数问题出在分区逻辑上：date - INTERVAL '1 year'会把2022-03-01和2021-03-01归到同一个分区，但ORDER BY date会将两年的日期混排，导致累计值包含了两年的数据，而非单独上年的同期累计。下面提供几种可行的解决方法：

方法一：先计算当年累计，再关联上年数据 #

这种方法逻辑清晰，性能也更优，适合数据量较大的场景：

WITH daily_yearly_cumulative AS (
    SELECT
        date,
        fact_mln,
        -- 计算每天的当年累计值
        SUM(fact_mln) OVER(PARTITION BY EXTRACT(YEAR FROM date) ORDER BY date) AS year_to_date
    FROM your_table
)
SELECT
    curr.date,
    curr.fact_mln,
    curr.year_to_date AS current_year_ytd,
    -- 关联上年同期的累计值
    prev.year_to_date AS prev_year_ytd
FROM daily_yearly_cumulative curr
LEFT JOIN daily_yearly_cumulative prev
    ON curr.date = prev.date + INTERVAL '1 year';

步骤说明：

1. 先用CTE按年份分区，计算每一天的当年累计金额（year_to_date）；
2. 将当前日期与上年的同一天做关联，直接获取上年的累计值。

方法二：使用子查询直接计算上年同期累计 #

如果数据量不大，子查询的方式更直观，不需要额外的CTE：

SELECT
    date,
    fact_mln,
    -- 计算上年从年初到同期日期的累计金额
    (SELECT SUM(fact_mln)
     FROM your_table t2
     WHERE t2.date BETWEEN DATE_TRUNC('year', t1.date) - INTERVAL '1 year'
                       AND t1.date - INTERVAL '1 year') AS prev_year_ytd
FROM your_table t1;

逻辑：对每一行的日期t1.date，查询表中所有在上年年初到上年同一天范围内的数据，求和得到累计值。

方法三：调整窗口函数的范围控制 #

如果坚持用窗口函数，可以通过范围筛选仅统计上年的日期：

SELECT
    date,
    fact_mln,
    SUM(fact_mln) OVER(
        ORDER BY date
        -- 仅统计上年同一天及之前的所有数据
        RANGE BETWEEN INTERVAL '1 year' PRECEDING AND INTERVAL '1 year' PRECEDING
    ) AS prev_year_day_value,
    -- 计算上年同期累计
    SUM(CASE WHEN EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN fact_mln ELSE 0 END) OVER(
        ORDER BY (date - INTERVAL '1 year')
    ) AS prev_year_ytd
FROM your_table;

注意：这种方式需要确保数据中每年的日期是连续的，否则累计值可能不准确。

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