窗口函数是:
相比于聚合函数来说,窗口函数的功能更加强大,窗口函数不同与之前的聚合函数,只能将一张表的多行数据转换为一行的数据,而聚合函数能够将一张表的多行数据转化为新表的多行数据,而不是单行的数据
窗口函数主要包含以下六种类型:
1.序号函数:
2.开窗聚合函数:
3.分布函数:
4.前后函数:
5.头尾函数:
6.其他函数:
一.序号函数:
1. 介绍: 序号函数一共有三种,这三种仅仅只在细节上有所不同,有:row_number() || rank() || dense_rank() 这三种序号函数能够实现分组排序,并且添加上序号
-- 关键语句:
row_nulber()||rank()||densc_rank() over(partition by 列1 order by 列2) as [名称]
2.操作:
-- 一:对每个部门的员工按照薪资排序,并给出排名 select dname,ename,salary, row_number() over(partition by dname ORDER BY salary desc) as rn from employee; 一:对于 row_number()的排列方式来说,是采用了所有数字都用的形式,不会因为相同而并列有排名,只按照人数排名 select dname,ename,salary, rank() over(partition by dname ORDER BY salary desc) as rn from employee; 二:对于 rank()的排列方式,对于相同薪资的人会并列名词,但是下一个人中间会有数字间断 select dname,ename,salary, dense_rank() over(partition by dname ORDER BY salary desc) as rn from employee; 三:对于使用dense_rank()的排列方式,能够有重复的排名,并且写一个排名的数字并不间断 对于排序窗口函数的使用,可以将其理解为新select(查询)了一行的数据,因此,我们也可以在这个基础上对其中的排名进行条件限制(分布查询) -- 二:选出各个部门薪资排名在前三名的人 select dname,ename,salary, ROW_NUMBER() over(PARTITION by dname ORDER BY salary desc) as os from employee ; -- 先查询所有的列表薪资排行 select * from (select dname,ename,salary, ROW_NUMBER() over(PARTITION by dname ORDER BY salary desc) as os from employee) a where a.os<=3; -- 再使用查询的表对其进行选择即可 -- 对所有的员工薪资进行全局排序,并且加上排序 select dname,ename,salary, ROW_NUMBER() over(order by salary desc) as os from employee; -- 能够展现所有部门员工的总的薪资排名,并且加上序号,按照人数进行排名
二:开创聚合函数:
1.介绍:
开窗聚合函数就是将之前所学过聚合函数:sum(), max(), min() ,avg()结合起来,在窗口函数当中进行应用
2.操作:
sum()开窗聚合函数 select dname, ename, salary, sum(salary) over(partition by dname order by hiredate) as os from employee; -- 一: sum开窗聚合函数,先通过使用部门进行分类,之后再将通过日期进行分类,所求的得最新一列代表得是从这一列,包含之前所有的列的总和SUM -- 二:显示每一个部门的员工在这个部门所占的比例 select dname, ename, salary, sum(salary) over(partition by dname) as os from employee; -- 也可以直接将其中的order by 直接去除掉 -- 三:设置相加的范围 -- 从开始到本行为止 select dname, ename, salary, sum(salary) over(partition by dname ORDER BY hiredate rows between unbounded preceding and current row) as c from employee; -- 实际效果跟上面的根据日期分开进行相加一样 -- 关键词:rows between unbounded preceding (代表的是从最开始的部分作为开始) and current (代表当前行) row -- 设置从本行上面三行进行相加到本行 select ename, dname, salary, sum(salary) over(partition by dname rows between 3 preceding and current row) as c from employee; -- 设置从上面三行一直加到到本行的下一行 select ename, dname, salary, sum(salary) over(partition by dname rows between 3 preceding and 1 following) as c from employee; -- 向下多少行 :x following 向上多行:x preceding -- 设置从当前行加到最后 select ename, dname, salary, sum(salary) over(partition by dname rows between current row and unbounded following) as c from employee; -- 关键词:unbounded following本组结尾
三:分布函数:
1.介绍:分布函数能够呈现出以当前行为基准,包括当前行的数据,寻找<=当前行的数据,最后将<=当前行的行数再除以分组的总行数即可
2.关键词:cume_dist()
3.操作:
select dname, ename, salary, cume_dist() over(order by salary) as ai from employee; -- 对于ai的第一行数据的解释,第一行数据为0.08333333333... 在此查询当中仅仅只按照order by 对于薪资进行排序查询,并没有对于部门进行分类,所以所有的部门是一个组,之后向下查找<=2000(第一行数据的薪资) 的行列,发现没有,而一共有10行,因此有:1/12=0.08333.. select dname, ename,salary, cume_dist() over(partition by dname order by salary) as ai from employee; -- 按照部门进行分组之后,以每一组进行查询,所以分布函数的分母变为了每一组的行数,继续从这一组查找小于等于此数据的值即可 -- 于cume_dist() 相对的,有通过rank()进行排序之后的percent_rank()窗口函数 -- 作用:PERCENT_RANK()函数能够通过对于使用序号排序函数之后的表格,对于每一行,计算(当前行的序号-1)/(本组的行总数-1); select dname, ename,salary, rank() over(partition by dname order by salary) as a1, PERCENT_RANK() over (partition by dname order by salary ) as a2 from employee; /*数据分析 第一行数据:a2显示为0,因为本行当中序号为1,而本组当中总的行列数为6 所以计算percent_rank值为(1-1)/(6-1)=0 对于第二行数据为2,本组总行为6,所以为(2-1)/(6-1)=0.2 */
四:前后函数
1.介绍:关键字:lag(x,y,str) 表示按照x以当前行的x作为标准,看上y行的x存不存在,存在的话新建设一行列,将下y行的填入本行当中,如果不存在,则填入默认值,默认值如果是null,则也返回为null
2.操作:
两种表达形式: 1.lag(x,y) -- 默认值为null,如果不存在 2.lag(x,y,str) -- 默认值为str,如果不存在 select dname, ename,salary,hiredate, lag(hiredate,1,'2001-01-01') over(partition by dname order by hiredate) time from employee; /*分析数据:第一行数据:hiredate;2021-11-01 time:2001-01-01 因为在本组当中上一行当中不存在有日期数据,所以将其使用默认值填入,为'2001-01-01' 对于第二行:因为上一行当中有日期的数据存在,所以直接将上一行数据填入本行即可 结果:hiredate :2021-11-02 time:2021-11-01 */
五:头尾函数:
1.关键语句:
关键词:first(x) 表示这一列x,从当前行往上,查找最小的一个
关键词:last(x) 表示这一列x,从当前行往上,查找最大的一个
2.操作:
-- 示例: select dname, ename,salary,hiredate, first_value(salary) over(partition by dname order by hiredate) as a1, last_value(salary) over (partition by dname order by hiredate ) as a2 from employee; -- 相当于从当前行包括当前行往上寻找最大或者是最小值
六:其他函数:
1.关键词:nth_value(x,n) 返回按照x这一列的本行数据往上进行寻找,依照排序方式排行第n的数据,如果不存在n行则返回为null
2.操作:
-- 先分部门,求出每个部门按照日期进行分组的数据的第2名的薪资 select dname, ename,salary, NTH_VALUE(salary,2) over (partition by dname order by hiredate ) as a2 from employee; 2.ntile() 将表格排序之后的主句分为n个等级,并且记录等技数 -- 将每个部门的员工按照入职日期分为三组 select dname, ename,salary,hiredate, NTILE(3) over(partition by dname order by hiredate) as a1 from employee; -- 寻找每个部门分组之后为1组的人,分3等级 -- 子查询的方式即可 select * from (select dname, ename,salary,hiredate, NTILE(3) over(partition by dname order by hiredate) as a1 from employee) t where t.a1=1;
OVER
