제 4-6절 윈도우 함수(WINDOW 함수)

1. WINDOW FUNTION

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

분석함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)으로도 불림

중첩해서는 사용이 불가능하나 서브쿼리에서 사용 가능

 

가. WINDOE FUNTION의 종류

1. 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER 대부분의 DBMS에서 지원
2. 집계 관련 함수 : SUM, MAX, MIN, AVG, COUNT 대부분의 DBMS에서 사용하나 SQL SERVER의 경우 OVER절 내의 ORDER BY 구문 지원하지 않음
3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE에서만 지원되는 함수
4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT, 1,2번은 전부, 3번은 표준에는 없지만 지원, 4번은 ORACLE에서만 지원
5. 선형분석 포함하는 통계 함수

 

나. SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS)

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

FROM 테이블 명;

- WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.

- ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.

- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

- ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.

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

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

다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

 

1. BETWEEN 사용 타입

ROWS | RANGE BETWEEN

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

AND

UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

 

2. BETWEEN 미사용 타입

ROWS | RANGE

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

 

 

2. 그룹 내 순위 함수

 

가. RANK 함수

특정 항목에 대한 순위를 구하는 함수, 특정 범위내에서 순위를 구할수도 있음, 동일값에 대해서는 동일한 순서 부여

예제) RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK : 전체 중에 연봉 순위

 RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK : 그룹별로 나눈 후 연봉 순위

 

나. DENSE_RANK 함수

동일한 건수를 하나의 건수로 취급

동점이 있을 시 같은 등수 예를 들어 순위는 1, 2, 2, 3, 4, 5, 5, 6

 

다. ROW_NUMBER 함수

동일한 값이라도 고유의 순서를 부여함 예를 들어 순위는 1, 2, 3, 4, 5, 6, 7, 8 로 같은 값이여도 유니크한 순위를 정함

ORACLE 경우는 ROWID가 적은 행부터 나옴

 

3. 일반 집계 함수

 

가. SUM 함수

누적값 구할 수 있음

예제) SELECT    MGR, ENAME, SAL,

SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM

FROM EMP;

 

RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

 

나. MAX 함수

INLINE VIEW로 파티션별 최대값을 가진 행 추출 가능

예제) 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, HIREDATE, SAL,

              MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN

FROM EMP;

 

라. AVG 함수

파티션별 ROWS 윈도우를 통해 평균값 구할 수 있음

예제) 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;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

(ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

 

마. COUNT 함수

예제) SELECT ENAME, SAL,

     COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT

FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다.

(RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)

 

4. 그룹 내 행 순서 함수

 

가. FIRST_VALUE 함수

파티션별 윈도우에 가장 먼저 나온 값, SQL SERVER 미지원(MIN 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가

예제) SELECT DEPTNO, ENAME, SAL,

     FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP

 FROM EMP;

 

나. LAST_VALUE 함수

파티션별 윈도우에 가장 나중에 나온 값, SQL SERVER 미지원(MAX 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가

예제) 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;

 

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

 

다. LAG 함수

파티션 별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE, SAL,

     LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL

 FROM EMP

 WHERE JOB = 'SALESMAN'

 

LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.

 

[실행 결과] ENAME  HIREDATE   SAL   PREV_SAL

 ---------  ------------- -------- ----------

ALLEN    1981-02-20 1600              0

WARD    1981-02-22 1250              0

TURNER 1981-09-08 1500        1600

MARTIN 1981-09-28 1250        1250

 

라. LEAD 함수

파티션 별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE,

     LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"

 FROM EMP;

 

[실행 결과] ENAME HIREDATE NEXTHIRED

----------- ------------ ---------------

ALLEN    1981-02-20 1981-02-22

WARD    1981-02-22 1981-04-02

TURNER 1981-09-08 1981-09-28

MARTIN 1981-09-28

 

5. 그룹 내 비율 함수

 

가. RATIO_TO_REPORT 함수

파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함

결과 값은 > 0 & <= 1이고, 합계는 1, SQL SERVER 미지원

예제 ) ELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R

 FROM EMP

 WHERE JOB = 'SALESMAN';

전체 합 중에 로우 별 연봉이 차지하는 비율이 나오게 됨, 개별 RATIO의 전체 합은 1

 

나. PERCENT_RANK 함수

윈도우에 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행 순서별 백분율을 구함

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R

 FROM EMP;

3개의 로우가 된다면 0, 0.5, 1 순서로 되고, 5개의 로우라면 0, 0.25, 0.5, 0.75, 1로 추출됨 ORDER BY역할

 

다. CUME_DIST 함수

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

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST

 FROM EMP;

5개의 로우가 선택되면 0.2, 0.4, 0.6, 0.8, 1의 값이 추출 ORDER BY역할

동일 순서면 뒤의 행의 함수 결과 값을 따름 예를 들면, 0.4, 0.4, 0.6, 0.8, 1

 

라. NTILE 함수

파티션별 전체 건수를 ARGUMENT 값으로 N 등분 한 결과, 조를 나누는 것과 같음

예제) SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE

 FROM EMP;

4개의 파티션으로 나뉘면, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4 등으로 로우가 추출되어서 파티션별 조를 정할 수 있음