Hive中Hql关于行转列及列转行的综合应用

建表语句

 create table user_tag_merge
 (   uid int,
     gender String,
     agegroup String,
     favor String
 )
 row format delimited  fields terminated by '\t'

导入数据

 insert into user_tag_merge values(1,'M','90后','sm');
 insert into user_tag_merge values(2,'M','70后','sj');
 insert into user_tag_merge values(3,'M','90后','ms');
 insert into user_tag_merge values(4,'F','80后','sj');
 insert into user_tag_merge values(5,'F','90后','ms');

先看需求：

原表数据格式为

需要将其转化格式为

 

直接上Hql

 --先将每行粒度增粗，进行拼接
 select
     uid,
     --str_to_map可以将一个固定格式的字符串转化为map类型
     str_to_map(concat("gender",":",gender,",","agegroup",":",agegroup,",","favor",":",favor)) tagMap
 from user_tag_merge;
 --在作为子集进行侧窗炸裂,然后根据两列进行分组
 select
     col1 tag_code,
     clo2 tag_value,
     collect_set(uid)
 from(
         select
             uid,
             --将拼接的字符串转化为map
             str_to_map(concat("gender",":",gender,",","agegroup",":",agegroup,",","favor",":",favor)) tagMap
         from user_tag_merge
 ​
     )t lateral view explode(tagMap) tmp as col1,clo2
 group by col1, clo2