Hive SQL中的列转行(lateral view与explode)、行转列

avatar
作者
筋斗云
阅读量:1

一、列转行

1、相关函数

列转行:将某列一行中的数据拆分成多行

1)Explode炸裂函数

将hive某列一行中复杂的 array 或 map 结构拆分成多行(只能输入array或map)

语法:
select explode(字段) as 字段命名 from 表名;
举例:

explode(array)使得结果中将array列表里的每个元素生成一行

select(array('1','2','3')) 

explode(map)使得结果中将map里的每一对元素作为一行,key为一列,value为一列

select explode(map('A','1','B','2','C','3'))

局限性:

1、不能关联原有的表中的其他字段

2、不能与group by、cluster by、distribute by、sort by联用

3、不能进行UDTF嵌套

2)posexplode()函数

explode():对一列进行炸裂可以使用

posexplode():对两列都进行多行转换,可以将index和数据都取出来,使用两次posexplode并令两次取到的index相等即可

举例:
select posexplode(collect_set('AA'))
posval
0AA

collect_set:将某字段进行去重处理,返回array类型

3)Lateral View

Lateral View配合 split, explode 等UDTF函数一起使用,它能够将一列数据拆成多行数据,并且对拆分后结果进行聚合,即将多行结果组合成一个支持别名的虚拟表。相当于拆出一张虚拟表,与原表进行关联。

语法:
select o.*, table_view.new_col from table_origin o lateral view UDTF(expression) table_view as new_col_1, new_col_2

lateral view:表示将UDTF分裂的字段放在虚拟表中, 然后和主表table_origin进行关联。
UDTF(expression):使用的UDTF函数,例如explode()
table_view : 对应的虚拟表的表名
new_col: 虚拟表里存放的有效字段(可多个)
注意:

  • lateral view的位置是from后where条件前
  • 生成的虚拟表的表名不可省略
  • from后可带多个lateral view
  • 如果要拆分的字段有null值,需要使用lateral view outer 替代,避免数据缺失

2、案例

例子1-explode+split

假设有如下movies表,字段名分别为movie(string)category(array<string>)

转换为

select movie,category_name from  movies  lateral view explode(category) table_tmp as category_name;  -- 结果: --《疑犯追踪》	悬疑 --《疑犯追踪》	动作 --《疑犯追踪》	科幻 --《疑犯追踪》	剧情 --《海豹突击队》	动作 -- ...

注:explode函数输入了一个string类型的参数,搭配split()函数

 例子2-explode+split

 转换为

select col1, col2, col5 from test lateral view explode(split(col3,','))  b AS col5 -- split(col3,",")相对字符串切割,得到数组

注:explode函数输入了一个string类型的参数,搭配split()函数

例子3-explode+split

select uid_split, game  from (  	select uid,game 	from user_game 	lateral view explode(split(game_list,",")) tmpTable as game ) a lateral view explode(split(uid, ",")) m as uid_split

 例子4-多列炸裂

1)创建测试表,插入数据

CREATE table student_score( 	stu_id string comment '学号', 	stu_name string comment '姓名', 	courses string comment '各个科目', 	scores string comment '各个分数' ) comment '学生成绩表';
insert into student_score values  ("1001", "张三","语文,数学,英语,历史,地理", "88,87,94,86,84"), ("1002", "李四", "语文,数学,英语,历史,地理", "78,89,75,79,68"), ("1003", "王五", "语文,数学,英语,历史,地理", "98,97,91,93,92"), ("1004", "朱六", "语文,数学,英语,历史,地理", "66,63,64,67,68");

 2)测试explode 行转列

select a.stu_id, a.stu_name, table_view.course  from student_score a  lateral view explode(split(courses, ',')) table_view as `course`;

 

3)查询每个学生课程对应的分数,使用posexplode函数 

先测试使用explode, 看看效果:

select a.stu_id, a.stu_name,      table_view1.course, table_view2.score from student_score a  lateral view explode(split(courses, ',')) table_view1 as `course`  lateral view explode(split(scores, ',')) table_view2 as `score`;

结果:

出现这种情况是因为两个并列的explode的sql没办法识别每个科目对应的成绩是多少,对于多个数组的行转列可以使用posexplode函数。
例如使用如下查询语句:

select stu_id, stu_name, course, score  from student_score  lateral view posexplode(split(courses, ',')) table_view1 as a, course  lateral view posexplode(split(scores, ',')) table_view2 as b, score  where a = b;

 例子5-posexplode

转换为

一次posexplode

select id,tim,single_id_index,single_id  from test.a  lateral view posexplode(split(id,',')) t as single_id_index, single_id;d;

 结果

single_id_index		single_id 0					a 1					b 2					c 3					d 0					f 1					b 2					c 3					d

两次posexplode+where筛选

select  	id,tim,single_id,single_tim  from  	test.a  	lateral view posexplode(split(id,',')) t as single_id_index, single_id 	lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim where  	single_id_index = single_yim_index;

例子6-explode+map

select column1, column2, column3, m_key, m_val from     (select column1, column2, column3, map("X1", X1, "X2", X2, "X3", X3, "X4", X4) as map1     from table1) as t1 lateral view explode(map1) xyz as m_key, m_val 

例子7-explode+str_to_map

转换为

select table_4.name, a.item, a.score from table_4 lateral view explode( str_to_map(concat('math=',math,'&english=',english),'&','=') ) a as item,score;

解析:首先使用str_to_map函数将math字段与english字段拼接后的结果转换为map类型,然后通过侧视图和explode函数将其爆炸开,给生成的临时侧视图一个名字,取名a并给列名取名为item,score,因为explode(map)爆炸的结果是每一个item为行,key为1列,value为1列,这样就恰好形成我们想要的结果。

二、行专列

1、相关函数

行转列:将多个列中的数据在一列中输出

1)Concat

concat(string1/col, string2/col, …)

输入任意个字符串(或字段,可以为int类型等),返回拼接后的结果

select concat(id,'-',name,'-',age)  from student;

2)Concat_ws

concat_ws(separator, str1, str2, …)

特殊形式的 concat(),参数只能为字符串,第一个参数为后面参数的分隔符。分隔符可以是与后面参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-', name, gender)  from student;

3)Collect_set(聚合,返回数组类型)

collect_set(col)

将某字段进行去重处理,返回array类型;该函数只接受基本数据类型

select collect_set(age)  from student;

collect_set 与 collect_list 的区别就是set去重,list不去重

4)case when

case when <expr> then <result>…else <default> end if(expr, true_result, false_result)

case when 语句是SQL中的一个非常重要的功能,可以完成很多复杂的计算,相当于一个表达式,可以放在任何可放表达式的地方。

2、案例

例子1

 转换为

select     t1.base,     concat_ws('|', collect_set(t1.name)) as name from     (select name,concat(constellation, ",", blood_type) as base     from person_info) as t1 group by     t1.base;

例子2

转换为

 

select  	stu_name, 	concat_ws(',',collect_set(course)) as course, 	concat_ws(',',collect_set(score)) as score from student group by stu_name

 例子3

转换为

Select name, sum(case when item=数学 then score end) as math, sum(case when item=英语 then score end) as english, From table Group by name

解析:首先写出select name from table group by name, 因为select后有几个字段,最终输出就是几个字段,所以我们需要把目标数据的”math”和“english”两个字段想办法得出来。

之后可以对item字段所有枚举的结果进行case when判断,将score填值进入,因为最后我们需要对name做一下聚合,需要明确的是一般选取字段一定要出现在groupby里面。

聚合函数可以不用,所以我们在外面套一层sum做聚合,这样得到sum的结果和单人得分结果是一致的,因为我们以name做了一遍聚合,而每个用户对一门课程只有一个成绩,所以这样就可以得到最终结果。

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!