mysql窗口函数的使用

妖狐艹你老母 2022-12-05 12:10 219阅读 0赞

窗口函数作为mysql8.0引入的新功能

可以解决很多排名相关的问题

## 一、建表 ##

create table  student(
    id int ,
    st_name  varchar(20),
    score int,
    date_time varchar(20),
    primary key (id,date_time) 
    );

## 二、插入数据 ##

insert  into student value(101,'张飞',62,'2017');
    insert  into student value(101,'张飞',30,'2007');
    insert  into student value(101,'张飞',82,'2020');
    insert  into student value(101,'张飞',62,'2015');
    insert  into student value(101,'张飞',62,'2019');
    insert  into student value(102,'刘备',92,'2017');
    insert  into student value(102,'刘备',72,'2010');
    insert  into student value(102,'刘备',62,'2011');
    insert  into student value(102,'刘备',98,'2020');
    insert  into student value(103,'孙悟空',98,'2010');
    insert  into student value(103,'孙悟空',98,'2011');
    insert  into student value(103,'孙悟空',98,'2012');
    insert  into student value(103,'孙悟空',97,'2013');
    insert  into student value(103,'孙悟空',98,'2014');
    insert  into student value(103,'孙悟空',99,'2015');
    insert  into student value(103,'孙悟空',100,'2018');
    insert  into student value(104,'哪吒',10,'2010');
    insert  into student value(104,'哪吒',20,'2011');
    insert  into student value(104,'哪吒',30,'2012');
    insert  into student value(104,'哪吒',40,'2013');
    insert  into student value(104,'哪吒',50,'2014');
    insert  into student value(104,'哪吒',40,'2015');
    insert  into student value(104,'哪吒',50,'2016');

## 三、窗口函数 ##

主要分4类：

用法：窗口函数 over(partition by 分组字段 order by 排序字段)  
（1）序号函数：rank()、dense\_rank()、row\_number()

*  select \*,rank() over(partition by id order by score)as ranking from student;

id, st_name, score, date_time, ranking
    101, 张飞, 30, 2007, 1
    101, 张飞, 62, 2015, 2
    101, 张飞, 62, 2017, 2
    101, 张飞, 62, 2019, 2
    101, 张飞, 82, 2020, 5
    102, 刘备, 62, 2011, 1
    102, 刘备, 72, 2010, 2
    102, 刘备, 92, 2017, 3
    102, 刘备, 98, 2020, 4
    103, 孙悟空, 97, 2013, 1
    103, 孙悟空, 98, 2010, 2
    103, 孙悟空, 98, 2011, 2
    103, 孙悟空, 98, 2012, 2
    103, 孙悟空, 98, 2014, 2
    103, 孙悟空, 99, 2015, 6
    103, 孙悟空, 100, 2018, 7
    104, 哪吒, 10, 2010, 1
    104, 哪吒, 20, 2011, 2
    104, 哪吒, 30, 2012, 3
    104, 哪吒, 40, 2013, 4
    104, 哪吒, 40, 2015, 4
    104, 哪吒, 50, 2014, 6
    104, 哪吒, 50, 2016, 6

*  select \*,dense\_rank() over(partition by id order by score)as ranking from student;

id, st_name, score, date_time, ranking
    101, 张飞, 30, 2007, 1
    101, 张飞, 62, 2015, 2
    101, 张飞, 62, 2017, 2
    101, 张飞, 62, 2019, 2
    101, 张飞, 82, 2020, 3
    102, 刘备, 62, 2011, 1
    102, 刘备, 72, 2010, 2
    102, 刘备, 92, 2017, 3
    102, 刘备, 98, 2020, 4
    103, 孙悟空, 97, 2013, 1
    103, 孙悟空, 98, 2010, 2
    103, 孙悟空, 98, 2011, 2
    103, 孙悟空, 98, 2012, 2
    103, 孙悟空, 98, 2014, 2
    103, 孙悟空, 99, 2015, 3
    103, 孙悟空, 100, 2018, 4
    104, 哪吒, 10, 2010, 1
    104, 哪吒, 20, 2011, 2
    104, 哪吒, 30, 2012, 3
    104, 哪吒, 40, 2013, 4
    104, 哪吒, 40, 2015, 4
    104, 哪吒, 50, 2014, 5
    104, 哪吒, 50, 2016, 5

*  select \*,row\_number() over(partition by id order by score)as ranking from student;

id, st_name, score, date_time, ranking
    101, 张飞, 30, 2007, 1
    101, 张飞, 62, 2015, 2
    101, 张飞, 62, 2017, 3
    101, 张飞, 62, 2019, 4
    101, 张飞, 82, 2020, 5
    102, 刘备, 62, 2011, 1
    102, 刘备, 72, 2010, 2
    102, 刘备, 92, 2017, 3
    102, 刘备, 98, 2020, 4
    103, 孙悟空, 97, 2013, 1
    103, 孙悟空, 98, 2010, 2
    103, 孙悟空, 98, 2011, 3
    103, 孙悟空, 98, 2012, 4
    103, 孙悟空, 98, 2014, 5
    103, 孙悟空, 99, 2015, 6
    103, 孙悟空, 100, 2018, 7
    104, 哪吒, 10, 2010, 1
    104, 哪吒, 20, 2011, 2
    104, 哪吒, 30, 2012, 3
    104, 哪吒, 40, 2013, 4
    104, 哪吒, 40, 2015, 5
    104, 哪吒, 50, 2014, 6
    104, 哪吒, 50, 2016, 7

对比上面数据可以看出，根据score进行的排名，当出现相同分数的时候，这三个函数结果就不一样了

**rank：存在并列第几的情况，相同排名的占用排名数值，直到出现下一个不同数值的时候，数值是递增的**

**dense\_rank：存在并列第几的情况，相同排名的不占用排名数值，直到出现下一个不同数值的时候，数值加1即可**

**row\_number：不存在并列第几的情况，排名依次递增即可**

（2）分布函数：percent\_rank()、cume\_dist()

*  select \*,percent\_rank() over(partition by id order by score)as ranking from student;

id, st_name, score, date_time, ranking
    101, 张飞, 30, 2007, 0
    101, 张飞, 62, 2015, 0.25
    101, 张飞, 62, 2017, 0.25
    101, 张飞, 62, 2019, 0.25
    101, 张飞, 82, 2020, 1
    102, 刘备, 62, 2011, 0
    102, 刘备, 72, 2010, 0.3333333333333333
    102, 刘备, 92, 2017, 0.6666666666666666
    102, 刘备, 98, 2020, 1
    103, 孙悟空, 97, 2013, 0
    103, 孙悟空, 98, 2010, 0.16666666666666666
    103, 孙悟空, 98, 2011, 0.16666666666666666
    103, 孙悟空, 98, 2012, 0.16666666666666666
    103, 孙悟空, 98, 2014, 0.16666666666666666
    103, 孙悟空, 99, 2015, 0.8333333333333334
    103, 孙悟空, 100, 2018, 1
    104, 哪吒, 10, 2010, 0
    104, 哪吒, 20, 2011, 0.16666666666666666
    104, 哪吒, 30, 2012, 0.3333333333333333
    104, 哪吒, 40, 2013, 0.5
    104, 哪吒, 40, 2015, 0.5
    104, 哪吒, 50, 2014, 0.8333333333333334
    104, 哪吒, 50, 2016, 0.8333333333333334

这个数值是怎么计算到的呢？答案是:(rank-1) / (rows-1)

rank就是我们前面使用rank()函数计算出来的排名，rows就是行数

比如我们拿张飞来说

第一行的rank=1，因此数值为(1-1)/4=0

第二行的rank=2，因此数值为(2-1)/4=0.25

第三行的rank=2，因此数值为(2-1)/4=0.25

第四行的rank=2，因此数值为(2-1)/4=0.25

第五行的rank=5，因此数值为(5-1)/4=1

*  select \*,cume\_dist() over(partition by id order by score)as ranking from student;

id, st_name, score, date_time, ranking
    101, 张飞, 30, 2007, 0.2
    101, 张飞, 62, 2015, 0.8
    101, 张飞, 62, 2017, 0.8
    101, 张飞, 62, 2019, 0.8
    101, 张飞, 82, 2020, 1
    102, 刘备, 62, 2011, 0.25
    102, 刘备, 72, 2010, 0.5
    102, 刘备, 92, 2017, 0.75
    102, 刘备, 98, 2020, 1
    103, 孙悟空, 97, 2013, 0.14285714285714285
    103, 孙悟空, 98, 2010, 0.7142857142857143
    103, 孙悟空, 98, 2011, 0.7142857142857143
    103, 孙悟空, 98, 2012, 0.7142857142857143
    103, 孙悟空, 98, 2014, 0.7142857142857143
    103, 孙悟空, 99, 2015, 0.8571428571428571
    103, 孙悟空, 100, 2018, 1
    104, 哪吒, 10, 2010, 0.14285714285714285
    104, 哪吒, 20, 2011, 0.2857142857142857
    104, 哪吒, 30, 2012, 0.42857142857142855
    104, 哪吒, 40, 2013, 0.7142857142857143
    104, 哪吒, 40, 2015, 0.7142857142857143
    104, 哪吒, 50, 2014, 1
    104, 哪吒, 50, 2016, 1

这个数值又是怎么计算出来的呢？答案是：rank\_rows / rows

rank\_rows :分组内小于、等于当前rank值的行数 / 分组内总行数

rows:分组内总行数

我们还是拿张飞来说

第一行，rank\_rows等于1，rows等于5，所以结果的0.2

第二行，rank\_rows等于4，rows等于5，所以结果的0.8

第三行，rank\_rows等于4，rows等于5，所以结果的0.8

第四行，rank\_rows等于4，rows等于5，所以结果的0.8

第五行，rank\_rows等于5，rows等于5，所以结果的1

（3）前后函数：lag()、lead()

*  select \*,lag(score,1) over(partition by id order by score)as score from student;

id, st_name, score, date_time, score
    101, 张飞, 30, 2007, 
    101, 张飞, 62, 2015, 30
    101, 张飞, 62, 2017, 62
    101, 张飞, 62, 2019, 62
    101, 张飞, 82, 2020, 62
    102, 刘备, 62, 2011, 
    102, 刘备, 72, 2010, 62
    102, 刘备, 92, 2017, 72
    102, 刘备, 98, 2020, 92
    103, 孙悟空, 97, 2013, 
    103, 孙悟空, 98, 2010, 97
    103, 孙悟空, 98, 2011, 98
    103, 孙悟空, 98, 2012, 98
    103, 孙悟空, 98, 2014, 98
    103, 孙悟空, 99, 2015, 98
    103, 孙悟空, 100, 2018, 99
    104, 哪吒, 10, 2010, 
    104, 哪吒, 20, 2011, 10
    104, 哪吒, 30, 2012, 20
    104, 哪吒, 40, 2013, 30
    104, 哪吒, 40, 2015, 40
    104, 哪吒, 50, 2014, 40
    104, 哪吒, 50, 2016, 50

*  select \*,lead(score,1) over(partition by id order by score)as score from student;

id, st_name, score, date_time, score
    101, 张飞, 30, 2007, 62
    101, 张飞, 62, 2015, 62
    101, 张飞, 62, 2017, 62
    101, 张飞, 62, 2019, 82
    101, 张飞, 82, 2020, 
    102, 刘备, 62, 2011, 72
    102, 刘备, 72, 2010, 92
    102, 刘备, 92, 2017, 98
    102, 刘备, 98, 2020, 
    103, 孙悟空, 97, 2013, 98
    103, 孙悟空, 98, 2010, 98
    103, 孙悟空, 98, 2011, 98
    103, 孙悟空, 98, 2012, 98
    103, 孙悟空, 98, 2014, 99
    103, 孙悟空, 99, 2015, 100
    103, 孙悟空, 100, 2018, 
    104, 哪吒, 10, 2010, 20
    104, 哪吒, 20, 2011, 30
    104, 哪吒, 30, 2012, 40
    104, 哪吒, 40, 2013, 40
    104, 哪吒, 40, 2015, 50
    104, 哪吒, 50, 2014, 50
    104, 哪吒, 50, 2016,

（4）头尾函数：first\_value()、last\_value()

*  select \*,first\_value(score) over(partition by id order by score)as score from student;

id, st_name, score, date_time, score
    101, 张飞, 30, 2007, 30
    101, 张飞, 62, 2015, 30
    101, 张飞, 62, 2017, 30
    101, 张飞, 62, 2019, 30
    101, 张飞, 82, 2020, 30
    102, 刘备, 62, 2011, 62
    102, 刘备, 72, 2010, 62
    102, 刘备, 92, 2017, 62
    102, 刘备, 98, 2020, 62
    103, 孙悟空, 97, 2013, 97
    103, 孙悟空, 98, 2010, 97
    103, 孙悟空, 98, 2011, 97
    103, 孙悟空, 98, 2012, 97
    103, 孙悟空, 98, 2014, 97
    103, 孙悟空, 99, 2015, 97
    103, 孙悟空, 100, 2018, 97
    104, 哪吒, 10, 2010, 10
    104, 哪吒, 20, 2011, 10
    104, 哪吒, 30, 2012, 10
    104, 哪吒, 40, 2013, 10
    104, 哪吒, 40, 2015, 10
    104, 哪吒, 50, 2014, 10
    104, 哪吒, 50, 2016, 10

*  select \*,last\_value(score) over(partition by id order by score)as score from student;

id, st_name, score, date_time, score
    101, 张飞, 30, 2007, 30
    101, 张飞, 62, 2015, 62
    101, 张飞, 62, 2017, 62
    101, 张飞, 62, 2019, 62
    101, 张飞, 82, 2020, 82
    102, 刘备, 62, 2011, 62
    102, 刘备, 72, 2010, 72
    102, 刘备, 92, 2017, 92
    102, 刘备, 98, 2020, 98
    103, 孙悟空, 97, 2013, 97
    103, 孙悟空, 98, 2010, 98
    103, 孙悟空, 98, 2011, 98
    103, 孙悟空, 98, 2012, 98
    103, 孙悟空, 98, 2014, 98
    103, 孙悟空, 99, 2015, 99
    103, 孙悟空, 100, 2018, 100
    104, 哪吒, 10, 2010, 10
    104, 哪吒, 20, 2011, 20
    104, 哪吒, 30, 2012, 30
    104, 哪吒, 40, 2013, 40
    104, 哪吒, 40, 2015, 40
    104, 哪吒, 50, 2014, 50
    104, 哪吒, 50, 2016, 50

### 参考 ：[https://blog.csdn.net/weixin\_39010770/article/details/87862407][https_blog.csdn.net_weixin_39010770_article_details_87862407] ###

[https_blog.csdn.net_weixin_39010770_article_details_87862407]: https://blog.csdn.net/weixin_39010770/article/details/87862407