最近这段时间在刷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;