当前位置:   article > 正文

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.  (1)explode(array<T> a) --> explode针对数组进行炸裂
  2.     语法:lateral view explode(split(a,',')) tmp  as new_column
  3.     返回值:string
  4.     说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串
  5.     举例:select student_score from test lateral view explode(split(student_score,',')) tmp as item; 输出结果为:
  6.       student_score        item
  7.       [a,b,c]        =>     a
  8.                             b
  9.                             c
  10.                
  11.  (2)explode(map<k,v> m) --> explode针对map键值对进行炸裂
  12.     举例:select explode(map('a',1,'b',2,'c',3)) as (key,value); 输出结果为:
  13.     得到                 key value
  14.       {a:1,b:2,c:3} =>   a   1
  15.                          b   2
  16.                          c   3
  • posexplode

  1. posexplode和explode之间的区别:posexplode除了返回数据,还会返回该值的下角标。
  2.  (1)posexplode(array<T> a)
  3.     语法:lateral view posexploed(split(a,',')) tmp as pos,item 
  4.     返回值:string
  5.     说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串(炸裂具备下角标 0,1,2,3)
  6.     举例1select posexplode (array('a','b','c')) as pos,item; 输出结果为:
  7.                   pos  item
  8.       [a,b,c] =>   0     a
  9.                    1     b
  10.                    2     c
  11.     ---------------------------------
  12.     举例2:对student_name进行炸裂,同时也对student_score进行炸裂,且需要保证炸裂后,学生和成绩一一对应,不能错乱。
  13.    lateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_name
  14.    lateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_score
  15. 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字段可以用于group by、order by 、limit等语句中,不需要再单独嵌套一层子查询

二、实际案例

2.1 每个学生及其成绩

0 问题描述

   根据学生成绩表,计算学生的成绩。

1 数据准备

  1. create table if not exists table10
  2. (
  3. class string comment '班级名称',
  4. student string comment '学生名称',
  5. score string comment '学生分数'
  6. )
  7. comment '学生成绩表';
  8. INSERT overwrite table table10
  9. VALUES ("1班","小A,小B,小C","80,92,70"),
  10. ("2班","小D,小E","88,62"),
  11. ("3班","小F,小G,小H","90,97,85");

2 数据分析

思路一:lateral view + explode

  1. select
  2. class,
  3. student,
  4. score,
  5. student_name,
  6. student_score
  7. from table10 lateral view explode(split(student, ',')) tmp1 as student_name
  8. lateral view explode(split(score, ',')) tmp2 as student_score;

  bug:上面逻辑能跑通,但是学生姓名和学生成绩对应不上,出现错乱,弃用。

 正确的代码如下:

  思路二: lateral view + posexplode

  1. select
  2. class,
  3. student,
  4. score,
  5. student_name,
  6. student_score
  7. from table10 lateral view posexplode(split(student, ',')) tmp3 as student_index_st, student_name
  8. lateral view posexplode(split(score, ',')) tmp4 as student_index_sc, student_score
  9. where student_index_st = student_index_sc;

 说明:student_index_st = student_index_sc 的作用:下角标对齐,实现学生和成绩一一对应

3 小结

   上述案例的学生成绩表中,【学生姓名】字段和【学生成绩】都是数组类型的字符串,我们需要对两个字段分别炸裂后,实现每个学生与其成绩一一对应,因此需要借助posexlode函数的pos下角标进行约束。(用explode函数无法实现)

2.2 日期交叉问题

0 问题描述

   统计每个品牌的总营销天数(营销日期有重叠的地方需要去重

1 数据准备

  1. create table promotion_info
  2. (
  3. promotion_id string comment '优惠活动id',
  4. brand string comment '优惠品牌',
  5. start_date string comment '优惠活动开始日期',
  6. end_date string comment '优惠活动结束日期'
  7. ) comment '各品牌活动周期表';
  8. insert overwrite table promotion_info
  9. values (1, 'oppo', '2021-06-05', '2021-06-09'),
  10. (2, 'oppo', '2021-06-11', '2021-06-21'),
  11. (3, 'vivo', '2021-06-05', '2021-06-15'),
  12. (4, 'vivo', '2021-06-09', '2021-06-21'),
  13. (5, 'redmi', '2021-06-05', '2021-06-21'),
  14. (6, 'redmi', '2021-06-09', '2021-06-15'),
  15. (7, 'redmi', '2021-06-17', '2021-06-26'),
  16. (8, 'huawei', '2021-06-05', '2021-06-26'),
  17. (9, 'huawei', '2021-06-09', '2021-06-15'),
  18. (10, 'huawei', '2021-06-17', '2021-06-21');

2 数据分析

思路一:用带有下标的炸裂函数posexplode将活动区间炸裂成具体的每一天的日期。即:将同一个品牌的所有活动日期都有列出来,再对重叠的日期进行统一去重

  1. select brand,
  2. count(distinct event_date)
  3. from
  4. (
  5. select
  6. promotion_id,
  7. brand,
  8. start_date,
  9. -- 用 start_date + 下角标pos
  10. date_add(start_date,pos) as event_date,
  11. pos
  12. from (
  13. select
  14. promotion_id,
  15. brand,
  16. start_date,
  17. end_date,
  18. split(space(datediff(end_date, start_date)), '') as ar
  19. from promotion_info
  20. ) tmp1
  21. lateral view posexplode(ar) tmp2 as pos, item
  22. )tmp2
  23. group by brand;

    思路一的代码拆解分析:

  1. 以一条数据为例,
  2. promotion_id brand start_date end_date
  3. 1 'oppo' '2021-06-05' '2021-06-09'
  4. 1) split(space(datediff(end_date, start_date)), '') as diff 的结果:
  5. 根据[9-5]=4,利用space函数生成长度是4的空格字符串,再利用split函数切割
  6. 1 (promotion_id) , 'oppo'(brand) , '2021-06-05'(start_date) ,'2021-06-09'(end_date)
  7. , diff ["","","","",""]
  8. 2)用posexplode经过转换增加行(列转行,炸裂),通过下角标pos来获取 event_date,
  9. 根据数组["","","","",""],得到pos的取值是0,1,2,3,4
  10. 炸裂得出下面五行数据(一行变五行)
  11. 1,oppo,2021-06-05(start_date),2021-06-05= date_add(2021-06-05,0) (event_date= start_date+pos)
  12. 1,oppo,2021-06-05(start_date),2021-06-06= date_add(2021-06-05,1) (event_date= start_date+pos)
  13. 1,oppo,2021-06-05(start_date),2021-06-07 = date_add(2021-06-05,2) (event_date= start_date+pos)
  14. 1,oppo,2021-06-05(start_date),2021-06-07 = date_add(2021-06-05,3) (event_date= start_date+pos)
  15. 1,oppo,2021-06-05(start_date),2021-06-08 = date_add(2021-06-05,4) (event_date= start_date+pos)
  16. 1,oppo,2021-06-05(start_date),2021-06-09 = date_add(2021-06-05,5) (event_date= start_date+pos)
  17. 炸裂的目的:活动的优惠时间段[ '2021-06-05' , '2021-06-09' ] 拆分成具体的
  18. 每一天event_date:
  19. '2021-06-05',
  20. '2021-06-06',
  21. '2021-06-07',
  22. '2021-06-08',
  23. '2021-06-09'
  24. 3)根据品牌brand进行分组,求count(distinct event_date) ,从而得到每品牌的总营销天数(营销日期有重叠的地方已经去重了)

      思路二:用带有下标的炸裂函数posexplode

  1. select brand,
  2. count(distinct event_date)
  3. from
  4. (
  5. select
  6. promotion_id,
  7. brand,
  8. start_date,
  9. date_add(start_date,pos) as event_date,
  10. pos
  11. from (
  12. select
  13. promotion_id,
  14. brand,
  15. start_date,
  16. end_date,
  17. split(repeat(',',datediff(end_date, start_date)),',') as ar
  18. from promotion_info
  19. ) tmp1
  20. lateral view posexplode(ar) tmp2 as pos, item
  21. )tmp2
  22. group by brand;

     思路二的代码拆解分析:跟思路一的逻辑基本是一样的 ,区别仅在于:用代码       split(repeat(',',datediff(end_date, start_date)),',') as ar 去替换 split(space(datediff(end_date, start_date)), '') as ar

     思路三的代码逻辑如下:

  1. select
  2. brand,
  3. --对品牌brand分组求sum的原因:同一个用户可能对应多段不交叉的活动
  4. sum(datediff(end_date, new_start_date) + 1) days
  5. from (
  6. select
  7. brand,
  8. new_start_date,
  9. end_date
  10. from (
  11. select
  12. brand,
  13. --判断逻辑:1.如果max_end_date是null(意味着当前行就是首行,不存在上一行了),直接取start_date
  14. --2.如果max_end_date不是null,进一步判断【当前行】的start_date与max_end_date的大小,如果start_date小,那用max_date+ 1的值作为【当前行】的新new_start_date
  15. if(max_end_date is null, start_date,
  16. if(start_date > max_end_date, start_date, date_add(max_end_date, 1))) new_start_date,
  17. end_date
  18. from (
  19. select
  20. brand,
  21. start_date,
  22. end_date,
  23. -- 开窗范围:同一个品牌内部:上无边界到截止到上一行
  24. -- 开窗的计算逻辑:max(end_date) --> 对【上无边界到上一行】的最大结束时间end_date进行标记,再与当前行的起始时间start_date进行比对
  25. max(end_date)
  26. over (partition by brand order by start_date rows between unbounded preceding and 1 preceding) max_end_date
  27. from promotion_info
  28. ) t1
  29. ) t2
  30. -- 需要保证每行数据的新的起始时间new_start_date 比 结束时间end_date 小
  31. where new_start_date < end_date
  32. ) t3
  33. group by brand;

     思路三:没有用到炸裂函数,关键思想是:当活动的上一个日期区间A 与 当前的日期区间B出现重叠(日期交叉,有重复数据)时,需要将区间B的起始时间改成区间A的结束时间。(修改之后需要保证B区间的结束时间> 开始时间)

3 小结

    上述代码中用到的函数有:

  1. 一、字符串函数
  2. 1、空格字符串函数:space
  3. 语法:space(int n)
  4. 返回值:string
  5. 说明:返回值是n的空格字符串
  6. 举例:select length (space(10)) --> 10
  7. 一般space函数和split函数结合使用:select split(space(3),''); --> ["","","",""]
  8. 2、split函数(分割字符串)
  9. 语法:split(string str,string pat)
  10. 返回值:array
  11. 说明:按照pat字符串分割str,会返回分割后的字符串数组
  12. 举例:select split ('abcdf','c') from test; -> ["ab","df"]
  13. 3、repeat:重复字符串
  14. 语法:repeat(string A, int n)
  15. 返回值:string
  16. 说明:将字符串A重复n遍。
  17. 举例:select repeat('123', 3); -> 123123123
  18. 一般repeat函数和split函数结合使用:select split(repeat(',',4),','); -->
  19. ["","","","",""]
  20. 二、炸裂函数
  21. explode
  22. 语法:lateral view explode(split(a,',')) tmp as new_column
  23. 返回值:string
  24. 说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串
  25. 举例:select student_score from test lateral view explode(split(student_score,','))
  26. tmp as student_score
  27. posexplode
  28. 语法:lateral view posexploed(split(a,',')) tmp as pos,item
  29. 返回值:string
  30. 说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串(炸裂具备瞎下角标 0,1,2,3)
  31. 举例:select student_name, student_score from test
  32. lateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_name
  33. lateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_score
  34. where student_score_index = student_name_index

2.3 用户消费金额

0 问题描述

    变更需求:table11表的第1,4列不表,第2列需要变更为连续日期,第3列需要变更成【截止当月的累积消费额】

1 数据准备

  1. create table if not exists table11
  2. (
  3. user_id string comment '用户标识',
  4. dt string comment '消费日期',
  5. price string comment '消费金额',
  6. qs int comment '用户应存期数'
  7. )
  8. comment '用户消费详情表';
  9. INSERT overwrite table table11
  10. VALUES ("A","2018-12-21","9439.30",12),
  11. ("A","2019-03-21","9439.30",12),
  12. ("A","2019-06-21","9439.30",12),
  13. ("A","2019-09-21","9439.30",12),
  14. ("B","2018-12-02","9439.30",10),
  15. ("B","2019-02-02","9439.30",10),
  16. ("B","2019-06-02","9439.30",10);

2 数据分析

  1. -- 思路一:利用posexplode函数进行炸裂,同时生成下角标pos,
  2. --将消费区间(一行)炸裂成多行
  3. select
  4. tmp3.user_id,
  5. tmp3.event_dt,
  6. -- sum() over(partition by .. order by .. ) 窗口计算的范围是:上无边界(起始行)到当前行,求消费金额的累积值(order by 后面没有窗口子句的情况下,窗口范围是:上无边界(起始行)到当前行)
  7. cast(sum(tmp4.price) over (partition by tmp3.user_id order by tmp3.event_dt) as decimal(18, 2)) as price,
  8. tmp3.max_qs
  9. from (
  10. select
  11. user_id,
  12. add_months(min_dt, pos) as event_dt,
  13. max_qs,
  14. pos
  15. from (
  16. select
  17. user_id,
  18. min(dt ) as min_dt,
  19. max(price) max_price,
  20. max(qs) max_qs
  21. from table11
  22. group by user_id
  23. ) tmp1 lateral view posexplode(split(space(max_qs), '')) tmp2 as pos, item
  24. ) tmp3
  25. left join (select
  26. user_id,
  27. dt,
  28. price
  29. from table11) tmp4
  30. on tmp3.user_id = tmp4.user_id and tmp3.event_dt= tmp4.dt;

3 小结

   利用posexplode的下角标pos进行填补连续。利用sum(price)over(partition by ..order by)进行消费金额的累积值统计(截止到当日)

(1)lateral view posexplode(split(space(max_qs), '')) tmp2 as pos, item;-->对字段 期数ds进行posexplode炸裂,一行变多行,且生成对应的下角标pos

(2)add_months(min_ds, pos) as new_ds; --> 基于min_dt + pos对消费日期 进行填补,组成连续的消费日期区间。

 待补充:炸裂的弊端是可能会发生数据膨胀,当数据集小的时候,用炸裂方便,当时数据集大时,需慎用。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/木道寻08/article/detail/742280
推荐阅读
相关标签
  

闽ICP备14008679号