Oracle分析查询

 

 --1. 窗口函数（累加、移动平均） 

 

    --基本语法：

    FUNCTION_NAME(ARGUMENT_LIST)

    OVER(<PARTITION_CLAUSE> <ORDER_BY_CLAUSE> <WINDOWING_CLAUSE>)

    (1)FUNCTION_NAME:窗口函数名称，如 SUM()、AVG()、MAX()、MIN()、COUNT()、VARIANCE()、FIRST_VALUE()、LAST_VALUE()等

    (2)ARGUMENT_LIST:参数列表

    (3)PARTITION_CLAUSE:可选的分区子句

    (4)ORDER_BY_CLAUSE:可选的排序子句

    (5)WINDOWING_CLAUSE:用于定义变化的或者固定的数据窗口，常用 ROWS ROW_EXPRESSION

       a:UNBOUNDED PRECEDING -> 表示该窗口从当前分区的第一行开始，并且结束于正在处理的当前行

       b:CURRENT ROW -> 表示窗口从当前行开始和结束

       c:NUMERIC_EXPRESSION PRECEDING -> 表示该窗口从当前行之前的数字表达式 NUMERIC_EXPRESSION 指定的行开始

       d:NUMERIC_EXPRESSION FOLLOWING -> 表示该窗口从当前行之后的数字表达式 NUMERIC_EXPRESSION 指定的行结束

       e:BETWEEN ... AND ... -> 表示窗口的范围

 

  --eg：（累加、移动平均） 

     SELECT DEPTNO,

           SUM(SAL)SUMSAL,           

           SUM(SUM(SAL)) 

             OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

             AS CUMMULATIVE_SAL,--累加

           AVG(SUM(SAL)) 

             OVER(ORDER BY DEPTNO ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 

             AS MOVING_2_AVGSAL, --移动平均

           AVG(SUM(SAL)) 

             OVER(ORDER BY DEPTNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 

             AS CENTER_1_AVGSAL ,--移动中心平均

           AVG(SUM(SAL)) 

             OVER() 

             AS TOTAL_AVGSAL,--整体平均

           FIRST_VALUE(SUM(SAL))

             OVER(ORDER BY DEPTNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 

             AS FIRST_VALUE --第一个值             

    FROM EMP 

    GROUP BY DEPTNO 

    ORDER BY DEPTNO;

 

   =====================================

   -- 2. 制表函数 

 

   SELECT * FROM EMP ORDER BY DEPTNO,JOB;

 

   SELECT DEPTNO,

   SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) AS TOTAL_PER_DEPT,-- 每个部门的工资支付总额

   ROUND(AVG(SUM(SAL)) OVER(PARTITION BY DEPTNO)) AS AVG_PER_DEPT,--每个部门的工资支付额基于岗位的平均工资支付额（即部门平均岗位支付工资）

   JOB,

   SUM(SUM(SAL)) OVER(PARTITION BY JOB) AS TOTAL_PER_JOB,--每个岗位的工资支付总额

   MIN(SUM(SAL)) OVER(PARTITION BY JOB) AS MIN_PER_JOB --每个岗位中每个部门工资支付额最低的支付额（即岗位最低工资支付额）

   FROM EMP 

   GROUP BY DEPTNO,JOB 

   ORDER BY DEPTNO,JOB; 

 

   --RATIO_TO_REPORT

   SELECT DEPTNO,JOB,SUM(SAL)PER_SAL,

          SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) AS TOTAL_PER_DEPT,

          SUM(SAL)/SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) AS PER_DEPT_RAT1,

          RATIO_TO_REPORT(SUM(SAL)) OVER (PARTITION BY DEPTNO) AS PER_DEPT_RAT2

   FROM EMP 

   GROUP BY DEPTNO,JOB 

   ORDER BY DEPTNO,JOB;