Presto-数据透视表

Question

嗨，我有一张这样的表：

Session id  Property
1           Radar
2           Bullet
1           Bullet
3           Radar
2           Price

我想把它转换成这样：

Radar   Bullet   Price
1       1
2        2
3

Answer 1

对于固定的属性列表，可以进行条件聚合：

select session_id,
max(case when property = 'Radar'  then 1 else 0 end) as radar,
max(case when property = 'Bullet' then 1 else 0 end) as bullet,
max(case when property = 'Price'  then 1 else 0 end) as price
from mytable
group by session_id

这将会话id放在第一列，0/1值放在每列，具体取决于给定会话是否拥有给定属性。

要生成您显示的确切输出(可能比上面的帮助小一些(，您可以执行以下操作：

select 
case when max(case when property = 'Radar'  then 1 else 0 end) = 1 then session_id end as radar,
case when max(case when property = 'Bullet' then 1 else 0 end) = 1 then session_id end as bullet,
case when max(case when property = 'Price'  then 1 else 0 end) = 1 then session_id end as price 
from mytable
group by session_id