MYSQL学习——开窗函数

开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着 不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性， 它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。

语法

开窗函数名 ([< 字段名 >]) over([partition by < 分组字段 >] [ order by < 排序字段 > [ desc ]] [<

细分窗口 >]) 

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由 over 关键字来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足 where 条件的所有行，开窗函数基于所有行进行计 算；如果不为空，则有三个参数来设置窗口：

• partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
• order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
• frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，

frame_start 和 frame_end 可以支持如下关键字，来确定不同的动态行记录：

• current row 边界是当前行，一般和其他范围关键字一起使用
• unbounded preceding 边界是分区中的第一行
• unbounded following 边界是分区中的最后一行
• expr preceding 边界是当前行减去expr的值
• expr following 边界是当前行加上expr的值

 - 开窗函数

- 聚合函数御用开窗函数

- - 所有员工的平均工资

-- 开窗函数
-- 聚合函数用于开窗函数
-- 所有员工的平均工资
select *, avg(sal) over() avg_sal from emp;

-- 各部门的平均工资

-- 各部门的平均工资

select *,avg(sal) over(partition by deptno) avg_sal from emp;

 

-- 各部门按入职日期计算累计工资总和

-- 各部门按入职日期计算累计工资总和
select *, sum(sal) over(partition by deptno order by hiredate) sum_sal from emp;

 

-- 各部门按入职日期计算当前行的前一行和后一行的平均工资

-- 各部门按入职日期计算当前行的前一行和后一行的平均工资
select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) avg_sal from emp;

 

- 序号函数        

row_number()

显示分区中不重复不间断的序号

dense_rank()

显示分区中重复不间断的序号

rank()

显示分区中重复间断的序号

-- 所有员工按入职日期显示排名

-- 序号函数
-- 所有员工按入职日期显示排名
select *, row_count() over(order by hiredate) 排名 from emp;

 

-- 各部门员工按照基本工资显示排名

-- 各部门员工按照基本工资显示排名
select *, 
		row_count() over(partition by deptno order by sal desc) 排名1, 
        dense_rank() over(partition by deptno order by sal desc) 排名2,
        rank() over(partition by deptno order by sal desc) 排名3
from emp;