关于oracle 开窗函数-一
关于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)
一.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)