我是孙斌，北理数学系毕业，分享数据分析相关知识，点击右上角“关注”，学习更多数据分析知识。

在MySQL中，分组group by一般和聚合函数连用，如group by+sum，这样能够得到每个组的总和，但是每个组内部的信息就被压缩掉了，例如，想知道每个组内的排名情况，以前这个问题不太好处理，有了窗口函数，这个问题就变得很简单了。

窗口函数有什么用

从 MySQL 8.0 开始，MySQL 加入了窗口函数，窗口函数有什么用呢？

主要用于解决分组排名问题，最典型的就是 TopN 问题，比如获取每个地区销售额前三名的地区。

有同学可能会有疑问，如果是分组，用 group by 不就行了？的确没错，可是现在要的是分组后每个组内的排名啊。

如果只用 group by，则只能得到每个分组的第一个值，无法展现出每个组内部的情况，或者只能得到每个组的总和或者均值等。

如何更高效地解决这种 TopN 问题呢？这就需要用到窗口函数了。

简单来说，就是对数据进行分组，每个分组内部其实还有很多记录，每个组相当于一个窗口，然后通过一些函数对每个窗口内的数据进行分析处理。

首先，看一个小例子。有以下销售数据表。

上表中，第一列是 id，第二列是销售人员姓名，第三列示订单日期，第四列是销售额，最后一列是地区。 现在我们希望将每个地区的销售人员按照销售额降序排列，并给出排名。 利用窗口函数可以这样写 SQL 代码。

select region, salesman, amount,
    rank() over (partition by region order by amount desc) as rk
from orders;

运行结果：

运行结果中最后一列是每个销售人员的排名，按照销售额降序排名。

上面的代码，核心部分为：

rank() over (partition by 字段1 order by 字段2)

这就是窗口函数的基本格式，由于这里要获取每个组的排名，所以用到了 rank()函数，partition 表示分块、分组，根据哪个字段进行分组，order by 表示排序。

当然，这里的 rank()函数可以替换为其他函数，根据需要实现的功能来定。

常用的窗口函数

常用的窗口函数还有 row_number, sum/avg/min/max, cume_dist, lag, first_value, last_value, ntile 等，这么多窗口函数，大致分为以下 5 种类型。

1. 排名函数：rank（获取排名）, row_number（获取行号，相当于排名）。
2. 聚合函数：sum（求和），avg（求均值），min（求最小值），max（求最大值）。
3. 分布函数：cume_dist（求累积占比）。
4. 前后函数：lag（向前取值），lead（向后取值）。
5. 头尾函数：first_value（第一个），last_value（最后一个）。

除了这些函数，还有其他一些函数，如 ntile，能够将有序数据分为 n 个组。

下面，我们通过一些具体的案例来说明这些函数的用法。

1、排名函数

前面的例子已经演示了用 rank 函数获取分组排名，用 row_number 函数也能实现同样的效果，代码如下。

select region, salesman, amount,
    row_number() over (partition by region order by amount desc) as rk
from orders;

运行结果：

这里本质上获取的是行号，如果两个数值相同，用 rank 函数获取的排名就会相同，而 row_number 则不会。

2、聚合函数

例如，我们希望分部门按照订单日期累加销售额，并将结果作为新的一列,这就要用到 sum 函数，SQL 代码如下。

select region, order_date, amount,
    sum(amount) over (partition by region order by order_date rows between 2 preceding and current row) as accum
from orders;

运行结果：

最后一列就是累加销售额。

3、分布函数

例如，想分地区按照日期统计每天的销售额累积占比，这就要用到 cume_dist 函数，SQL 代码如下。

select region, order_date, amount,
    cume_dist() over (partition by region order by order_date) as ration
from orders;

运行结果：

这里得到的就是销售额的累积占比。

4、前后函数

例如，想分地区按照销售额排序，查询前一名销售员和当前人员的销售额的差值。

解决这个问题可以分为两步。

第一步： 先找出每个销售人员的销售额的前一个数值，这就要用到 lag 函数取当前值的前一个值。

select region, salesman, order_date, amount,
    lag(amount, 1) over (partition by region order by order_date) as amount_pre
from orders;

运行结果：

最后一列得到的就是当前值的前一个值。

第二步： 作差，即用当前人员的销售额减去对应的前一个数值。

select region, salesman, order_date, amount, amount_pre, amount - amount_pre as dif
from(
    select region, salesman, order_date, amount,
        lag(amount, 1) over (partition by region order by order_date) as amount_pre
    from orders
) as t;

运行结果：

最后一列就是销售额的差值。

5、头尾函数

例如，我们希望先按照订单日期进行排序，然后分地区查询第 1 个销售人员的销售额。

这就要用到 first_value 函数去获取每组里的第一个值，SQL 代码如下。

select region, order_date, amount,
    first_value(amount) over (partition by region order by order_date) as first_amount
from orders;

运行结果：

最后一列就是第 1 个销售人员的销售额。

除了上述用法之外，还有其他一些用法，例如，我们希望按照地区将销售额分为 3 个等级。

这就要用到ntile函数，这个函数能够将有序数据分为 n 个等级。

select region, salesman, order_date, amount,
    ntile(3) over (partition by region order by amount) as rk
from orders;

运行结果：

这样就将每个地区的销售额分为 3 个等级。

以上就是 MySQL 中的窗口函数。

觉得对您有帮助，点赞+关注哦！