旋转轴列Postgresql

Question

我有一个表，看起来像这样:

username     item_name   total_units_per_username    
[e-mail]   laptop      2
[e-mail]   watch       2
[e-mail]  phone        3
[e-mail]  laptop       3
[e-mail]  watch        3

我想要的是一个像这样的表:

total_units_per_username    item_name               frequency
3                        phone, laptop, watch      1

本质上，我想透视item_name列，将total_units_per_username上的所有值连接起来，并计算出现的频率。我用雪花。

谢谢!

Answer 1

我认为你需要两个聚合:

select numitems, items, count(*) as freq
from (select username,
             string_agg(item_name order by item_name, ', ') as items,
             count(*) as numitems
      from t
      group by username
     ) t
group by numitems, items;

编辑:

您也可以使用array_agg():

执行此操作。

select numitems, items, count(*) as freq
from (select username,
             array_agg(item_name order by item_name) as items,
             count(*) as numitems
      from t
      group by username
     ) t
group by numitems, items;