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