HiveSQL题——炸裂函数(explode/posexplode)

目录

一、炸裂函数的知识点

1.1 炸裂函数

 explode 

posexplode

1.2 lateral view 侧写视图

二、实际案例

2.1 每个学生及其成绩

0 问题描述

1 数据准备

2 数据分析

3 小结

2.2 日期交叉问题

0 问题描述

1 数据准备

2 数据分析

3 小结

2.3 用户消费金额

0 问题描述

1 数据准备

2 数据分析

3 小结

---

一、炸裂函数的知识点

           炸裂函数（一行变多行）本质属于UDTF函数（接收一行数据，输出一行或者多行数据）。

1.1 炸裂函数

•  explode 

 (1)explode(array<T> a) --> explode针对数组进行炸裂
    语法：lateral view explode(split(a,',')) tmp  as new_column
    返回值：string
    说明:按照分隔符切割字符串，并将数组中内容炸裂成多行字符串
    举例：select student_score from test lateral view explode(split(student_score,',')) tmp as item; 输出结果为：
      student_score        item
      [a,b,c]        =>     a
                            b
                            c
               
 (2)explode(map<k,v> m) --> explode针对map键值对进行炸裂
    举例：select explode(map('a',1,'b',2,'c',3)) as (key,value); 输出结果为：
    得到                 key value
      {a:1,b:2,c:3} =>   a   1
                         b   2
                         c   3

• posexplode

posexplode和explode之间的区别：posexplode除了返回数据，还会返回该值的下角标。

 (1)posexplode(array<T> a) 
    语法：lateral view posexploed(split(a,',')) tmp as pos,item 
    返回值：string
    说明:按照分隔符切割字符串，并将数组中内容炸裂成多行字符串(炸裂具备下角标 0,1,2,3)
    举例1：select posexplode (array('a','b','c')) as pos,item; 输出结果为：
                  pos  item
      [a,b,c] =>   0     a
                   1     b
                   2     c
    ---------------------------------
    举例2：对student_name进行炸裂，同时也对student_score进行炸裂，且需要保证炸裂后，学生和成绩一一对应，不能错乱。
   lateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_name
   lateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_score
  where student_name_index = student_score_index;

1.2 lateral view 侧写视图

官网链接：LanguageManual LateralView - Apache Hive - Apache Software Foundation

• 定义：lateral view 通常与UDTF配合使用，侧视图的原理是将UDTF的结果构建成一个类似于视图的表，再将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。
• 举例：select id, name,  hobbies, hobby  from   person  lateral view explode(hobbies) tmp as hobby;  代码分析： 对原表person中的hobbies列进行炸裂（一行变多行），利用侧视图lateral view对该UDTF产生的记录设置字段名称为hobby, 再将原表中person的一每行与hobby进行连接形成一个虚拟表，命名为tmp。
• 注意：使用lateral view时侧写视图时，可以对UDTF产生的记录设置字段名称，上述例子为hobby，产生的hobby字