作者:杨涛涛记起来有一次去讲 MySQL 8.0 开发相关特性,在 QA 环节,有人对 MySQL 的几个非常用窗口函数有些困惑,当时现场给了一些示范并且做了详细的解答,今天我用几个简单例子分享下具体的用法。
资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。
有困惑的是这四个窗口函数(其实是因为我PPT里仅写了这四个窗口函数):frist_value、last_value、nth_value、ntile 。
谈到这四个窗口函数的具体用法,特别是前两个,得先熟悉 MySQL 窗口函数的框架用法。这里提到的窗口函数框架,其实就是定义一个分组窗口的边界,边界可以是具体的行号,也可以是具体的行内容,以这个边界为起点或者终点,来展现分组内的过滤数据。详情见我之前的发稿:https://opensource.actionsky....
接下来我们来看看这四个窗口函数如何使用。
- first_value: 用来返回一个分组窗口里的第一行记录,也即排名第一的那行记录。
localhost:ytt_new>select id,r1,r2 from t1;
+----+------+------+
| id | r1| r2|
+----+------+------+
|1 |10 |20 |
|2 |10 |30 |
|3 |10 |40 |
|4 |10 |50 |
|5 |10 |2 |
|6 |10 |3 |
|7 |11 |100 |
|8 |11 |101 |
|9 |11 |1 |
| 10 |11 |3 |
| 11 |11 |10 |
| 12 |11 |20 |
+----+------+------+
12 rows in set (0.00 sec)
比如想拿到每个分组里的第一名(升序),可以用row_number()函数,我们来回顾下:
localhost:ytt_new>select r1,r2 from (select r1,r2,row_number() over(partition by r1 order by r2) as rn from t1) T where T.rn = 1;
+------+------+
| r1| r2|
+------+------+
|10 |2 |
|11 |1 |
+------+------+
2 rows in set (0.00 sec)
此时如果用first_value来实现,写法会更加简单:
localhost:ytt_new>select distinct r1,first_value(r2) over(partition by r1 order by r2) as first_r2 from t1;
+------+----------+
| r1| first_r2 |
+------+----------+
|10 |2 |
|11 |1 |
+------+----------+
2 rows in set (0.00 sec)
- last_value: 和first_value相反,用来返回分组窗口里的最后一行记录,也即倒数第一的那行记录。
localhost:ytt_new>select distinct r1,last_value(r2) over(partition by r1 order by r2) 'last_r2' fromt1;
+------+---------+
| r1| last_r2 |
+------+---------+
|10 |2 |
|10 |3 |
|10 |20 |
|10 |30 |
|10 |40 |
|10 |50 |
|11 |1 |
|11 |3 |
|11 |10 |
|11 |20 |
|11 |100 |
|11 |101 |
+------+---------+
12 rows in set (0.01 sec)
究其原因是函数last_value的默认框架是 rows between unbounded preceding and current row 。这里默认框架意思是:限制窗口函数的取值边界为当前行和上限无穷大,所以对应的值就是当前行自己。
那正确的框架应该是什么样呢?正确的框架应该是让边界锁定整个分组的上下边缘,也即整个分组的上限与下限之间。所以正确的写法如下:
localhost:ytt_new>select distinct r1,last_value(r2) over(partition by r1 order by r2 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as 'last_r2' from t1;
+------+---------+
| r1| last_r2 |
+------+---------+
|10 |50 |
|11 |101 |
+------+---------+
2 rows in set (0.00 sec)
- nth_value: 用来返回分组内指定行的记录。
localhost:ytt_new>select * from (select distinct r1,nth_value(r2,1) over(partition by r1 order by r2) 'first_r2' fromt1) T where T.first_r2 is not null;
+------+----------+
| r1| first_r2 |
+------+----------+
|10 |2 |
|11 |1 |
+------+----------+
2 rows in set (0.00 sec)
【新特性解读 | MySQL 8.0 窗口函数一次疑问解答】这个函数的功能基本和函数row_number一致。不同的是row_number用来展示排名,而nth_value用来输入排名。
- ntile: 用来在分组内继续二次分组。
localhost:ytt_new>select id,r1,r2 from (select id,r1,r2, ntile(2) over(partition by r1 order by r2) 'ntile
' from t1) T where T.ntile=1;
+----+------+------+
| id | r1| r2|
+----+------+------+
|5 |10 |2 |
|6 |10 |3 |
|1 |10 |20 |
|9 |11 |1 |
| 10 |11 |3 |
| 11 |11 |10 |
+----+------+------+
6 rows in set (0.00 sec)
这四个窗口函数,特别是 last_value 需要注意。 不过在大多数场景下,记住几个常用的窗口函数即可,比如 row_number(),rank() 等等。
推荐阅读
- 使用自动增长的主键、雪花id或uuid作为Mysql主键的区别
- JavaSE|MySQL 数据库约束、聚合查询、多表查询
- Java|第1节 MySQL 架构篇 2021-12-24
- 面试技巧|2021 java面试题目(持续更新...)
- java|分享Java代码的一些小建议,脱离小白——学会优化代码50个方案
- mysql|mysql(InnoDB的主键采用聚簇索引,二级索引不采用聚簇索引)
- java|【Rust日报】2022-03-21 Firefox 现在约 10% 的代码为 Rust
- java|【Rust日报】2021-12-19 Foundry(以太坊应用程序开发工具包)
- MySQL|MySQL知识点串讲【适用于中高级开发】-持续更新