如何基于GROUP BY结果计算年度profit与gross的环比差值？

问题描述 #

需对业务原始数据按year分组，聚合profit和gross的年度总和，再计算每一年与上一年的profit、gross差值。直接在原表使用LAG()函数会计算原始行的差值，无法得到分组后年度汇总值的差值，需解决该问题。

示例原始数据 #

year, profit,gross
2020   252563.02E9   352563.02E9
2022   102563.02E9    352563.02E9
2021   352563.02E9    352563.02E9
2022   482563.02E9    352563.02E9
2021   002563.02E9     352563.02E9
2020   10231.02E9     352563.02E9
2022   345633.25E9    352563.00E9

已实现的年度分组聚合语句 #

WITH cal AS (
    SELECT 
        t.year, 
        CAST(SUM(t.profit) AS DECIMAL(17,2)) AS profit,
        CAST(SUM(t.gross) AS DECIMAL(17,2)) AS gross
    FROM shoptable t
    GROUP BY t.year
    ORDER BY t.year  
)

分组后年度汇总结果 #

year  profit   gross
2020  22311834.85   1030294447.42
2021  26598154.02   1704662197.69
2022  25234347.02    1841593955.36

错误尝试的语句 #

diff AS (
    SELECT
        t.year,
        t.profit,
        (t.profit) - LAG(t.profit) OVER(ORDER BY year) AS diff_profit
    FROM shoptable t 
)

错误原因：直接在原始表shoptable上使用LAG()，会基于单条原始数据行计算差值，而非分组后的年度汇总值，结果不符合需求。

预期输出 #

year  profit   gross                diff_profit    diff_gross  
2020  22311834.85   1030294447.42    4286319.17     674367750.27
2021  26598154.02   1704662197.69   -1363807.00     136931757.67
2022  25234347.02    1841593955.36    

正确实现方案 #

核心思路：在分组聚合后的临时表上使用窗口函数，因为此时每个年份仅对应一行汇总数据，LAG()或LEAD()可正确获取相邻年份的汇总值。

方案1：显示当年与上一年的差值（当年-上一年） #

此方案中，第一年（2020）的差值为NULL，后续年份显示当年减上一年的结果：

WITH cal AS (
    SELECT 
        t.year, 
        CAST(SUM(t.profit) AS DECIMAL(17,2)) AS profit,
        CAST(SUM(t.gross) AS DECIMAL(17,2)) AS gross
    FROM shoptable t
    GROUP BY t.year
)
SELECT 
    year,
    profit,
    gross,
    profit - LAG(profit) OVER(ORDER BY year) AS diff_profit,
    gross - LAG(gross) OVER(ORDER BY year) AS diff_gross
FROM cal
ORDER BY year;

方案2：匹配预期输出的显示逻辑（下一年-当年，显示在当前行） #

如果需要和预期输出一致，将下一年减当年的差值显示在当前年份行，使用LEAD()函数获取下一年的汇总值：

WITH cal AS (
    SELECT 
        t.year, 
        CAST(SUM(t.profit) AS DECIMAL(17,2)) AS profit,
        CAST(SUM(t.gross) AS DECIMAL(17,2)) AS gross
    FROM shoptable t
    GROUP BY t.year
)
SELECT 
    year,
    profit,
    gross,
    LEAD(profit) OVER(ORDER BY year) - profit AS diff_profit,
    LEAD(gross) OVER(ORDER BY year) - gross AS diff_gross
FROM cal
ORDER BY year;

执行后结果将完全匹配预期输出，2020行显示2021-2020的差值，2021行显示2022-2021的差值，2022行差值为NULL。

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