一文讲透 MySQL 的窗口函数

最新推荐文章于 2024-03-19 22:00:41 发布

SQL必知必会

最新推荐文章于 2024-03-19 22:00:41 发布

阅读量785

点赞数 1

分类专栏： SQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/jinahong101/article/details/111571159

版权

SQL 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

窗口函数在统计类的需求中很常见，稍微复杂一点的查询需求就有可能用到它，使用窗口函数可以极大的简化我们的 SQL 语句。像 Oracle、SQL Server 这些数据库在较早的版本就支持窗口函数了，MySQL 直到 8.0 版本后才支持它。

本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种：一种是非聚合窗口函数，另外一种是聚合窗口函数。

非聚合窗口函数是相对于聚合窗口函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

1 非聚合窗口函数

MySQL 支持的非聚合窗口函数见表1。

名称	描述
`CUME_DIST()`	累积分配值
`DENSE_RANK()`	当前行在其分区中的排名，稠密排序
`FIRST_VALUE()`	指定区间范围内的第一行的值
`LAG()`	取排在当前行之前的值
`LAST_VALUE()`	指定区间范围内的最后一行的值
`LEAD()`	取排在当前行之后的值
`NTH_VALUE()`	指定区间范围内第N行的值
`NTILE()`	将数据分到N个桶，当前行所在的桶号
`PERCENT_RANK()`	排名值的百分比
`RANK()`	当前行在其分区中的排名，稀疏排序
`ROW_NUMBER()`	分区内当前行的行号

表1 非聚合窗口函数列表

常用的函数有：ROW_NUMBER()、RANK()、DENSE_RANK()、LEAD()、LAG()、NTH_VALUE()、FIRST_VALUE()、LAST_VALUE()。

ROW_NUMBER()、RANK()、DENSE_RANK()

ROW_NUMBER()、RANK()、DENSE_RANK()都是排序函数，都可以给区间内的数生成序号。如果区间内不存在重复值，它们的计算结果一样。

当出现重复值时，ROW_NUMBER()不考虑重复值，它会给相同的两个值分配不同的编号，编号的范围是从 1 到分区的行数。RANK() 和DENSE_RANK() 给重复的值生成相同的编号。不同的是，RANK()生成的序号有间隙，即重复值的下一项的编号和重复值的编号并不连续（下一项的值的编号 $\neq$ 当前重复值项的编号+1），而DENSE_RANK()就不是这样。

这几个函数的区别可结合下面的例子分析。

SELECT 
  sal,
  ROW_NUMBER() OVER(ORDER BY sal) AS 'rn',
  RANK() OVER(ORDER BY sal) AS 'rk',
  DENSE_RANK() OVER(ORDER BY sal) AS 'dk' 
FROM
  emp;
  
sal          rn      rk      dk  
-------  ------  ------  --------
800.00        1       1         1
950.00        2       2         2
1100.00       3       3         3
1250.00       4       4         4
1250.00       5       4         4
1300.00       6       6         5
1500.00       7       7         6
1600.00       8       8         7
2450.00       9       9         8
2850.00      10      10         9
2975.00      11      11        10
3000.00      12      12        11
3000.00      13      12        11
5000.00      14      14        12

如果没有指定 partition by 分区字段，那么窗口函数操作的区间就是全部数据。我们可以让函数只作用在 deptno 分区。

SELECT 
  sal,
  deptno,
  ROW_NUMBER() OVER(
      PARTITION BY deptno 
      ORDER BY sal) AS 'rn'
FROM
  emp;
  
sal      deptno      rn  
-------  ------  --------
1300.00      10         1
2450.00      10         2
5000.00      10         3
800.00       20         1
1100.00      20         2
2975.00      20         3
3000.00      20         4
3000.00      20         5
950.00       30         1
1250.00      30         2
1250.00      30         3
1500.00      30         4
1600.00      30         5
2850.00      30         6

如果我们要获取 emp 表中每个部门工资最高的前两名员工的信息，使用 ROW_NUMBER() 就可以这么写。

SELECT 
  * 
FROM
  (SELECT 
    empno,
    ename,
    deptno,
    sal,
    ROW_NUMBER() OVER(
      PARTITION BY deptno 
  ORDER BY sal DESC
  ) AS rn 
  FROM
    emp) t 
WHERE rn <= 2; 


 empno  ename   deptno  sal          rn  
------  ------  ------  -------  --------
  7839  KING        10  5000.00         1
  7782  CLARK       10  2450.00         2
  7788  SCOTT       20  3000.00         1
  7902  FORD        20  3000.00         2
  7698  BLAKE       30  2850.00         1
  7499  ALLEN       30  1600.00         2

注意，如果在OVER() 中没有ORDER 子句，那么，ROW_NUMBER()生成的编号是不确定的，而RANK()、DENSE_RANK() 生成的编号都是 1 。

LAG()、LEAD()

LAG() 可以获得位于当前行之前的数据，如果指定了分区，则获取数据的范围只能在分区内。默认是获取上一条的记录，如果没有获取到，则返回 NULL。

LAG() 的表达式是LAG(expr [, N[, default]]) [null_treatment]over_clause ，我们可以指定向后获取第 N 行，以及在获取不到数据时指定默认值。

LEAD() 的表达式和 LAG() 是一样的，因此在 LEAD() 中也可以指定获取的行数 N 及默认值。

SELECT 
  empno,
  sal,
  LAG(sal) OVER(ORDER BY empno)AS 'lag',
  LEAD(sal) OVER(ORDER BY empno)AS 'lead',
  LAG(sal,2,0) OVER(ORDER BY empno)AS 'lag_2'
FROM
  emp LIMIT 6;


 empno  sal      lag      lead     lag_2    
------  -------  -------  -------  ---------
  7369  800.00   (NULL)   1600.00  0.00     
  7499  1600.00  800.00   1250.00  0.00     
  7521  1250.00  1600.00  2975.00  800.00   
  7566  2975.00  1250.00  1250.00  1600.00  
  7654  1250.00  2975.00  2850.00  1250.00  
  7698  2850.00  1250.00  2450.00  2975.00

我们使用 LAG(sal,2,0) 获取当前行向前偏移 2 行的值，在当前行的编号是 1 和 2 时，由于偏移的行不存在，只能返回默认值 0 。

FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()

这几个函数可以分别获取区间范围内第一行、最后一行、第 N 行的值。

SELECT 
  empno,
  deptno,
  FIRST_VALUE(empno) OVER(
      PARTITION BY deptno 
      ORDER BY empno)AS 'first',
  LAST_VALUE(empno) OVER(
      PARTITION BY deptno ORDER BY empno 
      ROWS BETWEEN unbounded preceding 
      AND unbounded following)AS 'last',
  NTH_VALUE(empno,2) OVER(
      PARTITION BY deptno 
      ORDER BY empno)AS 'nth_2'
FROM
  emp;
  
  
 empno  deptno   first    last   nth_2  
------  ------  ------  ------  --------
  7782      10    7782    7934    (NULL)
  7839      10    7782    7934      7839
  7934      10    7782    7934      7839
  7369      20    7369    7902    (NULL)
  7566      20    7369    7902      7566
  7788      20    7369    7902      7566
  7876      20    7369    7902      7566
  7902      20    7369    7902      7566
  7499      30    7499    7900    (NULL)
  7521      30    7499    7900      7521
  7654      30    7499    7900      7521
  7698      30    7499    7900      7521
  7844      30    7499    7900      7521
  7900      30    7499    7900      7521

当在OVER() 中指定了排序字段， FIRST_VALUE()、LAST_VALUE()、NTH_VALUE() 这几个函数的滑动窗口范围是从第一行到当前行（即 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），直接使用 LAST_VALUE() 得到的结果并不是我们直觉上想看到的那样。因此，需要把LAST_VALUE()的窗口范围改成 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

关于滑动窗口更加详细的描述，在后面有讲到。

NTILE()

NTILE(N)将一个分区划分为N个组（桶），给分区中的每一行分配其桶号，并返回其分区中当前行的桶号。

SELECT 
  empno,
  deptno,
  NTILE(2) OVER(
      PARTITION BY deptno 
      ORDER BY empno) AS ntile2
FROM
  emp;
  

 empno  deptno  ntile2  
------  ------  --------
  7782      10         1
  7839      10         1
  7934      10         2
  7369      20         1
  7566      20         1
  7788      20         1
  7876      20         2
  7902      20         2
  7499      30         1
  7521      30         1
  7654      30         1
  7698      30         2
  7844      30         2
  7900      30         2

CUME_DIST()

统计一组数据中小于等于（或大于等于，和 OVER() 中指定的排序行为有关系）当前行的值的百分比。

SELECT 
  sal,
  ROW_NUMBER() OVER(
ORDER BY sal) AS rn,
  CUME_DIST() OVER(
ORDER BY sal) AS dist
FROM
  emp;
  

sal          rn                 dist  
-------  ------  ---------------------
800.00        1    0.07142857142857142
950.00        2    0.14285714285714285
1100.00       3    0.21428571428571427
1250.00       4    0.35714285714285715
1250.00       5    0.35714285714285715
1300.00       6    0.42857142857142855
1500.00       7                    0.5
1600.00       8     0.5714285714285714
2450.00       9     0.6428571428571429
2850.00      10     0.7142857142857143
2975.00      11     0.7857142857142857
3000.00      12     0.9285714285714286
3000.00      13     0.9285714285714286
5000.00      14                      1

当我们在OVER() 指定排序的行为是 ORDER BY sal DESC时，看到的却是另一番景象。

SELECT 
  sal,
  ROW_NUMBER() OVER(
ORDER BY sal DESC) AS rn,
  CUME_DIST() OVER(
ORDER BY sal DESC) AS dist
FROM
  emp;
  

sal          rn                 dist  
-------  ------  ---------------------
5000.00       1    0.07142857142857142
3000.00       2    0.21428571428571427
3000.00       3    0.21428571428571427
2975.00       4     0.2857142857142857
2850.00       5    0.35714285714285715
2450.00       6    0.42857142857142855
1600.00       7                    0.5
1500.00       8     0.5714285714285714
1300.00       9     0.6428571428571429
1250.00      10     0.7857142857142857
1250.00      11     0.7857142857142857
1100.00      12     0.8571428571428571
950.00       13     0.9285714285714286
800.00       14                      1

PERCENT_RANK() 和 CUME_DIST() 一样，也是统计某个值的分配情况，只是它们的算法不一样。

PERCENT_RANK() 的计算公式：

(rank - 1) / (rows - 1)

其中，rank 表示行的等级（如果出现重复值，则用最小的那个编号），rows 表示分区的行数。具体请看下面这个例子。

SELECT 
  empno,
  sal,
  ROW_NUMBER() OVER(ORDER BY sal) AS rn,
  PERCENT_RANK() OVER(ORDER BY sal) AS percent
FROM
  emp;
  

sal          rn              percent  
-------  ------  ---------------------
800.00        1                      0
950.00        2    0.07692307692307693
1100.00       3    0.15384615384615385
1250.00       4    0.23076923076923078
1250.00       5    0.23076923076923078
1300.00       6    0.38461538461538464
1500.00       7    0.46153846153846156
1600.00       8     0.5384615384615384
2450.00       9     0.6153846153846154
2850.00      10     0.6923076923076923
2975.00      11     0.7692307692307693
3000.00      12     0.8461538461538461
3000.00      13     0.8461538461538461
5000.00      14                      1

当 sal = 800 时，percent = (1 -1 ) / (14 - 1) = 0；

当 sal = 1250 时，存在两个编号：4 跟 5。取最小的编号，则 percent = (4 -1 ) / (14 - 1) = 0.230769；

当 sal = 5000 时， percent = (14 -1 ) / (14 - 1) = 1。

注意，如果在OVER()中没有指定 ORDER 子句，那么 PERCENT_RANK() 计算的结果都是一样的。

2 聚合窗口函数

在下面这些聚合窗口函数后面加上 OVER() 子句，它们就变成了聚合窗口函数。

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

常用的聚合窗口函数有：AVG() OVER()、COUNT() OVER()、MAX() OVER()、MIN() OVER()、SUM() OVER()。

下面这个例子，它利用窗口函数只查一次 emp 表完成了这些需求：

统计所有员工的薪资；
统计每个部门的人数；
计算每个部门的平均薪资；
获取公司里面的最高薪资;
获取最早入职的员工的入职时间。

SELECT 
  empno AS '编号',
  ename AS '姓名',
  sal AS '薪资',
  deptno AS '部门编号',
  SUM(sal) OVER() AS '薪资总额',
  COUNT(*) OVER(PARTITION BY deptno) AS '部门人数',
  AVG(sal) OVER(PARTITION BY deptno) AS '部门平均薪资',
  MAX(sal) OVER() AS '最高薪资',
  MIN(hiredate) OVER() '最早入职时间'
FROM
  emp;

输出结果>>>

窗口函数示例

3 命名窗口函数

我们可以用 WINDOWS 关键字给窗口起别名，并在OVER() 中引用它。命名窗口子句位于 HAVING 子句和 ORDER 子句的位置之间，其语法如下：

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)]

我们可以同时定义多个窗口名字。

再来看下 window_spec 的定义：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

也就是说，我们在定义一个窗口函数时，可以指定下面这些内容：

引用的窗口别名，比如先定义了窗口 w1，再在窗口 w2 中引用 w1，就像这样WINDOW w1 AS (w2), w2 AS ()。但是，不能循环引用。
PARTITION 子句；
ORDER 子句；
滑动窗口范围。

对于在 SQL 中使用了多个窗口函数，且这些窗口函数中的 OVER() 的内容都相同，使用命名窗口函数就很合适。

比如，对于下面这条 SQL。

SELECT
  sal,
  ROW_NUMBER() OVER (ORDER BY sal) AS 'row_number',
  RANK()       OVER (ORDER BY sal) AS 'rank',
  DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
FROM emp;

可以改造成命名窗口的形式。一旦想改变 OVER() 里面的内容，只需改动命名窗口里面的内容，而不用像原来的 SQL 那样要改动每个窗口函数的内容。

SELECT
  sal,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK()       OVER w AS 'rank',
  DENSE_RANK() OVER w AS 'dense_rank'
FROM emp
WINDOW w AS (ORDER BY sal);

我们再来看一个复杂一点的例子。

SELECT
  deptno,
  sal,
  ROW_NUMBER() OVER w2 AS 'row_number',
  RANK()       OVER (w1 ORDER BY sal) AS 'rank',
  DENSE_RANK() OVER w3 AS 'dense_rank'
FROM emp
WINDOW w1 AS(PARTITION BY deptno),
	   w2 AS(ORDER BY sal),
	   w3 AS(w2)
ORDER BY sal;

我们在 RANK() OVER() 的子句里面引用了窗口 w1，并在其后面接入了 ORDER 子句；在定义窗口 w3 时，我们直接引用了窗口 w2，即窗口 w3 所表现的行为和 w2 一致。

实际上，上面这条 SQL 等价于下面这条 SQL。

SELECT
  deptno,
  sal,
  ROW_NUMBER() OVER (
      ORDER BY sal) AS 'row_number',
  RANK()       OVER (
      PARTITION BY deptno ORDER BY sal) AS 'rank',
  DENSE_RANK() OVER (ORDER BY sal) AS 'dense_rank'
FROM emp
ORDER BY sal;

4 动态窗口

我们来看下 OVER()的语法结构：

over_clause:
    {OVER (window_spec) | OVER window_name}
    
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

PARTITION 子句和 ORDER 子句大家都比较熟悉了，接下来给大家介绍 FRAME 子句。

FRAME 子句的就是用来实现动态窗口。窗口函数在每行记录上执行，有的函数的窗口不会发生变化，这种就属于静态窗口；有的函数随着记录不同，窗口大小也在不断变化，这种就属于动态窗口。

看下面这个例子，我们通过滑动窗口实现随着时间的变化累加部门的薪资，以及计算当前行和上下行记录的平均薪资。

SELECT 
  empno,
  deptno,
  sal,
  SUM(sal) OVER(PARTITION BY deptno ORDER BY hiredate ROWS UNBOUNDED PRECEDING) AS total,
  AVG(sal) OVER(PARTITION BY deptno ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS average
FROM
  emp;
  

 empno  deptno  sal      total     average        
------  ------  -------  --------  -------------
  7782      10  2450.00  2450.00   3725.000000  
  7839      10  5000.00  7450.00   2916.666667  
  7934      10  1300.00  8750.00   3150.000000  
  7369      20  800.00   800.00    1887.500000  
  7566      20  2975.00  3775.00   2258.333333  
  7902      20  3000.00  6775.00   2991.666667  
  7788      20  3000.00  9775.00   2366.666667  
  7876      20  1100.00  10875.00  2050.000000  
  7499      30  1600.00  1600.00   1425.000000  
  7521      30  1250.00  2850.00   1900.000000  
  7698      30  2850.00  5700.00   1866.666667  
  7844      30  1500.00  7200.00   1866.666667  
  7654      30  1250.00  8450.00   1233.333333  
  7900      30  950.00   9400.00   1100.000000

当计算 empno = 7782 这行记录时，total = 2450，average = （2450 + 5000）/ 2 = 3725；

当计算 empno = 7839 这行记录时，total = 2450 + 5000 = 7450，average = （2450 + 5000 + 1300）/ 3 = 2916.66；

当计算 empno = 7934 这行记录时，total = 2450 + 5000 + 1300 = 8750，average = （5000 + 1300）/ 2 = 3150；

可以通过基于行或者基于范围的方式指定窗口的大小：

基于行：选择当前行的前后几行。比如范围是当前行的往前两行和往后三行，就可以这么写语句 ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING。
基于范围：选择数据范围。例如获取值在区间 [c-2，c+3]的数据，语句就是 RANGE BETWEEN 2 PRECEDING AND 3 FOLLOWING，c 表示当前行的值。典型的应用场景是统计每天的日活、月活，这些用基于行的方式不好表示。

我们再来看关于指定窗口大小的表达式：

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

UNBOUNDED PRECEDING 边界是分区中的第一行

UNBOUNDED FOLLOWING 边界是分区中的最后一行

expr PRECEDING  边界是当前行减去expr的值

expr FOLLOWING  边界是当前行加上expr的值

关于 expr 的有效的表达式可以是：

10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING

注意，有些窗口函数即使指定了FRAME 子句，在计算的时候仍然选择的全分区的数据。这些函数包括：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

FRAME 子句的默认值取决于是否有 ORDER 子句。

当存在于 ORDER BY 时，默认值为分区起始行到当前行，包含和当前行的值相等的其它行。语句相当于是 ROW UNBOUNDED PRECEDING 或者 ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。
当不存在 ORDER BY 时，默认是分区的所有行，即 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

5 窗口函数的限制

窗口函数不能直接用在 UPDATE 和 DELETE 语句中，但可以用在子查询中；
不支持嵌套窗口函数；
聚合窗口函数中不能使用 DISTINCT ；
依赖于当前行的值的滑动窗口端点。

SQL必知必会

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
一文讲透 MySQL 的窗口函数

窗口函数在统计类的需求中很常见，稍微复杂一点的查询需求就有可能用到它，使用窗口函数可以极大的简化我们的 SQL 语句。像 Oracle、SQL Server 这些数据库在较早的版本就支持窗口函数了，MySQL 直到 8.0 版本后才支持它。本文将介绍一些常用的窗口函数的用法。窗口函数按照实现方式分成两种：一种是非聚合窗口函数，另外一种是聚合窗口函数。非聚合窗口函数是相对于聚合窗口函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段
复制链接

扫一扫