如何通过聚合在Hive中透视数据 [英] How to pivot data in Hive with aggregation

问题描述

我有一个像下面这样的表数据，我想用聚合来透视数据.

I have a table data like below and I want to pivot the data with aggregation .

ColumnA    ColumnB            ColumnC
1          complete            Yes
1          complete            Yes
2          In progress         No
2          In progress         No 
3          Not yet started     initiate 
3          Not yet started     initiate 

想像下面一样

ColumnA          Complete    In progress     Not yet started
1                 2               0                0
2                 0               2                0
3                 0               0                2

无论如何，我们可以在蜂巢或I​​mpala中实现这一目标吗?

Is there anyway that we can achieve this in hive or Impala?

推荐答案

在sum聚合中使用case:

select ColumnA,    
       sum(case when ColumnB='complete'        then 1 else 0 end) as Complete,
       sum(case when ColumnB='In progress'     then 1 else 0 end) as In_progress,
       sum(case when ColumnB='Not yet started' then 1 else 0 end) as Not_yet_started
  from table
 group by ColumnA
 order by ColumnA --remove if order is not necessary
;

这篇关于如何通过聚合在Hive中透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！