MySQL 窗口函数

MySQL 8.0 引入了 窗口函数(window functions) ,我们看一下都有哪些功能。

NameDescription
CUME_DIST()Cumulative distribution value
DENSE_RANK()Rank of current row within its partition, without gaps
FIRST_VALUE()Value of argument from first row of window frame
LAG()Value of argument from row lagging current row within partition
LAST_VALUE()Value of argument from last row of window frame
LEAD()Value of argument from row leading current row within partition
NTH_VALUE()Value of argument from N-th row of window frame
NTILE()Bucket number of current row within its partition.
PERCENT_RANK()Percentage rank value
RANK()Rank of current row within its partition, with gaps
ROW_NUMBER()Number of current row within its partition

这里我们使用 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

 647 total views

十一月读书计划


十一月读书计划:

1.房思琪的初恋乐园,林奕含,北京联合出版公司,2018

 2,394 total views,  4 views today

Nginx Log Rotation

Nginx 日志切割,在网上查了很多教程。还不如直接看 Ubuntu / CentOS 打包的 Nginx 日志切割配置靠谱。

CentOS 8

sudo dnf install nginx
cat /etc/logrotate.d/nginx
/var/log/nginx/*log {
     create 0664 nginx root
     daily
     rotate 10
     missingok
     notifempty
     compress
     sharedscripts
     postrotate
         /bin/kill -USR1 cat /run/nginx.pid 2>/dev/null 2>/dev/null || true
     endscript
 }

Ubuntu 20.10

sudo apt install nginx
cat /etc/logrotate.d/nginx
/var/log/nginx/*.log {
     daily
     missingok
     rotate 14
     compress
     delaycompress
     notifempty
     create 0640 www-data adm
     sharedscripts
     prerotate
         if [ -d /etc/logrotate.d/httpd-prerotate ]; then \
             run-parts /etc/logrotate.d/httpd-prerotate; \
         fi \
     endscript
     postrotate
         invoke-rc.d nginx rotate >/dev/null 2>&1
     endscript
 }

参考:

https://www.nginx.com/resources/wiki/start/topics/examples/logrotation/

https://www.digitalocean.com/community/tutorials/how-to-configure-logging-and-log-rotation-in-nginx-on-an-ubuntu-vps

 255 total views,  1 views today

九月读书计划

九月读书计划:

1.多余的人,莱蒙托夫,云南美术出版社,2019

 203 total views,  1 views today

八月读书计划

一年一度的上海书展又要开了,今年因为疫情的原因应该不会去了。

在书架上找了两年前去上海书展买的几本书,一直放在那里没看,不如趁这个机会看了吧。


八月读书计划:

1.既见君子, 张定浩, 华东师范大学出版社, 2014

2.取瑟而歌, 张定浩, 华东师范大学出版社, 2018

 115 total views

七月读书计划

七月读书计划:

1.美妙的瞬间:普希金诗选, 普希金, 广西师范大学出版社, 2014

 89 total views,  1 views today

五月读书计划

五月读书计划:

1.GraphQL 学习指南,Eve Porcello and Alex Banks,机械工业出版社,2019

 5,750 total views

四月读书计划

四月读书计划:

1.前夜,屠格涅夫,生活·读书·新知三联书店,2019

 387 total views,  1 views today