hive sql 行列转换 hive行转列列转行laterview

文章目录

• 一、行转列

• 0.前置
• syntax

• {1}explode，posexplode
• {2}lateral view、Multiple Lateral Views、LATERAL VIEW OUTER

• 1.case：

• {1}基本
• {2}配合正则处理无法简单使用split的列

• 二、列转行

• 1.使用concat_ws和collect嵌套

• {0}前置
• collect_list和collect_set
• concat_ws、group_concat()
• {1}case1：基本
• {2}case2：去重
• {3}case3：将分组且为数组的字段通过select查出，类似于group_concat
• {4}case4：collect_set()和regexp()结合使用

• 2.使用case when实现

一、行转列

0.前置

简单说就是一行变多行。一变多的依据就是将其中一单元格的数据分割为多份，然后其他列的数据重复多份。
explode是爆炸的意思，会把一个数组或者map转为一列多行，也就是把一个数组转为表，但这样只有在只查一个explode列的情况下才行，如果有多个explode列就会报错，所以使用lateral view。
又因为lateral view + explode是笛卡尔积的形式，会翻倍数据量(abc)，所以加了个posexplode，相当于给每个虚拟表加了个主键，然后就可以用这个主键来关联，可以减少数据量，避免笛卡尔积。

syntax

使用2个函数 lateral view 和explode

lateral view explode(split(column_name, ‘,’)) new_column_name
lateral view explode（split表达式）tableName as columeName

tableName表示虚拟表的名称；columeName表示虚拟表的虚拟字段名称。
如果分列后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

该函数在SQL语言中位于from之后，where之前。

{1}explode，posexplode

explode用于将一个数组转为多行，是个UDTF
https://zhuanlan.zhihu.com/p/115918587

{2}lateral view、Multiple Lateral Views、LATERAL VIEW OUTER

lateral view用于将explode产生的多行数据转为虚拟表，然后跟原表笛卡尔积join，否则hive不知怎么处理这种数据。

般配合UDTF(explode，UDTF是入一出多，为每个输入行生成零个或多个输出行)。

1.case：

{1}基本

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

{2}配合正则处理无法简单使用split的列

现在要计算出成绩的平均值。Hive中并没有处理这种列表数据的函数，因此需要先将grade字段拆分成多行，每行代表一个分数。

同时grades无法直接使用split，因为有{}

select name，grade from table 
lateral view explode(split(regexp_replace(grades,'\\{|\\},''),',') t as grade

解释一下上述代码处理过程：
首先使用regexp_replace()函数将grades字段中所有的“{”和“}”替换为‘’，也就是删除的意思；
然后使用split函数将grades字段按照逗号进行分割，经过这两步，{90,75,90}就变成了90 75 90
接下来使用explode()函数将grades字段变成多行，再使用“lateral view" 将变成多行后的grade字段转为虚拟表t，与原表进行笛卡尔积运算(相乘)。
上述语句中的t可以被理解成只有一个字段的虚拟表，该字段为grades字段分成多行后的结果，并且将该字段重命名为“grade”。最后将虚拟表t与原表进行笛卡儿积运算。

最终的输出结果如图所示：

二、列转行

多行转一行

1.使用concat_ws和collect嵌套

{0}前置

hive有时存在一个重复键对应多行数据记录的情况，如果想把数据做列转行（多行转一行）操作，就可以使用collect_set()、collect_list()把多行的同一列给collect成一个数组，然后用concat_ws()连接成一个字符串。
一般后面再配合group by，不然会导致出现完全相同的行。

concat_ws(',', collect_set(column_name))

collect_list和collect_set

Hive中collect相关的函数有2个：collect_list和collect_set，它们都是将分组中的某列转为一个数组返回，所以需要用concat_ws转成字符串。
不同的是collect_list不去重而collect_set去重，column_name的数据类型要求是string

concat_ws、group_concat()

{1}case1：基本

--建表
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}case2：去重

--用户每天点播视频的记录
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;

{3}case3：将分组且为数组的字段通过select查出，类似于group_concat

还可以利用collect来突破group by查询的时候要求出现在select后面的分组列都必须是出现在group by后面的限制，即select分组列必须是作为分组依据的列，但是有的时候我们想根据字段A进行分组然后再把字段B里面的某个元素查出来
代入到这个实验中就是按照用户进行分组，然后拿出一个他看过的视频名称：

select username, collect_list(video_name)[0] from t_visit_video group by username;

video_name不是分组列，依然能够取出这列中的数据。

{4}case4：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

2.使用case when实现

(case 单元格值 when 旧列) as 新列

select 
    a,
    max(case when b="A" then c end) col_A,
    max(case when b="B" then c end) col_B
from t1
group by a;