Hive高阶函数：explode函数、Lateral View侧视图、聚合函数、增强聚合

Hive高阶函数

explode函数

• explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。
• explode执行效果正好满足于输入一行输出多行。
• 一般情况下可以单独使用，也可以根据业务需要结合lateral view侧视图一起使用。
• SELECT时不能和其他字段一起返回，要借助lateral view侧视图。
• 例：

1. step1：建表

CREATE TABLE the_nba_championship(team_name STRING,champion_year ARRAY<STRING>
)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','COLLECTION ITEMS TERMINATED BY '|';

2. step2：加载数据文件到表中

LOAD DATA LOCAL INPATH '/root/hivedata/The_NBA_Championship.txt' INTO TABLE the_nba_championship;

3. step3:使用explode函数对champion_year进行拆分：

SELECT a.team_name,b.year
FROM the_nba_championship a LATERAL VIEW EXPLODE(champion_year) b AS year
ORDER BY b.year DESC;

Lateral View侧视图

• 是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制问题。
• 一般只要使用UDTF，就会固定搭配lateral view使用

原理

• 将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。
• 使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by、limit等语句中，不需要再单独嵌套一层子查询。

语法

SELECT ... FROM tablelA LATERAL VIEW UDTF(XXX) 别名 AS col1,col2,col3...;

• 例1：

SELECT a.team_name,b.year
FROM the_nba_championship a LATERAL VIEW EXPLODE(champion_year) b AS year;

• 例2：统计每个球队获取总管据的次数，并根据倒序排序

SELECT a.team_name ,COUNT(*) AS nums
FROM the_nba_championship a LATERAL VIEW EXPLODE(champion_year) b AS year
GROUP BY a.team_name
ORDER BY nums DESC;

聚合函数

• 功能：对一组值执行计算并返回单一的值
• 聚合函数是典型的输入多行输出一行，使用Hive的分类标准，属于UDAF类型函数。
• 通常搭配GROUP BY语法一起使用，分组后进行聚合操作。
• 若未指定GROUP BY子句，默认情况下，它会汇总所有行数据。
• 用法同MySQL
• 可以配合条件转换函数使用，如：

SELECTSUM(CASE WHEN sex = '男' THEN 1 ELSE 0 END)
FROM studen;

• 不支持嵌套聚合函数
• 计算时会忽略NULL值所在行（不会默认null为0，整行直接视为不存在）
• 可以结合struct构造数据，找出最大值对应的其他字段值，例：

SELECT sex,
max(struct(age,name)).col1 AS age,
max(struct(age,name)).col2 AS name,
FROM student
GROUP by sex;

增强聚合

• 包括grouping sets、cube、rollup这几个函数；主要适用于OLAP多维数据分析模式中。

grouping sets

• grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的group by结果合并(union all)。grouping id表示结果属于哪个分组集合。
• 例：

SELECT month,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__,ID
FROM cookie_info
GROUP BY month,day
GROUPING SETS(month,day)
ORDER BY GROUPING__ID;

等价于：

SELECT month,NULL,COUNT(DISTINCT cookieid)AS nums,1 AS GROUPING__ID
FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL AS month,day,COUNT(DISTINCT cookieid)AS nums,2 AS GROUPING__ID
FROM cookie_info GROUP BY day;

CUBE

• 表示根据GROUP BY维度的所有组合进行聚合
  对于cube来说，如果有n个维度，则所有组合的总个数是：2^n
• 若roll up有a,b,c三个维度，则所有组合情况是：(a,b,c),(a,b),(b,c),(a,c),(a),(b),©,()
• 例：

SELECT month,day,COUNT(DISTINCT cookieid)AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;

等价于：

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS GROUPING_ID FROM cookie_info GROUP BY month,day;

ROLL UP

• roll up是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
• 若roll up有a,b,c三个维度，则所有组合情况是：(a,b,c),(a,b),(a),()
• 例：

#以month维度进行层级聚合：
SELECT month,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;