做网站推广有啥活动,网站后缀pw,方又圆网站建设,制作软件的流程介绍#xff1a;
MSQL8.0新增窗口函数商口函数又被称为开窗函数#xff0c;与Oracle窗口函数类似#xff0c;属于MysaL的一大特点
非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值(即分组)#xff0c;非聚合函数一次只会处理一行数据。窗口聚…介绍
MSQL8.0新增窗口函数商口函数又被称为开窗函数与Oracle窗口函数类似属于MysaL的一大特点
非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值(即分组)非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时可将窗口范围内的数据输入到聚合函数中并不改变行数。
语法结构
window_function ( expr ) OVER( PARTITION BY ... ORDER BY ... frame_clause
)
其中window_function是窗口函数的名称;expr是参数有些函数不需要参数;oVER子句包含三个选项:
分区(PARTITION BY)
PARTITION BY选项用于将数据行拆分成多个分区(组)它的作用类似于GROUP BY分组。如果省略了PARTITION BY所有的数据作为一个组进行计算
排序(ORDER BY)
OVER子句中的ORDER BY选项用于指定分区内的排序方式与ORDER BY子句的作用类似以及
窗口大小(frame_clause)
frame_clause选项用于在当前分区内指定一个计算窗口也就是一个与当前行相关的数据子集
1序号函数
序号函数有三个: kow_NUMBER()、RANK()、DENSE_RANK()可以用来实现分组排序并添加序号。
格式
row_number ( ) | rank ( ) | dense_rank () over ( partition by ... order by ...
)
不加 partition by表示全局排序
--按每个部门的员工按薪资并排名
select
dname,
ename,
salary,
row_number() over (partition by dname order by salary desc) as rn
from employee;select
dname,
ename,
salary,
rank() over (partition by dname order by salary desc) as rn
from employee;--按每个部门的员工按薪资前3名并排名
select * FROM(
select
dname,
ename,
salary,
rank() over (partition by dname order by salary desc) as rn
from employee) as t
where t.rn3;
2开窗聚合函数
在窗口中每条记录动态地应用聚合函数SUM()、AVG()、MAX()、MIN()、COUNT())可以动态计算在推窗口内的各种聚合函数值。
如果没有order by排序语句默认把分组内的所有数据进行sum操作
select
dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate) as pv1
from employee;select
dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate rows between unbounded preceding and current row) as pv1
from employee;--和上面的结果相同select
dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate rows between 3 unbounded preceding and current row) as pv1
from employee;--从该行向上3行相加select
dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate rows between 3 unbounded preceding and 1 current row) as pv1
from employee;--从该行向上3行以及向后1行包括本行相加
3分布函数
1CUME_DIST
用途:分组内小于、等于当前rank值的行数/分组内总行数
应用场景:查询小于等于当前薪资(salary的比例
select
dname,
ename,
salary,
cume_dist() over ( order by salary desc) as rn ,
cume_dist() over (partition by dname order by salary ) as rn
from employee;列如3000
不分组的话数据中小于等于3000的有12个
故为3/120.252PERCENT_RANK
用途:每行按照公式(rank-1) /(rows-1)进行计算。其中rank为RANK()函数产生的序号rows为当前窗口的记录总行数
应用场景:不常用
select
dname,
ename,
salary,
rank() over (partition by dname order by salary desc) as rn1 ,
percent_rank() over (partition by dname order by salary ) as rn2
from employee;第一行(1-1)/(6-1)0
第二行(1-1)/(6-1)0
第三行(3-1)/(6-1)0.4
4前后函数-LAG和LEAD
介绍
用途:返回位于当前行的前n行LAG(expr,n)或后n行LEAD(expr,n)的expr的值
应用场景:查询前1名同学的成绩和当前同学成绩的差值
select
dname,
ename,
hiredate,
salary,
lag(hiredate,1,2000-01-01) over(partition by dname order by hiredate) as rn1 ,
lag(hiredate,2) over (partition by dname order by hiredate ) as rn2
from employee;select
dname,
ename,
hiredate,
salary,
lead(hiredate,1,2000-01-01) over(partition by dname order by hiredate) as rn1 ,
lead(hiredate,2) over (partition by dname order by hiredate ) as rn2
from employee; 5头尾函数-FIRST_VALUE和LAST_VALUE
用途:返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr)) expr的值
应用场景:截止到当前按照日期排序查询第1个入职和最后1个入职员工的薪资
select
dname,
ename,
hiredate,
salary,
first_value(salary) over(partition by dname order by hiredate) as rn1 ,
last_value(salary) over (partition by dname order by hiredate ) as rn2
from employee;
6其他函数-NTH_VALUE(expr,n)、NTILE(n)
1NTH_VALUE(expr,n)
用途:返回窗口中第n个expr的值。expr可以是表达式也可以是列名
应用场景:截止到当前薪资显示每个员工的薪资中排名第2或者第3的薪资
select
dname,
ename,
hiredate,
salary,
nth_value(salary,2) over(partition by dname order by hiredate) as second_salary ,
nth_value(salary,3) over (partition by dname order by hiredate ) as third_salary
from employee;
2NTILE(n)
用途:将分区中的有序数据分为n个等级记录等级数
应用场景:将每个部门员工按照入职日期分成3组
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate) as rn1
from employee;--每个部门的第一组
select* from (
select
dname,
ename,
hiredate,
salary,
ntile(3) over(partition by dname order by hiredate) as rn1
from employee) as t
where t.rn11;
