MySQL窗口函数的用法

在 MySQL 8.0 版本之前，没有提供排名函数，所以当需要在查询当中实现排名时，必须手写 @ 变量，使用起来比较麻烦。

在 MySQL 8.0 版本中，新增了一个窗口函数，用它可以实现很多新的查询方式。窗口函数类似于 SUM()、COUNT() 这样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。

下面通过案例来讲解通过窗口函数实现排名效果的方法。

创建公司部门表 branch，包含部门的名称和部门人数两个字段，创建语句如下：

mysql> CREATE TABLE branch
     (
     name char(255) NOT NULL,
     brcount INT(11) NOT NULL
     );
mysql> INSERT INTO branch(name,brcount)
     VALUES('branch1',5),
     ('branch2',10),
     ('branch3',8),
     ('branch4',20),
     ('branch5',9);

查询数据表 branch 中的数据：

mysql> SELECT * FROM branch;
+---------+----------+
| name    | brcount  |
+---------+----------+
| branch1 |       5  |
| branch2 |      10  |
| branch3 |       8  |
| branch4 |      20  |
| branch5 |       9  |
+---------+----------+

对公司部门人数按从小到大进行排名，可以利用窗口函数来实现：

mysql> SELECT *, rank() OVER w1 AS 'rank' FROM branch  window w1 AS (ORDER BY brcount);
+---------+---------+------+
| name    | brcount | rank |
+---------+---------+------+
| branch1 |      5  |   1  |
| branch3 |      8  |   2  |
| branch5 |      9  |   3  |
| branch2 |     10  |   4  |
| branch4 |     20  |   5  |
+---------+---------+------+

这里创建了名称为 w1 的窗口函数，规定对 brcount 字段进行排序，然后在 SELECT 子句中对窗口函数 w1 执行 rank() 方法，将结果输出为 rank 字段。

需要注意，这里的 window w1 是可选的。例如，在每一行中加入员工的总数，可以这样操作：

mysql> SELECT *, SUM(brcount) over() as total_count FROM branch;
+---------+---------+-------------+
| name    | brcount | total_count |
+---------+---------+-------------+
| branch1 |     5   |       52    |
| branch2 |     10  |       52    |
| branch3 |      8  |       52    |
| branch4 |     20  |       52    |
| branch5 |      9  |       52    |
+---------+---------+-------------+

可以一次性查询出每个部门的员工人数占总人数的百分比，查询结果如下：

mysql> SELECT *,(brcount)/(sum(brcount) over()) AS rate FROM branch;
+---------+---------+--------+
| name    | brcount | rate   |
+---------+---------+--------+
| branch1 |     5   | 0.0962 |
| branch2 |    10   | 0.1923 |
| branch3 |     8   | 0.1538 |
| branch4 |    20   | 0.3846 |
| branch5 |     9   | 0.1731 |
+---------+---------+--------+

MySQL窗口函数的用法

相关文章