hive 列转行，纵表转横表

hive 中行列转换一般选择爆炸函数explode和侧视图lateral view搭配使用

--建表
create database test_db1;
use test_db1;
drop table test;
create table test(
    hour int,
    uv int,
    pv int,
    ip int
)
row format delimited fields terminated by ',';

map函数使用

map()会将传入的值或字段转换为key:value形式

第一步使用map()函数将uv、pv、ip 转为键值对

select map('uv',uv,'pv',pv,'ip',ip) from test;

运行结果

第二步使用侧视图搭配爆炸函数把键值对炸开

select hour,type,num
from test
lateral view explode(map('uv',uv,'pv',pv,'ip',ip)) b as type,num;

第三步：分组聚合

select metrice,
       max(if(hour = 0, num, 0)) as `1`,
       max(if(hour = 1, num, 0)) as `2`,
       max(if(hour = 2, num, 0)) as `3`,
       max(if(hour = 3, num, 0)) as `4`,
       max(if(hour = 4, num, 0)) as `5`
from test
         lateral view explode(map('uv', uv, 'pv', pv, 'ip', ip)) b as metrice, num
group by metrice;

运行结果