[hive sql] 实现pivot 行转列的两个例子

问题1：将表1改成表2的形式
表1：

uid	item	cnt
1234	A	2
1234	B	1
1236	A	4

表2：

uid	A	B
1234	2	1
1236	4	–

代码如下：

select uid, sum(item_cnt['A']) as A, sum(item_cnt['B']) as B
from(
select uid, map(item, cnt) as item_cnt
from table1
)t
group by uid;

问题2： 将表3改成表2的形式
表3：
|uid|item |
|–|–|–|
|1234 |A |
|1234 |A |
|1234|B|
|1236|A|
|1236|A|
|1236|A|
|1236|A|

错误示范代码【雷坑】：
(也不算雷就是了 但是折磨了我好久（生产环境中比这个例子看上去更复杂整个人头都晕了但本质是一样的

select uid, item_list['A'] as A_cnt, item_list['B'] as B_cnt
from (
select uid, map(item, count(1)) as item_list
from table3
group by uid) t3
order by uid;

这样出来的结果会是

uid	A	B
1234	2	–
1234	–	1
1236	4	–

就很明显不对头啊， 这时候要做的就是加上sum，再一次group by，顺便再补个0
完整正确代码如下：

select uid, sum(nvl(item_list['A'],0)) as A_cnt, sum(nvl(item_list['B'],0)) as B_cnt
from (
select uid, map(item, count(1)) as item_list
from table3
group by uid) t3
group by uid
order by uid;

以上