Oracle 分析函数实验之窗口

窗口函数实验

create table window_tmp(
 aa number(5) NOT NULL);
 
 insert into window_tmp values(1);
 insert into window_tmp values(2);
 insert into window_tmp values(2);
 insert into window_tmp values(2);
 insert into window_tmp values(3);
 insert into window_tmp values(4);
 insert into window_tmp values(5);
 insert into window_tmp values(6);
 insert into window_tmp values(7);
 insert into window_tmp values(9);
 insert into window_tmp values(11);
 
 
 select * from window_tmp;
 
 -- 序号  aa值
rownum  AA
1 1
2 2
3 2
4 2
5 3
6 4
7 5
8 6
9 7
10 9
11 11
 -- 排序后
 select sum(aa) over (order by aa range between 1 preceding and 2 following) from window_tmp;

rownum AA SUM(AA)OVER(ORDERBYAARANGEBETW
1 1 10
2 2 14
3 2 14
4 2 14
5 3 18
6 4 18
7 5 22
8 6 18
9 7 22
10 9 20
11 11 11

1. 帮助理解:
就是说，对于aa=5的一行 ，sum为   5-1<=aa<=5+2 的和, 所以求的是4,5,6,7的和=22
   对于aa=2来说 ，求的是2-1<=aa<=2+2, 即1,2,2,2,3,4的和=10
   又如 对于aa=9 ，9-1<=aa<=9+2 有9和11两个数，所以sum=20
   对于aa=11, 11-1<=aa<=11+2,只有11,所以sum=11

2. 其它：
     over（order by salary rows between 2 preceding and 4 following）
          每行对应的数据窗口是之前2行，之后4行
3. 下面三条语句等效：          
     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)