如何实现SQL按年份分组并添加上年平均价格及价差列？

问题原因 #

你之前的SQL失败是因为LAG()函数里的PARTITION BY LEFT(D3611_Transaktionsda, 4)，这会把每一年的数据单独分成一个分区，每个分区只有一行数据，自然无法获取到上一年的价格。

可行SQL方案 #

可以通过先聚合年度数据，再对聚合结果使用窗口函数的方式实现需求，这里用CTE（公共表表达式）分步处理：

WITH yearly_summary AS (
    SELECT
        LEFT(D3611_Transaktionsda, 4) AS MYPERIOD,
        SUM(D3631_Antal) AS MYQTY1,
        SUM(D3653_Debiterbart) AS SALES,
        SUM(D3653_Debiterbart)/SUM(D3631_Antal) AS PRICE
    FROM PUPROTRA
    WHERE PUPROTRA.D3605_Artikelkod = 'XYZ'
        AND PUPROTRA.D3601_Ursprung = 'O'
        AND PUPROTRA.D3625_Transaktionsty = 'U'
        AND D3631_Antal <> 0
    GROUP BY LEFT(D3611_Transaktionsda, 4)
)
SELECT
    MYPERIOD,
    MYQTY1,
    SALES,
    PRICE,
    LAG(PRICE) OVER (ORDER BY MYPERIOD) AS PREV_PRICE,
    PRICE - LAG(PRICE) OVER (ORDER BY MYPERIOD) AS PRICE_DIFF
FROM yearly_summary
ORDER BY MYPERIOD;

方案说明 #

1. CTE部分：先完成年度数据的聚合，计算出每年的销量、销售额和平均价格，逻辑和你之前的年度汇总SQL一致。
2. 主查询部分：
   • 使用LAG(PRICE) OVER (ORDER BY MYPERIOD)获取上一年的平均价格，仅按MYPERIOD排序即可，不需要分区，因为所有年份是连续序列。
   • 直接用当年价格减去上年价格得到PRICE_DIFF（价差列）。

如果你的数据库不支持CTE，也可以用子查询实现：

SELECT
    MYPERIOD,
    MYQTY1,
    SALES,
    PRICE,
    LAG(PRICE) OVER (ORDER BY MYPERIOD) AS PREV_PRICE,
    PRICE - LAG(PRICE) OVER (ORDER BY MYPERIOD) AS PRICE_DIFF
FROM (
    SELECT
        LEFT(D3611_Transaktionsda, 4) AS MYPERIOD,
        SUM(D3631_Antal) AS MYQTY1,
        SUM(D3653_Debiterbart) AS SALES,
        SUM(D3653_Debiterbart)/SUM(D3631_Antal) AS PRICE
    FROM PUPROTRA
    WHERE PUPROTRA.D3605_Artikelkod = 'XYZ'
        AND PUPROTRA.D3601_Ursprung = 'O'
        AND PUPROTRA.D3625_Transaktionsty = 'U'
        AND D3631_Antal <> 0
    GROUP BY LEFT(D3611_Transaktionsda, 4)
) AS yearly_summary
ORDER BY MYPERIOD;

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