如何使用LAG函数按分区获取上一分区的RUNNING_TOTAL值？

原始数据表 #

已实现的季度分区累计值 #

按Name、Places、Quarter分区计算的累计值结果：

预期目标 #

新增Previous RT列，显示同一Name、Places分组下，上一个Quarter的Running Total总值，预期结果：

尝试的错误写法 #

使用了和Running Total相同的分区逻辑编写LAG函数，结果不符合预期：

LAG(RUNNING_TOTAL) OVER (PARTITION BY NAME, PLACES, QUARTER ORDER BY NAME, PLACES, QUARTER) AS PREVIOUS_RT

错误结果 #

问题核心是LAG函数的分区逻辑错误：要获取的是同一Name、Places下上一个Quarter的累计总值，不能把Quarter包含在PARTITION中。以下是两种实现方式：

方式1：多CTE分步实现（逻辑清晰） #

WITH quarter_totals AS (
    -- 计算每个季度的总累计值（即该季度的最终Running Total）
    SELECT 
        Name,
        Places,
        Quarter,
        SUM(Cost) AS quarter_total
    FROM your_table
    GROUP BY Name, Places, Quarter
),
prev_quarter_totals AS (
    -- 获取每个季度对应的上一季度累计值
    SELECT 
        Name,
        Places,
        Quarter,
        LAG(quarter_total) OVER (PARTITION BY Name, Places ORDER BY Quarter) AS previous_rt
    FROM quarter_totals
),
running_totals AS (
    -- 计算行级递增的Running Total
    SELECT 
        *,
        SUM(Cost) OVER (PARTITION BY Name, Places, Quarter ORDER BY Date) AS running_total
    FROM your_table
)
-- 关联所有结果得到最终表
SELECT 
    rt.Name,
    rt.Places,
    rt.Date,
    rt.Quarter,
    rt.Cost,
    rt.running_total,
    pqt.previous_rt
FROM running_totals rt
JOIN prev_quarter_totals pqt 
    ON rt.Name = pqt.Name 
    AND rt.Places = pqt.Places 
    AND rt.Quarter = pqt.Quarter
ORDER BY rt.Date;

方式2：嵌套窗口函数简化写法 #

如果你的SQL引擎支持窗口函数嵌套，可以用更简洁的语句实现：

SELECT 
    *,
    -- 行级递增累计值
    SUM(Cost) OVER (PARTITION BY Name, Places, Quarter ORDER BY Date) AS running_total,
    -- 先取每个季度的总累计值，再用LAG获取上季度的对应值
    LAG(SUM(Cost) OVER (PARTITION BY Name, Places, Quarter)) OVER (PARTITION BY Name, Places ORDER BY Quarter) AS previous_rt
FROM your_table
ORDER BY Date;

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