sum 开窗函数 oracle,关于oracle 开窗函数-一

当前位置:我的异常网» 数据库 » 关于oracle 开窗函数-一

关于oracle 开窗函数-一

www.myexceptions.net  网友分享于：2013-07-03  浏览：7次

关于oracle 开窗函数--1

一.OVER

1.SQL> SELECT deptno,ename,sal FROM emp ORDER BY deptno,ename;

DEPTNO ENAME             SAL

---------- ---------- ----------

10 CLARK            2450

10 KING             5000

10 MILLER           1300

20 FORD             3000

20 JONES            2975

20 SMITH             800

30 ALLEN            1600

30 BLAKE            2850

30 JAMES             950

30 MARTIN           1250

30 TURNER           1500

30 WARD             1250

已选择12行。

2.SQL> SELECT deptno,ename,sal,

2  SUM(sal) OVER(ORDER BY ename) 连续求和,

3  SUM(sal) OVER() 总和, --等效于SUM(sal)

4  100*ROUND(sal/SUM(sal) OVER(),4) "份额(%)"

5  FROM emp;

DEPTNO ENAME             SAL   连续求和       总和    份额(%)

---------- ---------- ---------- ---------- ---------- ----------

30 ALLEN            1600       1600      24925       6.42

30 BLAKE            2850       4450      24925      11.43

10 CLARK            2450       6900      24925       9.83

20 FORD             3000       9900      24925      12.04

30 JAMES             950      10850      24925       3.81

20 JONES            2975      13825      24925      11.94

10 KING             5000      18825      24925      20.06

30 MARTIN           1250      20075      24925       5.02

10 MILLER           1300      21375      24925       5.22

20 SMITH             800      22175      24925       3.21

30 TURNER           1500      23675      24925       6.02

30 WARD             1250      24925      24925       5.02

已选择12行。

3.SQL> SELECT deptno,ename,sal,

2  SUM(sal) OVER(PARTITION BY deptno ORDER BY ename) 部门连续求和,

3  SUM(sal) OVER(PARTITION BY deptno) 部门总和,

4  100*ROUND(sal/SUM(sal) OVER(PARTITION BY deptno),4) "部门份额(%)"

5  FROM emp;

DEPTNO ENAME             SAL 部门连续求和   部门总和 部门份额(%)

---------- ---------- ---------- ------------ ---------- -----------

10 CLARK            2450         2450       8750          28

10 KING             5000         7450       8750       57.14

10 MILLER           1300         8750       8750       14.86

20 FORD             3000         3000       6775       44.28

20 JONES            2975         5975       6775       43.91

20 SMITH             800         6775       6775       11.81

30 ALLEN            1600         1600       9400       17.02

30 BLAKE            2850         4450       9400       30.32

30 JAMES             950         5400       9400       10.11

30 MARTIN           1250         6650       9400        13.3

30 TURNER           1500         8150       9400       15.96

30 WARD             1250         9400       9400        13.3

已选择12行。

4.SQL> SELECT deptno,ename,sal,

2  SUM(sal) OVER(PARTITION BY deptno ORDER BY deptno DESC,sal DESC) dept_sum, --部门连续求和、部门降序、薪水降序

3  SUM(sal) OVER(ORDER BY deptno DESC,sal DESC) sum --部门降序、薪水降序

4  FROM emp;

DEPTNO ENAME             SAL   DEPT_SUM        SUM

---------- ---------- ---------- ---------- ----------

30 BLAKE            2850       2850       2850

30 ALLEN            1600       4450       4450

30 TURNER           1500       5950       5950

30 MARTIN           1250       8450       8450

30 WARD             1250       8450       8450

30 JAMES             950       9400       9400

20 FORD             3000       3000      12400

20 JONES            2975       5975      15375

20 SMITH             800       6775      16175

10 KING             5000       5000      21175

10 CLARK            2450       7450      23625

10 MILLER           1300       8750      24925

已选择12行。

5.SQL> SELECT deptno,ename,sal

2  FROM (SELECT emp.deptno,emp.ename,emp.sal,row_number() over(PARTITION BY de

ptno order by sal desc) rn FROM emp) --部门分组、薪水降序

3  WHERE rn<3; --部门薪水最高的两个人

DEPTNO ENAME             SAL

---------- ---------- ----------

10 KING             5000

10 CLARK            2450

20 FORD             3000

20 JONES            2975

30 BLAKE            2850

30 ALLEN            1600

已选择3行。

SQL> SELECT deptno,ename,sal --部门薪水最高的人，可能有多个

2  FROM emp t

3  WHERE NOT EXISTS(SELECT 1 FROM emp WHERE deptno=t.deptno AND sal>t.sal);

DEPTNO ENAME             SAL

---------- ---------- ----------

20 FORD             3000

10 KING             5000

30 BLAKE            2850

已选择3行。

SQL> SELECT deptno,ename,sal --查询每个部门薪水最高的两个人

2  FROM emp t

3  WHERE (SELECT COUNT(1) FROM emp WHERE deptno=t.deptno AND sal>t.sal)<=1;

DEPTNO ENAME             SAL

---------- ---------- ----------

30 ALLEN            1600

20 FORD             3000

10 KING             5000

20 JONES            2975

30 BLAKE            2850

10 CLARK            2450

已选择6行。

6.其它

SUM(sal) OVER(ORDER BY sal RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SUM(sal) OVER(ORDER BY sal ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SUM(sal) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SUM(sal) OVER(ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

文章评论