分析函数、聚合函数

分析函数：功能强大。每一组的每一行都可以返回一个统计值

1、OVER():开窗，单独查询的时候没有任何意义
--比如
  SELECT OVER() FROM DUAL;   ---会报错

2、它一般是结合其它的函数一起使用。比如：结合聚合函数解决一些复杂的报表统计
--语法结构：聚合函数OVER()

--比如:查询EMP表中的员工的信息以及平均工资
SELECT E.*,
       AVG(E.SAL)OVER() AVG_SAL
FROM EMP E;

--可以解决掉聚合函数不能跟未分组字段一起查询的限制

--比如：查询员工工资比公司平均工资高的员工
SELECT F.*
FROM (
SELECT E.*,
       AVG(SAL)OVER() AVG_SAL
FROM EMP E) F
WHERE F.SAL > F.AVG_SAL;

3、分析函数的分组：聚合函数OVER(PARTITION BY 分组字段1[,分组字段2,....])

--比如：查询emp表中各个部门的平均工资及员工信息
SELECT E.*,
       AVG(E.SAL)OVER(PARTITION BY E.DEPTNO) DEPT_AVG
FROM EMP E;

--小练习一把：查询部门员工的工资高于该部门的平均工资的员工信息及部门平均工资
SELECT F.*
FROM (
SELECT E.*,
       AVG(SAL)OVER(PARTITION BY E.DEPTNO) DEPT_AVG
FROM EMP E) F
WHERE F.SAL > F.DEPT_AVG;

---分析函数不能直接作为条件，如果需要它作为条件的话，那么需要把这个sql语句当做子查询，然后从这个新的临时表中查询

4、分析函数的排序：聚合函数OVER(ORDER BY 排序累计字段1 ASC/DESC[,排序累计字段2 ASC/DESC,...].)
--不同于之前学的排序（order by），分析函数的排序具有累计/累加的功能

--比如：按照入职日期求累计工资
SELECT E.*,
       SUM(SAL)OVER(ORDER BY E.HIREDATE ASC) hire_sal
FROM EMP E
ORDER BY SAL DESC;

--需要注意了：如果没有提及 累计/累加 等字样，需要慎用分析函数的order by
            --它跟之前学的 排序(ORDER BY) 互不影响

5、完整全部结合起来： 聚合函数OVER(PARTITION BY 分组字段 ORDER BY 排序累计字段 ASC/DESC)

--比如：按照入职日期查询每个部门的累计发放工资
SELECT E.*,
       SUM(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE ASC) DEPT_HIRE_SAL
  FROM EMP E;

---总结一下：
1、分析函数结合聚合函数 和 聚合函数 的区别：
  1) 分析函数可以跟任何字段一起查询
     聚合函数只能跟其他的聚合函数或者分组字段一起查询
     
  2) 分析函数的分组是用 PARTITION BY 分组字段，如果需要，每一个over()里面都要带   
     聚合函数的分组是用 GROUP BY 分组字段，它一次就够了，上面查询的都是分组之后得到的数据
     
  3) 分析函数有累计的功能：ORDER BY 排序累计字段
     聚合函数就没有
     
  4) 分析函数是根据分组情况，每个组的每一行都返回一个值
     聚合函数是根据分组情况，每个组只返回一个值
  
  5) 分析函数不能直接作为条件，如果需要作为条件的话，需要用到子查询，把包含分析函数的到的值的结果集作为子查询的表
     聚合函数可以作为条件放在 HAVING 后面

2、分析函数结合聚合函数没有其他的功能，只是使聚合函数的大腿更粗一点：
   解除了 聚合函数不能跟未分组字段一起查询的限制  
     
--小练习一把：查询各个工资等级的平均工资以及员工的信息以及工资等级
SELECT E.*,
       ROUND(AVG(E.SAL)OVER(PARTITION BY S.GRADE),2) GRADE_AVG,
       S.GRADE
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;