MySQL窗口函数

MySQL8开始支持窗口函数，对于查询中的每一行，使用与该行相关的行进行计算，我们通过如下一个示例来初步理解下窗口函数：

有如下一张表和数据：

CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `age` int NOT NULL COMMENT '年龄',
  `gender` int NOT NULL COMMENT '性别',
  `score` int NOT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO t1(name, age, gender, score) values('刘备', 50, 0, 20), ('曹操', 55, 0, 30), ('许褚', 40, 0, 20), ('赵云', 60, 0, 90), ('貂蝉', 40, 1, 79), ('甄夫人', 80, 1, 10);

结果：

mysql> select * from t1;
+----+--------+-----+--------+-------+
| id | name   | age | gender | score |
+----+--------+-----+--------+-------+
|  1 | 刘备   |  50 |      0 |    20 |
|  2 | 曹操   |  55 |      0 |    30 |
|  3 | 许褚   |  40 |      0 |    20 |
|  4 | 赵云   |  60 |      0 |    90 |
|  5 | 貂蝉   |  40 |      1 |    79 |
|  6 | 甄夫人 |  80 |      1 |    10 |
+----+--------+-----+--------+-------+
6 rows in set (0.04 sec)

针对上面这个表，我们考虑如下一个功能，要查出所有记录，并且每个记录上要展示出来该条记录对应性别的所有人的总分以及平均分，展示效果类似如下图：

在没有窗口函数前我们应该怎么做呢？可以使用子查询。

mysql> select *, (select avg(score) from t1 x2 where x2.gender = x1.gender) avgScore, (select sum(score) from t1 x2 where x2.gender = x1.gender) sumScore from t1 x1;
+------+--------+------+--------+-------+----------+----------+
| id   | name   | age  | gender | score | avgScore | sumScore |
+------+--------+------+--------+-------+----------+----------+
|    1 | 刘备   |   50 |      0 |    20 |  40.0000 |      160 |
|    2 | 曹操   |   55 |      0 |    30 |  40.0000 |      160 |
|    3 | 许褚   |   40 |      0 |    20 |  40.0000 |      160 |
|    4 | 赵云   |   60 |      0 |    90 |  40.0000 |      160 |
|    5 | 貂蝉   |   40 |      1 |    79 |  44.5000 |       89 |
|    6 | 甄夫人 |   80 |      1 |    10 |  44.5000 |       89 |
+------+--------+------+--------+-------+----------+----------+
6 rows in set (0.00 sec)

如果使用窗口函数则可以使用如下SQL

mysql> select *, avg(score) OVER (PARTITION BY gender) as avgScore, sum(score) OVER (PARTITION BY gender) as sumScore from t1 ;
+----+--------+-----+--------+-------+----------+----------+
| id | name   | age | gender | score | avgScore | sumScore |
+----+--------+-----+--------+-------+----------+----------+
|  1 | 刘备   |  50 |      0 |    20 |  40.0000 |      160 |
|  2 | 曹操   |  55 |      0 |    30 |  40.0000 |      160 |
|  3 | 许褚   |  40 |      0 |    20 |  40.0000 |      160 |
|  4 | 赵云   |  60 |      0 |    90 |  40.0000 |      160 |
|  5 | 貂蝉   |  40 |      1 |    79 |  44.5000 |       89 |
|  6 | 甄夫人 |  80 |      1 |    10 |  44.5000 |       89 |
+----+--------+-----+--------+-------+----------+----------+
6 rows in set (0.00 sec)

两者效果是完全一样的，可以看到使用窗口函数的SQL更加简洁清晰。

SQL中avg(score) OVER (PARTITION BY gender) as avgScore, sum(score) OVER (PARTITION BY gender) as sumScore就是窗口操作，在窗口操作中使用了聚合函数。

窗口的意义就是根据不同的条件，将数据划分为不同的分区，通过partition by语法来划分，比如上面的partition by gender就是通过gender字段来划分。

分类上图中的两个区，然后针对不同的区使用具体的窗口函数或者是聚合函数。

窗口操作通常会跟聚合来对比，聚合操作我们都知道，会根据条件将一组数据通过聚合函数汇聚为一条记录，而窗口操作则不然，他不会缩减行数，而是针对每一行（当前行）的数据应用与当前行相关的窗口操作。

其语法如下：

聚合函数|窗口函数 OVER (窗口定义) | OVER 窗口名

窗口定义和窗口名这两种方式都是允许的。

对于窗口定义方式，其语法如下：

窗口定义:
    [窗口明] [分区子句] [排序子句] [Frame子句]

上面都使用中括号表示，代表可选，如果都不设置，窗口操作就会退化成为聚合函数|窗口函数 over()，此时窗口就包含所有行。

窗口名称：窗口的名字

分区子句：代表如何进行分区，使用partition by 表达式来进行分区，其语法如下：

分区子句:
    PARTITION BY expr [, expr] ... # 可以有多个表达式。

比如：

select *, avg(score) OVER (PARTITION BY gender, age) as avgScore, sum(score) OVER (PARTITION BY gender) as sumScore from t1 ;

排序子句：代表分区内数据排序语句，特别说明的是如果语句中不使用该排序语句，则分区内数据是无序的，并没有隐含的排序规则，所有分区行是对等的，其语法如下：

排序子句:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

跟普通的排序没有什么区别。

Frame子句：分区窗口函数的子句，用于对分区内数据进行筛选，其语法如下：

Frame子句:
    frame单位 frame扩展

Frame单位的语法如下：

frame单位:
    {ROWS | RANGE}

它有有两个值：

ROWS：代表通过行位置进行过滤，比如我只想要第2行到第4行，就可以通过ROWS来控制。

RANGE：通过值范围来控制，如果有排序子句，则以连续相同的值为一个单位，如果无排序，则以整个分区为单位。

frame扩展的语法如下：

{frame_start | frame_between}
# frame_between的语法如下：
frame_between:
    BETWEEN frame_start AND frame_end

这里主要是frame_start和frame_end，他俩的取值如下：

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

CURRENT ROW：边界是当前行

UNBOUNDED PRECEDING：代表当前记录之前的所有记录（包含当前记录）

UNBOUNDED FOLLOWING：代表当前记录之后的所有记录（包含当前记录）

expr PRECEDING：代表当前行之前的expr行记录（包含当前行）

expr following ：代表当前行之后的expr行记录（包含当前行）

对Frame子句进行测试说明：

mysql> select *, sum(score) over(partition by gender order by score  ROWS  CURRENT ROW) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |   20 |
|  3 | 许褚   |  40 |      0 |    20 |   20 |
|  2 | 曹操   |  55 |      0 |    30 |   30 |
|  4 | 赵云   |  60 |      0 |    90 |   90 |
|  6 | 甄夫人 |  80 |      1 |    10 |   10 |
|  5 | 貂蝉   |  40 |      1 |    79 |   79 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

上面的sql中使用了ROWS CURRENT ROW，说明边界是当前行，所有SUM函数只会统计分区中当前行的score。

在看下exp PRECEDING的情况：

mysql> select *, sum(score) over(partition by gender order by score  ROWS 1 PRECEDING) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |   20 |
|  3 | 许褚   |  40 |      0 |    20 |   40 |
|  2 | 曹操   |  55 |      0 |    30 |   50 |
|  4 | 赵云   |  60 |      0 |    90 |  120 |
|  6 | 甄夫人 |  80 |      1 |    10 |   10 |
|  5 | 貂蝉   |  40 |      1 |    79 |   89 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

再看下range的情况

mysql> select *, sum(score) over(partition by gender order by score  range current row) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |   40 |
|  3 | 许褚   |  40 |      0 |    20 |   40 |
|  2 | 曹操   |  55 |      0 |    30 |   30 |
|  4 | 赵云   |  60 |      0 |    90 |   90 |
|  6 | 甄夫人 |  80 |      1 |    10 |   10 |
|  5 | 貂蝉   |  40 |      1 |    79 |   79 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

id = 1和 id = 3的两行中score都是20，所以视为一个单位。

mysql> select *, sum(score) over(partition by gender  range current row) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |  160 |
|  2 | 曹操   |  55 |      0 |    30 |  160 |
|  3 | 许褚   |  40 |      0 |    20 |  160 |
|  4 | 赵云   |  60 |      0 |    90 |  160 |
|  5 | 貂蝉   |  40 |      1 |    79 |   89 |
|  6 | 甄夫人 |  80 |      1 |    10 |   89 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

如果没有指定排序子句，则视整个分区为一个单位。

内置函数

窗口操作可以使用窗口函数，也可以使用聚合函数，MySQL内置了许多窗口函数，接下里一一讲解下。

ROW_NUMBER()

返回其分区中当前行的编号。行号的范围从 1 到分区行数。

mysql> select *, row_number() over w from t1 WINDOW w AS(PARTITION BY gender);
+----+--------+-----+--------+-------+---------------------+
| id | name   | age | gender | score | row_number() over w |
+----+--------+-----+--------+-------+---------------------+
|  1 | 刘备   |  50 |      0 |    20 |                   1 |
|  2 | 曹操   |  55 |      0 |    30 |                   2 |
|  3 | 许褚   |  40 |      0 |    20 |                   3 |
|  4 | 赵云   |  60 |      0 |    90 |                   4 |
|  5 | 貂蝉   |  40 |      1 |    79 |                   1 |
|  6 | 甄夫人 |  80 |      1 |    10 |                   2 |
+----+--------+-----+--------+-------+---------------------+
6 rows in set (0.00 sec)

可以看到两个不同的分区内数据进行排序，上面我没有指定排序，也可以指定排序，比如通过成绩进行排序。

mysql> select *, row_number() over w from t1 WINDOW w AS(PARTITION BY gender order by score desc);
+----+--------+-----+--------+-------+---------------------+
| id | name   | age | gender | score | row_number() over w |
+----+--------+-----+--------+-------+---------------------+
|  4 | 赵云   |  60 |      0 |    90 |                   1 |
|  2 | 曹操   |  55 |      0 |    30 |                   2 |
|  1 | 刘备   |  50 |      0 |    20 |                   3 |
|  3 | 许褚   |  40 |      0 |    20 |                   4 |
|  5 | 貂蝉   |  40 |      1 |    79 |                   1 |
|  6 | 甄夫人 |  80 |      1 |    10 |                   2 |
+----+--------+-----+--------+-------+---------------------+
6 rows in set (0.00 sec)

即便成绩相同，排序号也没有重复。

RANK()

该函数是排名函数，返回数据在分区内的排名，有间隙，比如1,1,3,4，没有2，有两个1，是因为前两个值相同。

mysql> select *, rank() over w from t1 WINDOW w AS(PARTITION BY gender order by score asc);
+----+--------+-----+--------+-------+---------------+
| id | name   | age | gender | score | rank() over w |
+----+--------+-----+--------+-------+---------------+
|  1 | 刘备   |  50 |      0 |    20 |             1 |
|  3 | 许褚   |  40 |      0 |    20 |             1 |
|  2 | 曹操   |  55 |      0 |    30 |             3 |
|  4 | 赵云   |  60 |      0 |    90 |             4 |
|  6 | 甄夫人 |  80 |      1 |    10 |             1 |
|  5 | 貂蝉   |  40 |      1 |    79 |             2 |
+----+--------+-----+--------+-------+---------------+
6 rows in set (0.00 sec)

DENSE_RANK()

该函数是排名函数，返回数据在分区内的排名，无间隙，比如1,1,2,3。

mysql> select *, dense_rank() over w from t1 WINDOW w AS(PARTITION BY gender order by score asc);
+----+--------+-----+--------+-------+---------------------+
| id | name   | age | gender | score | dense_rank() over w |
+----+--------+-----+--------+-------+---------------------+
|  1 | 刘备   |  50 |      0 |    20 |                   1 |
|  3 | 许褚   |  40 |      0 |    20 |                   1 |
|  2 | 曹操   |  55 |      0 |    30 |                   2 |
|  4 | 赵云   |  60 |      0 |    90 |                   3 |
|  6 | 甄夫人 |  80 |      1 |    10 |                   1 |
|  5 | 貂蝉   |  40 |      1 |    79 |                   2 |
+----+--------+-----+--------+-------+---------------------+
6 rows in set (0.00 sec)

第一分区中前两个的score都是20，所以排名都是1，然后是3和4。

PERCENT_RANK()

百分比排名，根据下方的公式计算，其中 rank 是行 rank，行是分区数行：

(rank - 1) / (rows - 1)

mysql> select *, rank() over w, PERCENT_RANK() over w from t1 WINDOW w AS(PARTITION BY gender order by score asc);
+----+--------+-----+--------+-------+---------------+-----------------------+
| id | name   | age | gender | score | rank() over w | PERCENT_RANK() over w |
+----+--------+-----+--------+-------+---------------+-----------------------+
|  1 | 刘备   |  50 |      0 |    20 |             1 |                     0 | (1 - 1) / (4 - 1)
|  3 | 许褚   |  40 |      0 |    20 |             1 |                     0 | (1 - 1) / (4 - 1)
|  2 | 曹操   |  55 |      0 |    30 |             3 |    0.6666666666666666 | (3 - 1) / (4 - 1)
|  4 | 赵云   |  60 |      0 |    90 |             4 |                     1 | (3 - 1) / (4 - 1)
|  6 | 甄夫人 |  80 |      1 |    10 |             1 |                     0 | (1 - 1) / (2 - 1)
|  5 | 貂蝉   |  40 |      1 |    79 |             2 |                     1 | (2 - 1) / (2 - 1)
+----+--------+-----+--------+-------+---------------+-----------------------+
6 rows in set (0.00 sec)

FIRST_VALUE()

返回窗口窗口中的第一个值

mysql> select *, first_value(score) over w from t1 WINDOW w AS(PARTITION BY gender order by score asc);
+----+--------+-----+--------+-------+---------------------------+
| id | name   | age | gender | score | first_value(score) over w |
+----+--------+-----+--------+-------+---------------------------+
|  1 | 刘备   |  50 |      0 |    20 |                        20 |
|  3 | 许褚   |  40 |      0 |    20 |                        20 |
|  2 | 曹操   |  55 |      0 |    30 |                        20 |
|  4 | 赵云   |  60 |      0 |    90 |                        20 |
|  6 | 甄夫人 |  80 |      1 |    10 |                        10 |
|  5 | 貂蝉   |  40 |      1 |    79 |                        10 |
+----+--------+-----+--------+-------+---------------------------+
6 rows in set (0.00 sec)

第一个窗口中的第一个score值是20，第二个窗口中第一个score值是10。

CUME_DIST()

返回累积分布值，根据排序规则计算，比如目前升序排列，则返回小于等于当前行某个列值的记录数/分区记录总数。

mysql>  select *, cume_dist() over w from t1 WINDOW w AS(PARTITION BY gender order by score asc);
+----+--------+-----+--------+-------+--------------------+
| id | name   | age | gender | score | cume_dist() over w |
+----+--------+-----+--------+-------+--------------------+
|  1 | 刘备   |  50 |      0 |    20 |                0.5 |
|  3 | 许褚   |  40 |      0 |    20 |                0.5 |
|  2 | 曹操   |  55 |      0 |    30 |               0.75 |
|  4 | 赵云   |  60 |      0 |    90 |                  1 |
|  6 | 甄夫人 |  80 |      1 |    10 |                0.5 |
|  5 | 貂蝉   |  40 |      1 |    79 |                  1 |
+----+--------+-----+--------+-------+--------------------+

要知道窗口函数按照性别分区，会得到gender = 0和gender = 1的两个分区，每个分区中按照score字段升序。

在gender = 0的分区中有四条记录，id值分别是1，3， 2，4。

id=1的记录score=20，在这四个记录总小于等于20的记录有2条，所以cume_dist()的计算结果是2 / 4 = 0.5。其他记录同理。

LAST_VALUE()

返回分区内的最后一个值

mysql> select *, last_value(score) over(partition by gender) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |   90 |
|  2 | 曹操   |  55 |      0 |    30 |   90 |
|  3 | 许褚   |  40 |      0 |    20 |   90 |
|  4 | 赵云   |  60 |      0 |    90 |   90 |
|  5 | 貂蝉   |  40 |      1 |    79 |   10 |
|  6 | 甄夫人 |  80 |      1 |    10 |   10 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

LAG()

LAG(expr [, N[, default]]) [null_treatment] over_clause函数，允许回查多行并从当前行访问行的数据。

expr:该表达式是最终返回的值，可以是一列名，也可以是使用函数处理的一列或者多列等等任意表达式。

N：代表偏移量，可省略，默认值是1。

default：返回的默认值，如果没找到第N行，则返回默认值NULL。

mysql> select *, lag(score, 1) over(partition by gender order by score desc) from t1;
+----+--------+-----+--------+-------+-------------------------------------------------------------+
| id | name   | age | gender | score | lag(score, 1) over(partition by gender order by score desc) |
+----+--------+-----+--------+-------+-------------------------------------------------------------+
|  4 | 赵云   |  60 |      0 |    90 |                                                        NULL |
|  2 | 曹操   |  55 |      0 |    30 |                                                          90 |
|  1 | 刘备   |  50 |      0 |    20 |                                                          30 |
|  3 | 许褚   |  40 |      0 |    20 |                                                          20 |
|  5 | 貂蝉   |  40 |      1 |    79 |                                                        NULL |
|  6 | 甄夫人 |  80 |      1 |    10 |                                                          79 |
+----+--------+-----+--------+-------+-------------------------------------------------------------+
6 rows in set (0.00 sec)

LEAD()

LEAD()的语法与LAG()一样，只是查找的方向不同，LEAD()向前查找。

mysql> select *, lead(score, 1) over(partition by gender order by score desc) a from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | a    |
+----+--------+-----+--------+-------+------+
|  4 | 赵云   |  60 |      0 |    90 |   30 |
|  2 | 曹操   |  55 |      0 |    30 |   20 |
|  1 | 刘备   |  50 |      0 |    20 |   20 |
|  3 | 许褚   |  40 |      0 |    20 | NULL |
|  5 | 貂蝉   |  40 |      1 |    79 |   10 |
|  6 | 甄夫人 |  80 |      1 |    10 | NULL |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

比如id = 4的记录，向前查找得到id = 2的记录，score = 30。

NTH_VALUE()

NTH_VALUE(exp, N)函数是取分区内第N行的数据，返回exp。

mysql> select *, nth_value(score, 2) over(partition by gender order by score) as x from t1;
+----+--------+-----+--------+-------+------+
| id | name   | age | gender | score | x    |
+----+--------+-----+--------+-------+------+
|  1 | 刘备   |  50 |      0 |    20 |   20 |
|  3 | 许褚   |  40 |      0 |    20 |   20 |
|  2 | 曹操   |  55 |      0 |    30 |   20 |
|  4 | 赵云   |  60 |      0 |    90 |   20 |
|  6 | 甄夫人 |  80 |      1 |    10 | NULL |
|  5 | 貂蝉   |  40 |      1 |    79 |   79 |
+----+--------+-----+--------+-------+------+
6 rows in set (0.00 sec)

NTILE(N)

NTILE(N)函数会将分区分为N个桶，为每个桶分配一个编号，并返回这个编号，N是大于0的整数，N即便特别大，比分区数据行数还大，行数也不会增多。

mysql> select *, ntile(5) over(partition by gender) as x from t1;
+----+--------+-----+--------+-------+---+
| id | name   | age | gender | score | x |
+----+--------+-----+--------+-------+---+
|  1 | 刘备   |  50 |      0 |    20 | 1 |
|  2 | 曹操   |  55 |      0 |    30 | 2 |
|  3 | 许褚   |  40 |      0 |    20 | 3 |
|  4 | 赵云   |  60 |      0 |    90 | 4 |
|  5 | 貂蝉   |  40 |      1 |    79 | 1 |
|  6 | 甄夫人 |  80 |      1 |    10 | 2 |
+----+--------+-----+--------+-------+---+
6 rows in set (0.00 sec)

虽然N = 5，但是因为分区行数最大=4，所以最多分为4个桶。

玖叶教程网

前端编程开发入门

窗口函数