2.2.6 윈도우 함수

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_REPOST

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;

• WINDOW_FUNCTION : 윈도우 함수
• ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.
• WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (sql server 에서는 지원하지 않음)

그룹 내 순위 함수(=그룹 내 WINDOW FUNCTION)

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;

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 함수

• 동일한 값이라도 다른 순위를 부여, 동일한 순위를 하나의 건수로 취급

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

일반 집계 함수

SUM 함수

• 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, HIREDEAT, SAL, 
       MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) 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 함수

• 사원들을 급여 기준으로 정렬하고, 본인 급여보다 50 이하 적거나, 150 이하로 많은 급여를 받는 인원수를 출력하는 예제

SELECT ENAME, SAL,
    COUNT(*) OVER (ORDER BY SAL
        RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
    AS SIM_CNT
FROM EMP;

그룹 내 행 순서 함수

FIRST VALUE 함수

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

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

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

• 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력하는 예제

SELECT  DEPTNO, ENAME, SAL,
        FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
        ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM    EMP ; 

LAST VALUE 함수

• 파티션별 윈도우에서 가장 나중에 나온 값을 구함
• MAX 함수를 활용하여 같은 결과를 얻을 수 있음
• 공동 등수를 인정하지 않고 가장 나중에 나온 행만 처리

SELECT  DEPTNO, ENAME, SAL,
        LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
        ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DEPT_POOR
FROM    EMP ; 

LAG 함수

• 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있음
• 인수를 최대 3개까지 가질 수 있음
• 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력하는 예제

SELECT  ENAME, HIREDATE, SAL,
        LAG(SAL) OVER (ORDER BY HIREDATE) PREV_SAL
FROM    EMP
WHERE   JOB = 'SALESMAN' ;

[실행 결과] 
ENAME   HIREDATE  SAL  PREV_SAL 
------- --------- ---- ------- 
ALLEN   1981-02-20 1600 
WARD    1981-02-22 1250 1600 
TURNER  1981-09-08 1500 1250 
MARTIN  1981-09-28 1250 1500 

4개의 행이 선택되었다.

LEAD 함수

• 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있음
• 인수를 최대 3개까지 가질 수 있음

SELECT  ENAME, HIREDATE,
        LEAD(HIREDATE) OVER (ORDER BY HIREDATE) NEXTHIRED
FROM    EMP ;

그룹 내 비율 함수

RATIO_TO_REPORT 함수

• 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음
• 결과 값은 0보다 크고 1보다 같거나 작음
• 개별 비율의 합을 구하면 1
• SQL Server에서는 지원하지 않음
• EX) 전체 급여에서 본인이 차지하는 비율을 구할 때

SELECT  ENAME, SAL,
        ROUND (RATIO_TO_REPORT(SAL) OVER (), 2) P_R
FROM    EMP
WHERE   JOB = 'SALESMAN' ;

PERCENT_RANK 함수

• 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 나중에 나오는 것을 1로 설정하여 값이 아닌 행의 순서별 백분률을 구함
• 결과 값은 0보다 크고 1보다 같거나 작음
• SQL Server에서는 지원하지 않음- 즉, 구간을 나누어 백분률로 표시하는 작업
• EX) 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력 할 때 

SELECT  ENAME, SAL,
        PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) P_R
FROM    EMP ;

CUME_DIST 함수

• 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함
• 결과 값은 0보다 크고 1보다 같거나 작음
• SQL Server에서는 지원하지 않음
• 다른 WINDOW 함수의 경우 동일 순서면 앞 행의 함수 결과 값을 따르는데, 이는 뒤 행의 함수 결과값을 기준으로 삼음
• 예시 ) 본인의 급여가 누적 순서상 몇 번째 위치 쯤에 있는지 0과 1사이 값으로 출력
• 만일 전체가 3건이라면 0.3333 단위의 간격을 가짐

SELECT  DEPTNO, ENAME, SAL,
        CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) CUME_DIST
FROM    EMP ;

NTILE 함수

• 파티션별 전체 검수를 AGRUMENT 값으로 N 등분한 결과를 구할 수 있다.-
• EX) 급여를 기준으로 4개의 그룹으로 분류
• 14명의 팀원을 4개 조로 나눈다고 했을때 나누면 몫이 3명 나머지가 2명이 되고 나머지 두명은 앞의 조부터 할당한다. 4 4 3 3 명으로 조를 나누게 됨 

SELECT  ENAME, SAL,
        NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM    EMP ;

WINDOWING 절

• 대상이 되는 행의 기준의 범위를 강력하게 지정할 수 있음
• ROWS는 물리적인 결과 행의 수를 뜻함
• RANGE는 논리적인 값에 의한 범위를 나타냄
• 둘 중 하나를 선택해서 사용 가능
• SQL Server에서는 지원하지 않음 

ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING

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

RANGE UNBOUNDED PRECEDING

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

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

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