如何转换结果？

txu3uszq 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(299)

我有这个问题

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   |

有人能帮忙吗？

sql presto transpose

来源：https://stackoverflow.com/questions/63242447/how-to-transpose-result

1条答案

按热度按时间

55ooxyrt1#

如果我理解了这个问题，您需要取消数据剥离并重新聚合：

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

普雷斯托还支持 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

赞(0）回复(0）举报 2021-07-24

我来回答

如何转换结果？

1条答案

相关问题

热门标签

最新问答