Oracle的over子函數的妙用

標籤：har   分析函數   分析   date   欄位   統計   分組   acl   利用   

摘要

• oracle的over 子函數可實現按指定的欄位分組排序，對於相同分組欄位的結果集進行排序,其中PARTITION BY 為分組欄位，ORDER BY 指定排序欄位這對統計分析這類問題意想不到的效果.

1. over函數的妙用

      例1： 累計求和

select fdate 日期,total 金額,tax 稅額,sum(total) over (order by fdate) 累計金額,sum(tax) over (order by fdate) 累計稅額 
from 
( select fildate fdate,sum(total) total,sum(tax) tax from stkin t group by fildate ) 
order by fdate;

      例2：累計求和月分組統計

select fdate 日期,total 金額,tax 稅額,sum(total) over (order by fdate) 累計金額,sum(tax) over (order by fdate) 累計稅額,sum(total) over (partition by yymm order by fdate) 本月累計金額,
sum(tax) over (partition by yymm order by fdate) 本月累計稅額,yymmfrom (select fildate fdate,to_char(fildate,‘yyyy-mm‘) yymm,sum(total) total,sum(tax) tax from stkin t group by fildate)
order by fdate,yymm;

      例3：佔比計算

select fdate 日期,total 金額,tax 稅額,sum(total) over (order by fdate,yymm) 累計金額,sum(tax) over (order by fdate,yymm) 累計稅額,sum(total) over (partition by yymm order by fdate,yymm) 本月累計金額,sum(tax) over (partition by yymm order by fdate,yymm) 本月累計稅額,sum(total) over () 累計金額,100*round(total/sum(total) over (),4) 佔比,100*round(total/sum(total) over (partition by yymm),4) 月佔比,yymmfrom (select fildate fdate,to_char(fildate,‘yyyy-mm‘) yymm,sum(total) total,sum(tax) tax from stkin t group by fildate) 
order by fdate,yymm;

      注1：partition by 表示分組

      注2：在"... from emp;"後面不要加order by 子句，使用的分析函數的(partition by yymm order by fdade,yymm)裡已經排序的語句了，如果再在句尾添加排序子句，一致倒罷了，不一致，結果就令人費勁了.

      2.利用 lag(),lead() 取上下n條記錄取值

      例1：

select fdate 日期,total 金額,tax 稅額,lag(total,1,null) over(order by fdate) 上1條記錄金額,lead(total,1,null) over(order by fdate) 下1條記錄金額,yymmfrom (select fildate fdate,to_char(fildate,‘yyyy-mm‘) yymm,sum(total) total,sum(tax) tax from stkin tgroup by fildate) order by fdate,yymm;

 

Oracle的over子函數的妙用