一文读懂SQL窗口函数

一. 窗口函数有什么用？
日常工作中，经常会用到在每组内排名，比如这些业务需求：

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

应对这些需求，需要使用到sql的高级功能窗口函数。
二. 什么是窗口函数？
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

语法:

<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)

那么窗口函数中的函数有哪些呢？

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等。

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

三. 如何使用？

结合实例，对几种窗口函数的用法进行解释。

1. 专用窗口函数rank

如果想在每个班级内按照成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“92”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from class

这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：
1）每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。窗口函数具备了group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？
这是因为，**group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。**例如下面统计每个班级的人数。

为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。
简单来说，窗口函数有以下功能：
1）同时具有分组和排序的功能。

2）不减少原表的行数。

3）语法如下：

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

2. 其他专业窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢？

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函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

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

看一下窗口函数是聚合函数时，会出来什么结果：

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 class

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响

4. 注意事项
partition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序：

四. 总结

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

但这就失去了窗口函数的功能，所以一般不要这么使用。
1.窗口函数语法

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

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

1） 专用窗口函数，比如rank, dense_rank, row_number等

2） 聚合函数，如sum. avg, count, max, min等

2.窗口函数有以下功能：

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

2）不减少原表的行数，所以经常用来在每组内排名

3.注意事项

窗口函数原则上只能写在select子句中

4.窗口函数使用场景

1）业务需求“在每组内排名”，比如：

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

五.练习题
5.1
请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT  product_id
       ,product_name
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
  FROM product;

把销售价格按照从低到高进行排列。
5.2
继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）

SELECT  product_name
       ,regist_date
       ,SUM(sale_price) OVER ( PARTITION BY regist_date ORDER BY regist_date) AS regist_date
  FROM product;

5.3
思考题

① 窗口函数不指定PARTITION BY的效果是什么？

不会指定分组，也就是没有范围约束。

② 为什么说窗口函数只能在SELECT子句中使用？实际上，在ORDER BY 子句使用系统并不会报错。

窗口函数一般用于对where或者group by子句处理后的数据进行操作，（很明显按照sql执行顺序）所以窗口函数原则上只能写在select子句中。