oracle分析函數技術詳解（配上開窗函數over()）

2024-08-29 13:54:25
字體：大中小
來源：轉載
供稿：網友
一、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;運行結果：
      
二、理解over()函數
1.1、兩個order by的執行時機分析函數（以及與其配合的開窗函數over（））是在整個sql查詢結束后(sql語句中的order by的執行比較特殊)再進行的操作, 也就是說sql語句中的order by也會影響分析函數的執行結果：
a) 兩者一致：如果sql語句中的order by滿足與分析函數配合的開窗函數over（）分析時要求的排序，即sql語句中的order by子句里的內容和開窗函數over（）中的order by子句里的內容一樣，
那么sql語句中的排序將先執行,分析函數在分析時就不必再排序；b) 兩者不一致：如果sql語句中的order by不滿足與分析函數配合的開窗函數over（）分析時要求的排序，即sql語句中的order by子句里的內容和開窗函數over（）中的order by子句里的內容不一樣，
那么sql語句中的排序將最后在分析函數分析結束后執行排序。
           
1.2、開窗函數over（）分析函數中的分組/排序/窗口      開窗函數over（）分析函數包含三個分析子句：分組子句(partition by)， 排序子句(order by)， 窗口子句(rows)      窗口就是分析函數分析時要處理的數據范圍，就拿sum來說,它是sum窗口中的記錄而不是整個分組中的記錄，因此我們在想得到某個欄位的累計值時，我們需要把窗口指定到該分組中的第一行數據到當前行, 如果你指定該窗口從該分組中的第一行到最后一行,那么該組中的每一個sum值都會一樣，即整個組的總和。
      窗口子句在這里我只說rows方式的窗口，range方式和滑動窗口也不提。
      窗口子句中我們經常用到指定第一行，當前行，最后一行這樣的三個屬性：第一行是 unbounded preceding，當前行是 current row，最后一行是 unbounded following，
注釋：
當開窗函數over（）出現分組(partition by)子句時，
unbounded preceding即第一行是指表中一個分組里的第一行， unbounded following即最后一行是指表中一個分組里的最后一行；
當開窗函數over（）省略了分組(partition by)子句時， 
unbounded preceding即第一行是指表中的第一行， unbounded following即最后一行是指表中的最后一行。
窗口子句不能單獨出現，必須有order by子句時才能出現，
例如:
last_value(sal) over(partition by deptno                      order by sal                      rows between unbounded preceding and unbounded following)以上示例指定窗口為整個分組。而出現order by子句的時候，不一定要有窗口子句，但效果會很不一樣，此時的窗口默認是當前組的第一行到當前行！
如果省略分組,則把全部記錄當成一個組。a) 如果存在order by則默認窗口是unbounded preceding and current row   --當前組的第一行到當前行b) 如果這時省略order by則窗口默認為unbounded preceding and unbounded following  --整個組 
而無論是否省略分組子句，如下結論都是成立的：
1、窗口子句不能單獨出現，必須有order by子句時才能出現。
2、當省略窗口子句時:a) 如果存在order by則默認的窗口是unbounded preceding and current row  --當前組的第一行到當前行，即在當前組中，第一行到當前行b) 如果同時省略order by則默認的窗口是unbounded preceding and unbounded following  --整個組
              所以，
lag(sal) over(order by sal) 解釋
over（order by salary）表示意義如下：
首先，我們要知道由于省略分組子句，所以當前組的范圍為整個表的數據行，
然后，在當前組（此時為整個表的數據行）這個范圍里執行排序（即order by salary），
最后，我們知道分析函數lag(sal)在當前組（此時為整個表的數據行）這個范圍里的窗口范圍為當前組的第一行到當前行，即分析函數lag(sal)在這個窗口范圍執行。
參見：
Oracle的LAG和LEAD分析函數
Oracle分析函數ROW_NUMBER()|RANK()|LAG()使用詳解
1.3、幫助理解over()的實例
例1：關注點：sql無排序，over()排序子句省略
SELECT DEPTNO, EMPNO, ENAME, SAL,        LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)FROM EMP;運行結果：
 
        例2：關注點：sql無排序，over()排序子句有，窗口省略
 
SELECT DEPTNO,       EMPNO,       ENAME,       SAL,       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO                             ORDER BY SAL DESC)  FROM EMP;運行結果：
 
                   例3：關注點：sql無排序，over()排序子句有，窗口也有，窗口特意強調全組數據
 
SELECT DEPTNO,       EMPNO,       ENAME,       SAL,       LAST_VALUE(SAL)        OVER(PARTITION BY DEPTNO             ORDER BY SAL             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL  FROM EMP;運行結果：
 
      例4：關注點：sql有排序（正序），over()排序子句無，先做sql排序再進行分析函數運算
 
SELECT DEPTNO,       MGR,       ENAME,       SAL,       HIREDATE,       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE  FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR;運行結果：
 
 
例5：關注點：sql有排序（倒序），over()排序子句無，先做sql排序再進行分析函數運算
 
SELECT DEPTNO,       MGR,       ENAME,       SAL,       HIREDATE,       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO) LAST_VALUE  FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC;運行結果：
                 例6：關注點：sql有排序（倒序），over()排序子句有，窗口子句無，此時的運算是：sql先選數據但是不排序，而后排序子句先排序并進行分析函數處理（窗口默認為第一行到當前行），最后再進行sql排序
 
 
SELECT DEPTNO,       MGR,       ENAME,       SAL,       HIREDATE,       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL ASC) LAST_VALUE  FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC;運行結果：
 
SELECT DEPTNO,       MGR,       ENAME,       SAL,       HIREDATE,       MIN(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) LAST_VALUE  FROM EMP WHERE DEPTNO = 30 ORDER BY DEPTNO, MGR DESC;運行結果：
              
三、常見分析函數詳解
為了方便進行實踐，特將演示表和數據羅列如下：
一、創建表
create table t(    bill_month varchar2(12) ,    area_code number,    net_type varchar(2),    local_fare number );      
二、插入數據
insert into t values('200405',5761,'G', 7393344.04); insert into t values('200405',5761,'J', 5667089.85); insert into t values('200405',5762,'G', 6315075.96); insert into t values('200405',5762,'J', 6328716.15); insert into t values('200405',5763,'G', 8861742.59); insert into t values('200405',5763,'J', 7788036.32); insert into t values('200405',5764,'G', 6028670.45); insert into t values('200405',5764,'J', 6459121.49); insert into t values('200405',5765,'G', 13156065.77); insert into t values('200405',5765,'J', 11901671.70); insert into t values('200406',5761,'G', 7614587.96); insert into t values('200406',5761,'J', 5704343.05); insert into t values('200406',5762,'G', 6556992.60); insert into t values('200406',5762,'J', 6238068.05); insert into t values('200406',5763,'G', 9130055.46); insert into t values('200406',5763,'J', 7990460.25); insert into t values('200406',5764,'G', 6387706.01); insert into t values('200406',5764,'J', 6907481.66); insert into t values('200406',5765,'G', 13562968.81); insert into t values('200406',5765,'J', 12495492.50); insert into t values('200407',5761,'G', 7987050.65); insert into t values('200407',5761,'J', 5723215.28); insert into t values('200407',5762,'G', 6833096.68); insert into t values('200407',5762,'J', 6391201.44); insert into t values('200407',5763,'G', 9410815.91); insert into t values('200407',5763,'J', 8076677.41); insert into t values('200407',5764,'G', 6456433.23); insert into t values('200407',5764,'J', 6987660.53); insert into t values('200407',5765,'G', 14000101.20); insert into t values('200407',5765,'J', 12301780.20); insert into t values('200408',5761,'G', 8085170.84); insert into t values('200408',5761,'J', 6050611.37); insert into t values('200408',5762,'G', 6854584.22); insert into t values('200408',5762,'J', 6521884.50); insert into t values('200408',5763,'G', 9468707.65); insert into t values('200408',5763,'J', 8460049.43); insert into t values('200408',5764,'G', 6587559.23); insert into t values('200408',5764,'J', 7342135.86); insert into t values('200408',5765,'G', 14450586.63); insert into t values('200408',5765,'J', 12680052.38); commit;            
三、first_value()與last_value()：求最值對應的其他屬性問題、取出每月通話費最高和最低的兩個地區。
SELECT BILL_MONTH,        AREA_CODE,       SUM(LOCAL_FARE) LOCAL_FARE,        FIRST_VALUE(AREA_CODE)        OVER(PARTITION BY BILL_MONTH             ORDER BY SUM(LOCAL_FARE) DESC             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRSTVAL,        LAST_VALUE(AREA_CODE)        OVER(PARTITION BY BILL_MONTH             ORDER BY SUM(LOCAL_FARE) DESC             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LASTVAL   FROM T  GROUP BY BILL_MONTH, AREA_CODE  ORDER BY BILL_MONTH運行結果：
   
四、rank(),dense_rank()與row_number()：求排序
rank，dense_rank，row_number函數為每條記錄產生一個從1開始至n的自然數，n的值可能小于等于記錄的總數。這3個函數的唯一區別在于當碰到相同數據時的排名策略。①row_number： row_number函數返回一個唯一的值，當碰到相同數據時，排名按照記錄集中記錄的順序依次遞增。②dense_rank： dense_rank函數返回一個唯一的值，當碰到相同數據時，此時所有相同數據的排名都是一樣的。③rank： rank函數返回一個唯一的值，當碰到相同的數據時，此時所有相同數據的排名是一樣的，同時會在最后一條相同記錄和下一條不同記錄的排名之間空出排名。
          
演示數據在Oracle自帶的scott用戶下：1、rank()值相同時排名相同，其后排名跳躍不連續
SELECT *   FROM (SELECT DEPTNO,                RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;運行結果：
2、dense_rank()值相同時排名相同，其后排名連續不跳躍
SELECT *   FROM (SELECT DEPTNO,                DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;運行結果：
3、row_number()值相同時排名不相等，其后排名連續不跳躍
SELECT *   FROM (SELECT DEPTNO,                ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RW,                ENAME,               SAL          FROM SCOTT.EMP)  WHERE RW <= 4;運行結果：
 
五、lag()與lead()：求之前或之后的第N行 lag和lead函數可以在一次查詢中取出同一字段的前n行的數據和后n行的值。這種操作可以使用對相同表的表連接來實現，不過使用lag和lead有更高的效率。lag(arg1,arg2,arg3)第一個參數是列名，第二個參數是偏移的offset，第三個參數是超出記錄窗口時的默認值。   舉例如下：SQL> select *  from kkk;                                                                                                                    ID NAME                                                   ---------- --------------------                                            1 1name                                                           2 2name                                                           3 3name                                                           4 4name                                                           5 5name                                                  SQL> select id,name,lag(name,1,0) over(order by id) from kkk;                                                                           ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      ---------- -------------------- ----------------------------               1 1name                0                                          2 2name                1name                                      3 3name                2name                                      4 4name                3name                                      5 5name                4name
SQL> select id,name,lead(name,1,0) over(order by id) from kkk;                                                                          ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     ---------- -------------------- -----------------------------              1 1name                2name                                      2 2name                3name                                      3 3name                4name                                      4 4name                5name                                      5 5name                0
SQL> select id,name,lead(name,2,0) over(order by id) from kkk;                                                                                                                      ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     ---------- -------------------- -----------------------------              1 1name                3name                                      2 2name                4name                                      3 3name                5name                                      4 4name                0                                          5 5name                0  SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;                                                                                                                          ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                   ---------- -------------------- ------------------------------                            1 1name                2name                                                     2 2name                3name                                                     3 3name                4name                                                     4 4name                5name                                                     5 5name                linjiqin  
---------------------------------------------------------------------------------------
   
六、rollup()與cube()：排列組合分組 1)、group by rollup(a, b, c)：首先會對(a、b、c)進行group by，然后再對(a、b)進行group by，其后再對(a)進行group by，最后對全表進行匯總操作。
      
2)、group by cube(a, b, c)：則首先會對(a、b、c)進行group by，然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，最后對全表進行匯總操作。
   
1、生成演示數據：Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as ds_trade SQL> conn system/oracle as sysdbaConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as SYS SQL> create table scott.t as select * from dba_indexes; Table created  SQL> connect scott/oracleConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as scott SQL>
    
2、普通group by體驗sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;
3、group by rollup(A,B,C)GROUP BY ROLLUP(A, B, C)：首先會對(A、B、C)進行GROUP BY，然后再對(A、B)進行GROUP BY，其后再對(A)進行GROUP BY，最后對全表進行匯總操作。sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);
4、group by cube(A,B,C)GROUP BY CUBE(A, B, C)：則首先會對(A、B、C)進行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對全表進行匯總操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);
   
七、max(),min(),sun()與avg()：求移動的最值總和與平均值問題：計算出各個地區連續3個月的通話費用的平均數(移動平均值)
 
SELECT AREA_CODE,        BILL_MONTH,       LOCAL_FARE,       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_sum",        AVG(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_avg",        MAX(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_max",        MIN(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY TO_NUMBER(BILL_MONTH)                             RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) "3month_min"   FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE           FROM T          GROUP BY T.AREA_CODE, T.BILL_MONTH)運行結果：
  
問題：求各地區按月份累加的通話費
SELECT AREA_CODE,        BILL_MONTH,       LOCAL_FARE,       SUM(LOCAL_FARE) OVER(PARTITION BY AREA_CODE                             ORDER BY BILL_MONTH ASC) "last_sum_value"   FROM (SELECT T.AREA_CODE, T.BILL_MONTH, SUM(T.LOCAL_FARE) LOCAL_FARE           FROM T          GROUP BY T.AREA_CODE, T.BILL_MONTH)  ORDER BY AREA_CODE, BILL_MONTH運行結果：
 
--------------------------------------------------------------------------Blog：http://www.cnblogs.com/linjiqin/J2EE、Android、linux、Oracle QQ交流群：142463980、158560018(滿)
另見：《Oracle分析函數ROW_NUMBER()|RANK()|LAG()使用詳解》