建表陳述句
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/518782.html
標籤：大數據
下一篇：MySQL資料庫-資料表（四）