Oracle 分析函數（筆記）

Oracle 分析函數（筆記）

分析函數是Oracle資料庫在9i版本中引入並在以後版本中不斷增強新函數種類。分析函數提供好了跨行、多層次彙總引用值的能力。分析函數所展現的效果使用傳統的SQL語句也能實現，但是實現方式比較複雜，效率較低。

分析函數具有三個基本的組成部分：分區子句，排序子句，開窗子句。基本文法為

function1 (argument1,argument2…argumentN)     over([partition- by -clause] [ order - by -clause] [windowing-clause]) --說明 --function1是所調用的接受0個或多個參數的分析函數 --分區子句按照分區列的值對資料進行分組，所有分區列中的值相同的列放在同一個分區中 --排序可以是升序也可以是降序，也可以使用nllls first 和nulls last子句將空值放在分區的最前或者最後 --開窗子句指定了分析函數運算的資料子集，具體文法如下：     [ ROWS | RANGE] BETWEEN <Start expr> AND < End expr> --其中     <Start expr> is [NUBOUNDED PRECEDING | CURRENT ROW | n PRECEDING | n PRECEDING]       < End expr> is [NUBOUNDED FOLLOWING| CURRENT ROW | n PRECEDING | n FOLLOWING]  --關鍵字preceding指定開窗語句的上邊界，following或current row子句指定下邊界  --注意分析函數不能進行嵌套。但可以通過將其包含的SQL語句放在嵌套語句中進行嵌套

常用函數列表

以lag函數為例

--無預設值 select zgqk flag,              kind,         sid,         lag(sid, 1) over(partition by flag, kindorder by flag, kind) sid_lag_1   from hidden_danger_ybyhxx   where userid= 717   order by flag, kind; --結果        FLAG KIND                SID  SID_LAG_1 ---------- ------------- ---------- ----------           1 A01                2295           1 A02                3414           1 A02                20785      3414           1 A02                2328      20785           1 A04                3412           1 A07                2297           1 A08                2332           1 A10                3420           1 A10                2298      3420           1 A10                2329      2298           1 A10                2327      2329           1 A11                2299           1 A11                3416      2299 --有預設值 select zgqk flag,              kind,         sid,         lag(sid, 1，sid) over(partition by flag, kindorder by flag, kind) sid_lag_1   from hidden_danger_ybyhxx   where userid= 717   order by flag, kind; --結果       FLAG KIND                SID  SID_LAG_1 ---------- ------------- ---------- ----------           1 A01                2295      2295           1 A02                3414      3414           1 A02                20785      3414           1 A02                2328      20785           1 A04                3412      3412           1 A07                2297      2297           1 A08                2332      2332           1 A10                3420      3420           1 A10                2298      3420           1 A10                2329      2298           1 A10                2327      2329           1 A11                2299      2299           1 A11                3416      2299           1 A12                2333      2333           1 B07                7800      7800           1 B07                12595      7800

特別聲明 listagg函數不支援開窗子句，樣本如下

select listagg(sid, ',' ) within group ( order by sid desc ) sid   from   (   select sid from user   where userid = 76298 order by sid   ); --結果為：  SID ------------------------------------- 31827,11199,9303,7901,5629,3254  --有沒有和wmsys.WM_CONCAT函數有點相似呢

Oracle函數之GREATEST函數詳解執行個體

Oracle函數之單行轉換函式

產生動態首碼且自增號碼的Oracle函數

Oracle函數之Replace()

Oracle函數大全

Oracle函數之case和decode的用法區別及效能比較