hive sql 行转列&列转行

数据的存储有时候存在一个主键对应多行数据记录的情况，如果我们想把数据做行转列（合并）操作，就可以使用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
仅供学习用，如有侵权请联系删除。