SQL篇-常用窗口函数_sql窗口函数

前言

1、什么是窗口函数

2、窗口函数的语法

3、窗口函数中的元素

1）函数名 window_function_name

2) 分区 partition_defintion

3) 排序 order_definition

4) 框架 frame_definition

4、窗口函数的应用

1）排名函数 rank()，dense_rank()、row_number() 静态窗口（不用frame）

2）聚合函数  avg()、count()、sum()、min()、max()、percent_rank()、cume_dist()

3）取值函数

前后函数lag()、lead()

头尾函数first_val() 、last_val()

其他函数nth_value()、ntile()

参考文献

---

前言

在我们日常数据分析中，常常遇到要在组内排名、组内取topn值等需求，例如下面的业务需求：

排名问题：每个品牌的商品按销售额来排名
topN问题：找出每个品牌排名前N的商品

面对这类需求，就需要使用sql的高级功能窗口函数了。窗口函数是个神奇的存在，可以把多行代码变一行处理，把复杂的子查询变简单。让我们来一起了解窗口函数吧！

1、什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），作用于一个数据集合。窗口函数的一个概念就是当前行，当前行属于某个窗口就是从整个数据集选取一部分数据进行聚合/排名等操作。

2、窗口函数的语法

window_function_name(window_name/expression)
OVER (
 
[partition_defintion]
 
[order_definition]
 
[frame_definition])

窗口数据集由"[partition_defintion]"，"[order_definition]"，"[frame_definition]"确定。

3、窗口函数中的元素

1）函数名 window_function_name

静态窗口函数不能用frame子句；滑动窗口函数指加入order by或frame子句后，函数区域变为到当前行的数据集。

静态窗口函数：

排名函数 rank（）、dense_rank()、row_number()；

滑动窗口函数：

聚合函数 sum、 avg、count、max、min、percent_rank()、cum_dist()；

取值函数 first_value()、last_value()、nth_value()、lag()、lead()、ntile()

2) 分区 partition_defintion

窗口按照指定字段进行分区，分区语句为partition by <指定字段>，窗口函数功能在分区内执行，并在跨越分区边界时重新初始化。如果没有指定 partition by 语句，且没有后面的frame元素限制，就把所有数据当做一整个区。

3) 排序 order_definition

按照指定字段进行排序，排序语句为order by <指定字段>[asc/desc]。和partition by 子句配合使用，就是对分区后的数据进行排序；如果单独使用且没有后面的frame元素的限制，就是对整个区的所有数据进行排序。

4) 框架 frame_definition

窗口框架的作用是对分区进一步细分，框架frame是当前分区的一个子集，在分区里面再进一步细分窗口，子句用来定义子集的规则，通常用来作为滑动窗口使用，某些窗口函数属于静态窗口，frame子句就没有作用。

frame_unit有两种，分别是ROWS和RANGE，ROWS是基于行号，RANGE是基于值的范围。

使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字：

• CURRENT ROW: 当前行
• UNBOUNDED PRECEDING:   区间的第一行
• UNBOUNDED FOLLOWING：区间的最后一行
• expr  NPRECEDING:   当前行之前的N行，可以是数字，也可以是一个能计算出数字的表达式
• expr  NFOLLOWING：当前行之后的N行，可以是数字，也可以是一个能计算出数字的表达式

4、窗口函数的应用

接下来，结合实例，介绍以上几种窗口函数的用法

-- 创建成绩表
drop table if exists score;
CREATE TABLE IF NOT EXISTS `score` (
`学号` INT (5) zerofill NOT NULL,
`姓名` VARCHAR (10) NOT NULL,
`课程号` VARCHAR (15) NOT NULL,
`成绩` INT (5) NOT NULL DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='成绩表';
-- 查看表结构	
DESC score;

接下来插入数据

-- 插入数据
INSERT INTO `score`(`学号`, `姓名`, `课程号`, `成绩`) 
VALUES 
( '0005', '范若若', '00001', '99' ),( '0005', '范若若', '00002', '81' ),( '0005', '范若若', '00003', '81' ),
( '0001', '范闲',   '00001', '95' ),( '0001', '范闲',   '00002', '91' ),( '0001', '范闲',   '00003', '90' ),
( '0004', '范思辙', '00001', '93' ),( '0004', '范思辙', '00002', '93' ),( '0004', '范思辙', '00003', '97' ),
( '0003', '王启年', '00001', '94' ),( '0003', '王启年', '00002', '84' ),( '0003', '王启年', '00003', '96' ),
( '0002', '林婉儿', '00001', '96' ),( '0002', '林婉儿', '00002', '86' ),( '0002', '林婉儿', '00003', '91' ),
( '0006', '叶轻眉', '00001', '100'),( '0006', '叶轻眉', '00002', '88' ),( '0006', '叶轻眉', '00003', '88' ),
( '0007', '庆帝',   '00001', '98' ),( '0007', '庆帝',   '00002', '88' ),( '0007', '庆帝',   '00003', '93' );
-- 查询成绩表
select * from score order by 学号;

MySQL从8.0版本开始也和Sql Server、Oracle一样支持在查询中使用窗口函数，本文使用 MySQL 8.0.23版本通过实例来介绍窗口函数的应用。

1）排名函数 rank()，dense_rank()、row_number() 静态窗口（不用frame）

-- 按照姓名分区,成绩升序排名。 排名函数 rank()，dense_rank()、row_number()
SELECT 姓名,课程号,成绩,
    rank() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS r,
    dense_rank() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS dr,
    row_number() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS rn
FROM score;

2）聚合函数  avg()、count()、sum()、min()、max()、percent_rank()、cume_dist()

首先介绍常见聚合函数中的avg()：求平均、count()：统计个数、sum()：求和、min()：求最小值、max()：求最大值，下面使用窗口函数和聚合函数求每个人成绩的均值、个数、总分、最小值和最大值。

-- 求每个人成绩的均值、个数、总分、最小值和最大值
SELECT *,
	   avg(成绩)   over w AS score_avg,
       count(成绩) over w AS score_count,
	   sum(成绩)   over w AS score_sum,
       min(成绩)   over w AS score_min,
	   max(成绩)   over w AS score_max
FROM score
WINDOW w AS (partition by 姓名)
order by 学号;

在上面这个例子中，通过对姓名分区后，聚合了各个课程的成绩，例如score_sum列 对每个人分区后，加总各个课程的分数得到总分，但结果中保留了每一行的信息，出现了重复的总分行。我们可以直接使用group by函数得到唯一的的信息，避免重复。

-- 直接使用group by 函数
SELECT 学号,姓名,
	   round(avg(成绩),0)    AS score_avg,
       count(成绩)  AS score_count,
	   sum(成绩)    AS score_sum,
       min(成绩)    AS score_min,
	   max(成绩)    AS score_max
FROM score
group by 姓名
order by 学号;

下面介绍不太常用的两个聚合函数percent_rank()：累计百分比、cume_dist()：累计分布值函数

percent_rank()，和之前的rank()函数相关，每条记录按照如下公式计算：（rank-1）/（rows-1）其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

cume_dist()，和之前的rank()函数也相关，每条记录按照如下公式计算：相对位置（行排名）/rows

例如：叶轻眉的课程号00002和00003的成绩一样都为88，属于同一个等级1。因此这些行都以第2行为基准：2/3=0.66666，如下图所示：

-- percent_rank累计百分比、cume_dist累计分布值
SELECT *,
       rank() over w r,
	   percent_rank()  over w AS pr,
	   cume_dist()   over w AS cd
FROM score
WINDOW w AS (partition by 姓名 order by 成绩);

注意：这两个函数不同的地方在于起始点，percent_rank 这个函数从0开始，而cume_list函数从1开始计算百分比。

3）取值函数

• 前后函数lag()、lead()

用途：分区中位于当前行前n行/后n行的记录值。

使用场景：查询上一个订单距离当前订单的时间间隔等

-- 按照课程号分区，成绩列每条记录的前一行lag()/后一行lead()
SELECT *,
		lag(成绩, 1) over w as  first_row,
		lead(成绩,1) over w as  last_row
FROM score
WINDOW w AS (partition by 课程号);

注：lag(expr, [n, [default]])分区中位于当前行前n行的记录值,若n缺失默认值为1，如果没有返回default，没有default，返回NULL；lead()的语法和其相反，其余类似。

• 头尾函数first_val() 、last_val()

用途：返回分区中的第一个/最后一个记录值

-- 按照课程号分区的第一个和最后一个成绩分数
SELECT *,
		first_value(成绩) over w as first_values,
		last_value(成绩) over w as last_values
FROM score
WINDOW w AS (partition by 课程号);

• 其他函数nth_value()、ntile()

(1)nth_value()

用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名；

应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额,对头尾函数进行补充；

 
-- 按照课程号分区取每个分区成绩的第2和第3个值
SELECT *,
		nth_value(成绩, 2) over w as  second_score,
		nth_value(成绩, 3) over w as  third_score
FROM score
WINDOW w AS (partition by 课程号);

(2)ntile()

用途：将分区中的有序数据分为n个桶，记录桶号；

应用场景：将每个用户的订单按照订单金额分成3组。

此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均。

-- 按照课程号分区并按成绩升序排序,将各分区分为3桶
SELECT *,
		ntile(3) over w as nt
FROM score
WINDOW w AS (partition by 课程号 order by 成绩 asc);

窗口函数非常有意思，对于一些使用常规思维无法实现的SQL需求，大家可以尝试一下窗口函数，相信会有意想不到的收获。而在MySQL8.0以前的版本上无法使用窗口函数，大家也可以试一试自己写窗口函数。

4）frame语句

ROWS基于行号的应用

• PRECEDING:   区间的前面
• FOLLOWING：区间的后面

-- 计算每分区内当前行的前1行(共两行)的平均值以及和,计算成绩的移动平均数与求和。
SELECT 姓名,课程号,成绩,
	   avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC ROWS 1 preceding) AS moving_avg,
       sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC ROWS 1 preceding) AS moving_sum
FROM score;

RANGE是基于值范围的应用

-- 计算每个分区内从第一行到当前行的平均值以及和
SELECT 姓名,课程号,成绩,
	avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_avg,
    sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_sum
FROM score;

-- 计算每个分区内从当前行到最后一行的平均值以及和
SELECT 姓名,课程号,成绩,
    avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS moving_avg,
    sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS moving_sum
FROM score;

参考文献

1. https://zhuanlan.zhihu.com/p/165210822?utm_source=wechat_session
2. https://www.cnblogs.com/DataArt/p/9961676.html?ivk_sa=1024320u
3. https://blog.csdn.net/lkforce/article/details/102938990
4. https://zhuanlan.zhihu.com/p/111755529