3、开窗函数

1.开窗函数：（重要）

​ 语法：函数名() over([partition by 分组字段,...] [order by 排序字段 [desc|asc],... [定位框架] ])

1.开窗函数中可以使用的函数：

聚合函数：count()

​ sum()
​ max()
​ min()
​ avg()
​ 注意：在使用聚合函数的时候如果over里面设置了order by字段则代表进行的是累计操作。

定位框架：

语法：range|rows between 边界规则1 and 边界规则2;

常用的边界规则：
     unbounded preceding:向上一直到第一行
     current row:当前行
     n preceding:向上n行
     unbounded following:向下一直到最后一行
     n following:向下n行
     默认：range between unbounded preceding and current row

注意：1.默认定位框架从第一行到当前行。
2.range是按照值的范围进行累计，rows按照行进行累计

partition by 和group by 区别：

partition by只能在over窗体中使用，作用是用来将数据按照指定的字段进行分组，但是不会影响原来的数据，只是在后面新增一个窗口列来显示汇总之后的数据。
group by：按照指定的字段进行分组，但是只能显示分组字段以及最终的汇总结果，不能显示原表中所有的数据。

排名函数：

row_number():是按照行号进行显示名次，并不会关注并列的数据情况，所以他的名次都是连续。
rank():按照值进行排名，相同的值名次是一样，但是整个排名并不是连续的。
dense_rank():按照值进行显示名次，相同的值名次一样，并且整个排名是连续的。

解决问题：

​ 1.如果要获取某一些小组中的前n条数据。
​ 2.统计连续性的问题。
​ 使用开窗函数先按照指定的字段分组，然后对该小组的数据使用row_number()排名，然后将时间减去名次，
​ 如果得到的结果是一样的则代表连续，否则就是不连续。

注意：所有的排名函数在使用的时候必须要保证over窗体中有order by子句。

移动函数：

​ lag(字段,n):上移函数,将指定的字段的值上移n行
​ lead(字段,n):下移函数,将指定的字段的值下移n行。

解决的问题：

​ 主要是用来进行同比率环比率的计算。

--查看每一个部门中工资最高的前两名员工信息（不关注并列情况）

SELECT * FROM (
SELECT e.*,row_number() OVER(PARTITION BY deptno ORDER BY sal DESC) 名次 FROM emp e WHERE deptno IS NOT NULL) WHERE 名次<=2;

--显示员工表中的所有的员工的工资总以及每一个员工的信息

SELECT sum(sal) FROM emp;

--方式一：

SELECT * FROM emp e,(SELECT sum(sal)总工资 FROM emp);

--方式二：

SELECT e.*,SUM(sal) OVER() FROM emp e;

--计算每一个部门的平均工资以及人员信息。

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

--计算员工的工资高于他所在部门的平均工资的员工信息

SELECT * FROM(
SELECT e.*,AVG(sal) OVER(PARTITION BY deptno) 平均工资 FROM emp e) WHERE sal>平均工资;


SELECT e.*,SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM emp e;

--将员工表中的数据根据工资从大到小排序，并且显示名次。

SELECT a.*,ROWNUM FROM  (SELECT  * FROM emp ORDER BY sal DESC) a;

--使用开窗函数

SELECT e.*,row_number() OVER(ORDER BY sal DESC)名次1, RANK() OVER(ORDER BY sal DESC)名次2,
dense_Rank() OVER(ORDER BY sal DESC)名次3 FROM emp e;

--连续性问题

SELECT team,MIN(y) be,MAX(y) en FROM (
SELECT n.*,y-row_number() OVER(PARTITION BY team ORDER BY y)结果 FROM nba n) GROUP BY team,结果 HAVING COUNT(*)>1;

--移动函数

SELECT  e.*,LAG(sal,2) OVER(ORDER BY sal)下移,LEAD(sal,2) OVER(ORDER BY sal) 上移 FROM  emp e;

SELECT * FROM sale;

--计算每一个月的同比和环比

SELECT s.*,ROUND((sell-LAG(sell,1) OVER(ORDER BY MONTHS))/(LAG(sell,1) OVER(ORDER BY MONTHS))*100,2)||'%' FROM sale s;

--计算环比率

SELECT a.*,NVL2(round((sell-上月)/上月*100,2),round((sell-上月)/上月*100,2)||'%',NULL) 环比率 FROM (
SELECT s.*,LAG(sell,1) OVER(ORDER BY MONTHS) 上月  FROM sale s) a;

--计算同比率

SELECT a.*,NVL2(round((sell-去年)/去年*100,2),round((sell-去年)/去年*100,2)||'%',NULL) 同比率 FROM (
SELECT s.*,LAG(sell,12) OVER(ORDER BY MONTHS) 去年  FROM sale s) a;