mysql中的开窗函数,你了解多少?
开窗函数
什么是开窗函数? 开窗函数是在MySQL8.0以后才新加的功能,因此,要想直接使用开窗函数,则mysql版本要8.0以上。其实开窗函数是在满足某种条件的记录集合上执行的特殊函数。开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。接下来,我们介绍开窗函数的相关语法;具体语法如下:
开窗函数名([< 字段名> ]) over ([partition by < 分组字段> ] [order by < 排序字段> [desc ]] [< 细分窗口> ])
rows between xxx and xxx 可出现的关键词: unbounded :无限的 preceding :在…之前 following:在…之后 current row:当前行
-- over()中的关键字:
-- 行数在前2行和后4行
over(order by salary rows between 2 preceding and 4 following)
-- 下面三条语句等效(都是查所有数据):
-- 行数前无限到后无限
over(order by salary rows between unbounded preceding and unbounded following)
over(order by salary range between unbounded preceding and unbounded following)
-- 等效
over (partition by null )
row_number () over (partition by … order by …)
rank () over (partition by … order by …)
dense_rank () over (partition by … order by …)
count () over (partition by … order by …)
max () over (partition by … order by …)
min () over (partition by … order by …)
sum () over (partition by … order by …)
avg () over (partition by … order by …)
first_value () over (partition by … order by …)
last_value () over (partition by … order by …)
lag () over (partition by … order by …)
lead () over (partition by … order by …)
引入 oracle 数据库的 Scott 测试表数据
数据说明
1.emp员工表
2.dept部门表
3.salgrade工资等级表
查询所有员工的平均工资
select avg (sal) from emp;
使用开窗函数实现
select * ,avg (sal) over () from emp;
从结果上看我们的窗口函数显示的是每一行,而普通的聚合函数则是只有一个结果
查询部门的平均工资
select deptno, avg (sal) avg_sal from emp group by deptno;
接下来使用开窗函数查询部门的平均工资
select * ,avg (sal) over (partition by deptno) avg_sal from emp;
既然开窗函数都可以通过我们的一般聚合函数实现,那么为什么还要有开窗函数呢?其实,我们在前下面的一个案例,就只能用开窗函数实现了,其它的函数不可以实现的;
查询员工工资,并显示第一行到最后一行的工资汇总
select emp.* , sum (sal)over (order by sal rows between unbounded preceding and unbounded following )from emp;
查询员工工资,并显示第一行到当前行的工资汇总
select emp.* , sum (sal)over (order by sal rows between unbounded preceding and current row )from emp;
查询员工工资,并显示从当前行到最后一行的汇总
select emp.* , sum (sal)over (order by sal rows between current row and unbounded following )from emp;
查询员工工资,并显示从上一行到当前行的汇总
select e.* ,sum (sal) over (order by sal rows between 1 preceding and current row ) from emp e;
查询员工工资,并显示上一行和下一行的汇总
select emp.* ,sum (SAL) over ( order by SAL rows between 1 preceding and 1 following ) from emp;
查询员工工资,按照部门分组并显示每个组的工资总和
select emp.* ,sum (sal) over (partition by emp.deptno order by sal rows between unbounded preceding and unbounded following )from emp;
查询员工工资,按照部门分组并显示每个组从第一行到当前行的工资总和
select emp.* ,sum (sal)over (partition by deptno order by sal rows between unbounded preceding and current row )from emp;
普通的聚合函数用group by分组,每个分组返回一个统计值。 分析函数采用partition by分组,并且每组每行都可以返回一个统计值。
first_value()与last_value()
取首尾记录值
select emp.* ,last_value (sal) over (order by sal) from emp;
select emp.* ,first_value (sal) over (order by sal) from emp;
select emp.* ,last_value (sal) over (order by sal rows between unbounded preceding and unbounded following ) from emp;
rank(),dense_rank()与row_number():求排序 以下三个分析函数用于计算一个行在一组有序行中的排位 序号从1开始 ROW_NUMBER 返回连续的排序,不论值是否相等 DENSE_RANK 具有相等值的行排序相同,序号是连续的 RANK 具有相等值的行排序相同,序数随后跳跃
select emp.* ,row_number () over (order by sal desc ) from emp;
select emp.* ,rank () over (order by sal) from emp;
select emp.* ,dense_rank () over (order by sal) from emp;
lag() 允许您回顾多行并从当前行访问行的数据
lead()从同一结果集中的当前行访问后续行的数据。
select emp.* ,lag (SAL,1 ) over (order by sal) from emp;
select emp.* ,lead (SAL,1 ) over (order by sal) from emp;
过了复试线,但没联系好老师怎么办?首先恭喜你过了考研的复试线。最好联系一位老师,不过不联系也没关系。今年的研究生复试情况稍微特殊,受疫情影响,并不像以前的情况(多数学校复试条件仍是笔试面试),今年多数高校施行的是网
小学不补课的孩子,中学后补课吗?我家孩子今年是小学四年级升五年级,从来没有补过课,包括兴趣班都没上过,原来我有打算让上一个书法班,结果在我们乡镇里面根本找不到书法班,令人失望,所以迄今为止什么班都没上过。但是纵观
井下工人8小时工作合理吗?我有权威发言,干煤矿二十四年,八点班早上五点起床,洗脸刷牙,骑车到矿(半点),食堂吃饭,换做衣进班(六点二十进班),四十五分进结束,下井路上一小时,到达工作大巷,七点四十五,到工作
有没有遇到过事赶事的情况?每个人的一生,多少都会碰见一些特倒霉的时候,事赶事,一件接一件!就拿我来说,2021的下半年,真的就是很倒霉。从7月到12月,每个月都在医院打逛逛。7月底,我因怀疑乳腺癌,住院手术
有什么事情是去了孟加拉国才知道的?去过一次孟加拉国后,我就飘了!我一个月工资才6000元,在国内日常开销都不够,没想到去了孟加拉国后却成了顶级富豪。在这里30万可以买一处豪宅,最多可以娶四个老婆,顿时感觉人生到达了
32岁了一事无成,欠了30多万,逾期了很多,有跟我一样的吗?本人女,30岁,没结婚没孩子,没车没房住农村里,也没有稳定工作,好歹也是大学毕业,高不成低不就,工厂不愿去,好的工作靠关系轮不上我,自己家里也是农村的,没有关系,也没有亲戚朋友帮衬
今年60岁退休,养老金3200,每月存多少可以老而无忧?我来帮你算算账。生活开支。1一日三餐。上了60岁,胃口自然没有年轻人大,也不会如他们般挑剔。俗话说青菜萝卜营养好,加上豆腐香菇,再来点瘦肉鱼虾。按一天15元计,450元一个月足以,
你会不会看不起农民,为什么?我出生农民家庭,从小生活在农村,农村人最朴实,最善良,农民像牛,任劳任怨为我国耕种土地,把粮食贡献给国家,吃的是草,挤出的是奶,又像蜡烛,燃烧了自已,照亮了别人,像蜜蜂一样辛勤劳动
坐牢出狱后,是不是开公司免税三年?美帝国主义可能是这样,坐过牢是选举总统的加分项。复员兵我记得倒是有税收优惠。一个蹲笆篱子出来的社会边缘人,还想跟兵哥哥一个待遇?多吃点菜,少喝酒,这样就不会做梦了要多吃一点花生米,
开车遇到红白喜事丧事,到底怎么做才妥当?现在就连农村人买车也不是啥新鲜事,以车代步是社会发展,时代进步的一个标志村村通公路,还是用水泥浇铸的,是农村经济腾飞的致富路,也是无法置否的事实。有路必有车经过,农村如此,城市也一
公司里有个男同事,娘娘腔,非常讨厌,但是职位比我高,应当如何相处?人都有自已的喜好,也可以有自己想法,但不可以自己的喜好要求别人。职场之大,无奇不有。如果你有洁癖,看见每天衣冠不整的同事是不是有意见如果你是个及其认真的人,遇见大大咧咧的同事是不是