Hive BBigData

Hive开窗函数

Posted by SH on September 14, 2020

Hive开窗函数

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

4类30+个分析/窗口函数：

  • 聚合分析函数

    1
2
3
4
5

    SUM     -- 该函数计算组中表达式的累积和
COUNT   -- 对一组内发生的事情进行累积计数
MIN     -- 在一个组中的数据窗口中查找表达式的最小值
MAX     -- 在一个组中的数据窗口中查找表达式的最大值
AVG     -- 用于计算一个组和数据窗口内表达式的平均值。

  • 排名分析函数

    1
2
3
4
5
6
7

    ROW_NUMBER    -- 正常排序[1,2,3,4] -- 必须有order_by
RANK          -- 跳跃排序[1,2,2,4] -- 必须有order_by
DENSE_RANK    -- 密集排序[1,2,2,3] -- 必须有order_by
FIRST         -- 从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST          -- 从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE   -- 返回组中数据窗口的第一个值
LAST_VALUE    -- 返回组中数据窗口的最后一个值。

  • 数学分析函数

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    STDDEV    		-- 计算当前行关于组的标准偏离
STDDEV_POP		-- 该函数计算总体标准偏离，并返回总体变量的平方根
STDDEV_SAMP		-- 该函数计算累积样本标准偏离，并返回总体变量的平方根
VAR_POP    		-- 该函数返回非空集合的总体变量（忽略null）
VAR_SAMP   		-- 该函数返回非空集合的样本变量（忽略null）
VARIANCE   		-- 如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP
COVAR_POP  		-- 返回一对表达式的总体协方差
COVAR_SAMP 		-- 返回一对表达式的样本协方差
CORR     		-- 返回一对表达式的相关系数
CUME_DIST  		-- 计算一行在组中的相对位置
NTILE     		-- 将一个组分为"表达式"的散列表示（类于Hive的分桶原理）
PERCENT_RANK 	-- 和CUME_DIST（累积分配）函数类似
PERCENTILE_DISC -- 返回一个与输入的分布百分比值相对应的数据值
PERCENTILE_CONT -- 返回一个与输入的分布百分比值相对应的数据值
RATIO_TO_REPORT -- 该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比
REGR_ (Linear Regression) Functions -- 这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

  • 行比较分析函数

    1
2
3

    -- 可以访问结果集中的其它行而不用进行自连接
LAG		-- LAG可以访问组中当前行之前的行
LEAD    -- LEAD与LAG相反，LEAD可以访问组中当前行之后的行

示例数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 数据表：T_Person 表保存了人员信息，FName 字段为人员姓名，FCity 字段为人员所在的城市名，FAge 字段为人员年龄，FSalary 字段为人员工资

CREATE TABLE T_Person (FName VARCHAR(20),FCity VARCHAR(20),FAge INT,FSalary INT);

INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Tom','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Tim','ChengDu',21,4000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Jim','BeiJing',22,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Lily','London',21,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('John','NewYork',22,1000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('YaoMing','BeiJing',20,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Swing','London',22,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Guo','NewYork',20,2800);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('YuQian','BeiJing',24,8000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Ketty','London',25,8500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Kitty','ChengDu',25,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Merry','BeiJing',23,3500);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Smith','ChengDu',30,3000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Bill','BeiJing',25,2000);
INSERT INTO T_Person(FName,FCity,FAge,FSalary) VALUES('Jerry','NewYork',24,3300);

1）基本格式

开窗函数格式： 函数名(列) OVER(选项)。

如开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

1
2
3
4
5
6
7
8
9

SELECT
	fname,
	fcity,
	fsalary,
	count( * ) over ( ) -- 工资小于 5000员工数 
FROM
	t_person 
WHERE
	fsalary < 5000;

结果：

image-20210518193633495

2）PARTITION BY 子句

1
2
3
4
5
6
7
8
9

-- 按城市分区显示每一个人员的信息以及所属城市的人员数
SELECT
	fname,
	fcity,
	fage,
	fsalary,
	count( * ) over ( PARTITION BY fcity ) -- 所在城市人数 
FROM
	t_person;

结果：

image-20210518193815844

3）同时使用多个开窗函数

1
2
3
4
5
6
7
8
9
10

-- 显示每一个人员的信息、所属城市的人员数以及同龄人的人数：
SELECT
	fname,
	fcity,
	fage,
	fsalary,
	count( * ) over ( PARTITION BY fcity ) -- 所属城市的人个数,
	count( * ) over ( PARTITION BY fage ) -- 同龄人个数 
FROM
	t_person;

结果：

image-20210518193912950

4）ORDER BY子句

使用ORDER BY子句来指定排序规则，

1

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义。

边界规则的可取值见下表：

可取值 说明 示例
CURRENT ROW 当前行  
N PRECEDING 前N行 2 PRECEDING
UNBOUNDED PRECEDING 一直到第一条记录  
N FOLLOWING 后N行 2 FOLLOWING
UNBOUNDED FOLLOWING 一直到最后一条记录  

rows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 查询从第一行到当前行的工资总和
SELECT
	fname,
	fcity,
	fage,
	fsalary,
	sum( fsalary ) over ( ORDER BY fsalary rows BETWEEN unbounded preceding AND current ROW ) -- 当前行工资求和 
FROM
	t_person;
-- “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架，
-- 为了简化使用，如果使用的是这种定位框架，则可以省略定位框架声明部分，
-- 也就是说上边的sql可以简化成：
SELECT
	fname,
	fcity,
	fage,
	fsalary,
	sum( fsalary ) over ( ORDER BY fsalary ) -- 到当前行工资求和 
FROM
	t_person;

结果：

image-20210518194213973

range

1
2
3
4
5
6
7
8
9

-- 把rows换成range，是按照范围进行定位的
SELECT
	fname,
	fcity,
	fage,
	fsalary,
	sum( fsalary ) over ( ORDER BY fsalary RANGE BETWEEN unbounded preceding AND current ROW ) -- 到当前行工资求和 
FROM
	t_person;

结果：

image-20210518194144331

ROWS和RANGE区别

“ROWS”是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果按照 “RANGE”进行范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所以计算的累积和为从第一条到2000元工资的人员结束，所以对 Lily、Swing、Bill这三个人进行开窗函数聚合计算的时候得到的都是7000（ “ 1000+2000+2000+2000 ”）。

其他示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 这里的开窗函数表示按照FSalary进行排序，
-- 然后计算从当前行前两行（2 PRECEDING）到当前行后两行（2 FOLLOWING）的工资和，
-- 注意：
-- 对于第一条和第二条而言它们的“前两行”是不存在或者不完整的，
-- 因此计算的时候也是要按照前两行是不存在或者不完整进行计算，
-- 同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似的处理。
SELECT
	FName,
	FSalary,
	SUM( FSalary ) OVER ( ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) -- 前二后二和 
FROM
	T_Person;

-- 算工资排名
-- 计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的个数，
-- 可以看作是计算人员的工资水平排名。
SELECT
	FName,
	FSalary,
	COUNT( * ) OVER ( ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
FROM
	T_Person;
	
-- 结合max求到目前行的最大值
-- 计算从第一行（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的最大工资值。
SELECT
	FName,
	FSalary,
	FAge,
	MAX( FSalary ) OVER ( ORDER BY FAge ) -- 此行之前最大值 
FROM
	T_Person;
	
-- over(partition by XX  order by XX)  partition by和order by 结合
-- 员工信息+同龄人最高工资，按工资排序
SELECT
	FName,
	FSalary,
	FAge,
	MAX( FSalary ) OVER ( PARTITION BY FAge ORDER BY fsalary ) -- 同龄人最高工资 
FROM
	T_Person;

5）排名分析函数

除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，还可以在开窗函数中使用一些高级的函数，RANK()、DENSE_RANK()、ROW_NUMBER()。

RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个函数的功能存在一定的差异:

  • RANK()：相同排序返回序号一样，下一个序号不连续，比如[1,2,2,2,5,6]；
  • DENSE_RANK()：相同排序返回序号一样，相关等级不会跳过，下一个序号连续，比如[1,2,2,2,3,4]；
  • ROW_NUMBER()：相同排序返回序号不一样，比如[1,2,3,4,5,6]。
1
2
3
4
5
6
7
8
9
10

-- 工资从高到低排名
SELECT
	FName,
	FSalary,
	FAge,
	RANK ( ) OVER ( ORDER BY fsalary DESC ) f_RANK,
	DENSE_RANK ( ) OVER ( ORDER BY fsalary DESC ) f_DENSE_RANK,
	ROW_NUMBER ( ) OVER ( ORDER BY fsalary DESC ) f_ROW_NUMBER 
FROM
	T_Person;

结果：

image-20210518194522666

6）lag和lead函数

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是 LAG 和 LEAD 与 left join、right join 等自连接相比，效率更高，SQL更简洁。

语法：

1
2
3
4
5

lag(exp_str, offset, defval) over(partion by ..order by …)
lead(exp_str, offset, defval) over(partion by ..order by …)
-- exp_str：字段名
-- offset：偏移量
-- defval：默认值

示例：

1
2
3
4
5
6
7
8
9
10
11
12

select 
	fname,
	fcity,
	fage,
	fsalary,
	-- 员工当前城市中按工资排序比自己工资高一位的工资
	lag(fsalary, 1, -1) over(partition by fcity order by fsalary desc) city_lag_salary,
	-- 员工所在城市平均工资
	avg(fsalary) over(partition by fcity) city_avg_salary,
from 
	t_person;

结果：

image-20210519112724773

CATALOG
    FEATURED TAGS
    Linux BBigData CSAPP LeetCode TOUR TensorFlow HADOOP Spark Java Kafka Kubernetes
    FRIENDS