oracle 分析函数（笔记）

分析函数是oracle数据库在9i版本中引入并在以后版本中不断增强的新函数种类。分析函数提供好了跨行、多层次聚合引用值的能力。分析函数所展现的效果使用传统的SQL语句也能实现，但是实现方式比较复杂，效率较低。

分析函数具有三个基本的组成部分：分区子句，排序子句，开窗子句。基本语法为

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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函数为例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

--无默认值 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函数不支持开窗子句，示例如下

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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函数有点相似呢