行转列
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
=======================
select t2.name,
t3.subject,
t3.score
from (
select name,
max(case when subject = 'a' then score else 0 end) a,
max(case when subject = 'b' then score else 0 end) b
from (
select 'zhangsan' name,
'a' subject,
100 score
union all select 'zhangsan' name,
'b' subject,
90 score
union all select 'lisi' name,
'a' subject,
95 score
union all select 'lisi' name,
'b' subject,
85 score
)t1
group by name
)t2 lateral view explode(str_to_map(concat('a=',a,'&b=',b),'&','=')) t3 as subject,
score