over(partiton by )的学习

sun(A1)over(partition by A2 order by A3)
以A2分组，按A3排序，求出A1的累积和。

 

rank() over(partition by A2 order by A3)
以A2分组，按A3排序，排列号

 

sun(A1)over(partition by A2 )
以A2分组，求出组中成员A1的sum

sun(A1)over(order by A3)
以A3排序，求出A1的累积和

sun(A1)over()
相当于sum(A1)

 

例如

SELECT p.deptno,p.ename,p.sal ,p.hiredate,
SUM(p.sal) over(PARTITION BY p.deptno ORDER BY p.hiredate) ,
SUM(p.sal) over(PARTITION BY p.deptno),
SUM(p.sal) over(ORDER BY p.hiredate)
FROM emp p 

 

deptno  ename          sal                  hiredate             a1            a2                 a3

20         SMITH          800.00          1980-12-17          800          10875          800
30         ALLEN          1600.00        1981-2-20          1600          9400            2400
30         WARD          1250.00          1981-2-22          2850          9400          3650
20         JONES          2975.00          1981-4-2          3775          10875          6625
30         BLAKE          2850.00          1981-5-1          5700          9400            9475
10         CLARK          2450.00          1981-6-9          2450          8750            11925
30         TURNER       1500.00          1981-9-8          7200          9400            13425
30         MARTIN        1250.00          1981-9-28          8450          9400          14675
10          KING          5000.00          1981-11-17          7450        8750           19675
30          JAMES          950.00          1981-12-3          9400          9400            23625
20          FORD          3000.00          1981-12-3          6775          10875         23625
10          MILLER          1300.00          1982-1-23          8750       8750           24925
20          SCOTT          3000.00          1987-4-19          9775          10875        27925
20          ADAMS          1100.00          1987-5-23          10875       10875        29025