窗口函数实验
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)