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

xmjla07d 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(87)

我试图弄清楚如何使用SQL显示前一个兄弟的父分组的聚合值。希望下面的例子能让这一点更清楚：
给定此表：
| 所属部门|成本| Cost |
| --|--| ------------ |
| 服装|四十五| 45 |
| 服装|三十五| 35 |
| 电子产品|一百二十| 120 |
| 服装|二十个| 20 |
| 服装|二十五个| 25 |
| 服装|四十| 40 |
| 电子产品|一百五十| 150 |
| 电子产品|一百四十| 140 |
我想创建一个查询，它将返回以下结果集：
| 所属部门|费用总额|上一区域总和| Prev Region Sum |
| --|--|--| ------------ |
| 服装|八十|空值| NULL |
| 电子产品|一百二十|空值| NULL |
| 服装|四十五|两百| 200 |
| 服装|四十|四十五| 45 |
| 电子产品|二百九十|四十五| 45 |
基本上，我希望按Region和Department对数据进行分组和聚合，但我也希望引用基于前一个Region的Region分组的聚合（假设它们按字母顺序排序）。
因此，如果您查看结果集中的“Prev Region Sum”列-您可以看到前两个East行获得null，因为在“East”之前没有区域，下一个South行获得200，因为这是所有“East”记录的成本之和，而“West”行获得45，因为这是所有South记录的成本之和。
下面是这个例子的SQL，如果你想尝试一下的话。正如你所看到的，除了“Prev Region Sum”列的表达式之外，我还有其他所有内容：

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(Cost)) OVER (PARTITION BY Region ORDER BY Region, Department) AS "Prev Region Sum" 
FROM
    so_sales
GROUP BY
    Region,
    Department
ORDER BY
    Region,
    Department;

字符串

postgresql

来源：https://stackoverflow.com/questions/76814301/displaying-the-aggregation-value-of-the-prior-siblings-parent-grouping-using-sq

1条答案

按热度按时间

umuewwlo1#

with data(Region, Department, Cost) as (
    select 'East', 'Clothing', 45  union all
    select 'East', 'Clothing', 35  union all
    select 'East', 'Electronics', 120  union all
    select 'South', 'Clothing', 20  union all
    select 'South', 'Clothing', 25  union all
    select 'West', 'Clothing', 40  union all
    select 'West', 'Electronics', 150  union all
    select 'West', 'Electronics', 140  
)
select region, Department, sum_of_cost, region_sum, 
    lag(region_sum, rn::int) over(order by region, rn) as prev_region_sum
from (
    select region, Department, sum_of_cost,
        sum(sum_of_cost) over(partition by region order by region) as region_sum,
        row_number() over(partition by region order by department) as rn
    from (
        select  
            region, Department, 
                sum(cost) as sum_of_cost
        from data 
        group by Region, Department
    ) d
) d
order by region, department
;

字符串
https://dbfiddle.uk/5WVz9smg

赞(0）回复(0）举报 2023-08-04

我来回答

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

1条答案

相关问题

热门标签

最新问答