还有第 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);