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

2024-02-01 07:20

本文主要是介绍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]        =>     abc(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   1b   2c   3
  • posexplode

 (1)posexplode(array<T> a)  --> posexplode和explode之间的区别:posexplode除了返回数据,还会返回该值的下角标。语法: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     a1     b2     c---------------------------------举例2:对student_name进行炸裂,同时也对student_score进行炸裂,且需要保证炸裂后,学生和成绩一一对应,不能错乱。lateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_namelateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_score;

1.2 lateral view 侧写视图

  • 定义:lateral view 通常与UDTF配合使用,lateral view 可以将UDTF应用到源表的每行数据, 将每行数据转换成一行或者多行,并将源表中每行的输出结果与该行连接起来,形成一个虚拟表
  • 举例:select id, name,  hobbies, hobby  from   person  lateral view explode(hobbies) tmp as hobby;  分析: 对源表person中的hobbies列 进行炸裂(一行变多行),新字段命名hobby, 利用侧视图lateral view 将源表person的每行与hobby连接起来,形成一个虚拟表,命名为tmp。

二、实际案例

2.1 每个学生及其成绩

0 问题描述

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

1 数据准备

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

2 数据分析

-- 思路一:lateral view + explode
selectclass,student,score,student_name,student_score
from table10 lateral view explode(split(student, ',')) tmp1 as student_namelateral view explode(split(score, ',')) tmp2 as student_score;
-- bug:上面逻辑能跑通,但是学生姓名和学生成绩对应不上,出现错乱,弃用。

    正确的代码如下:

-- 思路二: lateral view + posexplode
selectclass,student,score,student_name,student_score
from table10 lateral view posexplode(split(student, ',')) tmp3 as student_index_st, student_namelateral view posexplode(split(score, ',')) tmp4 as student_index_sc, student_score
where student_index_st = student_index_sc;-- student_index_st = student_index_sc 的作用:下角标对齐,实现学生和成绩一一对应

3 小结

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

2.2 日期交叉问题

0 问题描述

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

1 数据准备

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

2 数据分析

--思路一:用带有下标的炸裂函数posexplode将活动区间炸裂成具体的每一天的日期
-- 即:将同一个品牌的所有活动日期都有列出来,再对重叠的日期进行统一去重select brand,count(distinct event_date)from
(selectpromotion_id,brand,start_date,-- 用 start_date + 下角标pos date_add(start_date,pos) as event_date,pos
from (selectpromotion_id,brand,start_date,end_date,split(space(datediff(end_date, start_date)), '') as arfrom promotion_info) tmp1lateral view posexplode(ar) tmp2 as pos, item
)tmp2
group by brand;

    思路一的代码拆解分析:

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

      思路二的代码逻辑如下:

-- 思路二:用带有下标的炸裂函数posexplode
select brand,count(distinct event_date)from
(selectpromotion_id,brand,start_date,date_add(start_date,pos) as event_date,pos
from (selectpromotion_id,brand,start_date,end_date,split(repeat(',',datediff(end_date, start_date)),',') as arfrom promotion_info) tmp1lateral view posexplode(ar) tmp2 as pos, item
)tmp2
group by brand;

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

     思路三的代码逻辑如下:

-- 思路三:
selectbrand,--对品牌brand分组求sum的原因:同一个用户可能对应多段不交叉的活动sum(datediff(end_date, new_start_date) + 1) days 
from (selectbrand,new_start_date,end_datefrom (selectbrand,--判断逻辑:1.如果max_end_date是null(意味着当前行就是首行,不存在上一行了),直接取start_date--2.如果max_end_date不是null,进一步判断【当前行】的start_date与max_end_date的大小,如果start_date小,那用max_date+ 1的值作为【当前行】的新new_start_dateif(max_end_date is null, start_date,if(start_date > max_end_date, start_date, date_add(max_end_date, 1))) new_start_date,end_datefrom (selectbrand,start_date,end_date,-- 开窗范围:同一个品牌内部:上无边界到截止到上一行-- 开窗的计算逻辑:max(end_date)  --> 对【上无边界到截止到上一行】的最大结束时间end_date进行标记,再与当前行的起始时间start_date进行比对max(end_date)over (partition by brand order by start_date rows between unbounded preceding and 1 preceding) max_end_datefrom promotion_info) t1) t2-- 需要保证每行数据新的起始时间new_start_date 是比 结束时间end_date 小的where new_start_date < end_date) t3
group by brand;

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

3 小结

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

一、字符串函数1、空格字符串函数:space语法:space(int n)返回值:string说明:返回值是n的空格字符串举例:select length (space(10)) --> 10一般space函数和split函数结合使用:select split(space(3),'');  -->   ["","","",""]2、split函数(分割字符串)语法:split(string str,string pat)返回值:array说明:按照pat字符串分割str,会返回分割后的字符串数组举例:select split ('abcdf','c') from test; -> ["ab","df"]3、repeat:重复字符串语法:repeat(string A, int n)返回值:string说明:将字符串A重复n遍。举例:select repeat('123', 3); -> 123123123一般repeat函数和split函数结合使用:select split(repeat(',',4),',');  -->  ["","","","",""]二、炸裂函数explode 语法:lateral view explode(split(a,',')) tmp  as new_column返回值:string说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串举例:select student_score from test lateral view explode(split(student_score,',')) 
tmp as student_scoreposexplode语法:lateral view posexploed(split(a,',')) tmp as pos,item 返回值:string说明:按照分隔符切割字符串,并将数组中内容炸裂成多行字符串(炸裂具备瞎下角标 0,1,2,3)举例:select student_name, student_score from testlateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_namelateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_scorewhere student_score_index = student_name_index

2.3 用户消费金额

0 问题描述

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

1 数据准备

create table if not exists table11
(user_id  string comment '用户标识',dt       string comment '消费日期',price    string comment '消费金额',qs       int comment '用户应存期数'
)comment '用户消费详情表';
INSERT overwrite table table11
VALUES ("A","2018-12-21","9439.30",12),("A","2019-03-21","9439.30",12),("A","2019-06-21","9439.30",12),("A","2019-09-21","9439.30",12),("B","2018-12-02","9439.30",10),("B","2019-02-02","9439.30",10),("B","2019-06-02","9439.30",10);

2 数据分析

-- 思路一:利用posexplode的下角标pos进行炸裂,消费区间生成对应的每天的消费日期
selecttmp3.user_id,tmp3.event_dt,-- sum() over(partition by .. order by .. ) 窗口计算的范围是:上无边界到当前行,求消费金额的累积值cast(sum(tmp4.price) over (partition by tmp3.user_id order by tmp3.event_dt) as decimal(18, 2)) as price,tmp3.max_qs
from (selectuser_id,add_months(min_dt, pos) as event_dt,max_qs,posfrom (selectuser_id,min(dt ) as min_dt,max(price) max_price,max(qs)    max_qsfrom table11group by user_id) tmp1 lateral view posexplode(split(space(max_qs), '')) tmp2 as pos, item) tmp3left join (selectuser_id,ds,pricefrom table11) tmp4on tmp3.user_id = tmp4.user_id and tmp3.new_ds = tmp4.ds;

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对消费日期 进行填补,组成连续的消费日期区间。

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

这篇关于HiveSQL题——炸裂函数(explode/posexplode)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/666503

相关文章

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

C++操作符重载实例(独立函数)

C++操作符重载实例,我们把坐标值CVector的加法进行重载,计算c3=c1+c2时,也就是计算x3=x1+x2,y3=y1+y2,今天我们以独立函数的方式重载操作符+(加号),以下是C++代码: c1802.cpp源代码: D:\YcjWork\CppTour>vim c1802.cpp #include <iostream>using namespace std;/*** 以独立函数

函数式编程思想

我们经常会用到各种各样的编程思想,例如面向过程、面向对象。不过笔者在该博客简单介绍一下函数式编程思想. 如果对函数式编程思想进行概括,就是f(x) = na(x) , y=uf(x)…至于其他的编程思想,可能是y=a(x)+b(x)+c(x)…,也有可能是y=f(x)=f(x)/a + f(x)/b+f(x)/c… 面向过程的指令式编程 面向过程,简单理解就是y=a(x)+b(x)+c(x)

利用matlab bar函数绘制较为复杂的柱状图,并在图中进行适当标注

示例代码和结果如下:小疑问:如何自动选择合适的坐标位置对柱状图的数值大小进行标注?😂 clear; close all;x = 1:3;aa=[28.6321521955954 26.2453660695847 21.69102348512086.93747104431360 6.25442246899816 3.342835958564245.51365061796319 4.87

OpenCV结构分析与形状描述符(11)椭圆拟合函数fitEllipse()的使用

操作系统:ubuntu22.04 OpenCV版本:OpenCV4.9 IDE:Visual Studio Code 编程语言:C++11 算法描述 围绕一组2D点拟合一个椭圆。 该函数计算出一个椭圆,该椭圆在最小二乘意义上最好地拟合一组2D点。它返回一个内切椭圆的旋转矩形。使用了由[90]描述的第一个算法。开发者应该注意,由于数据点靠近包含的 Mat 元素的边界,返回的椭圆/旋转矩形数据

Unity3D 运动之Move函数和translate

CharacterController.Move 移动 function Move (motion : Vector3) : CollisionFlags Description描述 A more complex move function taking absolute movement deltas. 一个更加复杂的运动函数,每次都绝对运动。 Attempts to

✨机器学习笔记(二)—— 线性回归、代价函数、梯度下降

1️⃣线性回归(linear regression) f w , b ( x ) = w x + b f_{w,b}(x) = wx + b fw,b​(x)=wx+b 🎈A linear regression model predicting house prices: 如图是机器学习通过监督学习运用线性回归模型来预测房价的例子,当房屋大小为1250 f e e t 2 feet^

JavaSE(十三)——函数式编程(Lambda表达式、方法引用、Stream流)

函数式编程 函数式编程 是 Java 8 引入的一个重要特性,它允许开发者以函数作为一等公民(first-class citizens)的方式编程,即函数可以作为参数传递给其他函数,也可以作为返回值。 这极大地提高了代码的可读性、可维护性和复用性。函数式编程的核心概念包括高阶函数、Lambda 表达式、函数式接口、流(Streams)和 Optional 类等。 函数式编程的核心是Lambda

PHP APC缓存函数使用教程

APC,全称是Alternative PHP Cache,官方翻译叫”可选PHP缓存”。它为我们提供了缓存和优化PHP的中间代码的框架。 APC的缓存分两部分:系统缓存和用户数据缓存。(Linux APC扩展安装) 系统缓存 它是指APC把PHP文件源码的编译结果缓存起来,然后在每次调用时先对比时间标记。如果未过期,则使用缓存的中间代码运行。默认缓存 3600s(一小时)。但是这样仍会浪费大量C

PHP7扩展开发之函数方式使用lib库

前言 首先说下什么是lib库。lib库就是一个提供特定功能的一个文件。可以把它看成是PHP的一个文件,这个文件提供一些函数方法。只是这个lib库是用c或者c++写的。 使用lib库的场景。一些软件已经提供了lib库,我们就没必要再重复实现一次。如,原先的mysql扩展,就是使用mysql官方的lib库进行的封装。 在本文,我们将建立一个简单的lib库,并在扩展中进行封装调用。 代码 基础