Oracle 分析函数（Analysis Function）相关用法

测试表结构：

SQL> desc can_do;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER(9)
NAME                                               VARCHAR2(20)
DEPART                                             VARCHAR2(50)
AGE                                                NUMBER(3)
SALARY                                             NUMBER(7)

测试表数据：

ID NAME DEPART AGE SALARY 1 zhanga mochasoft 28 3000 2 zhangb mochasoft 27 3500 3 zhangc mochasoft 29 4000 15 zhaoe hp 25 6000 4 wanga microsoft 26 8000 14 zhaod hp 26 8050 13 zhaoc hp 26 8090 8 liub ibm 26 8800 5 wangb microsoft 30 9000 12 zhaob hp 27 9050 7 liua ibm 24 9900 6 wangc microsoft 25 10000 11 zhaoa hp 28 11150 9 liuc ibm 27 12000 10 liud ibm 28 13000

1、over的用法：

<1>、over(order by c.salary range between 3000 preceding and 150 following) ：

select avg(c.salary) over(order by c.salary range between 3000 preceding and 150 following) as result,
       c.salary
from can_do c

select(8000+6000+8050+8090)/4 from dual;

RESULT SALARY 3000 3000 3250 3500 3500 4000 4125 6000 7535 8000 7535 8050 7535 8090 7788 8800 8141.428571 9000 8498.333333 9050 8861.25 9900 8861.25 10000 9650 11150 10183.33333 12000 11537.5 13000

<2>、over(order by c.salary rows between 1 preceding and 2 following)

select avg(c.salary) over(order by c.salary rows between 1 preceding and 2 following) as result,
       c.salary
from can_do c
select (8050+8000+8090)/3 from dual;

<3>、over(order by c.salary rows between unbounded preceding and unbounded following)

select avg(c.salary) over(order by c.salary rows between unbounded preceding and unbounded following) as result,
       c.salary
from can_do c

<4>、over(order by c.salary range between unbounded preceding and unbounded following)

select avg(c.salary) over(order by c.salary range between unbounded preceding and unbounded following) as result,
       c.salary
from can_do c

说明：<3>和<4>即计算所有行，相当于没有条件限制或者没有范围的约束。

<5>、over(partition by c.depart) ：分区域统计（平均数）

select avg(c.salary) over(partition by c.depart) as result,c.salary,c.depart from can_do c

如下：

select distinct avg(c.salary) over(partition by c.depart) as result,c.depart from can_do c

<6>、连续求和问题，sum()：如下：

select sum(d.salary) over (order by rownum) as result,d.salary,d.name from can_do d

select sum(d.salary) over (partition by d.depart) as result,d.salary,d.depart from can_do d

select sum(d.salary) over (partition by null) as result,d.salary,d.depart from can_do d

<7>、级别问题，rank()和dense_rank()，rank()会跳号，dense_rank()不会跳号：

select rank() over (partition by e.depart order by e.salary desc) as result,e.salary,e.name,e.depart from can_do e

select rank() over (order by f.salary desc) as result,f.salary,f.name from can_do f

select dense_rank() over (order by f.salary desc) as result,f.salary,f.name from can_do f

<8>、将分组最大值（最小值）统计出来：

select max(g.salary) over(partition by g.depart) as result,g.name,g.depart,g.salary from can_do g

select min(g.salary) over(partition by g.depart) as result,g.name,g.depart,g.salary from can_do g

select distinct min(g.salary) over(partition by g.depart) as result,g.depart from can_do g

select distinct max(g.salary) over(partition by g.depart) as result,g.depart from can_do g

Tip：如果没有指定over()函数部分，则会有如下提示：

SQL> select rank() ,e.salary,e.name,e.depart from can_do e;
select rank() ,e.salary,e.name,e.depart from can_do e
       *
ERROR at line 1:
ORA-30484: missing window specification for this function

可以参见：xsb.itpub.net/post/419/33028

<9>、row_number()的用法：

select row_number() over(order by h.salary) as result,h.salary from can_do h

select row_number() over(order by h.salary desc) as result,h.salary from can_do h

select row_number() over(partition by h.depart order by h.salary) as result,h.depart,h.salary from can_do h

【编写于 2009-01-21】