如何用SQL显示前一个同级父分组的聚合值

给定如下销售数据表：

需要查询得到以下结果：按区域和部门分组计算成本总和，同时显示按字母排序的前一个区域的总成本（东部无前置区域，显示NULL；南部显示东部总成本200；西部显示南部总成本45）：

原代码问题分析 #

原代码中LAG(SUM(Cost)) OVER (PARTITION BY Region ORDER BY Region, Department)的错误在于：PARTITION BY Region会把每个区域的数据单独分组，导致LAG只能取当前区域内的前一行值，无法跨区域获取前一个区域的总成本。

修正后的SQL方案 #

方法一：分步CTE计算 #

先分别计算部门级、区域级的聚合值，再关联得到结果：

CREATE TABLE so_sales (
    Region VARCHAR,
    Department VARCHAR,
    Cost INTEGER
);

INSERT INTO so_sales (Region, Department, Cost)
VALUES 
    ('East', 'Clothing', 45),
    ('East', 'Clothing', 35),
    ('East', 'Electronics', 120),
    ('South', 'Clothing', 20),
    ('South', 'Clothing', 25),
    ('West', 'Clothing', 40),
    ('West', 'Electronics', 150),
    ('West', 'Electronics', 140);

-- 计算部门级成本总和
WITH dept_sums AS (
    SELECT
        Region,
        Department,
        SUM(Cost) AS "Sum of Cost"
    FROM so_sales
    GROUP BY Region, Department
),
-- 计算区域级总成本，并获取前一个区域的总和
region_sums AS (
    SELECT
        Region,
        SUM(Cost) AS total_region_cost,
        LAG(SUM(Cost)) OVER (ORDER BY Region) AS prev_region_total
    FROM so_sales
    GROUP BY Region
)
-- 关联两个结果集
SELECT
    d.Region,
    d.Department,
    d."Sum of Cost",
    r.prev_region_total AS "Prev Region Sum"
FROM dept_sums d
JOIN region_sums r ON d.Region = r.Region
ORDER BY d.Region, d.Department;

方法二：嵌套窗口函数 #

用嵌套窗口函数直接在部门聚合结果中获取前区域总和，写法更简洁：

CREATE TABLE so_sales (
    Region VARCHAR,
    Department VARCHAR,
    Cost INTEGER
);

INSERT INTO so_sales (Region, Department, Cost)
VALUES 
    ('East', 'Clothing', 45),
    ('East', 'Clothing', 35),
    ('East', 'Electronics', 120),
    ('South', 'Clothing', 20),
    ('South', 'Clothing', 25),
    ('West', 'Clothing', 40),
    ('West', 'Electronics', 150),
    ('West', 'Electronics', 140);

SELECT
    Region,
    Department,
    SUM(Cost) AS "Sum of Cost",
    -- 先计算当前区域总成本，再取前一个区域的总成本
    LAG(SUM(SUM(Cost)) OVER (PARTITION BY Region)) OVER (ORDER BY Region) AS "Prev Region Sum"
FROM so_sales
GROUP BY Region, Department
ORDER BY Region, Department;

代码说明 #

• 方法一通过CTE分步拆解逻辑，先得到部门级聚合值，再得到区域级聚合及前区域值，最后关联匹配，逻辑直观易读。
• 方法二用嵌套窗口函数：内层窗口SUM(SUM(Cost)) OVER (PARTITION BY Region)计算当前区域的总成本，外层窗口LAG(...) OVER (ORDER BY Region)获取按字母排序的前一个区域总成本，实现一步到位。

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