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语句放在嵌套语句中进行嵌套

常用函数列表

Oracle 分析函数(笔记)

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