oracle的分析函數over(Partition by...)

　　Sql代碼

　　over（Partition by…） 一個超級牛皮的ORACLE特有函數

　　最近工作中才接觸到這個功能強大而靈活的函數

　　oracle的分析函數over 及開窗函數

　　一分析函數over

　　Oracle從開始提供分析函數分析函數用於計算基於組的某種聚合值它和聚合函數的不同之處是對於每個組返回多行而聚合函數對於每個組只返回一行

　　下面通過幾個例子來說明其應用

　　:統計某商店的營業額

　　date       sale

　　          

　　          

　　          

　　          

　　          

　　規則按天統計每天都統計前面幾天的總額

　　得到的結果

　　DATE   SALE       SUM

　　

　　                         天

　　                         天+天

　　                         天+天+天

　　                         

　　                         

　　:統計各班成績第一名的同學信息

　　NAME   CLASS S

　　

　　fda         

　　ffd         

　　dss         

　　cfe         

　　gds         

　　gf          

　　ddd         

　　adf         

　　asdf        

　　dd         

　　通過

　　

　　select * from

　　（

　　select nameclasssrank（）over（partition by class order by s desc） mm from t

　　）

　　where mm=

　　

　　得到結果

　　NAME   CLASS S                       MM

　　

　　dss                               

　　gds                               

　　gf                                

　　ddd                               

　　注意

　　在求第一名成績的時候不能用row_number（）因為如果同班有兩個並列第一row_number（）只返回一個結果

　　rank（）和dense_rank（）的區別是

　　rank（）是跳躍排序有兩個第二名時接下來就是第四名

　　dense_rank（）l是連續排序有兩個第二名時仍然跟著第三名

　　分類統計 （並顯示信息）

　　A   B   C

　　

　　m   a  

　　n   a  

　　m   a  

　　n   b  

　　n   b  

　　x   b  

　　x   b  

　　x   b  

　　h   b  

　　select acsum（c）over（partition by a） from t

　　得到結果

　　A   B   C        SUM（C）OVER（PARTITIONBYA）

　　

　　h   b          

　　m   a          

　　m   a          

　　n   a          

　　n   b          

　　n   b          

　　x   b          

　　x   b          

　　x   b          

　　如果用sumgroup by 則只能得到

　　A   SUM（C）

　　

　　h  

　　m  

　　n  

　　x  

　　無法得到B列值

　　=====

　　select * from test

　　數據

　　A B C

　　

　　

　　

　　

　　

　　將B欄位值相同的對應的C 欄位值加總

　　select abc SUM（C） OVER （PARTITION BY B） C_Sum

　　from test

　　A B C C_SUM

　　

　　

　　

　　

　　

　　如果不需要已某個欄位的值分割那就要用 null

　　eg: 就是將C的欄位值summary 放在每行後面

　　select abc SUM（C） OVER （PARTITION BY null） C_Sum

　　from test

　　A B C C_SUM

　　

　　

　　

　　

　　

　　求個人工資占部門工資的百分比

　　SQL> select * from salary;

　　NAME DEPT SAL

　　

　　a

　　b

　　c

　　d

　　SQL> select namedeptsalsal*/sum（sal） over（partition by dept） percent from salary;

　　NAME DEPT SAL PERCENT

　　

　　a

　　b

　　c

　　d

　　二開窗函數

　　開窗函數指定了分析函數工作的數據窗口大小這個數據窗口大小可能會隨著行的變化而變化舉例如下

　　:

　　over（order by salary） 按照salary排序進行累計order by是個默認的開窗函數

　　over（partition by deptno）按照部門分區

　　:

　　over（order by salary range between preceding and following）

　　每行對應的數據窗口是之前行幅度值不超過之後行幅度值不超過

　　例如對於以下列

　　aa

　　sum（aa）over（order by aa range between preceding and following）

　　得出的結果是

　　AA                       SUM

　　

　　                      

　　                      

　　                      

　　                      

　　                      

　　                      

　　                      

　　                      

　　                      

　　                      

　　

　　對於aa=來說 sum=+++++=     ;

　　又如 對於aa= <=aa<=+ 只有一個數所以sum=    ;

　　:其它

　　over（order by salary rows between preceding and following）

　　每行對應的數據窗口是之前行之後行

　　:下面三條語句等效

　　over（order by salary rows between unbounded preceding and unbounded following）

　　每行對應的數據窗口是從第一行到最後一行等效

　　over（order by salary range between unbounded preceding and unbounded following）

　　等效

　　over（partition by null）

　　常用的分析函數如下所列

　　row_number（） over（partition by … order by …）

　　rank（） over（partition by … order by …）

　　dense_rank（） over（partition by … order by …）

　　count（） over（partition by … order by …）

　　max（） over（partition by … order by …）

　　min（） over（partition by … order by …）

　　sum（） over（partition by … order by …）

　　avg（） over（partition by … order by …）

　　first_value（） over（partition by … order by …）

　　last_value（） over（partition by … order by …）

　　lag（） over（partition by … order by …）

　　lead（） over（partition by … order by …）

　　示例

　　SQL> select typeqty from test;

　　TYPE QTY

　　

　　

　　

　　SQL> select typeqtyto_char（row_number（） over（partition by type order by qty））||/||to_char（count（*） over（partition by type）） as cnt from test;

　　TYPE QTY CNT

　　

　　 /

　　 /

　　 /

　　 /

　　 /

　　SQL> select * from test;

　　

　　

　　

　　

　　

　　SQL> select tidmcto_char（brn）||/||tid）e

　　 from test t

　　（select rownum rn from （select max（to_number（id）） mid from test） connect by rownum <=mid ））L

　　 where brn<=to_number（tid）

　　order by id

　　ID MC TO_CHAR（BRN）||/||TID

　　

　　 /

　　 /

　　 /

　　 /

　　 /

　　 /

　　 / /

　　 /CNOUG /

　　 rows selected

　　*******************************************************************

　　關於partition by

　　這些都是分析函數好像是以後才有的 row_number（）和rownum差不多功能更強一點（可以在各個分組內從開時排序） rank（）是跳躍排序有兩個第二名時接下來就是第四名（同樣是在各個分組內） dense_rank（）l是連續排序有兩個第二名時仍然跟著第三名相比之下row_number是沒有重復值的 lag（argargarg） arg是從其他行返回的表達式 arg是希望檢索的當前行分區的偏移量是一個正的偏移量時一個往回檢索以前的行的數目 arg是在arg表示的數目超出了分組的范圍時返回的值

　　

　　select deptnorow_number（） over（partition by deptno order by sal） from emp order by deptno;

　　

　　select deptnorank（） over （partition by deptno order by sal） from emp order by deptno;

　　

　　select deptnodense_rank（） over（partition by deptno order by sal） from emp order by deptno;

　　

　　select deptnoenamesallag（enamenull） over（partition by deptno order by ename） from emp ord er by deptno;

　　

　　select deptnoenamesallag（enameexample） over（partition by deptno order by ename） from em p

　　order by deptno;

　　

　　select deptno salsum（sal） over（partition by deptno） from emp;每行記錄後都有總計值  select deptno sum（sal） from emp group by deptno;

　　 求每個部門的平均工資以及每個人與所在部門的工資差額

　　select deptnoenamesal

　　round（avg（sal） over（partition by deptno）） as dept_avg_sal

　　round（salavg（sal） over（partition by deptno）） as dept_sal_diff

　　from emp;

From:http://tw.wingwit.com/Article/program/Oracle/201311/18056.html