SQL窗口函数的使用

这篇文章着重于理解数据库中使用窗口函数进行高级SQL语句的编写方法，并加以使用。

窗口函数相较于普通SQL，有什么不同？

简单来说，窗口函数有以下功能：

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

2、不减少原表的行数

运用场景举例

排名问题：每个部门按业绩来排名

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

复购分析：App内要分析复购用户有多少

累计问题：医院要经常统计累计患者数

这类在每组内排名场景使用普通SQL语句很复杂，不易实现，这个时候就需要窗口函数简化查询语句的编写。

窗口函数的基本使用

窗口函数的基本语法如下：

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

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

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

1、专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。

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

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

专用窗口函数rank

以一个班级表举例：

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

得到上面结果的sql语句代码如下:

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

partition by用来对表分组,order by子句的功能是对分组后的结果进行排序。

窗口函数具备了group by子句分组的功能和order by子句排序的功能。那么为什么还要用窗口函数呢？

因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

其他专用窗口函数

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。

可以用一张图解释：

聚合函数作为窗口函数

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求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。

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

注意：partition子句可是省略，省略就是不指定分组。但是，这就失去了窗口函数的功能，所以一般不这么使用。

一个练习具体例子

1.获取每个部门中薪水最高的员工相关信息

参考答案

WITH mix AS (
    SELECT d.dept_no, d.emp_no, s.salary
    FROM dept_emp d
    JOIN salaries s ON d.emp_no = s.emp_no
),
rank_table AS (
    SELECT *,
           RANK() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS salary_rank
    FROM mix
)
SELECT dept_no, emp_no, salary
FROM rank_table
WHERE salary_rank = 1;

参考来源: 知乎：通俗易懂的学会SQL窗口函数