【Hive学习笔记】窗口函数

SQL 窗口函数速查表

1 什么是窗口函数？

又叫OLAP函数，Online Anallytical Processing，联机分析处理，可以对数据库数据进行实时分析处理。窗口代表范围。

窗口函数与聚合函数不同，窗口函数对于每个组返回多行，而聚合函数对于每个组只返回一行

引用图片：窗口函数概述

1.1 为什么叫“窗口”函数？

因为分组后的结果称为“窗口”，表示“范围“。

对于窗口函数，每个分组可以看作是一个窗口，分组内的每一行根据其所在分组内的行数据进行函数计算，获取计算结果，作为该行的窗口函数结果值。

1.2 窗口函数特点

（1）同时具有分组和排序的功能

（2）不减少原表的行数

（3）窗口函数只能出现在 SELECT 列表和 ORDER BY 子句中

1.3 窗口函数应用场景

（1）分组排名
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询
（6）同比/环比增长率

1.4 执行顺序

窗口函数是对where或者group by子句处理后的结果进行操作，所以其查询语句执行顺序为：

1. FROM
2. WHERE
3. GROUP BY
4. 聚合函数
5. HAVING
6. 窗口函数
7. SELECT DISTINCT
8. ORDER BY
9. LIMIT

2 窗口函数语法

窗口函数与其他函数的语法区别主要在于OVER子句

通过 OVER 子句来标记窗口，OVER 子句中的内容可以指定窗口分组以及排序的方法。

<窗口函数> over (partition by <用于分组的列名>
                 order by <用于排序的列名>
                 frame_clause <窗口大小>)

partiton by将数据按要求分组，order by再在组内进行排序，该过程不改变原表行数。

若 OVER 子句中的参数为空，则将整个查询结果集作为一个单一的分组，组内的每条记录的窗口函数计算结果为整个分组内的字段值求和。

即

如果 OVER() 子句里面的内容为空，则窗口大小为整个查询的结果集，使用结果集中的所有记录计算结果。

如果 OVER() 子句里面的内容不为空，则使用里面指定的窗口分组规则、排序规则对分区内的记录进行分组和排序。

向 over() 中添加参数，来限定窗口大小

（1）partition by 分组

类似 group by，基于每一行数据所在的组进行计算并返回结果

也可以理解为

partition by 设定排序的对象范围，通过 partition by 分组后的记录集合称为“窗口”（范围的意思）。

group by 与 partition by 区别

引用猴子老师的一张图：通俗易懂的学会：SQL窗口函数

现有一张班级表

group by分组汇总后改变了表的行数，一行只有一个类别

如果省略了 partition by，所有的数据作为一个组进行计算，也就是不考虑分组

（2）order by 排序

为分组内的行的排列顺序。

（3）frame_clause 窗口大小

frame_clause选项的作用是对分组进一步细分，在当前分组内指定一个计算窗口。

指定了窗口之后，就不再基于分组进行计算，而是基于窗口内的数据进行计算。窗口会随着当前处理的数据行而移动

也可理解为 限制窗口框架

可以使用 rows 和 range。

• rows 子句，可以将分组中包含的行指定为当前行之前或之后的行。例如，rows N preceding （表示之前 N 行）
• range 子句，按照排序列的当前值，根据相同值来确定分组中的行

---

参考：SQL 窗口函数速查表

frame_start 和 frame_end 可以是以下几种：

• current row：对于 rows 方式，代表了当前行；对于 range ，代表了当前行的所有对等行。

• unbounded preceding：代表了分组中的第一行。

• unbounded following：代表了分组中的最后一行。

• n preceding：对于 rows 方式，代表了当前行之前的第 n 行；对于 range ，代表了等于当前行的值减去 n 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。

• n following：对于 rows 方式，代表了当前行之后的第 n 行；对于 range ，代表了等于当前行的值加上 n 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。

如果没指定帧的话，默认的frame取决于ORDER BY。

rows 和 range 的默认值始终是 UNBOUNDED PRECEDING AND CURRENT ROW。

所以

当ORDER BY后面缺少窗口从句条件，窗口大小默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
（分组中的第一行，当前行的所有对等行）


当ORDER BY和窗口从句都缺失, 窗口大小默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
（分组中的第一行，分组中的最后一行）

---

3 常用的窗口函数

窗口函数分为聚合聚合函数、排名窗口函数、取值窗口函数

3.1 聚合窗口函数：

COUNT、SUM、MIN、MAX、AVG以及 GROUP_CONCAT

以猴子老师的例子，详细参考通俗易懂的学会：SQL窗口函数

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

聚合函数后的（）内不为空，而是填入指定聚合的列名。

3.1.1 案例

当ORDER BY后面缺少窗口从句条件，窗口大小默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
（分组中的第一行，当前行的所有对等行）


当ORDER BY和窗口从句都缺失, 窗口大小默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
（分组中的第一行，分组中的最后一行）

成绩向上累计

select *,sum(成绩) over(order by 学号) as current_sum
from 班级表

这里简单的解释一下，若 OVER 子句中的参数为空，则窗口大小为整个数据集，不分组，不排序。

---

查找单科成绩高于该科目平均成绩的学生名单

select * 
from (select * ,avg(成绩) over( partition by 科目 )as avg_score 
	  from 各科成绩表 )as b
where 成绩>avg_score; 

移动平均

select *,avg(成绩) over (order by 学号 
						// 自身记录以及前2行的平均，rows N preceding （之前~行）
						rows 2 preceding) as current_avg 
from 班级表;

引用图片：https://zhuanlan.zhihu.com/p/134018034

3.2 排名窗口函数（专用窗口函数）

rank()、dense_ rank()、row_number()

三者区别，这里函数以猴子老师的例子，详细参考通俗易懂的学会：SQL窗口函数

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

也就是

• rank 有并列名次的行，会占用下一名次的位置
• dense_rank 有并列名次的行，不占用下一名次的位置
• row_number 不考虑并列名次的情况

这三种窗口函数后的（ ）保持空即可

3.2.1 案例

排名问题：按成绩排名，如果两个分数相同，排名要是并列

select 
	*,dense_rank() over (order by 成绩 desc) as dese_rank 
from 班级表;

TopN问题：找出每个部门排名前N的员工进行奖励（查询出每组内的最大N个数据）

或者

如何找到每个类别下用户最喜欢的产品是哪个？
如何找到每个类别下用户点击最多的5个商品是什么？

select * 
from (select 
			*,row_number () over (partition by <分组的列名>  
									order by <排序的列名> desc)as ranking 
	  from 表名) as a
where ranking <= N;

其他排名功能包括：

• cume_dist ：计算分组中当前行的相对排名。即计算每行数据在其分组内的累积分布，也就是排在该行数据之前的所有数据所占的比率，取值范围（0,1]。

来自：不剪发的Tony老师

• ntile：尽可能平均地划分每个窗口分组的行，即于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。
• percent_rank：当前行的百分比排名

来自：不剪发的Tony老师

注：排名窗口函数不支持动态的窗口大小（frame_clause），而是以整个分区（PARTITION BY）作为分析的窗口。

3.3 取值窗口函数

取值窗口函数用于返回指定位置上的数据。常见的取值窗口函数包括：

• FIRST_VALUE，返回窗口内第一行的数据。

• LAST_VALUE，返回窗口内最后一行的数据。

• NTH_VALUE，返回窗口内第 N 行的数据。

• LAG，返回分区中当前行之前的第 N 行的数据。

• LEAD，返回分区中当前行之后第 N 行的数据。

LAG和LEAD函数不支持动态的窗口大小（frame_clause），而是以整个分区（PARTITIONBY）作为分析的窗口。