最初由 xxscfh 发布
[B]请问西门吹牛:
在9.2文档中看到sum中 RANGE UNBOUNDED PRECEDING 是什么意思? [/B]
和order by一起搭配的。
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
order by column从句是对column分区。
那么就是要计算该分区所有的行了。
SQL> select deptno,sal,sum(sal) over( order by deptno RANGE UNBOUNDED PRECEDING ) sumover from emp ;
DEPTNO SAL SUMOVER
---------- ---------- ----------
10 2450 8750
10 5000 8750
10 1300 8750
20 800 19625
20 1100 19625
20 3000 19625
20 3000 19625
20 2975 19625
30 1600 29025
30 2850 29025
30 1250 29025
DEPTNO SAL SUMOVER
---------- ---------- ----------
30 950 29025
30 1500 29025
30 1250 29025
29025
29025
16 rows selected.
所以还不能完全满足楼主的意思,如果order by的字段有重复值。
我们可以用rows从句的between and
SQL> select deptno,sal,sum(sal) over( order by deptno rows between UNBOUNDED PRECEDING and current row ) sumover from emp ;
DEPTNO SAL SUMOVER
---------- ---------- ----------
10 2450 2450
10 5000 7450
10 1300 8750
20 800 9550
20 1100 10650
20 3000 13650
20 3000 16650
20 2975 19625
30 1600 21225
30 2850 24075
30 1250 25325
DEPTNO SAL SUMOVER
---------- ---------- ----------
30 950 26275
30 1500 27775
30 1250 29025
29025
29025
16 rows selected.
SQL>
不过这样没有分区,你要再加上分区语句。
SQL> select deptno,sal,sum(sal) over( partition by deptno order by deptno rows between UNBOUNDED PRECEDING and current row ) sumover from emp ;
DEPTNO SAL SUMOVER
---------- ---------- ----------
10 2450 2450
10 5000 7450
10 1300 8750
20 800 800
20 1100 1900
20 3000 4900
20 3000 7900
20 2975 10875
30 1600 1600
30 2850 4450
30 1250 5700
DEPTNO SAL SUMOVER
---------- ---------- ----------
30 950 6650
30 1500 8150
30 1250 9400
16 rows selected.