MySQL8窗口函数揭秘：轻松掌握数据分析的利器

今天又把mysql8的窗口函数拿起来回顾了一下，目前很少用到，又莫名的感觉很有用，就再回顾一遍吧。

先来一张全家福

CUME_DIST()

CUME_DIST 是 cumulative distribution 的缩写，意思是累积分布。这个函数主要用来计算当前行的值在整个结果集中有多大比例。换句话说，就是看看有多少行的值小于或等于当前行的值，并用一个百分比来表示。

我们建一个学生的成绩表

然后我们查询：

SELECT
  name,
  score,
  CUME_DIST() OVER (ORDER BY score) AS 'cume_dist'
FROM
  student;

结果集是：

也就是

• luke的成绩是20分，cume_dist是0.2，也就是有20%的学生成绩小于或等于20分
• cedric的成绩是40分，cume_dist是0.4，也就是有40%的学生成绩小于或等于40分
• chester的成绩是80分，cume_dist是0.8，也就是有80%的学生成绩小于或等于80分

CUME_DIST 主要是提供了一种直观的方法来理解数据的分布和相对位置，在排名和分位数分析，分层抽样，绩效评估和奖金分配，产品和用户行为分析等情况应用较多。

RANK()

RANK() 函数用于为结果集中的行生成一个排名编号。如果有相同的值，它们会被赋予相同的排名编号，并且接下来的排名会跳过重复的排名数量。

我们还是用上边的例子，这时候我们将lynette的成绩改为80。

然后查询RANK()。

SELECT
  id,
  name,
  score,
  RANK() OVER ( ORDER BY score DESC) AS 'rank'
FROM
  student;

可以看到。josh第一，lynette和chester并列第二，因为有两个第二，所以没第三了。这里的cedric是第四，luke是第五。

可以想到RANK()的使用场景就比CUME_DIST()广多了，成绩排名，业绩排名，体育比赛成绩，排行榜等等，排名的地方都能用到。

DENSE_RANK()

DENSE_RANK() 函数用于为结果集中的行生成一个排名编号。与 RANK() 函数不同的是，如果有相同的值，它们会被赋予相同的排名编号，但后续的排名不会跳过数字。这使得排名更加紧凑。

我们直接看结果

SELECT
  id,
  name,
  score,
  DENSE_RANK() OVER ( ORDER BY score DESC) AS 'dense_rank'
FROM
  student;

这里的的lynette和chester都是第二，但后边的cedric紧随其后，成为了第三。

DENSE_RANK()和RANK()的使用场景大致差不多，只需要注意区别即可。

FIRST_VALUE()，LAST_VALUE()，NTH_VALUE()

• FIRST_VALUE()返回窗口中第一行出现的值
• LAST_VALUE()返回窗口中最后一行出现的值
• NTH_VALUE()返回窗口中第N行出现的值
  这三个一起说吧，大致意思差不多

再壮大一下我们的数据队伍，加一个班级的列：

这时候，我们按照班级分组后，进行查询FIRST_VALUE()：

SELECT
  id,
  name,
  score,
	class,
  FIRST_VALUE(name) OVER (PARTITION BY class ORDER BY score DESC) AS 'first_boy'
FROM
  student;

这里表示，按照class分组之后，再按照score排序，然后获取每个组的第一名。结果是这样的

可以看到一班的第一名是lynette，二班的第一是josh。

再看看LAST_VALUE()。

SELECT
    id,
    name,
    score,
    class,
  LAST_VALUE(name) OVER (PARTITION BY class ORDER BY score DESC) AS 'last_boy'
FROM
  student;

这里是不是有点看不懂了，稍等，我解释一下，就拿二班的数据来说吧，它其实是一行一行来读的。

• 先取到了josh这一行，求最后一名，只有它自己，就显示自己。
• 在取到了chester这一行，chester是最后一个，所以就显示chester
• 再读到cedric这一行，cedric是最后一个，所以就显示cedric。

也就是他是一行一行读的，每读一行判断一次。

这时候看NTH_VALUE()。

SELECT
    id,
    name,
    score,
    class,
  NTH_VALUE(name,2) OVER (PARTITION BY class ORDER BY score DESC) AS 'boy'
FROM
  student;

结果是：

这就好理解多了。获取的是每个班级的第二名，读到第一行的时候，第二名是不存在的，所以是null，读到第二名了，也就有了，读到第三名的时候，第二名是不变的。

这几个函数在排名和分析，时间序列分析，滚动窗口计算，分组内的特定记录这些场景使用比较多。

LAG()和LEAD()

LAG()表示从当前行往前取第N行，如果没有N，默认为1，如果不存在前一行，则默认返回null

也就是说LAG() 函数用于访问一个数据行的前几行的值（相对于当前行）。这个函数非常有用，当你需要在结果集中进行时间序列分析或比较当前行和前几行的值时。

还是之前的例子，我们两个班合一起，全年级排名:

SELECT
    id,
    name,
    score,
    class,
  LAG(score,1, 0) OVER (ORDER BY score DESC) AS 'previous_score'
FROM
  student;

这样previous_score()显示的就是根据成绩排名之后，前一个人的成绩。若是对比差异，岂不是就方便和很多。

LEAD()则表示从当前行往后取第N行。

SELECT
    id,
    name,
    score,
    class,
  LEAD(score,1, 0) OVER (ORDER BY score DESC) AS 'last_score'
FROM
  student;

结果是：

显示了这个人之后一个人的分数情况。

这两个函数常用于时间序列分析，差异分析，趋势分析，事件序列分析等情况。

NTILE()

这个函数用于将一个结果集划分为指定数量的大致相等的组（桶）。每一行会被分配一个组号，从 1 到指定的组数。这个函数非常有用，当你需要将数据分成几部分进行分析时，例如分位数分析、绩效评估等。

我们直接看结果：

SELECT
    id,
    name,
    score,
    class,
  NTILE(3) OVER (ORDER BY score DESC) AS 'score_group'
FROM
  student;

就是先根据成绩排序，然后再分成需要的几部分，数量大致是相等的。

在分位数分析，绩效评估，市场细分，资源分配，统计分析等场景使用广泛。

PERCENT_RANK()

PERCENT_RANK() 函数用于计算每一行的百分排名（百分位数），即一行在其分区中的相对位置。它返回一个介于 0 和 1 之间的值，表示当前行的排名相对于分区中的其他行的百分比。百分排名公式为：

PERCENT_RANK = (rank−1)/(total rows−1)

SELECT
    id,
    name,
    score,
    class,
    PERCENT_RANK() OVER (ORDER BY score DESC) AS 'percent_rank'
FROM
  student;

这个值不是一个具体的排名，而是大概在数据集的什么位置，因为我们是按照成绩倒序排的：

• Josh的percent_rank是0，表示他的成绩比较靠前
• lynette的percent_rank是0.25，表示他的成绩在前25%处。
• luke的percent_rank是1，表示他的成绩比较靠后。

在成绩排名，绩效评估，市场分析，资源分配等场景适用。

ROW_NUMBER()

ROW_NUMBER()就比较简单了显示当前行在其分组内的序号，不管是否出现重复值，其排序结果都是1，2，3，4，5

SELECT
  id,
  name,
  score,
	class,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS 'row_number'
FROM
  student;

好了，要是业务中能用到的，大致是能想起来了吧，要是用不到，过一阵再回顾一遍吧。