如何在《雪花》中实现多重聚合的枢纽 [英] How to pivot multiple aggregation in Snowflake

问题描述

我的表结构如下

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;

这篇关于如何在《雪花》中实现多重聚合的枢纽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！