Hive高阶函数
explode函数
- explode接收map、array类型的数据作为输入,然后把输入数据中的每个元素拆开变成一行数据,一个元素一行。
- explode执行效果正好满足于输入一行输出多行。
- 一般情况下可以单独使用,也可以根据业务需要结合lateral view侧视图一起使用。
- SELECT时不能和其他字段一起返回,要借助lateral view侧视图。
- 例:
- step1:建表
CREATE TABLE the_nba_championship(
team_name STRING,
champion_year ARRAY<STRING>
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|';
- step2:加载数据文件到表中
LOAD DATA LOCAL INPATH '/root/hivedata/The_NBA_Championship.txt' INTO TABLE the_nba_championship;
- 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
- 可以配合条件转换函数使用,如:
SELECT
SUM(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;