1. 多行转多列
原表test表存储格式如下,希望进行优化
name | subject | score |
---|---|---|
张三 | 语文 | 90 |
张三 | 数学 | 85 |
张三 | 英语 | 92 |
李四 | 语文 | 75 |
李四 | 数学 | 90 |
李四 | 英语 | 80 |
王五 | 语文 | 95 |
王五 | 数学 | 100 |
王五 | 英语 | 98 |
name | Chinese | Math | English |
---|---|---|---|
张三 | 90 | 85 | 92 |
李四 | 75 | 90 | 80 |
王五 | 95 | 100 | 98 |
方法一:利用str_to_map函数
select name
,info['语文'] as Chinese
,info['数学'] as Math
,info['英语'] as English
from (select name,str_to_map(concat_ws(',',collect_set(concat_ws(':',subject,cast(score as string))))) as info
from test
group by name
) a
方法二:利用case when函数
select name
,max(case when subject = '语文' then score else 0 end) as Chinese
,max(case when subject = '数学' then score else 0 end) as Math
,max(case when subject = '英语' then score else 0 end) as English
from test
group by name
2. 多列转多行
在数据可视化时如果表很宽很短会显得不那么好看,这个时候可以将数据转成瘦长的格式
date | uv | newuv | video | newvideo | vv | vip_num | new_vip_num |
---|---|---|---|---|---|---|---|
2019-05-10 | 5000000 | 200000 | 3000000 | 10000 | 20000000 | 500000 | 80000 |
date | label | value |
---|---|---|
2019-05-10 | UV | 5000000 |
2019-05-10 | 新增UV | 200000 |
2019-05-10 | 视频存量 | 3000000 |
2019-05-10 | 新增视频 | 10000 |
2019-05-10 | 播放量 | 20000000 |
2019-05-10 | 会员数 | 500000 |
2019-05-10 | 新增会员数 | 80000 |
实现方法:
select a.date
,b.label
,b.value
from (select *
from daily_report
) a
LATERAL VIEW explode (map(
'UV', uv
,'新增UV', newuv
,'视频存量', video
,'新增视频', newvideo
,'播放量', vv
,'会员数', vip_num
,'新增会员数', new_vip_num
)) b as label, value