[ Hive ] 函数高阶 : 窗口函数.UDTF.UDAF

1 UDTF表生成函数

1. explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行
2. explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列
3. explode函数可以直接使用，但是如果在select条件中，包含explode和其他字段就必须结合lateral view侧视图使用

4. 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

1. 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;

2. 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;

3. 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 窗口函数

1. 如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数
2. 窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中
3. 个人认为:窗口函数只是分组+排序的同时,不同于聚合函数+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);