行转列
select dt_month,valid_num,unvalid_num from test.test_xw_rowtocol
数据如下
转换要求:行转列需保留列名,如下图
方式一:采用union all的形式
select
dt_month
,'valid_num' as type
,sum(valid_num) as num
from temp.temp_xw_rowtocol
group by dt_month
union all
select
dt_month
,'unvalid_num' as type
,sum(unvalid_num) as num
from temp.temp_xw_rowtocol
group by dt_month
方式二:使用lateral view和str_to_map
select
a.dt_month
,add_t.type
,add_t.num
from temp.temp_xw_rowtocol a
lateral view explode(str_to_map(concat('valid_num=',valid_num
,'&unvalid_num=',unvalid_num
),'&','='
)
) add_t as type,num
方式三:思路和方式二一致,稍微简单点,缺点只能含explode函数(列名称默认key和value也可自定义)
select
explode(str_to_map(concat('valid_num=',valid_num
,'&unvalid_num=',unvalid_num
),'&','='
)
)
from temp.temp_xw_rowtocol a
;
ps:方式一在指标少时方便使用,当行转列的指标比较多时代码量会比较大,维护困难
方式二极大的减少重复的代码量,需要熟悉lateral view和str_to_map,concat等函数使用,concat主要拼接key-value形式存储的字符串,如在一个项目开发中使用一个sql处理出十多个指标,后期需要列存储各指标值时,使用方式二就可以使代码减少数10倍,极大提升代码可读性和可维护性,同时key值可以自定义。
列转行
通过group by
select
a.dt_month
,sum(case when type = 'valid_num' then num end) as valid_num
,sum(case when type = 'unvalid_num' then num end) as unvalid_num
from temp.temp_xw_coltorow a
group by a.dt_month
具体HIVE函数参考官方WIKI: 点击打开链接