详解Mysql 窗口函数 解决排名问题yyds

最近这段时间在刷leetcode数据库的题，在题解中发现窗口函数可以解决很多排名的问题

but  before this ……我并不知道有<窗口函数>这样的概念。可能是一直再用mysql5.6的版本，没有对mysql8的版本有过了解^^^ 简言之：窗口函数只有mysql8以上的版本才支持

so  make a simple understanding of this

窗口函数有一个大体的分类

 一、基本语法：

        函数    OVER（）；

        参数体：

        partition by字句：分组。窗口函数按照哪些字段分组，在不同的分组上分别执行；

        order by字句：排序。窗口 函数按照哪些字段排序；

例如：select *,dense_rank()   over(partition by uid order by salary desc) from employee;

  二、分类实例

        1、序号函数：row_number()、rank()、dense_rank()

        应用场景：排名，求最高

SELECT
	*,
	dense_rank ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `dense_rank`,
	rank ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `rank`,
	row_number ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `row_number` 
FROM
	emplyee;

        结果：

 对比发现：

dense_rank()：按照排名来，有并列就是并列第几，然后按照顺序排名；

rank()：并列的就是并列第几，但是后面的排名会算在并列的后面，例如并列第一的有3位，接下来的顺序就是从4开始的；

row_number()：不管并列，一切按照顺序来进行排名

        2、分布函数         percent_rank()     cume_dist()   

percent_rank()：用于计算分区或结果集中行的百分比

结果计算方法：(rank - 1) / (total_rows - 1)

SELECT
	*,
	rank ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `rank`,
	percent_rank ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `percent_rank` 
FROM
	emplyee;

结果         

 cume_dist()：小于或等于这个值的行数除以总行数的行数

结果算法：row_number()/total_rows;查询小于等于当前成绩（score）的比例

SELECT
	*,
	row_number ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `row_number`,
	cume_dist ( ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `cume_dist` 
FROM
	emplyee;

 结果：

 

        3、前后函数   lag()         lead()

lag(param1,param2,param3)：param1:表中列名，param2:前N行，param3:超出行数时默认设置值；

lead()：同上；

LAG()
select *,lag(salary,1) over(partition by departmentid order by salary desc) from emplyee;


Lead()
select *,lead(salary,1) over(partition by departmentid order by salary desc) from emplyee;

 结果：lag()

         lead()

        4、头尾函数     first_value()     last_value()

         应用场景：截止到当前成绩，查询第1个和最后1个同学的分数

first_value()/last_val():返回第一个或者最后一个的值

SELECT
	*,
	first_value ( salary ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `first_value`,
	last_value ( salary ) over ( PARTITION BY departmentid ORDER BY salary DESC ) AS `last_value` 
FROM
	emplyee;