2.6 윈도우 함수(WINDOW FUNCTION)

1. WINDOW FUNCTION 개요

: PL/SQL, SQL/PL, T-SQL, PRO*C 같은 절차형 프로그램을 작성하거나, INLINE VIEW를 이용해 복잡한 SQL 문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 WINDOW FUNCTION

 

윈도우 함수를 활용하면 복잡한 프로그램을 하나의 SQL 문장으로 쉽게 해결할 수 있다.

 

분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전한 기능

 

▷ WINDOW FUNCTION 종류

1. 그룹 내 순위 관련 함수

: RANK, DENSE_RANK, ROW_NUMBER

 

2. 그룹 내 집계 관련 함수

: SUM, MAX, MIN, AVG, COUNT

SQL Server 의 경우 집계 합수는 뒤에서 설명할 OVER 절 내의 ORDER BY 구문을 지원하지 않는다.

 

3. 그룹 내 행 순서 관련 함수

: FIRST_VALUE, LAST_VALUE, LAG, LEAD

ORACLE에서만 지원되는 함수

 

4. 그룹 내 비율 관련 함수

: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

RATIO_TO_REPORT는 ORACLE에서만 지원

 

5. 선형 분석을 포함한 통계 분석 관련 함수

 

 

▷ WINDOW FUNCTION SYNTAX

- WINDOW 함수에는 OVER 문구가 키워드로 필수 포함됨

 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER

([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])

FROM 테이블 명;

 

: WINDOWING절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.

SQL server에서는 지원하지 않는다.

 

BETWEEN 사용 타입
ROWS I RANGE BETWEEN
 UNBOUNDED PRECEDING I CURRENT ROW I VALUE EXPR PRECEDING/FOLLOWING
AND
 UNBOUNDED FOLLOWING I CURRENT ROW I VALUE_EXPR PRECEDING/FOLLOWING

BETWEEN 미사용 타입
ROWS I RANGE
 UNBOUNDED PRECEDING I CURRENT ROW I VALUE EXPR PRECEDING

 

 

2. 그룹 내 순위 함수

 

가. RANK 함수

: ORDER BY 를 포함한 QUERY 문에서 특정 항목 (칼럼)에 대한 순위를 구하는 함수

 

예제] 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력

 

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; 

 

예제] 앞의 SQL문의 결과는 JOB과 SALARY 기준으로 정렬이 되어 있지 않다. 새로운 SQL에서는 전체 SALARY 순위를 구하는 ALL_RANK 칼럼은 제외하고, 업무별로 SALARY 순서를 구하는 JOB_RANK만 알아보도록 한다

 

SELECT JOB, ENAME, SAL, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK

FROM EMP;

 

 

나. DENSE_RANK 함수

: DENSE_RANK 함수는 RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 차이

 

예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력

 

SELECT JOB, ENAME, SAL,
             RANK() OVER (ORDER BY SAL DESC) RANK,
             DENSE_RANK () OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP 

 

=> FORD와 SCOTT의 다음 순위의 JONES의 경우 RANK는 4등으로 DENSE_RANK는 3등으로 표시

 

다. ROW_NUMBER 함수

: ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.

 

예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력된다.

 

SELECT JOB, ENAME, SAL,
             RANK() OVER (ORDER BY SAL DESC) RANK,
             ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP

 

 

=> ORACLE의 경우 ROWID가 적은 행이 먼저 나온다.

이 부분은 DB별로 다른 결과가 나올 수 있으므로, 만일 동일 값에 대한 순서까지

 

 

3. 일반 집계 함수

 

가. SUM 함수

 

예제] 사원들의 급여와 같은 매니저를 두고 있는 사람들의 SALARY 합을 구한다.

 

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP

 

 

예제] OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력한다.

(SQL server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다)

 

SELECT MGR, ENAME, SAL,
             SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS MGR_SUM
FROM EMP