oracle中sql分析函数
oracle中sql分析函数
分析函数能够将复杂的需求使用简单的sql实现，而且执行效率较高。
测试环境：
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
create table test(name varchar2(20),val number);
insert into test values('a',1);
insert into test values('b',2);
insert into test values('c',3);
insert into test values('d',5);
insert into test values('e',7);
insert into test values('f',9);
insert into test values('g',12);
insert into test values('h',15);
SQL> select * from test;
NAME VAL
-------------------- ----------
a 1
b 2
c 3
d 5
e 7
f 9
g 12
h 15
1、连需求和
SQL> select name,val,sum(val) over(order by name desc) "连需求和" from test;
NAME VAL 连需求和
-------------------- ---------- ----------
h 15 15
g 12 27
f 9 36
e 7 43
d 5 48
c 3 51
b 2 53
a 1 54
已选择8行。
2、依据差值范围求和
SQL> select name,val,sum(val)over(order by val range between 2 preceding and 1 f
ollowing) "范围求和" from test;
NAME VAL 范围求和
-------------------- ---------- ----------
a 1 3
b 2 6
c 3 6
d 5 8
e 7 12
f 9 16
g 12 12
h 15 15
已选择8行。
SQL> select name,val,sum(val)over(order by val desc range between 2 preceding an
d 1 following ) "范围求和" from test;
NAME VAL 范围求和
-------------------- ---------- ----------
h 15 15
g 12 12
f 9 9
e 7 16
d 5 12
c 3 10
b 2 6
a 1 6
已选择8行。
3、不连续求和
SQL> select name,val,sum(val)over() "不连续求和" from test;
NAME VAL 不连续求和
-------------------- ---------- ----------
a 1 54
b 2 54
c 3 54
d 5 54
e 7 54
f 9 54
g 12 54
h 15 54
已选择8行。
求比例值：
SQL> select name,val,sum(val)over(order by val) "连续求和"
2 ,sum(val)over() "总和",100*round(val/sum(val)over(),4) "所占比例" from test
;
NAME VAL 连续求和 总和 所占比例
-------------------- ---------- ---------- ---------- ----------
a 1 1 54 1.85
b 2 3 54 3.7
c 3 6 54 5.56
d 5 11 54 9.26
e 7 18 54 12.96
f 9 27 54 16.67
g 12 39 54 22.22
h 15 54 54 27.78
已选择8行。
4、使用子分区查询
数据环境：
update test set name='a' where val<3;
update test set name='b' where val>=3 and val<10;
update test set name='c' where val>10;
SQL> select * from test;
NAME VAL
-------------------- ----------
a 1
a 2
b 3
b 5
b 7
b 9
c 12
c 15
已选择8行。
--按name连续求和
SQL> select name,val,sum(val)over(partition by name order by val desc) "按name连
续求和" from test;
NAME VAL 按name连续求和
-------------------- ---------- --------------
a 2 2
a 1 3
b 9 9
b 7 16
b 5 21
b 3 24
c 15 15
c 12 27
已选择8行。
--按name求和
SQL> select name,val,sum(val)over(partition by name ) "按name求和" from test;
NAME VAL 按name求和
-------------------- ---------- ----------
a 1 3
a 2 3
b 3 24
b 5 24
b 9 24
b 7 24
c 12 27
c 15 27
已选择8行。
5、使用rank()进行排序操作，rank()如果前面有并列则下一名会空出并列的此时，dense_rank()则会按顺序向下排名。
SQL> select name,val,dense_rank() over(partition by name order by val nulls las
) "在name列内部排序" ,
2 dense_rank()over(order by val nulls last) "整体排序" from test;
NAME VAL 在name列内部排序 整体排序
-------------------- ---------- ---------------- ----------
a 1 1 1
a 2 2 2
b 3 1 3
b 5 2 4
b 5 2 4
b 9 3 5
c 12 1 6
c 15 2 7
已选择8行。
6、求top n，使用row_number(),它与rank的不同是不会出现并列情况，会依据行的先后顺序给出top n
SQL> select name,val,row_number()over(partition by name order by val nulls last
) row_num from test;
NAME VAL ROW_NUM
-------------------- ---------- ----------
a 1 1
a 2 2
b 3 1
b 5 2
b 5 3
b 9 4
c 12 1
c 15 2
已选择8行。
7、窗口之行窗口（使用rows关键字）
--依据name分组，向前取两条（共三条）记录求平均值
SQL> select name ,val,avg(val)over(partition by name order by val rows 2 precedi
ng) x from test
2 ;
NAME VAL X
-------------------- ---------- ----------
a 1 1
a 2 1.5
b 3 3
b 5 4
b 5 4.33333333
b 9 6.33333333
c 12 12
c 15 13.5
已选择8行。
SQL> select name,val,sum(val)over( order by val rows between 1 preceding and 1
following ) x from test
2 ;
NAME VAL X
-------------------- ---------- ----------
a 1 3
a 2 6
b 3 10
b 5 15
a 7 19
b 7 23
b 9 28
c 12 36
c 15 27
已选择9行。
8、窗口之范围窗口（使用range关键字）
SQL> select name,val,sum(val)over(order by val range between 2 preceding and 2 f
ollowing) x from test;
NAME VAL X
-------------------- ---------- ----------
a 1 6
a 2 6
b 3 11
b 5 22
a 7 28
b 7 28
b 9 23
c 12 12
c 15 15
已选择9行。
SQL> select name,val,sum(val)over(order by val range 2 preceding ) x from test;
NAME VAL X
-------------------- ---------- ----------
a 1 1
a 2 3
b 3 6
b 5 8
a 7 19
b 7 19
b 9 23
c 12 12
c 15 15
已选择9行。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。