1. WINDOW FUNCTION 개요
2. 그룹 내 순위 함수
3. 일반 집계 함수
4. 그룹 내 행 순서 함수
5. 그룹 내 비율 함수
WINDOW FUNCTION SYNTAX
SELECT WINDOW_FUNCTION ( ARGUMENTS ) OVER
( PARTITION BY 칼럼 ORDER BY 절 WINDOWING 절)
FROM 테이블 명 ;
BETWEEN 사용 타입
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
ROW | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING
RANK : 1 2 2 4
DENSE_RANK : 1 2 2 3
ROW_NUMBER : 1 2 3 4
* 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력
* 동일 SALARY 에 대해서 같은 순위 부여
* ORDER BY SAL DESC 와 PARTITION BY JOB ORDER BY SAL DESC 가 충돌 하여 ORDER BY SAL DESC 기준으로 정렬
SQL> SELECT JOB, ENAME, SAL,
RANK() OVER ( ORDER BY SAL DESC ) ALL_RANK,
RANK() OVER ( PARTITION BY JOB ORDER BY SAL DESC ) JOB_RANK
FROM EMP ;
JOB ENAME SAL ALL_RANK JOB_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 1 << 동일 SALARY(명시적으로 ORDER BY 절 추가)
ANALYST SCOTT 3000 2 1 << 동일 SALARY
MANAGER JONES 2975 4 1
MANAGER BLAKE 2850 5 2
MANAGER CLARK 2450 6 3
SALESMAN ALLEN 1600 7 1
SALESMAN TURNER 1500 8 2
CLERK MILLER 1300 9 1
SALESMAN WARD 1250 10 3
SALESMAN MARTIN 1250 10 3
CLERK ADAMS 1100 12 2
CLERK JAMES 950 13 3
CLERK SMITH 800 14 4
14 rows selected.
* 전체 SALARY 순위 구하는 ALL_RANK 칼럼 제외, 업무별로 SALARY 순서를 구하는 JOB_RANK 만 출력
* 결과적으로 JOB 과 SALARY 별로 정렬
SQL> SELECT JOB, ENAME, SAL,
RANK() OVER ( PARTITION BY JOB ORDER BY SAL DESC ) JOB_RANK
FROM EMP ;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
-------------------------------------------------------------------
CLERK MILLER 1300 1
CLERK ADAMS 1100 2
CLERK JAMES 950 3
CLERK SMITH 800 4
-------------------------------------------------------------------
MANAGER JONES 2975 1
MANAGER BLAKE 2850 2
MANAGER CLARK 2450 3
-------------------------------------------------------------------
PRESIDENT KING 5000 1
-------------------------------------------------------------------
SALESMAN ALLEN 1600 1
SALESMAN TURNER 1500 2
SALESMAN MARTIN 1250 3
SALESMAN WARD 1250 3
-------------------------------------------------------------------
14 rows selected.
* 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력
SQL> SELECT JOB, ENAME, SAL,
RANK() OVER ( ORDER BY SAL DESC ) RANK,
DENSE_RANK() OVER ( ORDER BY SAL DESC ) DENSE_RANK
FROM EMP ;
JOB ENAME SAL RANK DENSE_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 2
ANALYST SCOTT 3000 2 2
MANAGER JONES 2975 4 3 <== RANK(1,2,2,4)와 DENSE_RANK(1,2,2,3)의 차이점
MANAGER BLAKE 2850 5 4
MANAGER CLARK 2450 6 5
SALESMAN ALLEN 1600 7 6
SALESMAN TURNER 1500 8 7
CLERK MILLER 1300 9 8
SALESMAN WARD 1250 10 9
SALESMAN MARTIN 1250 10 9
CLERK ADAMS 1100 12 10
CLERK JAMES 950 13 11
CLERK SMITH 800 14 12
14 rows selected.
* 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력
* 같은 SALARY 에서는 어떤 순서가 정해질지 알수 없다.(Oracle 의 경우 rowid 가 적은 행이 먼저 나옴 )
* DBMS(Oracle rowid) 에 의해 정렬을 원지 않는다면, 명시적으로 ORDER BY 추가 할 것
SQL> SELECT JOB, ENAME, SAL,
RANK() OVER ( ORDER BY SAL DESC ) RANK,
ROW_NUMBER() OVER ( ORDER BY SAL DESC ) ROW_NUMBER
FROM EMP ;
JOB ENAME SAL RANK ROW_NUMBER
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 2
ANALYST SCOTT 3000 2 3 << RANK(1,2,2,4) 와 ROW_NUMBER(1,2,3,4) 차이점
MANAGER JONES 2975 4 4
MANAGER BLAKE 2850 5 5
MANAGER CLARK 2450 6 6
SALESMAN ALLEN 1600 7 7
SALESMAN TURNER 1500 8 8
CLERK MILLER 1300 9 9
SALESMAN WARD 1250 10 10
SALESMAN MARTIN 1250 10 11 << RANK(10,10) 와 ROW_NUMBER(10,11) 차이점
CLERK ADAMS 1100 12 12
CLERK JAMES 950 13 13
CLERK SMITH 800 14 14
14 rows selected.
* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구함
* PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화
SQL> SELECT MGR, ENAME, SAL, SUM(SAL) OVER ( PARTITION BY MGR ) MGR_SUM
FROM EMP ;
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7566 FORD 3000 6000
7566 SCOTT 3000 6000
---------------------------------------------------------------
7698 JAMES 950 6550
7698 ALLEN 1600 6550
7698 WARD 1250 6550
7698 TURNER 1500 6550
7698 MARTIN 1250 6550
---------------------------------------------------------------
7782 MILLER 1300 1300
---------------------------------------------------------------
7788 ADAMS 1100 1100
---------------------------------------------------------------
7839 BLAKE 2850 8275
7839 JONES 2975 8275
7839 CLARK 2450 8275
---------------------------------------------------------------
7902 SMITH 800 800
---------------------------------------------------------------
KING 5000 5000
14 rows selected.
* OVER 절 내의 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고
이전 SALARY 데이터까지의 누적값을 출력 ( <<< ORDER BY SAL 추가 )
* SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.( !!!! )
* RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정
* 동일 순위의 모든 값에 대한 총합계로 누적됨 ( 아래 << 950 + 1250 + 1250 = 3450 )
SQL> SELECT MGR, ENAME, SAL, SUM(SAL) OVER ( PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING ) AS MGR_SUM
FROM EMP ;
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7566 SCOTT 3000 6000
7566 FORD 3000 6000
---------------------------------------------------------------
7698 JAMES 950 950
7698 WARD 1250 3450 << 950 + 1250 + 1250 = 3450
7698 MARTIN 1250 3450 << 950 + 1250 + 1250 = 3450
7698 TURNER 1500 4950 << 950 + 1250 + 1250 + 1500 = 4950
7698 ALLEN 1600 6550
---------------------------------------------------------------
7782 MILLER 1300 1300
---------------------------------------------------------------
7788 ADAMS 1100 1100
---------------------------------------------------------------
7839 CLARK 2450 2450
7839 BLAKE 2850 5300
7839 JONES 2975 8275
---------------------------------------------------------------
7902 SMITH 800 800
---------------------------------------------------------------
KING 5000 5000
14 rows selected.
* SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.( !!!! )
==> MS SQL 2012 에서 수행 결과 에러 없이, ORDER BY 되어서 결과가 정상적으로 출력됨
SELECT MGR, ENAME, SAL, SUM(SAL) OVER ( PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING ) AS MGR_SUM
FROM EMP ;
MGR ENAME SAL MGR_SUM
------ ---------- --------------------------------------- ---------------------------------------
NULL KING 5000.00 5000.00
7566 FORD 3000.00 6000.00
7566 SCOTT 3000.00 6000.00
7698 JAMES 950.00 950.00
7698 MARTIN 1250.00 3450.00
7698 WARD 1250.00 3450.00
7698 TURNER 1500.00 4950.00
7698 ALLEN 1600.00 6550.00
7782 MILLER 1300.00 1300.00
7788 ADAMS 1100.00 1100.00
7839 CLARK 2450.00 2450.00
7839 BLAKE 2850.00 5300.00
7839 JONES 2975.00 8275.00
7902 SMITH 800.00 800.00
(14개 행이 영향을 받음)
* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값 구함
SQL> SELECT MGR, ENAME, SAL, MAX(SAL) OVER ( PARTITION BY MGR) AS MGR_MAX
FROM EMP ;
MGR ENAME SAL MGR_MAX
---------- ---------- ---------- ----------
7566 FORD 3000 3000
7566 SCOTT 3000 3000
---------------------------------------------------------------
7698 JAMES 950 1600
7698 ALLEN 1600 1600
7698 WARD 1250 1600
7698 TURNER 1500 1600
7698 MARTIN 1250 1600
---------------------------------------------------------------
7782 MILLER 1300 1300
---------------------------------------------------------------
7788 ADAMS 1100 1100
---------------------------------------------------------------
7839 BLAKE 2850 2975
7839 JONES 2975 2975
7839 CLARK 2450 2975
---------------------------------------------------------------
7902 SMITH 800 800
---------------------------------------------------------------
KING 5000 5000
---------------------------------------------------------------
14 rows selected.
* 추가로, INLINE VIEW 를 이용해 파티션별로 최대값을 가진 행만 추출
SQL> SELECT MGR, ENAME, SAL
FROM ( SELECT MGR, ENAME, SAL, MAX(SAL) OVER ( PARTITION BY MGR ) AS IV_MAX_SAL
FROM EMP )
WHERE SAL = IV_MAX_SAL ;
MGR ENAME SAL
---------- ---------- ----------
7566 FORD 3000 << 최대값 가지는 ROW 가 2건 모두 출력
7566 SCOTT 3000 <<
7698 ALLEN 1600
7782 MILLER 1300
7788 ADAMS 1100
7839 JONES 2975
7902 SMITH 800
KING 5000
8 rows selected.
* 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자 기준으로 정렬하고, SALARY 최소값을 구함
SQL> SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ) AS MIN_SAL
FROM EMP ;
MGR ENAME HIREDATE SAL MIN_SAL
---------- ---------- --------------- ---------- ----------
7566 FORD 03-DEC-81 3000 3000
7566 SCOTT 13-JUL-87 3000 3000
---------------------------------------------------------------
7698 ALLEN 20-FEB-81 1600 1600
7698 WARD 22-FEB-81 1250 1250
7698 TURNER 08-SEP-81 1500 1250
7698 MARTIN 28-SEP-81 1250 1250
7698 JAMES 03-DEC-81 950 950
---------------------------------------------------------------
7782 MILLER 23-JAN-82 1300 1300
---------------------------------------------------------------
7788 ADAMS 13-JUL-87 1100 1100
---------------------------------------------------------------
7839 JONES 02-APR-81 2975 2975
7839 BLAKE 01-MAY-81 2850 2850
7839 CLARK 09-JUN-81 2450 2450
---------------------------------------------------------------
7902 SMITH 17-DEC-80 800 800
---------------------------------------------------------------
KING 17-NOV-81 5000 5000
---------------------------------------------------------------
14 rows selected.
* EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY 구함
* 평균 SALARY 의 제한 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 지원만을 대상으로 함
* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한건, 현재 행, 뒤의 한 건을 범위로 지정
* ROWS 는 현재 행의 앞뒤 ROWS 를 말함
SQL> SELECT MGR, ENAME, HIREDATE, SAL,
ROUND( AVG(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG
FROM EMP ;
MGR ENAME HIREDATE SAL MGR_AVG
---------- ---------- --------------- ---------- ----------
7566 FORD 03-DEC-81 3000 3000
7566 SCOTT 13-JUL-87 3000 3000
---------------------------------------------------------------
7698 ALLEN 20-FEB-81 1600 1425 << ( 앞 로우 없음 + 1600 + 1250 )/2 = 1425
7698 WARD 22-FEB-81 1250 1450
7698 TURNER 08-SEP-81 1500 1333 << ( 1250 + 1500 + 1250 )/3 = 1333
7698 MARTIN 28-SEP-81 1250 1233
7698 JAMES 03-DEC-81 950 1100 << ( 1250 + 950 + 뒤 로우 없음 )/2 = 1100
---------------------------------------------------------------
7782 MILLER 23-JAN-82 1300 1300
---------------------------------------------------------------
7788 ADAMS 13-JUL-87 1100 1100
---------------------------------------------------------------
7839 JONES 02-APR-81 2975 2913
7839 BLAKE 01-MAY-81 2850 2758
7839 CLARK 09-JUN-81 2450 2650
---------------------------------------------------------------
7902 SMITH 17-DEC-80 800 800
---------------------------------------------------------------
KING 17-NOV-81 5000 5000
---------------------------------------------------------------
14 rows selected.
* 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받은 인원수를 출력
* RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행(Row)의 급여값을 기준으로 급여가 -50 에서 +150 의 범위 내에 포함된 모든 행이 대상
* RANGE 는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시
SQL> SELECT ENAME, SAL,
COUNT(*) OVER ( ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ) AS SIM_CNT,
TO_CHAR(SAL-50)||'->'||TO_CHAR(SAL+150) AS "RANGE"
FROM EMP ;
ENAME SAL SIM_CNT RANGE
---------- ---------- ---------- ------------------
SMITH 800 2 750->950
JAMES 950 2 900->1100
ADAMS 1100 3 1050->1250 << 1100, 1250, 1250 ==> 3
WARD 1250 3 1200->1400
MARTIN 1250 3 1200->1400
MILLER 1300 3 1250->1450
TURNER 1500 2 1450->1650
ALLEN 1600 1 1550->1750
CLARK 2450 1 2400->2600
BLAKE 2850 4 2800->3000
JONES 2975 3 2925->3125
SCOTT 3000 3 2950->3150
FORD 3000 3 2950->3150
KING 5000 1 4950->5150
14 rows selected.
* 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력
* ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정
* FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리함
* DEPTNO = 20 파티션의 최고 연봉은 3000 이고, SCOTT 과 FORD 가 대상 ==> 이중 하나인 SCOTT 만 처리됨
* 명시적인 정렬을 위해서 INLINE VIEW 나, OVER () 내의 ORDER BY 절에 칼럼 추가 할 것
SQL> SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING ) AS DEPT_RICH
FROM EMP ;
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
---------------------------------------------------------------
20 SCOTT 3000 SCOTT << DEPTNO = 20
20 FORD 3000 SCOTT << DEPTNO = 20
20 JONES 2975 SCOTT
20 ADAMS 1100 SCOTT
20 SMITH 800 SCOTT
---------------------------------------------------------------
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
---------------------------------------------------------------
14 rows selected.
* MSSQL 2012 [ 이젠 지원됨 확인 ]
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING ) AS DEPT_RICH
FROM EMP ;
DEPTNO ENAME SAL DEPT_RICH
------ ---------- --------------------------------------- ----------
10 KING 5000.00 KING
10 CLARK 2450.00 KING
10 MILLER 1300.00 KING
20 FORD 3000.00 FORD
20 SCOTT 3000.00 FORD
20 JONES 2975.00 FORD
20 ADAMS 1100.00 FORD
20 SMITH 800.00 FORD
30 BLAKE 2850.00 BLAKE
30 ALLEN 1600.00 BLAKE
30 TURNER 1500.00 BLAKE
30 WARD 1250.00 BLAKE
30 MARTIN 1250.00 BLAKE
30 JAMES 950.00 BLAKE
(14개 행이 영향을 받음)
* FIRST_VALUE 를 처리하기 위해 ORDER BY 정렬 조건을 추가
SQL> SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC << 임의의 SCOTT 이 아닌 FORD 를 추출 하기 위해 ASC( F > S ) 정렬 필요함
ROWS UNBOUNDED PRECEDING ) AS DEPT_RICH
FROM EMP ;
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 FORD 3000 FORD
20 SCOTT 3000 FORD
20 JONES 2975 FORD
20 ADAMS 1100 FORD
20 SMITH 800 FORD
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
14 rows selected.
* 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력
* ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정
* 공동 등수를 인정하기 않고 가장 나중에 나온 행만 처리
* 명시적인 정렬을 위해서 INLINDE VIEW 나 OVER() 내의 ORDER BY 조건 컬럼 명시
SQL> SELECT DEPTNO, ENAME, SAL,
LAST_VALUE (ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DEPT_POOR
FROM EMP ;
DEPTNO ENAME SAL DEPT_POOR
---------- ---------- ---------- ----------
10 KING 5000 MILLER
10 CLARK 2450 MILLER
10 MILLER 1300 MILLER
---------------------------------------------------------------
20 SCOTT 3000 SMITH
20 FORD 3000 SMITH
20 JONES 2975 SMITH
20 ADAMS 1100 SMITH
20 SMITH 800 SMITH
---------------------------------------------------------------
30 BLAKE 2850 JAMES
30 ALLEN 1600 JAMES
30 TURNER 1500 JAMES
30 MARTIN 1250 JAMES
30 WARD 1250 JAMES
30 JAMES 950 JAMES
---------------------------------------------------------------
14 rows selected.
* MS SQL 2012 ( 지원됨 )
SELECT DEPTNO, ENAME, SAL,
LAST_VALUE (ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DEPT_POOR
FROM EMP ;
DEPTNO ENAME SAL DEPT_POOR
------ ---------- --------------------------------------- ----------
10 MILLER 1300.00 MILLER
10 CLARK 2450.00 MILLER
10 KING 5000.00 MILLER
20 SMITH 800.00 SMITH
20 ADAMS 1100.00 SMITH
20 JONES 2975.00 SMITH
20 SCOTT 3000.00 SMITH
20 FORD 3000.00 SMITH
30 JAMES 950.00 JAMES
30 WARD 1250.00 JAMES
30 MARTIN 1250.00 JAMES
30 TURNER 1500.00 JAMES
30 ALLEN 1600.00 JAMES
30 BLAKE 2850.00 JAMES
(14개 행이 영향을 받음)
* 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 보인 급여와 함께 출력
SQL> SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER ( ORDER BY HIREDATE ) AS PREV_SAL
FROM EMP
WHERE JOB ='SALESMAN';
ENAME HIREDATE JOB SAL PREV_SAL
---------- --------------- --------- ---------- ----------
ALLEN 20-FEB-81 SALESMAN 1600
WARD 22-FEB-81 SALESMAN 1250 1600 <<
TURNER 08-SEP-81 SALESMAN 1500 1250 <<
MARTIN 28-SEP-81 SALESMAN 1250 1500 <<
* LAG(인수1,인수2,인수3 )
* 인수1 - 입력 칼럼
* 인수2 - 몇 번째 앞의 행을 가져올지 결정(DEFAULT 1)
* 인수3 - 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력
* MS SQL 2012
SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER ( ORDER BY HIREDATE ) AS PREV_SAL
FROM EMP
WHERE JOB ='SALESMAN';
ENAME HIREDATE JOB SAL PREV_SAL
---------- ---------- --------- --------------------------------------- ---------------------------------------
ALLEN 1981-02-20 SALESMAN 1600.00 NULL
WARD 1981-02-22 SALESMAN 1250.00 1600.00
TURNER 1981-09-08 SALESMAN 1500.00 1250.00
MARTIN 1981-09-28 SALESMAN 1250.00 1500.00
(4개 행이 영향을 받음)
* 2 행 앞의 SALARY 가져오기, 가져올 값이 없으면 0으로 처리
SQL> SELECT ENAME, HIREDATE, SAL,
LAG(SAL, 2, 0) OVER ( ORDER BY HIREDATE ) AS PREV_SAL
FROM EMP
WHERE JOB ='SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------------- ---------- ----------
ALLEN 20-FEB-81 1600 0 <<
WARD 22-FEB-81 1250 0 <<
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
SQL>
SQL> SELECT ENAME, HIREDATE,
LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED"
FROM EMP ;
ENAME HIREDATE NEXTHIRED
---------- --------------- ---------------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81 <<
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81 08-SEP-81
TURNER 08-SEP-81 28-SEP-81
MARTIN 28-SEP-81 17-NOV-81
KING 17-NOV-81 03-DEC-81
JAMES 03-DEC-81 03-DEC-81
FORD 03-DEC-81 23-JAN-82
MILLER 23-JAN-82 13-JUL-87
SCOTT 13-JUL-87 13-JUL-87
ADAMS 13-JUL-87
14 rows selected.
* LEAD(인수1,인수2,인수3 )
* 인수1 - 입력 칼럼
* 인수2 - 몇 번째 후의 행을 가져올지 결정(DEFAULT 1)
* 인수3 - 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력
* MS SQL 2012
SELECT ENAME, HIREDATE,
LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED"
FROM EMP ;
ENAME HIREDATE NEXTHIRED
---------- ---------- ----------
SMITH 1980-12-17 1981-02-20
ALLEN 1981-02-20 1981-02-22
WARD 1981-02-22 1981-04-02
JONES 1981-04-02 1981-05-01
BLAKE 1981-05-01 1981-06-09
CLARK 1981-06-09 1981-09-08
TURNER 1981-09-08 1981-09-28
MARTIN 1981-09-28 1981-11-17
KING 1981-11-17 1981-12-03
JAMES 1981-12-03 1981-12-03
FORD 1981-12-03 1982-01-23
MILLER 1982-01-23 1987-07-13
ADAMS 1987-07-13 1987-07-13
SCOTT 1987-07-13 NULL
(14개 행이 영향을 받음)
* 칼럼 값에 대한 백분률 -> RATIO_TO_REPORT
* 행의 순서에 대한 (0부터 1사이 값) 백분률 -> PERCENT_RANK
* 1/(파티션)전체 건수로 표현하는 백분률 -> CUME_DIST
* JOB 이 SALESMAN 인 사람들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력
SQL> SELECT ENAME, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (),2) AS R_R,
SUM(SAL) Over ( Order by JOB ) AS "Total"
FROM EMP
WHERE JOB='SALESMAN' ;
ENAME SAL R_R Total
---------- ---------- ---------- ----------
ALLEN 1600 .29 5600 << ( 1600/5600 )
WARD 1250 .22 5600 << ( 1250/5600 )
MARTIN 1250 .22 5600 << ( 1250/5600 )
TURNER 1500 .27 5600 << ( 1500/5600 )
* 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번재 위치쯤에 있는지 0과 1사이의 값으로 출력
SQL> SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R
FROM EMP ;
DEPTNO ENAME SAL P_R
---------- ---------- ---------- ----------
10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1 <<
---------------------------------------------------------------
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1 <<
---------------------------------------------------------------
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1 <<
---------------------------------------------------------------
14 rows selected.
* MS SQL 2012
SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R
FROM EMP ;
DEPTNO ENAME SAL P_R
------ ---------- --------------------------------------- ----------------------
10 KING 5000.00 0
10 CLARK 2450.00 0.5
10 MILLER 1300.00 1
20 FORD 3000.00 0
20 SCOTT 3000.00 0
20 JONES 2975.00 0.5
20 ADAMS 1100.00 0.75
20 SMITH 800.00 1
30 BLAKE 2850.00 0
30 ALLEN 1600.00 0.2
30 TURNER 1500.00 0.4
30 WARD 1250.00 0.6
30 MARTIN 1250.00 0.6
30 JAMES 950.00 1
(14개 행이 영향을 받음)
* 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력
SQL> SELECT DEPTNO, ENAME, SAL,
CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A
FROM EMP ;
DEPTNO ENAME SAL A
---------- ---------- ---------- ----------
10 MILLER 1300 .333333333
10 CLARK 2450 .666666667
10 KING 5000 1
20 SMITH 800 .2
20 ADAMS 1100 .4
20 JONES 2975 .6
20 SCOTT 3000 1
20 FORD 3000 1
30 JAMES 950 .166666667
30 MARTIN 1250 .5
30 WARD 1250 .5
30 TURNER 1500 .666666667
30 ALLEN 1600 .833333333
30 BLAKE 2850 1
14 rows selected.
* MS SQL 2012
SELECT DEPTNO, ENAME, SAL,
CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A
FROM EMP ;
DEPTNO ENAME SAL A
------ ---------- --------------------------------------- ----------------------
10 MILLER 1300.00 0.333333333333333
10 CLARK 2450.00 0.666666666666667
10 KING 5000.00 1
20 SMITH 800.00 0.2
20 ADAMS 1100.00 0.4
20 JONES 2975.00 0.6
20 SCOTT 3000.00 1
20 FORD 3000.00 1
30 JAMES 950.00 0.166666666666667
30 WARD 1250.00 0.5
30 MARTIN 1250.00 0.5
30 TURNER 1500.00 0.666666666666667
30 ALLEN 1600.00 0.833333333333333
30 BLAKE 2850.00 1
(14개 행이 영향을 받음)
SQL> SQL> SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC ) AS QUAR_TILE
FROM EMP ;
ENAME SAL QUAR_TILE
---------- ---------- ----------
KING 5000 1
FORD 3000 1
SCOTT 3000 1
JONES 2975 1
---------------------------------------------------------------
BLAKE 2850 2
CLARK 2450 2
ALLEN 1600 2
TURNER 1500 2
---------------------------------------------------------------
MILLER 1300 3
WARD 1250 3
MARTIN 1250 3
---------------------------------------------------------------
ADAMS 1100 4
JAMES 950 4
SMITH 800 4
---------------------------------------------------------------
14 rows selected.