1 UDTF表生成函数
- explode函数接收map或者array类型的数据作为参数,然后把参数中的每个元素炸开变成一行数据。一个元素一行
- explode(map)将map里的每一对元素作为一行,其中key为一列,value为一列
- explode函数可以直接使用,但是如果在select条件中,包含explode和其他字段就必须结合lateral view侧视图使用
- explode函数+lateral view侧视图
--lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as 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;
2 UDAF聚合函数
2.1 基础聚合
UDAF函数通常把它叫做聚合函数,多进一出,也就是输入多行输出一行。
count:统计检索到的总行数。 |
---|
sum:求和 |
avg:求平均 |
min:最小值 |
max:最大值 |
数据收集函数(去重): collect_set(col) |
数据收集函数(不去重): collect_list(col) |
-- 聚合函数和case when条件转换函数、coalesce函数、if函数使用
select
sum(CASE WHEN sex = '男'THEN 1 ELSE 0 END)
from student;
select
sum(if(sex = '男',1,0))
from student;
-- 聚合参数不支持嵌套聚合函数
select avg(count(*)) from student;
-- 聚合参数针对null的处理方式
--null null 0
select max(null), min(null), count(null);
-- 下面这两个不支持null
select sum(null), avg(null);
-- 聚合操作时针对null的处理
CREATE TABLE tmp_1 (val1 int, val2 int);
INSERT INTO TABLE tmp_1 VALUES (1, 2),(null,2),(2,3);
select * from tmp_1;
--第二行数据(NULL, 2) 在进行sum(val1 + val2)的时候会被忽略
select sum(val1), sum(val1 + val2) from tmp_1;
-- 可以使用coalesce函数解决
select
sum(coalesce(val1,0)),
sum(coalesce(val1,0) + val2)
from tmp_1;
-- 配合distinct关键字去重聚合
--此场景下,会编译期间会自动设置只启动一个reduce task处理数据 性能可能会不会 造成数据拥堵
select count(distinct sex) as cnt1 from student;
--可以先去重 在聚合 通过子查询完成
--因为先执行distinct的时候 可以使用多个reducetask来跑数据
select count(*) as gender_uni_cnt
from (select distinct sex from student) a;
--案例,需求:找出student中男女学生年龄最大的及其名字
--这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值
select sex,
max(struct(age, name)).col1 as age,
max(struct(age, name)).col2 as name
from student
group by sex;
select struct(age, name) from student;
select struct(age, name).col1 from student;
select max(struct(age, name)) from student;
2.2 增强聚合
增强聚合的grouping_sets、cube、rollup这几个函数主要适用于OLAP多维数据分析模式中,多维分析中的维指的分析问题时看待问题的维度、角度。
案例:
字段:月份、天、用户cookieid
-
Grouping sets
等价于将不同维度的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,(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,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 nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
-
Cube
根据GROUP BY的维度的所有组合进行聚合
对于cube,如果有n个维度,则所有组合的总个数是:2^n
------cube---------------
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 nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
-
Rollup
rollup是Cube的子集,以最左侧的维度为主,从该维度进行层级聚合。
比如ROLLUP有a,b,c3个维度,则所有组合情况是:((a,b,c),(a,b),(a),())。
--rollup-------------
--比如,以month维度进行层级聚合:
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
--把month和day调换顺序,则以day维度进行层级聚合:
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie_info
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
3 窗口函数
- 如果函数具有OVER子句,则它是窗口函数。如果它缺少OVER子句,则它是一个普通的聚合函数
- 窗口函数聚合后还可以访问当中的各个行,并且可以将这些行中的某些属性添加到结果集中
- 个人认为:窗口函数只是分组+排序的同时,不同于聚合函数+group by隐藏其他行而聚合为一行,将聚合结果另开一个窗口列,此外,非分组字段和聚合字段的使用不会产生歧义
3.1 语法
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
--聚合函数:比如sum max avg等
--排序函数:比如rank row_number等
--分析函数:比如lead lag first_value等
--OVER [PARTITION BY <...>] 类似于group by 用于指定分组 每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是一组
--[ORDER BY <....>] 用于指定每个分组内的数据排序规则 支持ASC、DESC
--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行
3.2 窗口聚合函数
函数 | 作用 |
---|---|
sum(…) over( ) | 对表所有行求和 |
sum(…) over( order by … ) | 连续累积求和 |
sum(…) over( partition by…) | 同组内所有行求和 |
sum(…) over( partition by… order by … ) | 在每个分组内,连续累积求和 |
3.3 窗口表达式
用以控制窗口范围,关键字rows between
选项 | 范围 |
---|---|
preceding | 往前 |
following | 往后 |
current row | 当前行 |
unbounded | 边界 |
unbounded preceding | 表示从前面的起点 |
unbounded following | 表示到后面的终点 |
实例:
---窗口表达式
--第一行到当前行(不使用窗口表达式则默认为该情况)
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime
rows between unbounded preceding and current row) as pv2
from website_pv_info;
--向前3行至当前行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime
rows between 3 preceding and current row) as pv4
from website_pv_info;
--向前3行 向后1行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime
rows between 3 preceding and 1 following) as pv5
from website_pv_info;
--当前行至最后一行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime
rows between current row and unbounded following) as pv6
from website_pv_info;
--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime
rows between unbounded preceding and unbounded following) as pv6
from website_pv_info;
3.4 窗口排序函数
函数 | 功能 |
---|---|
row_number | 在每个分组中,为每行分配一个从1开始的唯一序列号,递增,不考虑重复 |
rank | 在每个分组中,为每行分配一个从1开始的序列号,考虑重复,挤占后续位置 |
dense_rank | 在每个分组中,为每行分配一个从1开始的序列号,考虑重复,不挤占后续位置 |
-----窗口排序函数
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info
WHERE cookieid = 'cookie1';
分组TopN:
--需求:找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;
ntile函数:
将每个分组内的数据分为指定的若干个桶里(分为若干个部分),并且为每一个桶分配一个桶编号,比如需求:提取排名前三分之一数据,NTILE函数即可满足
--把每个分组内的数据分为3桶
SELECT
cookieid,
createtime,
pv,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;
3.5 窗口分析函数
函数 | 功能 |
---|---|
LAG(col,n,DEFAULT) | 用于统计窗口内往上第n行值 |
LEAD(col,n,DEFAULT) | 用于统计窗口内往下第n行值 |
FIRST_VALUE | 取分组内排序后,截止到当前行,第一个值 |
LAST_VALUE | 取分组内排序后,截止到当前行,最后一个值 |
-----------窗口分析函数----------
--LAG
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime)
AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;
4 抽样函数
在HQL中,可以通过三种方式采样数据:随机采样,存储桶表采样和块采样。
-- Random随机抽样
-- 需求:随机抽取2个学生的情况进行查看
SELECT * FROM student
DISTRIBUTE BY rand() SORT BY rand() LIMIT 2;
-- 使用order by+rand也可以实现同样的效果 但是效率不高
SELECT * FROM student
ORDER BY rand() LIMIT 2;
-- block抽样
-- 根据行数抽样
SELECT * FROM student TABLESAMPLE(1 ROWS);
-- 根据数据大小百分比抽样
SELECT * FROM student TABLESAMPLE(50 PERCENT);
-- 根据数据大小抽样
--支持数据单位 b/B, k/K, m/M, g/G
SELECT * FROM student TABLESAMPLE(1k);
-- bucket table抽样
-- 根据整行数据进行抽样
SELECT * FROM t_usa_covid19_bucket TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand());
-- 根据分桶字段进行抽样 效率更高
describe formatted t_usa_covid19_bucket;
SELECT * FROM t_usa_covid19_bucket TABLESAMPLE(BUCKET 1 OUT OF 2 ON state);