explode

LATERAL VIEW EXPLODE函数详解及应用

千次阅读

2021-10-31 13:00:31

需求背景：在进行统计分析的时候有时候会有类似这样的需求比如求某个平台某一天所有的订单总和，或者淘宝所有pc 端的交易总和，这个时候我们可以基于原本基础的数据进行炸裂处理之后得出结结果值，方便后续进行查询，这种提前进行预聚合的思想长用于即席查询分析的场景中，比如为了适用于某张报表的多条件查询可以采用此种方式结合预聚合的方式进行操作。

数据说明：

+-----+-----------+------------+------------+---------+---------+
|id   |device_type|business_gmv|order_source|pay_money|event_day|
+-----+-----------+------------+------------+---------+---------+
|10001|jingdong   |16          |1           |1000     |20211020 |
|10004|jingdong   |15          |1           |2000     |20211021 |
+-----+-----------+------------+------------+---------+---------+

使用炸裂函数进行如下操作：

select
   device_type_all,
   business_gmv_all,
   order_source,
   pay_money,
   event_day_all
from order_hbi
LATERAL VIEW OUTER EXPLODE(array('ALL', device_type)) col1 AS device_type_all
LATERAL VIEW OUTER EXPLODE(array('ALL', business_gmv)) col1 AS business_gmv_all
LATERAL VIEW OUTER EXPLODE(array('ALL', event_day)) col1 AS event_day_all
order by event_day_all asc

语句说明：
1.首先将device_type 和ALL的组合数组进行炸裂，那么这里原来的两行数据在各自加上ALL 之后会得到一共四行数据：

+---------------+----------------+------------+---------+-------------+
|device_type_all|business_gmv_all|order_source|pay_money|event_day_all|
+---------------+----------------+------------+---------+-------------+
|ALL            |16              |1           |1000     |20211020     |
|jingdong       |16              |1           |1000     |20211020     |
|ALL            |15              |1           |2000     |20211021     |
|jingdong       |15              |1           |2000     |20211021     |
+---------------+----------------+------------+---------+-------------+

2.将business_gmv 和ALL 的组合数组进行炸裂，则由上述的数据再次翻倍，即每一行在基于business_gmv 进行和ALL 的炸裂则得到如下的八行数据

+---------------+----------------+------------+---------+-------------+
|device_type_all|business_gmv_all|order_source|pay_money|event_day_all|
+---------------+----------------+------------+---------+-------------+
|ALL            |ALL             |1           |1000     |20211020     |
|ALL            |16              |1           |1000     |20211020     |
|jingdong       |ALL             |1           |1000     |20211020     |
|jingdong       |16              |1           |1000     |20211020     |
|ALL            |ALL             |1           |2000     |20211021     |
|jingdong       |15              |1           |2000     |20211021     |
|ALL            |15              |1           |2000     |20211021     |
|jingdong       |ALL             |1           |2000     |20211021     |
+---------------+----------------+------------+---------+-------------+

再基于上述结果进行event_day 的炸裂，则最终可以得到如下的结果的16行数据：

+---------------+----------------+------------+---------+-------------+
|device_type_all|business_gmv_all|order_source|pay_money|event_day_all|
+---------------+----------------+------------+---------+-------------+
|ALL            |ALL             |1           |1000     |20211020     |
|ALL            |16              |1           |1000     |20211020     |
|jingdong       |ALL             |1           |1000     |20211020     |
|jingdong       |16              |1           |1000     |20211020     |
|jingdong       |15              |1           |2000     |20211021     |
|ALL            |ALL             |1           |2000     |20211021     |
|ALL            |15              |1           |2000     |20211021     |
|jingdong       |ALL             |1           |2000     |20211021     |
|ALL            |ALL             |1           |1000     |ALL          |
|ALL            |16              |1           |1000     |ALL          |
|jingdong       |16              |1           |1000     |ALL          |
|jingdong       |15              |1           |2000     |ALL          |
|jingdong       |ALL             |1           |1000     |ALL          |
|ALL            |ALL             |1           |2000     |ALL          |
|ALL            |15              |1           |2000     |ALL          |
|jingdong       |ALL             |1           |2000     |ALL          |
+---------------+----------------+------------+---------+-------------+

基于上述结果统计的目的是可以统计多纬度的指标的聚合结果

4.加上group by 进行统计结果的分析，聚合订单金额

select
   device_type_all,
   business_gmv_all,
   order_source,
   sum(pay_money) pay_money,
   event_day_all
from order_hbi
LATERAL VIEW OUTER EXPLODE(array('ALL', device_type)) col1 AS device_type_all
LATERAL VIEW OUTER EXPLODE(array('ALL', business_gmv)) col1 AS business_gmv_all
LATERAL VIEW OUTER EXPLODE(array('ALL', event_day)) col1 AS event_day_all
group by device_type_all,business_gmv_all,order_source,event_day_all
order by event_day_all asc

可以得到如下的结果：

+---------------+----------------+------------+---------+-------------+
|device_type_all|business_gmv_all|order_source|pay_money|event_day_all|
+---------------+----------------+------------+---------+-------------+
|jingdong       |ALL             |1           |1000.0   |20211020     |
|ALL            |ALL             |1           |1000.0   |20211020     |
|ALL            |16              |1           |1000.0   |20211020     |
|jingdong       |16              |1           |1000.0   |20211020     |
|jingdong       |15              |1           |2000.0   |20211021     |
|ALL            |15              |1           |2000.0   |20211021     |
|jingdong       |ALL             |1           |2000.0   |20211021     |
|ALL            |ALL             |1           |2000.0   |20211021     |
|ALL            |16              |1           |1000.0   |ALL          |
|jingdong       |16              |1           |1000.0   |ALL          |
|ALL            |ALL             |1           |3000.0   |ALL          |
|ALL            |15              |1           |2000.0   |ALL          |
|jingdong       |15              |1           |2000.0   |ALL          |
|jingdong       |ALL             |1           |3000.0   |ALL          |
+---------------+----------------+------------+---------+-------------+

比如我们可以直接从这个结果中选择所有设备类型（device_type_all）并且所有的bussiness_gmv 并且所有日期的订单总量则可以直接选取如下的数据，订单总金额为3000

|ALL            |ALL             |1           |3000.0   |ALL

也可以宣组jingdong 所有business_gmv_all 所有日期的订单总额则为：3000

|jingdong       |ALL             |1           |3000.0   |ALL          |

假如不想要某个字段的聚合结果可以用 !=ALL 先过滤掉，比如：business_gmv_all !=‘ALL’

SELECT device_type_all,sum(pay_money) gmv,event_day_all
FROM (
select
   device_type_all,business_gmv_all,order_source,
   sum(pay_money) pay_money,
   event_day_all
from order_hbi
LATERAL VIEW OUTER EXPLODE(array('ALL', device_type)) col1 AS device_type_all
LATERAL VIEW OUTER EXPLODE(array('ALL', business_gmv)) col1 AS business_gmv_all
LATERAL VIEW OUTER EXPLODE(array('ALL', event_day)) col1 AS event_day_all
group by device_type_all,business_gmv_all,order_source,event_day_all
order by event_day_all asc
) tmp
where business_gmv_all !='ALL'
group by device_type_all,event_day_all

得到如下结果：可以选取想要的数据避免重复计算，直接选取即可

+---------------+------+-------------+
|device_type_all|gmv   |event_day_all|
+---------------+------+-------------+
|ALL            |1000.0|20211020     |
|jingdong       |1000.0|20211020     |
|jingdong       |2000.0|20211021     |
|ALL            |2000.0|20211021     |
|ALL            |3000.0|ALL          |
|jingdong       |3000.0|ALL          |
+---------------+------+-------------+

大数据

更多相关内容

pandas dataframe 中的explode函数用法详解
2020-09-16 20:57:06

主要介绍了pandas dataframe 中的explode函数用法详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

收起
php中利用explode函数分割字符串到数组
2020-12-19 19:26:36

分割字符串 //利用 explode 函数分割字符串到数组复制代码代码如下: <?php $source = “hello1,hello2,hello3,hello4,hello5”;//按逗号分离字符串 $hello = explode(‘,’,$source); for($index=0;$index”; }...

收起
Python pandas 列转行操作详解(类似hive中explode方法)
2020-12-17 13:42:06

如果要爆炸B这一列，可以直接用explode方法（前提是你的pandas的版本要高于或等于0.25） df.explode('B') A B 0 1 1 1 1 2 2 2 1 3 2 2 2. 如果需要爆炸的有2列及以上 df=pd.DataFrame({

收起
python 实现 hive中类似 lateral view explode的功能示例
2020-12-17 13:28:33

背景：加入现在有这样的数据，可能一条ocr代表两个label，并且label通过”,”分隔。我们想把数据转换成下面的。原始数据： label ocr ...日常行车服务,汽车资讯去加油站，加完油后直接离开？...

收起
PHP explode()函数的几个应用和implode()函数有什么区别
2020-12-19 18:33:39

explode()函数介绍 explode() 函数可以把字符串分割为数组。语法：explode(separator,string,limit)。参数描述 separator 必需。规定在哪里分割字符串。 string 必需。要分割的字符串。 limit 可选。...

收起
PHP数组和explode函数示例总结
2020-12-19 16:42:12

PHP数组和explode函数应用实例，供大家学习参考。例1： <?php $province = array("北京","上海","天津","重庆","河北","山西","内蒙古","辽宁","吉林","黑龙江","江苏","浙江","安徽","福建","江西","山东",...

收起
php explode函数实例代码
2020-12-19 12:02:36

explode() 函数把字符串分割为数组。语法 explode(separator,string,limit) 参数描述 separator 必需。规定在哪里分割字符串。 string 必需。要分割的字符串。 limit 可选。规定所返回的数组元素的最大数目...

收起
PHP的explode和implode的使用说明
2020-12-19 11:00:42

也是php爱好者,和喜好php进阶的朋友们必须控制的东东,学习php的友朋们也晓得,数组也是必需把握的,能够那样道,进阶php,也便是学习php中的数组而在数组中通常要用到分割字符串啊什么的,便要用到explode ,和implode函数...

收起
php中的explode()函数实例介绍
2020-12-20 05:58:28

explode（）函数基于字符串分隔符拆分字符串，即它将字符串拆分为出自分隔符的位置。此函数返回一个数组，其中包含通过拆分原始字符串形成的字符串，我们可以通过访问数组来轻松检索字符串的每个部分它的语法结构...

收起
DevExpress之饼状图突出（Explode）设置实例
2020-09-04 02:16:23

主要介绍了DevExpress之饼状图突出（Explode）设置方法,以实例形式展示了饼状图突出设置的具体实现过程,非常具有实用价值,需要的朋友可以参考下

收起
PHP使用preg_split和explode分割textarea存放内容的方法分析
2020-10-19 14:31:56

主要介绍了PHP使用preg_split和explode分割textarea存放内容的方法,结合实例形式分析preg_split和explode函数的功能、使用技巧与文本字符串分割过程中的相关注意事项,需要的朋友可以参考下

收起
eXplode-开源
2021-04-24 22:36:39

eXplode是存储系统检查器。它包含用于实际代码的通用模型检查器，而不是抽象模型。使用此模型检查器，eXplode可以针对许多可能的崩溃来系统地检查存储系统，以查找崩溃恢复错误。

收起

开源软件
php连接函数implode与分割explode的深入解析
2020-12-18 19:32:48

目前学习php的人有很多，很多进行php培训的朋友在学习中总会问到这样一个问题:php连接函数implode是什么呢？...implode() 连接函数：此函数实现将数组元素连接成字符串，连接之前我们要给它两个参数，一个是连接符一个...

收起
PHP中substr()与explode()函数用法分析
2020-12-19 20:32:00

本文实例讲述了PHP中substr()与explode()函数用法。分享给大家供大家参考。具体方法如下： substr(string,start,length):本函数将字符串 string 的第 start 位起的字符串取出 length 个字符,若 start 为负数,则从...

收起
PHP explode()函数用法、切分字符串
2020-12-19 15:43:47

// ### 切分字符串 #### function jb51netcut($start,$end,$file){ $content=explode($start,$file); $content=explode($end,$content[1]); return $content[0]; } ?> explode定义和用法 explode() 函数把字符串...

收起
PHP 中使用explode()函数切割字符串为数组的示例
2020-12-19 23:43:40

explode()函数的作用：使用一个字符串分割另一个字符串，打散为数组。例如：字符串 $pizza = "第1 第2 第3 第4 第5 第6"; 根据空格分割后：$pieces = explode(” “, $pizza); $pieces是分割后的数组，我们打印...

收起
PHP源码之explode使用说明
2020-10-28 10:39:09

最近一直在想有关字符串操作的一些效率上的事情，截取字串的问题，都会避免不了重新分配空间的消耗，也顺带看了explode这个函数的源码，理解下，拿出自己的分析共享下

收起
php中使用explode查找某个字符是否存在的方法
2020-10-28 11:38:41

为了使输入方便，减少在装修编辑模块的时候出现太多的文字框，需要用到在一个框内输入两三种不同内容，然后再使用一些特殊字符来分隔开，用explode来建立一组数据的方法。

收起
EXPLODE:使用 MATLAB 分解点-matlab开发
2021-06-01 11:18:32

使用 MATLAB 分解点

收起

matlab
php使用explode()函数将字符串拆分成数组的方法
2020-10-24 16:59:16

主要介绍了php使用explode()函数将字符串拆分成数组的方法,具有一定参考借鉴价值,需要的朋友可以参考下

收起
php代码-获取 url 参数 urlargs($str, $aname) - strstr() explode()
2021-07-16 14:49:07

php代码-获取 url 参数 urlargs($str, $aname) - strstr() explode()

收起

代码
Hive Lateral View + explode 详解
千次阅读 2022-04-28 18:37:16
百度explode()时，经常会出现lateral view + explode相关的文章，很少单独写explode()。分别了解ecplode() 与lateral view的各自作用很重要，不然过程都不知道实现的，换个UDTF函数就不会使用了。 ..
hive中的函数分为3类,UDF函数、UDAF函数、UDTF函数
- UDF：一进一出
- UDAF:聚集函数，多进一出，类似于：count/max/min
- UDTF:一进多出,如explore()、posexplode()，UDTF函数的时候只允许一个字段
百度explode()时，经常会出现lateral view + explode相关的文章，很少单独写explode()。分别了解ecplode() 与lateral view的各自作用很重要，不然过程都不知道实现的，换个UDTF函数就不会使用了。

一、UDTF函数 explode() 讲解

UDTF函数作用都是输入一行数据，将该行数据拆分、并返回多行数据。不同的UDTF函数只是拆分的原理不同、作用的数据格式不同而已。
这里详细讲解explode()用法，学会这一个其他的UDTF函数也会使用。

explode()将一行数据转换成列数据，可以用于array和map类型的数据
1）explode()用于array的语法如下：
```
select explode(arraycol) as newcol from tablename;

#arraycol：arrary数据类型字段。
#tablename：表名
```
2）explode()用于map的语法如下：
```
select explode(mapcol) as (keyname,valuename) from tablename;
#tablename：表名
#mapcol：map类型的字段
#keyname：表示key转换成的列名称，用于代表key转换之后的列名。
#valuename：表示value转换成的列名称，用于代表value转换之后的列名称。
```
explode()用于map类型的数据时，由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。

3）以上为explode()函数的用法，此函数存在局限性：
- 其一：不能关联原有的表中的其他字段。
- 其二：不能与group by、cluster by、distribute by、sort by联用。
- 其三：不能进行UDTF嵌套。
- 其四：不允许选择其他表达式。
二、百度explode()，总会出现lateral view，它们各自的作用是什么？

第一部分对explode()函数做了简单的讲解，知道它作用的数据格式为array和map ,也知道了如何单独使用explode，可能脑袋还是有点懵，下面将结合案例一起学习。

UDTF函数(如：explode)只能只允许对拆分字段进行访问，即select时只能出现explode作用的字段，不能在选择表中其它的字段，否则会报错。
但是实际中需求中经常要拆某个字段,然后一起与别的字段一起取。这时就要使用lateral view。

lateral view为侧视图,其实就是用来和像类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接UDTF外的select字段的目的。

不加lateral view的UDTF函数只能提取单个字段拆分,并不能塞回原来数据表中。加上lateral view就可以将拆分的单个字段数据与原始表数据关联上。在使用lateral view的时候需要指定视图别名和生成的新列别名。

1、udtf + lateral view 格式一
```
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*
```
- lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
- UDTF(expression)：使用的UDTF函数，例如explode()。
- tableAlias：表示UDTF函数转换的虚拟表的名称。
- columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。
2、udtf + lateral view 格式二
```
from basetable (lateral view)*
```
- 在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。
- from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。
```
eg:
SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

#col1为表baseTable字段中的map或者array类型
#col2为表baseTable字段中的map或者array类型
```
3、udtf + lateral view 格式三
```
from basetable (lateral view outer)*
```
```
from basetable (lateral view outer)*
```
它比格式二只是多了一个outer，这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来，UDTF默认是忽略输出空的，加上outer之后，会将空也输出，显示为NULL。这个功能是在Hive0.12是开始支持的。
```
eg:
select name,key,value from student_score lateral view outer explode(score) scntable as key,value;
```
-------------可借助下面逻辑理解-------------
```
# 查看表数据
hive> select * from udtf_test;
OK
jim5    ["james5","datacloase"]
jim4    ["james4","datacloase"]
jim3    ["james3","datacloase"]
jim2    ["james2","datacloase"]
Time taken: 0.084 seconds, Fetched: 4 row(s)

# 1）hive只允许对其拆分字段进行访问
hive> select explode(subordinates) from udtf_test;
OK
james5
datacloase
james4
datacloase
james3
datacloase
james2
datacloase
Time taken: 0.075 seconds, Fetched: 8 row(s)

#2）同时select 查询 explode作用字段及其它字段时，报错
hive> select explode(subordinates),name from udtf_test;
FAILED: SemanticException 1:29 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'name'

#3）借助lateral view，同时查询explode作用字段及其它字段
hive> select name,subordinate from udtf_test
    > lateral view explode(subordinates)sub as subordinate;
OK
jim5    james5
jim5    datacloase
jim4    james4
jim4    datacloase
jim3    james3
jim3    datacloase
jim2    james2
jim2    datacloase
Time taken: 0.06 seconds, Fetched: 8 row(s)
```
三、explode、posexplode与lateral view 3套案例练习

拓展：
explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统）
不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，用json存储的概率比较大，直接导入hive基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。

1、找出相同数字的号码超过5位的手机号

1) 使用数据
```
jimmhe  18191512076
xiaosong    18392988059
jingxianghua    18118818818
donghualing 17191919999
```
2) 创建表
```
CREATE TABLE udtf_test1(
  name string, 
  phonenumber string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t'
```
3) 加载数据
```
load data local inpath '/home/atguigu/data/test_20211215.txt' into table udtf_test1;
```
4) 查看加载表数据
```
hive> select * from udtf_test1;
OK
jimmhe  18191512076
xiaosong    18392988059
jingxianghua    18118818818
donghualing 17191919999
Time taken: 0.076 seconds, Fetched: 4 row(s)
```
5) 解题分析思路
split将电话号码，拆分成数组，在用explode炸裂：
```
select name,phonenumber
from(
select 
    name
    ,phonenumber
    ,phone_num
from udtf_test1
lateral view explode(split(phonenumber,'')) view_number as phone_num)aa
group by name,phonenumber,phone_num
having count(1)>=5
```
2、求一下每个学生成绩最好的学科及分数、最差的学科及分数、平均分数

有一张hive表，分别是学生姓名name(string)，学生成绩score(map<string,string>),成绩列中key是学科名称，value是对应学科分数，请用一个hql求一下每个学生成绩最好的学科及分数、最差的学科及分数
1）表数据如下：
```
zhangsan|Chinese:80,Math:60,English:90
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60
```
2）创建表：
```
create table stu_score_test(name string,score map<String,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
```
3）导入数据：
```
load data local inpath '/home/atguigu/data/test_20211216' into table stu_score_test;
```
4）查看导入后表数据：
```
hive> select * from stu_score_test;
OK
zhangsan    {"Chinese":"80","Math":"60","English":"90"}
lisi    {"Chinese":"90","Math":"80","English":"70"}
wangwu  {"Chinese":"88","Math":"90","English":"96"}
maliu   {"Chinese":"99","Math":"65","English":"60"}
Time taken: 0.164 seconds, Fetched: 4 row(s)
```
5）解题思路
explode拆分map数据类型：
```
select 
    name,course,csorce
from(
    select 
        name
        ,course
        ,csorce 
        ,rank()over(partition by name order by csorce) last_rn
        ,rank()over(partition by name order by csorce desc) best_rn
    from stu_score_test
    lateral view  explode(score)  score_view as course,csorce
    )aa
where last_rn=1 or best_rn=1
```
3、计算酒店每天有多少个房间的入住---重点

1）需求如下

2）原始数据
```
7,2004,2021-03-05,2021-03-07
23,2010,2021-03-05,2021-03-06
7,1003,2021-03-07,2021-03-08
8,2014,2021-03-07,2021-03-08
14,3001,2021-03-07,2021-03-10
18,3002,2021-03-08,2021-03-10
23,3020,2021-03-08,2021-03-09
25,2006,2021-03-09,2021-03-12
```
3) 建表
```
create table temp_hotal_live(
user_id  varchar(50),
room_code  varchar(50),
Check_date varchar(50),
leave_date varchar(50)
)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ','
;
```
4) 分析思路
用posplode炸裂，补充完整时间：
```
SELECT 
start_dd,end_dd,count(1)
from
(SELECT
    user_id,  --用户id
    check_date,  --入店时间
    leave_date,  --离店时间
    date_add( check_date, pos ) start_dd,
    date_add( check_date, pos+1 ) end_dd
    FROM
    temp_hotal_live
    lateral VIEW 
    posexplode ( split ( REPEAT('A,',datediff( leave_date, check_date )) , ',' ) ) t AS pos, val
)
group BY start_dd,end_dd
```
- datediff,计算住了多少天，两个时间之间的差值；
- REPEAT(),把字符串复制多少次，把'A,'本题是把A,复制；
- split，把字符串按分隔符分割为数组；
- posexplode ：炸裂，并排序；可以行转列，并把索引取出。
收起
展开全文
hive 数据仓库