sql - 通过行聚合和新列创建转换表

Question

我是新来发布表格视图，所以我将尝试解释我的数据的外观：我有客户 ID、订单 ID、销售日期、购买的产品以及产品的价格。我们销售 3 种产品：K、N 和 E。每行显示客户购买的产品及其价格。客户可以在同一订单中多次购买同一产品，也可以购买其他产品。下面我试图汇总每个产品的价格，这样最后我将为每个客户和订单创建一个列，并创建新的价格列。

目前正在编写 CTE：

with N as (select Customer_ID, Order_ID, Sales_Date,
       sum(Price)
from orders
group by 1,2,3
where product = 'N'),
K as (select Customer_ID, Order_ID, Sales_Date,
       sum(Price)
from orders
group by 1,2,3
where product = 'K'),
E as (select Customer_ID, Order_ID, Sales_Date,
       sum(Price)
from orders
group by 1,2,3
where product = 'E')
select N.*,
K.Price as K_Price,
E.Price as E_Price
from N as N
left join K as K on K.Customer_ID=N.Customer_ID
left join E as E on E.Customer_ID=N.Customer_ID

有没有更有效的方法来做到这一点？如果产品选项从 3 增加到 20 - 我将有 20 个 CTE，也许以不同的方式编写查询会更好？

标签： sqlpostgresqlaggregateaggregate-functionswhere-clause

Answer 1

您可以使用条件聚合：

select Customer_ID, Order_ID, Sales_Date,
       sum(price) filter (where product = 'N') as n_price,
       sum(price) filter (where product = 'K') as k_price,
       sum(price) filter (where product = 'E') as e_price
from orders o
group by Customer_ID, Order_ID, Sales_Date;