[SQLD] 26. 윈도우 함수(WINDOW FUNCTION)

제 6절 윈도우 함수(WINDOW FUNCTION)

1. WINDOW FUNCTION 개요

  기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면, 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어려운 일이었다. 하지만, 윈도우 함수를 이용한다면 행과 행간의 관계를 쉽게 정의할 수 있게 된다.

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

  다른 함수들처럼 중첩해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.

- 그룹 내 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER

- 그룹 내 집계 관련 함수 : SUM, MAX, MIN, AVG, COUNT

- 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

- 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPOST

- 통계 분석 관련 함수 : ... 

WINDOW FUNCTION SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER

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

FROM 테이블 명;

2. 그룹 내 순위 함수 

가. RANK 함수 

- ORDER BY 절을 포함한 QUERY 구문에서 특정항목에 대한 순위를 구하는 함수

- 동일한 데이터 값이 나올 경우 그 데이터에 대해 같은 순위를 부여 

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;

ORDER BY 절의 충돌로 인해 ORDER BY SAL DESC 구문에 대해서만 정렬 수행함

나. DENSE_RANK 함수

- 동일한 순위를 하나의 건수로 취급, 그러나 동일한 값에 대해서는 동일한 순위를 부여

 

SELECT JOB, ENAME, SAL,

RANK()    OVER (ORDER BY SAL DESC) RANK
DENSE_RANK()    OVER (ORDER BY SAL DESC) DENSE_RANK

FROM EMP;

다. ROW_NUMBER 함수

- RANK, DENSE_RANK 함수는 동일한 값에 대해 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여

SELECT JOB, ENAME, SAL,

RANK()    OVER (ORDER BY SAL DESC) RANK,

ROW_NUBMER OVER(ORDER BY SAL DESC) ROW_NUMBER

FROM EMP;

RANK            : 동일한 값에 대해 같은 순위 부여

DENSE_RANK     :동일한 값에 대해 같은 순위 부여, 동일한 순위를 하나의 건수로 취급

ROW_NUMBER   :동일한 값에 대해 다른 순위 부여, 동일한 순위를 하나의 건수로 취급

3. 일반 집계 함수 

가. SUM 함수

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

FROM EMP;

나. MAX 함수

SELECT MGR, ENAME, SAL, MAX(SAL), OVER (PARTITION BY MGR) AS MGR_MAX

FROM EMP;

인라인뷰를 이용한 파티션 별 최대값 가진 행 추출(서브쿼리 칼럼을 메인쿼리가 사용가능)

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;

다. MIN 함수

SELECT MGR, ENAME, SAL, MIN(SAL), OVER (PARTITION BY MGR) AS MGR_MIN

FROM EMP;

라. AVG 함수

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;

마. COUNT 함수

SELECT ENAME, SAL,

COUNT(*) OVER (ORDER BY SAL

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)

AS SIM_CNT

FROM EMP;

4. 그룹 내 행 순서 함수

가. FIRST_VALUE 함수

- 파티션별 윈도우에서 가장 먼저 나온 값을 구함

- SQL Server는 지원하지 않는 기능

- MIN 함수를 활용하여 같은 결과를 얻을 수 있음

- 공동 등수를 인정하지 않고 처음 나온 행만 처리

나. LAST_VALUE 함수

- 파티션별 윈도우에서 가장 나중에 나온 값을 구함

- SQL Server는 지원하지 않는 기능

- MAX 함수를 활용하여 같은 결과를 얻을 수 있음

- 공동 등수를 인정하지 않고 가장 나중에 나온 행만 처리

다. LAG 함수

- 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음

- SQL Server에서는 지원하지 않는 함수

- 인수를 최대 3개까지 가질 수 있음

- 두번째 인수는 몇 번째 앞의 행을 가져올지 결정

- 세번째 인자는 가져올 데이터가 없는 경우 NULL 값이 들어오는데, 이 값을 다른 값으로 바꾸어 줄 수 있기에 NVL이나 ISNULL 기능과 같음 

라. LEAD 함수

- 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음

- SQL Server에서는 지원하지 않는 함수

- 인수를 최대 3개까지 가질 수 있음

- 두번째 인자는 몇 번째 후의 행을 가져올지 결정

- 세번째 인자는 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데, 이 값을 다른 값으로 바꾸어 줄 수 있기에 NVL이나 ISNULL 기능과 같음

5. 그룹 내 비율 함수

가. RATIO_TO_REPORT 함수

- 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음

- 결과 값은 0보다 크고 1보다 같거나 작음

- 개별 비율의 합을 구하면 1

- SQL Server에서는 지원하지 않음 

- 예시 ) 전체 급여에서 본인이 차지하는 비율을 구할 때

나. PERCENT_RANK 함수

- 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 나중에 나오는 것을 1로 설정하여 값이 아닌 행의 순서별 백분률을 구함

- 결과 값은 0보다 크고 1보다 같거나 작음

- SQL Server에서는 지원하지 않음

- 즉, 구간을 나누어 백분률로 표시하는 작엄

- 예시 ) 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력 할 때 

다. CUME_DIST 함수

- 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함

- 결과 값은 0보다 크고 1보다 같거나 작음

- SQL Server에서는 지원하지 않음

- 다른 WINDOW 함수의 경우 동일 순서면 앞 행의 함수 결과 값을 따르는데, 이는 뒤 행의 함수 결과값을 기준으로 삼음

- 예시 ) 본인의 급여가 누적 순서상 몇 번째 위치 쯤에 있는지 0과 1사이 값으로 출력

- 만일 전체가 3건이라면 0.3333 단위의 간격을 가짐

라. NTILE 함수

- 파티션별 전체 검수를 AGRUMENT 값으로 N 등분한 결과를 구할 수 있다.

- 예시 ) 급여를 기준으로 4개의 그룹으로 분류

- 14명의 팀원을 4개 조로 나눈다고 했을때 나누면 몫이 3명 나머지가 2명이 되고 나머지 두명은 앞의 조부터 할당한다. 4 4 3 3 명으로 조를 나누게 됨 

* WINDOWING 절

- 대상이 되는 행의 기준의 범위를 강력하게 지정할 수 있음

- ROWS는 물리적인 결과 행의 수를 뜻함

- RANGE는 논리적인 값에 의한 범위를 나타냄

- 둘 중 하나를 선택해서 사용 가능

- SQL Server에서는 지원하지 않음 

ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING

: 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정

RANGE UNBOUNDED PRECEDING

: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정

BETWEEN 사용 타입

- ROWS

- RANGE BETWEEN UNBOUNDED PRECEDING

- CURRENT ROW

- VALUE_EXPR PRECEDING / FOLLOWING AND UNBOUNDED FOLLOWING 

BETWEEN 미사용 타입

- ROWS

- RANGE UNBOUNDED PRECEDING

- CURRENT ROW

- VALUE_EXPR PRECEDING