当前位置：Gxlcms > 数据库问题 > MySQL8.0窗口函数实践及小结

MySQL8.0窗口函数实践及小结

时间：2021-07-01 10:21:17 帮助过：5人阅读

use testdb; Database changed /* 创建表 */ mysql> create table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1)，key idx_stuNo_course(stu_no,course)); Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------+ | Tables_in_testdb | +------------------+ | tb_score | +------------------+ /* 新增一批测试数据 */ mysql> insert into tb_score(stu_no,course,score)values(‘2020001‘,‘mysql‘,90),(‘2020001‘,‘C++‘,85),(‘2020003‘,‘English‘,100),(‘2020002‘,‘mysql‘,50),(‘2020002‘,‘C++‘,70),(‘2020002‘,‘English‘,99); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into tb_score(stu_no,course,score)values(‘2020003‘,‘mysql‘,78),(‘2020003‘,‘C++‘,81),(‘2020003‘,‘English‘,80),(‘2020004‘,‘mysql‘,80),(‘2020004‘,‘C++‘,60),(‘2020004‘,‘English‘,100); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into tb_score(stu_no,course,score)values(‘2020005‘,‘mysql‘,98),(‘2020005‘,‘C++‘,96),(‘2020005‘,‘English‘,70),(‘2020006‘,‘mysql‘,60),(‘2020006‘,‘C++‘,90),(‘2020006‘,‘English‘,70); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into tb_score(stu_no,course,score)values(‘2020007‘,‘mysql‘,50),(‘2020007‘,‘C++‘,66),(‘2020007‘,‘English‘,76),(‘2020008‘,‘mysql‘,90),(‘2020008‘,‘C++‘,69),(‘2020008‘,‘English‘,86); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into tb_score(stu_no,course,score)values(‘2020009‘,‘mysql‘,70),(‘2020009‘,‘C++‘,66),(‘2020009‘,‘English‘,86),(‘2020010‘,‘mysql‘,75),(‘2020010‘,‘C++‘,76),(‘2020010‘,‘English‘,81); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> insert into tb_score(stu_no,course,score)values(‘2020011‘,‘mysql‘,90),(‘2020012‘,‘C++‘,85),(‘2020011‘,‘English‘,84),(‘2020012‘,‘English‘,75),(‘2020013‘,‘C++‘,96),(‘2020013‘,‘English‘,88); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0

2、统计每门课程分数的排名

根据每门课程的分数从高到低进行排名，此时，会出现分数相同时怎么处理的问题，下面就根据不同的窗口函数来处理不同场景的需求

ROW_NUMBER

由结果可以看出，分数相同时按照学号顺序进行排名

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
    -> from tb_score;
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  2 |
| 2020006 | C++     |  90.0 |  3 |
| 2020001 | C++     |  85.0 |  4 |
| 2020012 | C++     |  85.0 |  5 |
| 2020003 | C++     |  81.0 |  6 |
| 2020010 | C++     |  76.0 |  7 |
| 2020002 | C++     |  70.0 |  8 |
| 2020008 | C++     |  69.0 |  9 |
| 2020007 | C++     |  66.0 | 10 |
| 2020009 | C++     |  66.0 | 11 |
| 2020004 | C++     |  60.0 | 12 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  2 |
| 2020002 | English |  99.0 |  3 |
| 2020013 | English |  88.0 |  4 |
| 2020008 | English |  86.0 |  5 |
| 2020009 | English |  86.0 |  6 |
| 2020011 | English |  84.0 |  7 |
| 2020010 | English |  81.0 |  8 |
| 2020003 | English |  80.0 |  9 |
| 2020007 | English |  76.0 | 10 |
| 2020012 | English |  75.0 | 11 |
| 2020005 | English |  70.0 | 12 |
| 2020006 | English |  70.0 | 13 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  3 |
| 2020011 | mysql   |  90.0 |  4 |
| 2020004 | mysql   |  80.0 |  5 |
| 2020003 | mysql   |  78.0 |  6 |
| 2020010 | mysql   |  75.0 |  7 |
| 2020009 | mysql   |  70.0 |  8 |
| 2020006 | mysql   |  60.0 |  9 |
| 2020002 | mysql   |  50.0 | 10 |
| 2020007 | mysql   |  50.0 | 11 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

DENSE_RANK

为了让分数相同时排名也相同，则可以使用DENSE_RANK函数，结果如下：

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn 
    -> from  tb_score  ;    
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  1 |
| 2020006 | C++     |  90.0 |  2 |
| 2020001 | C++     |  85.0 |  3 |
| 2020012 | C++     |  85.0 |  3 |
| 2020003 | C++     |  81.0 |  4 |
| 2020010 | C++     |  76.0 |  5 |
| 2020002 | C++     |  70.0 |  6 |
| 2020008 | C++     |  69.0 |  7 |
| 2020007 | C++     |  66.0 |  8 |
| 2020009 | C++     |  66.0 |  8 |
| 2020004 | C++     |  60.0 |  9 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  1 |
| 2020002 | English |  99.0 |  2 |
| 2020013 | English |  88.0 |  3 |
| 2020008 | English |  86.0 |  4 |
| 2020009 | English |  86.0 |  4 |
| 2020011 | English |  84.0 |  5 |
| 2020010 | English |  81.0 |  6 |
| 2020003 | English |  80.0 |  7 |
| 2020007 | English |  76.0 |  8 |
| 2020012 | English |  75.0 |  9 |
| 2020005 | English |  70.0 | 10 |
| 2020006 | English |  70.0 | 10 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  2 |
| 2020011 | mysql   |  90.0 |  2 |
| 2020004 | mysql   |  80.0 |  3 |
| 2020003 | mysql   |  78.0 |  4 |
| 2020010 | mysql   |  75.0 |  5 |
| 2020009 | mysql   |  70.0 |  6 |
| 2020006 | mysql   |  60.0 |  7 |
| 2020002 | mysql   |  50.0 |  8 |
| 2020007 | mysql   |  50.0 |  8 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

RANK

DENSE_RANK的结果是分数相同时排名相同了，但是下一个名次是紧接着上一个名次的，如果2个并列的第1之后，下一个我想是第3名，则可以使用RANK函数实现

mysql> select stu_no,course,score, rank()over(partition by course order by score desc ) rn 
    -> from  tb_score;
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  1 |
| 2020006 | C++     |  90.0 |  3 |
| 2020001 | C++     |  85.0 |  4 |
| 2020012 | C++     |  85.0 |  4 |
| 2020003 | C++     |  81.0 |  6 |
| 2020010 | C++     |  76.0 |  7 |
| 2020002 | C++     |  70.0 |  8 |
| 2020008 | C++     |  69.0 |  9 |
| 2020007 | C++     |  66.0 | 10 |
| 2020009 | C++     |  66.0 | 10 |
| 2020004 | C++     |  60.0 | 12 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  1 |
| 2020002 | English |  99.0 |  3 |
| 2020013 | English |  88.0 |  4 |
| 2020008 | English |  86.0 |  5 |
| 2020009 | English |  86.0 |  5 |
| 2020011 | English |  84.0 |  7 |
| 2020010 | English |  81.0 |  8 |
| 2020003 | English |  80.0 |  9 |
| 2020007 | English |  76.0 | 10 |
| 2020012 | English |  75.0 | 11 |
| 2020005 | English |  70.0 | 12 |
| 2020006 | English |  70.0 | 12 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  2 |
| 2020011 | mysql   |  90.0 |  2 |
| 2020004 | mysql   |  80.0 |  5 |
| 2020003 | mysql   |  78.0 |  6 |
| 2020010 | mysql   |  75.0 |  7 |
| 2020009 | mysql   |  70.0 |  8 |
| 2020006 | mysql   |  60.0 |  9 |
| 2020002 | mysql   |  50.0 | 10 |
| 2020007 | mysql   |  50.0 | 10 |
+---------+---------+-------+----+
36 rows in set (0.01 sec)

这样就实现了各种排序需求。

NTILE