oracle 分析函數（筆記），oracle函數

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函數有點相似呢