问题描述
我的表结构如下
product_id 期间 销售额 利润 x1 L13 $100 $10 x1 L26 $200 $20 x1 L52 $300 $30 x2 L13 $500 $110 x2 L26 $600 $120 x2 L52 $700 $130我希望透视Period列,并在这些列中包含销售额和利润。我需要一张像下面这样的桌子。
product_id Sales_L13 Sales_L26 Sales_L52 利润_L13 利润_L26 利润_L52 x1 $100 $200 $300 $10 $20 $30 x2 $500 $600 $700 $110 $120 $130我正在使用雪花编写查询。我尝试使用Snowflake的pivot
函数,但在那里我只能指定一个聚合函数。
有谁可以帮助我了解如何实现此解决方案?
感谢任何帮助。
谢谢
推荐答案
我们先堆积销售和利润,然后再进行调整,怎么样?我会让您来修复我弄乱的列名。
with cte (product_id, period, amount) as
(select product_id, period||'_profit', profit from t
union all
select product_id, period||'_sales', sales from t)
select *
from cte
pivot(max(amount) for period in ('L13_sales','L26_sales','L52_sales','L13_profit','L26_profit','L52_profit'))
as p (product_id,L13_sales,L26_sales,L52_sales,L13_profit,L26_profit,L52_profit);
如果希望为销售额和利润两次透视期间,则需要复制该列,以便每个透视实例都有一个。显然,这将创建空值,因为在第一个透视之后,重复列仍然存在。为了处理这个问题,我们可以在最后的SELECT中使用max
。下面是该实现的外观
select product_id,
max(L13_sales) as L13_sales,
max(L26_sales) as L26_sales,
max(L52_sales) as L52_sales,
max(L13_profit) as L13_profit,
max(L26_profit) as L26_profit,
max(L52_profit) as L52_profit
from (select *, period as period2 from t) t
pivot(max(sales) for period in ('L13','L26','L52'))
pivot(max(profit) for period2 in ('L13','L26','L52'))
as p (product_id, L13_sales,L26_sales,L52_sales,L13_profit,L26_profit,L52_profit)
group by product_id;
在这一点上,它是一个眼睛腾空而起。您最好使用conditional aggregation
,或者更好地在报告应用程序中处理透视。更紧凑的替代方案conditional aggregation
使用decode
select product_id,
max(decode(period,'L13',sales)) as L13_sales,
max(decode(period,'L26',sales)) as L26_sales,
max(decode(period,'L52',sales)) as L52_sales,
max(decode(period,'L13',profit)) as L13_profit,
max(decode(period,'L26',profit)) as L26_profit,
max(decode(period,'L52',profit)) as L52_profit
from t
group by product_id;