Oracle分析函數入門

標籤：toolbar   alt   code   無限   rac   hive   --   src   comm   

一、Oracle分析函數入門

分析函數是什嗎？
分析函數是Oracle專門用於解決複雜報表統計需求的功能強大的函數，它可以在資料中進行分組然後計算基於組的某種統計值，並且每一組的每一行都可以返回一個統計值。

          

分析函數和彙總函式的不同之處是什嗎？
普通的彙總函式用group by分組，每個分組返回一個統計值，而分析函數採用partition by分組，並且每組每行都可以返回一個統計值。

              

分析函數的形式
分析函數帶有一個開窗函數over()，包含三個分析子句:分組(partition by), 排序(order by), 視窗(rows) ，他們的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
註：視窗子句在這裡我只說rows方式的視窗,range方式和滑動視窗也不提

    

分析函數例子(在scott使用者下類比)

樣本目的：顯示各部門員工的工資，並附帶顯示該部分的最高工資。

--顯示各部門員工的工資，並附帶顯示該部分的最高工資。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following針對當前所有記錄的前一條、後一條記錄，也就是表中的所有記錄
            --unbounded：不受控制的，無限的
            --preceding：在...之前
            --following：在...之後
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

運行結果：

               

樣本目的：按照deptno分組，然後計算每組值的總和

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

運行結果：

     

樣本目的：對各部門進行分組，並附帶顯示第一行至當前行的匯總

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至當前行的匯總
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

運行結果：

   

樣本目標：當前行至最後一行的匯總

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指當前行到最後一行的匯總
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

運行結果：

   

 樣本目標：當前行的上一行(rownum-1)到當前行的匯總

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指當前行的上一行(rownum-1)到當前行的匯總 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

運行結果：

    

樣本目標：   當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的匯總     

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指當前行的上一行(rownum-1)到當前行的下輛行(rownum+2)的匯總
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

運行結果：

Oracle分析函數入門