龙空技术网

mysql中的开窗函数,你了解多少?

腾科IT教育 261

前言:

眼前朋友们对“oracleorderby子查询”大约比较注意,同学们都想要了解一些“oracleorderby子查询”的相关知识。那么小编也在网络上网罗了一些对于“oracleorderby子查询””的相关知识,希望朋友们能喜欢,小伙伴们一起来了解一下吧!

开窗函数

什么是开窗函数? 开窗函数是在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 byorder by …)

rank() over(partition byorder by …)

dense_rank() over(partition byorder by …)

count() over(partition byorder by …)

max() over(partition byorder by …)

min() over(partition byorder by …)

sum() over(partition byorder by …)

avg() over(partition byorder by …)

first_value() over(partition byorder by …)

last_value() over(partition byorder by …)

lag() over(partition byorder by …)

lead() over(partition byorder 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;

标签: #oracleorderby子查询