윈도우 함수 (WINDOW FUNCTION) #

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수이며 WINDOW 함수는 다른 함수와는 달리 중첩해서 사용하지는 못하지만 서브쿼리에서는 사용할 수 있다.

Window Function 종류 #

• 순위함수 : RANK, DENSE_RANK, ROW_NUMBER
• 집계함수 : SUM, MAX, MIN, AVG, COUNT
• 순서함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
• 선형 분석을 포함한 통계 분석 관련 함수

Window Function 문법 #

SELECT WINDOW_FUNCTION (args) OVER (PARTITION BY COLUMNS ORDER BY 절 WINDOWING 절)
FROM TABLE_NAME;

WINDOWING 절 #

BETWEEN 사용 타입 #

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

BETWEEN 미사용 타입 #

ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

설명 #

• ARGS (N) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
• PARTITION BY 칼럼 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
• WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 지정할 수 있다. ROWS는 물리적 결과 행의 수를, RANGE는 논리적 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.

예제 #

SELECT JOB, EMPLOYEE_NAME, SALARY
     , RANK()       OVER (PARTITION BY JOB ORDER BY SALARY DESC)  RANK
     , DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SALARY DESC)  DENSE_RANK
     , ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SALARY DESC)  ROW_NUMBER
FROM   EMPLOYMENT
;
SELECT JOB, EMPLOYEE_NAME, SALARY
     , SUM(SALARY)   OVER (PARTITION BY MANAGER_NO)
     , MAX(SALARY)   OVER (PARTITION BY MANAGER_NO)
     , MIN(SALARY)   OVER (PARTITION BY MANAGER_NO)
     , AVG(SALARY)   OVER (PARTITION BY MANAGER_NO)
     , COUNT(SALARY) OVER (PARTITION BY MANAGER_NO)
FROM   EMPLOYMENT
;    
SELECT DEPTNO, ENAME, SAL
     , FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH 
     , LAST_VALUE(ENAME)  OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR 
FROM   EMP
;
SELECT DEPTNO, ENAME, SAL
     , LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL 
     , LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP 
WHERE  JOB = 'SALESMAN'
;    
SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R 
FROM EMP 
WHERE  JOB = 'SALESMAN'
;
SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R 
FROM   EMP
;
SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST 
FROM   EMP
;
SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE 
FROM   EMP
;

참고문헌 #

• 이화식,『대용량 데이터베이스솔루션 1』, 서울:(주)엔코아, 1996