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

建表语句

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

复制代码

导入数据

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

复制代码

先看需求：
原表数据格式为

需要将其转化格式为
 
直接上Hql

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

复制代码

 
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

继续阅读请点击广告