平时很少用到map类型,这次用到了记录一下。
举个栗子:
select
mid,
tags['电影'] as movie,
tags['音乐'] as music
from(
select
mid,str_to_map(concat_ws(',',collect_list(concat(key,":",value)))) as tags
from (
select
mid,
key,
concat_ws('-',collect_list(value))as value
from
(
select 1 as mid,'电影' as key,'惊悚' as value
union ALL
select 1 as mid,'音乐' as key,'摇滚' as value
union ALL
select 1 as mid,'电影' as key,'喜剧' as value
) a
group by mid,key)t
group by mid) tt
构造数据:
mid |
key |
value |
1 |
电影 |
惊悚 |
1 |
音乐 |
摇滚 |
1 |
电影 |
喜剧 |
运行结果:
mid |
movie |
music |
1 |
惊悚-喜剧 |
摇滚 |
适用场景:行转列,且mid的key个数可能不一样的情况。