ORACLE学习笔记（6）（分析函数）

—Oracle分析函数 ----(开窗函数，窗口函数)

分析函数介绍
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，
并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？

◎普通的聚合函数用group by分组，
◎每个分组返回一个统计值，
◎而分析函数采用 partition by 分组，
◎并且每组每行都可以返回一个统计值。
分析函数的语法结构：

函数名()OVER(PARTITION BY 字段 ORDER BY )

----------------查询EMP表中每个部门每个人的平均工资

SELECT
     A.DEPTNO
    ,A.ENAME 
    ,AVG(A.SAL)
FROM EMP A
GROUP BY A.DEPTNO
          ,A.ENAME ;
-----------------------------

SELECT
     A.DEPTNO
    ,A.ENAME 
    ,AVG(A.SAL)OVER(PARTITION BY  A.DEPTNO ORDER BY 1)
FROM EMP A
 ;

----------查询整个公司的员工姓名以及平均工资

SELECT
     A.ENAME 
    ,AVG(A.SAL)
FROM EMP A
GROUP BY A.ENAME ;

-----------------------
SELECT
     A.ENAME 
    ,AVG(A.SAL)OVER( /*ORDER BY 2*/)
FROM EMP A
 ;

---

–4.2 分析函数种类和用法

1. MAX(),MIN(),SUM(),AVG(),COUNT() --加了ORDER BY 是累计求值
   —求EMP表中每个部门的员工姓名、总工资

SELECT
     A.DEPTNO
    ,A.ENAME
    ,SUM(A.SAL)OVER(PARTITION BY A.DEPTNO ORDER BY 1)
FROM EMP A;
-----------------------
SELECT
     A.DEPTNO
    ,A.ENAME
    ,SUM(A.SAL)
FROM EMP A
GROUP BY A.DEPTNO
    ,A.ENAME;

—求每个部门工资高于部门平均工资的员工数量占整个部门人数的百分比

----每个部门工资高于部门平均工资:求每个部门中的员工工资比部门平均工资高

------------------------方法1
      SELECT 
            A1.DEPTNO
           ,A1.ENAME
           ,COUNT(A1.ENAME)OVER(PARTITION BY A1.DEPTNO) /A1.COUNT_N
      FROM (select
                 A.DEPTNO
                ,A.ENAME
                ,A.SAL
                ,AVG(A.SAL)OVER(PARTITION BY A.DEPTNO ORDER BY 1) AVG_SAL
                ,COUNT(A.EMPNO)OVER(PARTITION BY A.DEPTNO) COUNT_N
            FROM EMP A
            )A1
      WHERE A1.SAL>AVG_SAL ;
-----------------------------方法2
        SELECT DISTINCT
              B.DEPTNO
             ,COUNT(B.ENAME)OVER(PARTITION BY B.DEPTNO)/A1.COUNT_EMP 
        FROM EMP B
        INNER JOIN (SELECT 
                         A.DEPTNO
                        ,A.ENAME
                        ,AVG(A.SAL)OVER(PARTITION BY A.DEPTNO) AVG_SAL
                        ,COUNT(A.EMPNO)OVER(PARTITION BY A.DEPTNO) COUNT_EMP
                    FROM EMP A
                    )A1
                ON B.ENAME=A1.ENAME
               AND B.SAL>A1.AVG_SAL ;
----------------------------------------------------------
SELECT A.DEPTNO,
       SUM(CASE
             WHEN A.SAL > A.AVG THEN
              1
             ELSE
              0
           END) / COUNT(1)
  FROM (SELECT 
              E.*
             , AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG 
        FROM EMP E
        ) A
 GROUP BY A.DEPTNO;

---------求EMP 表中工资高于部门平均工资且工资高于其上级领导工资的员工姓名、工资、部门平均工资

SELECT
      A1.ENAME 员工
     --,B.ENAME   领导
     ,A1.SAL  员工工资
   --  ,B.SAL   领导工资
     ,A1.AVG_SAL 部门平均工资
FROM EMP B
INNER JOIN (SELECT
               A.EMPNO,
               A.ENAME,
               A.JOB,
               A.MGR,
               A.HIREDATE,
               A.SAL,
               A.COMM,
               A.DEPTNO,
               AVG(A.SAL)OVER(PARTITION BY A.DEPTNO ) AVG_SAL
          FROM EMP A
          )A1
        ON B.EMPNO=A1.MGR
       AND B.SAL<A1.SAL
       AND A1.SAL>A1.AVG_SAL ;