数据的存储有时候存在一个主键对应多行数据记录的情况,如果我们想把数据做行转列(合并)操作,就可以使用collect_set()和concat_ws()函数嵌套,返回string。
concat_ws(',', collect_set(column_name))
仅仅使用collect_set(column_name)函数返回的是数组,见下边第3条说明。
说明:collect_set()去重,collect_list()不去重,column_name的数据类型要求是string
1、多行转列
create table students_info
(`sno` string comment '学生编号',
`name` string comment '姓名',
`depart` string comment '选修课程'
)
--学生信息表数据插入
insert into students_info values (103,'张三','公司法')
,(103,'张三','心理学')
,(105,'王五','python程序设计')
,(109,'李麻子','数据结构与算法')
,(109,'李麻子','机器学习');
select * from students_info
对数据进行行转列操作
select sno, name, concat_ws(',', collect_set(depart)) as depart from students_info
group by sno, name
2、列转多行
使用函数lateral view explode(split(column_name, ‘,’)) new_column_name
create table students_info
(`SNO` string comment '学生编号',
`name` string comment '姓名',
`DEPART` string comment '选修课程'
)
--成绩表数据插入
insert into students_info values (103,'张三','公司法,心理学')
,(105,'王五','python程序设计')
,(109,'李麻子','数据结构与算法,机器学习');
select * from students_info
对数据进行列转行操作
select sno, name, add_depart from students_info a
lateral view explode(split(a.depart, ',')) b as add_depart
3、多行转列之collect_list()和collect_set()
Hive中collect相关的函数有collect_list和collect_set,它们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重。
创建一张实验用表,存放用户每天点播视频的记录:
create table t_visit_video
(username string,
video_name string
) partitioned by (day string)
--学生信息表数据插入
insert into t_visit_video values ('张三','大唐双龙传')
,('张三','神探狄仁杰')
,('王五','机器人总动员')
,('王五','放牛班的春天')
,('王五','盗梦空间')
,('李四','天下无贼')
,('李四','霸王别姬')
,('李四','霸王别姬');
按用户分组,取出每个用户每天看过的所有视频的名字:
select username, collect_list(video_name) from t_visit_video group by username;
上面的查询结果存在视频名称重复情况,因为霸王别姬实在太好看了,所以李四这家伙看了两遍,这直接就导致得到的观看过视频列表有重复的,所以应该增加去重,使用collect_set,其与collect_list的区别就是会去重:
select username, collect_set(video_name) from t_visit_video group by username;
李四的观看记录中霸王别姬只出现了一次,实现了去重效果。
突破group by限制
还可以利用collect来突破group by的限制,Hive中在group by查询的时候要求出现在select后面的分组列都必须是出现在group by后面的,即select分组列必须是作为分组依据的列,但是有的时候我们想根据字段A进行分组然后随便取出每个分组中的一个字段B,代入到这个实验中就是按照用户进行分组,然后随便拿出一个他看过的视频名称即可:
select username, collect_list(video_name)[0] from t_visit_video group by username;
video_name不是分组列,依然能够取出这列中的数据。
实际案例:
collect_set()和regexp()结合使用
--在正则表达式中"|"表示“或”
select case when pro_code regexp '43|45|' then '白名单'
when pro_code regexp '44|46|56' then '非白名单'
else '商户付息' end as is_whitelist,
count(distinct a.partner_id) as store_number
from databasex.temp_partner_info a
left join (select partner_id, concat_ws(',',collect_set(market_pro_code)) as pro_code
from databasex.temp_partner_prod_map
where dt='${pdate}' group by partner_id) d
on a.partner_id=d.partner_id
where a.dt='${pdate}'
and a.partner_type='2'
group by case when pro_code regexp '43|45|' then '白名单'
when pro_code regexp '44|46|56' then '非白名单'
else '商户付息' end
内容参考链接:https://zhuanlan.zhihu.com/p/59351085,
https://www.cnblogs.com/kimbo/p/6208973.html,
https://www.cnblogs.com/cc11001100/p/9043946.html
仅供学习用,如有侵权请联系删除。