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行。