如何使用多(我需要2)列作为postgresql交叉表的分组列?

mv1qrgav  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(360)

我想在postgress中构造一个交叉表,它有两列用于分组,inst和date,事件用于分类。

From:
inst    date    event       times
inst1   jan01   high_cpu    5
inst1   jan01   high_io     3
inst1   jan01   net_slow    4
inst1   jan02   high_cpu    6
inst1   jan02   high_io     2
inst1   jan02   net_slow    3
inst2   jan01   high_cpu    2
inst2   jan01   high_io     2
inst2   jan01   net_slow    5
inst2   jan02   high_cpu    3
inst2   jan02   high_io     6
inst2   jan02   net_slow    1

Want output like this:
inst    date    high_cpu    high_io     net_slow
inst1   jan01   5           3           4
inst1   jan02   6           2           3
inst2   jan01   2           2           5
inst2   jan02   3           6           1

如何构造postgressql代码,以便我同时拥有inst和date列进行分组?我查了一下文件,好像主要是说单分组栏,谢谢。

cvxl0en2

cvxl0en21#

最简单的方法是使用筛选聚合:

select inst, "date",
       sum(times) filter (where event = 'high_cpu') as high_cpu,
       sum(times) filter (where event = 'high_io') as high_io,
       sum(times) filter (where event = 'net_slow') as net_slow
from the_table
group by inst, "date"
order by inst, "date"

相关问题