数据库窗口函数总结

---

一、什么是窗口函数

1.1 定义

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

1.2 基本语法

窗口函数的基本语法：

<窗口函数> over ( [partition by <用于分组的列名>]

                   [order by <用于排序的列名>] )

语法中<窗口函数>的位置，可以放以下两种函数：

• 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
• 2） 聚合函数，如sum. avg, count, max, min等

1.3 注意事项

• partition by 不会改变原表的行数；group by 会改变原表的行数;
• 专用窗口函数里的（）不需要任何参数，保持空着就行,反之聚合函数（）不能为空;
• 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中;
• partition子句可以省略，省略就是不指定分组，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响;
• order by 子句也可以省略;
• 传统的聚合、排序等函数都是基于全局整表的，窗口函数可以基于表中的每个细分部分。

1.4 窗口函数分类

窗口函数可以分为专用窗口函数和聚合函数。

专用窗口函数可以进一步细分¹²：

1. 排名函数：根据SQL标准能够支持4种窗口函数用于排名计算。

• ROW_NUMBER：根据指定的顺序，从1开始计算连续的行号。
• NTILE：函数对一个数据分区中的有序结果集进行划分，将其分成数量大致相等的块，根据输入的块数和指定的窗口进行排序。
• RANK与DENSE_RANK：计算一组数值中的排序值

2. 分布函数：分布函数主要作用是为静态统计服务提供数据的分布情况。

• 排名分布函数：PERCENT_RANK(百分位排名)和CUME_DIST(累积分布)
• 逆分布函数：PERCENT_CONT(百分位连续)和PERCENTILE_DISC(百分位离散)

3. 偏移量函数：LAG和LEAD

• LAG(col,n,DEFAULT): 用于统计窗口内往上第n行值;
• LEAD(col,n,DEFAULT):与LAG相反,用于统计窗口内往下第n行值

4. 取值函数

• FIRST_VALUE():取分组内排序后，截止到当前行，第一个值;
• LAST_VALUE():取分组内排序后，截止到当前行，最后一个值。

1.4 窗口函数的万能模板

万能模板³：

select * from(select* rows number () over(partition by 分组 order by 排序 desc)as ranking from 表名）as a where ranking <=N;

二、窗口函数的作用

2.1 解决的问题类型

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

• 排名问题：每个部门按业绩来排名
• topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

2.2 具备的功能

窗口函数有以下功能：

• 1）同时具有分组和排序的功能
• 2）不减少原表的行数

接下来，就结合实例，给大家介绍几种窗口函数的用法。

三.几种常用窗口函数的使用

3.1 专用窗口函数rank, dense_rank, row_number⁴⁵

三个排序函数的差异：

• RANK(): 生成数据项在分组中的排名，排名相等会在名次中留下空位。比如会出现1、2、2、4、4、6、7
• ROW_NUMBER(): 从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列。结果只会是1、2、3、4、5、6、7
• DENSE_RANK(): 生成数据项在分组中的排名，排名相等会在名次中不会留下空位，比如会出现1、2、2、3、3、4、5这种

几个不同排序函数的一些差异，可以根据不同的业务场景选择合适的函数。

示例：

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 班级表

得到结果：

3.2 聚合函数作为窗口函数⁵

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

示例：

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 班级表

得到结果：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

四、参考资料

---

1. SQL:窗口函数的简介和分类 ↩︎

2. sql常用函数——窗口函数 ↩︎

3. 窗口函数 ↩︎

4. 常用窗口函数以及应用场景 ↩︎

5. 通俗易懂的学会：SQL窗口函数 ↩︎ ↩︎