ORDER BY column_list
[ ASC | DESC ] [NULLS | {first | last } ]
[ ROWS | RANGE ]
[BETWEEN start_point AND end_point ] )
SQL> select empno, ename, sal,
2 avg(sal) over() avg1
3 from emp;
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7369 SMITH 800 2073.21429
7499 ALLEN 1600 2073.21429
7521 WARD 1250 2073.21429
7566 JONES 2975 2073.21429
7654 MARTIN 1250 2073.21429
7698 BLAKE 2850 2073.21429
7782 CLARK 2450 2073.21429
7788 SCOTT 3000 2073.21429
7839 KING 5000 2073.21429
7844 TURNER 1500 2073.21429
7876 ADAMS 1100 2073.21429
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7900 JAMES 950 2073.21429
7902 FORD 3000 2073.21429
7934 MILLER 1300 2073.21429
14 개의 행이 선택되었습니다.
SQL> select empno, ename, sal,
2 avg(sal) over(ORDER BY SAL) avg1
3 from emp;
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7369 SMITH 800 800
7900 JAMES 950 875
7876 ADAMS 1100 950
7521 WARD 1250 1070
7654 MARTIN 1250 1070
7934 MILLER 1300 1108.33333
7844 TURNER 1500 1164.28571
7499 ALLEN 1600 1218.75
7782 CLARK 2450 1355.55556
7698 BLAKE 2850 1505
7566 JONES 2975 1638.63636
EMPNO ENAME SAL AVG1
---------- ---------- ---------- ----------
7788 SCOTT 3000 1848.07692
7902 FORD 3000 1848.07692
7839 KING 5000 2073.21429
14 개의 행이 선택되었습니다.
SQL> select DEPTNO, empno, ename, sal,
2 avg(sal) over(PARTITION BY DEPTNO) avg1
3 from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
10 7934 MILLER 1300 2916.66667
20 7566 JONES 2975 2175
20 7902 FORD 3000 2175
20 7876 ADAMS 1100 2175
20 7369 SMITH 800 2175
20 7788 SCOTT 3000 2175
30 7521 WARD 1250 1566.66667
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
30 7900 JAMES 950 1566.66667
30 7698 BLAKE 2850 1566.66667
30 7654 MARTIN 1250 1566.66667
14 개의 행이 선택되었습니다.
SQL> select DEPTNO, empno, ename, sal,
2 avg(sal)
3 over(
4 PARTITION BY DEPTNO
5 ORDER BY SAL
6 ROWS BETWEEN UNBOUNDED PRECEDING
7 AND CURRENT ROW
8 ) avg1
9 from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7934 MILLER 1300 1300
10 7782 CLARK 2450 1875
10 7839 KING 5000 2916.66667
20 7369 SMITH 800 800
20 7876 ADAMS 1100 950
20 7566 JONES 2975 1625
20 7788 SCOTT 3000 1968.75
20 7902 FORD 3000 2175
30 7900 JAMES 950 950
30 7654 MARTIN 1250 1100
30 7521 WARD 1250 1150
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
30 7844 TURNER 1500 1237.5
30 7499 ALLEN 1600 1310
30 7698 BLAKE 2850 1566.66667
14 개의 행이 선택되었습니다.
SQL>
SQL> select DEPTNO, empno, ename, sal,
2 avg(sal)
3 over(
4 PARTITION BY DEPTNO
5 ORDER BY SAL
6 RANGE BETWEEN UNBOUNDED PRECEDING
7 AND CURRENT ROW
8 ) avg1
9 from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7934 MILLER 1300 1300
10 7782 CLARK 2450 1875
10 7839 KING 5000 2916.66667
20 7369 SMITH 800 800
20 7876 ADAMS 1100 950
20 7566 JONES 2975 1625
20 7788 SCOTT 3000 2175
20 7902 FORD 3000 2175
30 7900 JAMES 950 950
30 7654 MARTIN 1250 1150
30 7521 WARD 1250 1150
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
30 7844 TURNER 1500 1237.5
30 7499 ALLEN 1600 1310
30 7698 BLAKE 2850 1566.66667
14 개의 행이 선택되었습니다.
SQL>
SQL> select DEPTNO, empno, ename, sal,
2 avg(sal)
3 over(
4 PARTITION BY DEPTNO
5 ORDER BY SAL
6 ROWS BETWEEN UNBOUNDED PRECEDING
7 AND UNBOUNDED FOLLOWING
8 ) avg1
9 from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7934 MILLER 1300 2916.66667
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
20 7369 SMITH 800 2175
20 7876 ADAMS 1100 2175
20 7566 JONES 2975 2175
20 7788 SCOTT 3000 2175
20 7902 FORD 3000 2175
30 7900 JAMES 950 1566.66667
30 7654 MARTIN 1250 1566.66667
30 7521 WARD 1250 1566.66667
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
30 7698 BLAKE 2850 1566.66667
14 개의 행이 선택되었습니다.
SQL>
SQL> select DEPTNO, empno, ename, sal,
2 avg(sal)
3 over(
4 PARTITION BY DEPTNO
5 ORDER BY SAL
6 RANGE BETWEEN UNBOUNDED PRECEDING
7 AND UNBOUNDED FOLLOWING
8 ) avg1
9 from emp;
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
10 7934 MILLER 1300 2916.66667
10 7782 CLARK 2450 2916.66667
10 7839 KING 5000 2916.66667
20 7369 SMITH 800 2175
20 7876 ADAMS 1100 2175
20 7566 JONES 2975 2175
20 7788 SCOTT 3000 2175
20 7902 FORD 3000 2175
30 7900 JAMES 950 1566.66667
30 7654 MARTIN 1250 1566.66667
30 7521 WARD 1250 1566.66667
DEPTNO EMPNO ENAME SAL AVG1
---------- ---------- ---------- ---------- ----------
30 7844 TURNER 1500 1566.66667
30 7499 ALLEN 1600 1566.66667
30 7698 BLAKE 2850 1566.66667
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, SAL,
2 CUME_DIST() OVER(ORDER BY SAL) ,
3 PERCENT_RANK() OVER(ORDER BY SAL)
4 FROM EMP;
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) PERCENT_RANK()OVER(ORDERBYSAL)
---------- ---------- --------------------------- ------------------------------
7369 800 .071428571 0
7900 950 .142857143 .076923077
7876 1100 .214285714 .153846154
7521 1250 .357142857 .230769231
7654 1250 .357142857 .230769231
7934 1300 .428571429 .384615385
7844 1500 .5 .461538462
7499 1600 .571428571 .538461538
7782 2450 .642857143 .615384615
7698 2850 .714285714 .692307692
7566 2975 .785714286 .769230769
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) PERCENT_RANK()OVER(ORDERBYSAL)
---------- ---------- --------------------------- ------------------------------
7788 3000 .928571429 .846153846
7902 3000 .928571429 .846153846
7839 5000 1 1
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, SAL,
2 CUME_DIST() OVER(ORDER BY SAL) ,
3 NTILE(4) OVER(ORDER BY SAL)
4 FROM EMP;
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) NTILE(4)OVER(ORDERBYSAL)
---------- ---------- --------------------------- ------------------------
7369 800 .071428571 1
7900 950 .142857143 1
7876 1100 .214285714 1
7521 1250 .357142857 1
7654 1250 .357142857 2
7934 1300 .428571429 2
7844 1500 .5 2
7499 1600 .571428571 2
7782 2450 .642857143 3
7698 2850 .714285714 3
7566 2975 .785714286 3
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) NTILE(4)OVER(ORDERBYSAL)
---------- ---------- --------------------------- ------------------------
7788 3000 .928571429 4
7902 3000 .928571429 4
7839 5000 1 4
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, SAL,
2 CUME_DIST() OVER(ORDER BY SAL) ,
3 ROW_NUMBER() OVER(ORDER BY SAL)
4 FROM EMP;
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) ROW_NUMBER()OVER(ORDERBYSAL)
---------- ---------- --------------------------- ----------------------------
7369 800 .071428571 1
7900 950 .142857143 2
7876 1100 .214285714 3
7521 1250 .357142857 4
7654 1250 .357142857 5
7934 1300 .428571429 6
7844 1500 .5 7
7499 1600 .571428571 8
7782 2450 .642857143 9
7698 2850 .714285714 10
7566 2975 .785714286 11
EMPNO SAL CUME_DIST()OVER(ORDERBYSAL) ROW_NUMBER()OVER(ORDERBYSAL)
---------- ---------- --------------------------- ----------------------------
7788 3000 .928571429 12
7902 3000 .928571429 13
7839 5000 1 14
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, SAL,
2 FIRST_VALUE(SAL) OVER(ORDER BY SAL) FV,
3 LAST_VALUE(SAL) OVER(ORDER BY SAL) LV
4 FROM EMP;
EMPNO SAL FV LV
---------- ---------- ---------- ----------
7369 800 800 800
7900 950 800 950
7876 1100 800 1100
7521 1250 800 1250
7654 1250 800 1250
7934 1300 800 1300
7844 1500 800 1500
7499 1600 800 1600
7782 2450 800 2450
7698 2850 800 2850
7566 2975 800 2975
EMPNO SAL FV LV
---------- ---------- ---------- ----------
7788 3000 800 3000
7902 3000 800 3000
7839 5000 800 5000
14 개의 행이 선택되었습니다.
SQL> SELECT DEPTNO, GROUPING(DEPTNO),
2 JOB, GROUPING(JOB),
3 COUNT(*),
4 AVG(SAL) * 12
5 FROM EMP
6 GROUP BY ROLLUP(DEPTNO, JOB);
| DEPTNO | GROUPING(DEPTNO) | JOB | GROUPING(JOB) | COUNT(*) | AVG(SAL)*12 |
| 10 | 0 | CLERK | 0 | 1 | 15600 |
| 10 | 0 | MANAGER | 0 | 1 | 29400 |
| 10 | 0 | PRESIDENT | 0 | 1 | 60000 |
| 10 | 0 | 1 | 3 | 35000 | |
| 20 | 0 | CLERK | 0 | 2 | 11400 |
| 20 | 0 | ANALYST | 0 | 2 | 36000 |
| 20 | 0 | MANAGER | 0 | 1 | 35700 |
| 20 | 0 | 1 | 5 | 26100 | |
| 30 | 0 | CLERK | 0 | 1 | 11400 |
| 30 | 0 | MANAGER | 0 | 1 | 34200 |
| 30 | 0 | SALESMAN | 0 | 4 | 16800 |
| 30 | 0 | 1 | 6 | 18800 | |
| 1 | 1 | 14 | 24878.57143 |
- GROUPING SETS 와 GROUP BY ROLLUP/CUBE 의 비교
| SELECT DEPTNO, JOB, MGR, AVG(SAL) FROM EMP GROUP BY GROUPING SETS( (DEPTNO,JOB), (JOB,MGR)) ORDER BY 1,2,3,4; |
= | SELECT DEPTNO, JOB,NULL AS MGR, AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB UNION ALL SELECT NULL, JOB, MGR, AVG(SAL) FROM EMP GROUP BY JOB,MGR ORDER BY 1,2,3,4; |