熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 16:16:04  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.