HIVE数据透视和计数

2024-05-19 • 问答

我有一张桌子，我试图找出如何根据这些数据进行旋转和计数。这个例子可能不太合适，但是结果正是我想要的。

示例输入：

name |chinese|math|english
tom  |A      |A   |B
tom  |B      |A   |C
peter|B      |C   |C
peter|A      |B   |C

示例输出：

name |object |A|B|C
tom  |chinese|1|1|0
tom  |math   |2|0|0
tom  |english|0|1|1
peter|chinese|1|1|0
peter|math   |0|1|1
peter|english|0|0|2

结合使用UNION ALL。

演示：

with your_table as (
select stack(4,'tom','A','B','C','peter','C'
) as (name,chinese,math,english)
)

select name,'chinese' as object,count(case when chinese='A' then 1 end) as A,count(case when chinese='B' then 1 end) as B,count(case when chinese='C' then 1 end) as C
  from your_table 
 group by name
    UNION ALL 
select name,'math' as object,count(case when math='A' then 1 end) as A,count(case when math='B' then 1 end) as B,count(case when math='C' then 1 end) as C
  from your_table 
 group by name
    UNION ALL 
select name,'english' as object,count(case when english='A' then 1 end) as A,count(case when english='B' then 1 end) as B,count(case when english='C' then 1 end) as C
  from your_table 
 group by name;

结果：

name    object  a       b       c
peter   chinese 1       1       0
tom     chinese 1       1       0
peter   math    0       1       1
tom     math    2       0       0
peter   english 0       0       2
tom     english 0       1       1

HIVE数据透视和计数

baihe703 回答：HIVE数据透视和计数

大家都在问