PostgreSQL中聚合列行间差值计算：LAG函数报错解决

用户创建了如下表结构：

CREATE TABLE fake_sales
(
    group_id INT NOT NULL,
    year SMALLINT CHECK(year > 0),
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

希望使用LAG窗口函数对比当年销售额与上一年销售额，编写了如下SQL语句：

SELECT
    year,
    SUM(amount) AS annual_sale,
    LAG(annual_sale, 1) OVER (PARTITION BY year ORDER BY year) change_amount
FROM fake_sales
GROUP BY year;

但出现报错：

ERROR: column "annual_sale" does not exist

请问如何针对聚合列实现当前行与上一行的对比？

报错原因 #

SQL执行顺序是先处理FROM/GROUP BY逻辑，再计算SELECT里的聚合函数，最后才执行窗口函数。因此在窗口函数中直接引用SELECT定义的别名annual_sale时，该别名还未生成，导致报错。此外原SQL中PARTITION BY year是冗余错误的——按年份分组后每个分区仅一行数据，LAG无法获取上一行内容。

可行实现方式 #

方式1：嵌套子查询/CTE（推荐，可读性高）

先通过子查询或CTE计算出每年的总销售额，再在外层查询中使用LAG窗口函数获取上一年数据：

-- 子查询版本
SELECT
    year,
    annual_sale,
    LAG(annual_sale, 1) OVER (ORDER BY year) AS prev_year_sale,
    annual_sale - LAG(annual_sale, 1) OVER (ORDER BY year) AS change_amount
FROM (
    SELECT
        year,
        SUM(amount) AS annual_sale
    FROM fake_sales
    GROUP BY year
) AS annual_sales
ORDER BY year;

-- CTE版本
WITH annual_sales AS (
    SELECT
        year,
        SUM(amount) AS annual_sale
    FROM fake_sales
    GROUP BY year
)
SELECT
    year,
    annual_sale,
    LAG(annual_sale, 1) OVER (ORDER BY year) AS prev_year_sale,
    annual_sale - LAG(annual_sale, 1) OVER (ORDER BY year) AS change_amount
FROM annual_sales
ORDER BY year;

方式2：窗口聚合函数直接嵌套

无需提前分组，直接用窗口聚合计算年度销售额，再嵌套LAG函数获取上一年数据，注意需用DISTINCT去重：

SELECT DISTINCT
    year,
    SUM(amount) OVER (PARTITION BY year) AS annual_sale,
    LAG(SUM(amount) OVER (PARTITION BY year), 1) OVER (ORDER BY year) AS prev_year_sale,
    SUM(amount) OVER (PARTITION BY year) - LAG(SUM(amount) OVER (PARTITION BY year), 1) OVER (ORDER BY year) AS change_amount
FROM fake_sales
ORDER BY year;

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