tanghengzhi.com

MySQL 8.0 引入了 窗口函数（window functions） ，我们看一下都有哪些功能。

这里我们使用 world 示例数据库进行演示，首先使用 docker 安装 mysql，然后导入 world.sql。

docker run --name db -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker exec -it db mysql -u root -p < C:\Users\tanghengzhi\Downloads\world.sql

比较常用的有排序：rank(), dense_rank() 和 row_number() 。

mysql> select Name, LifeExpectancy, rank() over (w), dense_rank() over(w), row_number() over(w) 
from country 
where Continent = 'Europe' 
window w as (order by LifeExpectancy desc) 
limit 10;
+---------------+----------------+-----------------+----------------------+----------------------+
| Name          | LifeExpectancy | rank() over (w) | dense_rank() over(w) | row_number() over(w) |
+---------------+----------------+-----------------+----------------------+----------------------+
| Andorra       |           83.5 |               1 |                    1 |                    1 |
| San Marino    |           81.1 |               2 |                    2 |                    2 |
| Switzerland   |           79.6 |               3 |                    3 |                    3 |
| Sweden        |           79.6 |               3 |                    3 |                    4 |
| Iceland       |           79.4 |               5 |                    4 |                    5 |
| Gibraltar     |           79.0 |               6 |                    5 |                    6 |
| Italy         |           79.0 |               6 |                    5 |                    7 |
| Spain         |           78.8 |               8 |                    6 |                    8 |
| France        |           78.8 |               8 |                    6 |                    9 |
| Liechtenstein |           78.8 |               8 |                    6 |                   10 |
+---------------+----------------+-----------------+----------------------+----------------------+
10 rows in set (0.00 sec)

还有第 N 行的值，first_value(), last_value() 和 nth_value()。

mysql> select Name, LifeExpectancy, first_value(Name) over(w) as firth, last_value(Name) over(w) as last, nth_value(Name, 2) over(w) as second, nth_value(Name, 4) over (w) as fourth 
from country 
where Continent = 'Europe' 
window w as (order by LifeExpectancy desc) 
limit 10;
+---------------+----------------+---------+------------+------------+--------+
| Name          | LifeExpectancy | firth   | last       | second     | fourth |
+---------------+----------------+---------+------------+------------+--------+
| Andorra       |           83.5 | Andorra | Andorra    | NULL       | NULL   |
| San Marino    |           81.1 | Andorra | San Marino | San Marino | NULL   |
| Switzerland   |           79.6 | Andorra | Sweden     | San Marino | Sweden |
| Sweden        |           79.6 | Andorra | Sweden     | San Marino | Sweden |
| Iceland       |           79.4 | Andorra | Iceland    | San Marino | Sweden |
| Gibraltar     |           79.0 | Andorra | Italy      | San Marino | Sweden |
| Italy         |           79.0 | Andorra | Italy      | San Marino | Sweden |
| Spain         |           78.8 | Andorra | Monaco     | San Marino | Sweden |
| France        |           78.8 | Andorra | Monaco     | San Marino | Sweden |
| Liechtenstein |           78.8 | Andorra | Monaco     | San Marino | Sweden |
+---------------+----------------+---------+------------+------------+--------+
10 rows in set (0.00 sec)

其他的窗口函数不是很常用，就不一一介绍了，需要用到的请参考官方文档。

接下来是一个在实际使用中遇到的 Bug：

由于涉及到具体业务，这里不方便直接展示相关 SQL 语句。

还是使用演示数据库，查询各大洲的总人口，总人口排名，以及该洲人口最多的国家，按照总人口倒叙排列。

mysql> select Continent, sum(Population), rank() over(order by sum(Population) desc) as `rank`, substring_index(group_concat(Name order by Population desc), ',', 1) as `The most populous country` 
from country 
group by Continent 
order by sum(Population) desc;

MySQL 8.0.16：

+---------------+-----------------+------+----------------------------------------------+
| Continent     | sum(Population) | rank | The most populous country                    |
+---------------+-----------------+------+----------------------------------------------+
| Asia          |      3705025700 |    1 | China                                        |
| Africa        |       784475000 |    2 | Nigeria                                      |
| Europe        |       730074600 |    3 | Russian Federation                           |
| North America |       482993000 |    4 | United States                                |
| South America |       345780000 |    5 | Brazil                                       |
| Oceania       |        30401150 |    6 | Australia                                    |
| Antarctica    |               0 |    7 | South Georgia and the South Sandwich Islands |
+---------------+-----------------+------+----------------------------------------------+
7 rows in set (0.00 sec)

MySQL 8.0.18:

+---------------+-----------------+------+----------------------------------------------+
| Continent     | sum(Population) | rank | The most populous country                    |
+---------------+-----------------+------+----------------------------------------------+
| Asia          |      3705025700 |    1 | South Georgia and the South Sandwich Islands |
| Africa        |       784475000 |    2 | South Georgia and the South Sandwich Islands |
| Europe        |       730074600 |    3 | South Georgia and the South Sandwich Islands |
| North America |       482993000 |    4 | South Georgia and the South Sandwich Islands |
| South America |       345780000 |    5 | South Georgia and the South Sandwich Islands |
| Oceania       |        30401150 |    6 | South Georgia and the South Sandwich Islands |
| Antarctica    |               0 |    7 | South Georgia and the South Sandwich Islands |
+---------------+-----------------+------+----------------------------------------------+
7 rows in set (0.00 sec)

可以看到，同样一个 SQL 语句，在 MySQL 8.0.16 和 MySQL 8.0.18 版本的查询结果不一样的。最新的 MySQL 8.0.22 版本也存在这个问题，目前已经反馈给阿里云，阿里云的小伙伴提交了 MySQL Bug，等待后续解决方案吧。

在这个 Bug 被修复之前，只能先临时修改一下 SQL 语句，如下：

mysql> select Continent, sum(Population), rank() over(w) as `rank`, substring_index(group_concat(Name order by Population desc), ',', 1) as `The most populous country` 
from country 
group by Continent 
window w as (order by sum(Population) desc);

参考：

https://hub.docker.com/_/mysql

https://dev.mysql.com/doc/world-setup/en/

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

https://bugs.mysql.com/bug.php?id=101691

 497 total views,  1 views today