oracle函数及触发器

2024-10-19

oracle函数及触发器（通用2篇）

oracle函数及触发器篇1

oracle的分析函数over及开窗函数

eg:相关解析：

表t_pi_part

字段idcodename

value 1222a

value 2222b

value 3 333c

给code相同的part code 添加行标，根据id 排序

select p.* ,row_number()over(partition by p.code order order by a.id desc) as row_index from t_pi_part p;

一：分析函数over

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行，

下面通过几个例子来说明其应用。

1：统计某商店的营业额。

datesale

120

215

314

418

530

规则：按天统计：每天都统计前面几天的总额

得到的结果：

DATESALESUM

----- -------- ------

12020--1天

21535--1天+2天

31449--1天+2天+3天

41867.

53097.

2:统计各班成绩第一名的同学信息

NAMECLASS S

----- ----- ----------------------

fda180

ffd178

dss195

cfe274

gds292

gf399

ddd399

adf345

asdf355

3dd378

通过：

select * from

(

select name,class,s,rank()over(partition by class order by s desc) mm from t2

)

where mm=1

得到结果：

NAMECLASS SMM

----- ----- ---------------------- ----------------------

dss1951

gds2921

gf3991

ddd3991

注意：

1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

2.rank()和dense_rank()的区别是：

--rank()是跳跃排序，有两个第二名时接下来就是第四名

--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

3.分类统计 (并显示信息)

ABC

-- -- ----------------------

ma2

na3

ma2

nb2

nb1

xb3

xb2

xb4

hb3

select a,c,sum(c)over(partition by a) from t2

得到结果：

ABCSUM(C)OVER(PARTITIONBYA)

-- -- ------- ------------------------

hb33

ma24

na36

nb26

nb16

xb39

xb29

xb49

如果用sum，group by 则只能得到

ASUM(C)

-- ----------------------

无法得到B列值

=====

select * from test

数据:

A B C

1 1 1

1 2 2

1 3 3

2 2 5

3 4 6

---将B栏位值相同的对应的C 栏位值加总

select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum

from test

A B C C_SUM

1 1 1 1

1 2 2 7

2 2 5 7

1 3 3 3

3 4 6 6

---如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum

from test

A B C C_SUM

1 1 1 17

1 2 2 17

1 3 3 17

2 2 5 17

3 4 6 17

求个人工资占部门工资的百分比

SQL>select * from salary;

NAME DEPT SAL

---------- ---- -----

a 10

b 10 3000

c 10 5000

d 20 4000

SQL>select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;

NAME DEPT SAL PERCENT

---------- ---- ----- ----------

a 10 2000 20

b 10 3000 30

c 10 5000 50

d 20 4000 100

二：开窗函数

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

1：

over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno)按照部门分区

2：

over(order by salary range between 5 preceding and 5 following)

每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

例如:对于以下列

sum(aa)over(order by aa range between 2 preceding and 2 following)

得出的结果是

AASUM

---------------------- -------------------------------------------------------

110

214

318

418

522

618

722

就是说，对于aa=5的一行，sum为5-1<=aa<=5+2 的和

对于aa=2来说，sum=1+2+2+2+3+4=14;

又如对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9;

3：其它：

over(order by salary rows between 2 preceding and 4 following)

每行对应的数据窗口是之前2行，之后4行

4：下面三条语句等效：

over(order by salary rows between unbounded preceding and unbounded following)

每行对应的数据窗口是从第一行到最后一行，等效：

over(order by salary range between unbounded preceding and unbounded following)

等效

over(partition by null)