如何转换结果

Question

我有这个查询

Select week(date_time) week,
sum(X1) as X1,
sum(X2) as X2,
sum(X3) as X3,
sum(X4) as X4
From Fee
Where date_time between '2020-07-01' and current_date

我得到的结果如下：

week | X1 | X2 | X3 | X4
27   |
28   |
29   |
30   |
31   |

但我想将结果转换为：

week | 27 | 28 | 29 | 30 | 31
X1   |
X2   |
X3   |
X4   |

有人能帮忙吗？

Answer 1

如果我理解这个问题，你想取消数据透视并重新聚合：

select v.x,
sum(case when week(date_time) = 27 then 1 else 0 end) as week_27,
sum(case when week(date_time) = 28 then 1 else 0 end) as week_28,
sum(case when week(date_time) = 29 then 1 else 0 end) as week_29,
sum(case when week(date_time) = 30 then 1 else 0 end) as week_30,
sum(case when week(date_time) = 31 then 1 else 0 end) as week_31
from fee f left join lateral
(values (f.x1), (f.x2), (f.x3), (f.x4)) v(x)
group by v.x

Presto也支持filter，所以最好写成：

select v.x,
count(*) filter (where week(date_time) = 27) as week_27,
count(*) filter (where week(date_time) = 28) as week_28,
count(*) filter (where week(date_time) = 29) as week_29,
count(*) filter (where week(date_time) = 30) as week_30,
count(*) filter (where week(date_time) = 31) as week_31
from fee f left join lateral
(values (f.x1), (f.x2), (f.x3), (f.x4)) v(x)
group by v.x