SQLP 요점정리 #06 : 2과목 SQL - 2장-3 SQL활용 : WINDOW Function

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

2장 SQL 활용 (#3)

WINDOW FUNCTION (윈도우 함수)

• 행과 행간을 비교, 연산하는 함수

종류

1. 순위 관련 : RANK, DENSE_RANK, ROW_NUMBER
2. 집계 관련 : SUM, MAX, MIN, AVG, COUNT
3. 순서 관련 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 비율 관련 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
5. 통계 분석 (순형 분석 포함) : CORP, COVAR_POP, STDDEV 등... (여기서는 다루지 않음)

문법 (syntax)

SELECT 함수명 (인자) OVER
        ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
  FROM 테이블;

• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 분리
• ORDER BY 절 : 순위 지정시 그 순서
• WINDOWING 절 : 함수의 대상을 지정
  • ROWS : 물리적인 행수를 지정
  • RANGE : 논리적인 값에 의한 범위 지정

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

1. 순위 함수

• RANK : 특정 칼럼에 대한 순위를 구함

SELECT JOB, ENAME, SAL,
       RANK() OVER (ORDER BY SAL DESC) ALL_RANK,                  -- SAL의 DESC 순서로 RANK
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK  -- JOB별로 SAL의 DESC 순서로 RANK
  FROM SCOTT.EMP;

한문장에 RANK가 2개 이상일 경우 먼저 나온것을 기준으로 ORDER BY된 결과가 나온다.
JOB_RANK를 먼저 썼을 경우에는 ORDER BY JOB, SAL DESC 와 같은 순서로 결과가 나온다.

• DENSE_RANK : 동일한 순위를 하나의 건수로 계산한다.
• ROW_NUMBER : 동일한 순위에 대해서도 등수를 따로 매긴다. (Oracle의 경우 rowid가 낮은게 먼저 나옴)

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

앞의 결과를 보면

• RANK의 경우 2위가 2개이고 다음이 4위인데,
• DENSE_RANK는 2위가 2개이고 다음이 3위이다.
• ROW_NUMBER는 1 ~ 14로 모든 row의 순위가 다르다.

2. 집계 함수

• SUM : 파티션별 윈도우의 합

SELECT DEPTNO, ENAME, SAL, 
       SUM(SAL) OVER (PARTITION BY DEPTNO) "부서별 연봉합계"
  FROM SCOTT.EMP;

위 예제는 별로 어렵지 않다.
이제 부서별로 SAL 순서로 ORDER BY한 다음에 부서별 연봉합계 대신 부서별 연봉누계로 출력해보겠다.

SELECT DEPTNO, ENAME, SAL, 
       SUM(SAL)  OVER (PARTITION BY DEPTNO ORDER BY SAL RANGE UNBOUNDED PRECEDING) "부서별 연봉누계",
       RANK()    OVER (PARTITION BY DEPTNO ORDER BY SAL) IDX
  FROM SCOTT.EMP;

• RANGE UNBOUNDED PRECEDING : 자신(ROW)을 기준으로 앞쪽(PRECEDING)으로 현재 PARTITION 내의 모든값(UNBOUNDED)

동일한 RANK에 대해서는 누계값도 동일하게 취급하여 두 값을 모두 더하게 된다.

• MIN, MAX : 파티션별 윈도우의 최소, 최대값

SELECT DEPTNO, ENAME, SAL, 
       MIN(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) MIN_DEPT,
       MAX(SAL) OVER (PARTITION BY DEPTNO) MAX_DEPT
  FROM SCOTT.EMP;

• AVG : 파티션별 윈도우의 평균값

SELECT DEPTNO, ENAME, SAL, HIREDATE,
       ROUND(AVG(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AVG
  FROM SCOTT.EMP;

• ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING : 자신(ROW)을 기준으로 앞쪽(PRECEDING) 1개와 뒤쪽(FOLLOWING) 1개의 ROW만을 대상으로 함. 만약 PARTITION 내에 앞에 값이 없으면 뒤에 값과 자신의 2개에 대한 평균만 계산.

• COUNT : 파티션별 개수를 구함

SELECT DEPTNO, ENAME, SAL, 
       COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) COUNT
  FROM SCOTT.EMP;

• RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING : 값으로 앞뒤 100 사이에 있는 것만을 대상으로 함. 즉 자신의 연봉보다 +- 100 사이의 사원수를 측정

3. 순서 함수

• FIRST_VALUE : 파티션내에서 가장 먼저 나온 값 (MIN으로 비슷하게 사용이 가능)
• LAST_VALUE : 파티션애에서 가장 나중에 나온 값 (MAX 로 비슷하게 사용이 가능)

각 부서별 가장 연봉이 많은 사람(RICH)와 가장 연봉이 적은 사람(POOR)를 구해보자.
참고로 다음 SQL은 결과가 이상하게 나온다.

SELECT DEPTNO, ENAME, SAL, 
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL) POOR,
       LAST_VALUE (ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL) RICH
  FROM SCOTT.EMP;

FIRST_VALUE의 경우에는 제대로 나왔는데, LAST_VALUE에 대해서는 뭔가 좀 이상하다.
파티션 내에서 현재(ROW)를 기준으로만 계산이 되었다. LAST_VALUE의 경우에는 범위를 명시적으로 지정해주면 정상적으로 출력된다.

SELECT DEPTNO, ENAME, SAL, 
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL) POOR,
       LAST_VALUE (ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) RICH
  FROM SCOTT.EMP;

• 이 경우에는 SAL 순으로 정렬되었기 때문에 의미적으로 UNBOUNDED PRECEDING 대신에 CURRENT ROW를 적어도 결과는 같다.

• LAG : 이전 몇 번째 행의 값을 가져온다.
• LEAD : 이후 몇 번째 행의 값을 가져온다.

SELECT DEPTNO, ENAME, SAL, 
       LAG (SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) LAG,
       LEAD(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) LEAD
  FROM SCOTT.EMP;

LAG, LEAD 함수는 인자를 3개까지 가질수 있다.

LAG(컬럼, 몇칸 = 1, ISNULL = NULL)

• 첫번째 인자 : 어느 컬럼 값을 출력할지 지정
• 두번째 인자 : 몇칸 앞 (또는 뒤)의 값을 가져올지 지정 (default = 1)
• 세번째 인자 : NULL일 경우 어떻게 출력할지 지정 (default = NULL)

SELECT DEPTNO, ENAME, SAL, 
       LAG (SAL,1,0) OVER (ORDER BY SAL) LAG_1,
       LAG (SAL,2,0) OVER (ORDER BY SAL) LAG_2,
       LEAD(SAL,1,0) OVER (ORDER BY SAL) LEAD_1,
       LEAD(SAL,2,0) OVER (ORDER BY SAL) LEAD_2
  FROM SCOTT.EMP;

4. 비율 함수

• RATIO_TO_REPORT : 파티션내 전체 SUM값 대비 백분율 비율 (0 ~ 1)

전 직원들의 연봉을 전체연봉 대비 얼마나 차지하는지를 구해보자.

SELECT ENAME, SAL, JOB,
       ROUND(RATIO_TO_REPORT(SAL) OVER(),3) AS RATIO
  FROM EMP

• PERCENT_RANK : 파티션내의 순서별 백분율. (처음값 = 0, 마지막값 = 1)
• CUME_DIST : 파티션내의 전체건수에서 자신보다 작거나 같은 건수에 대한 누적백분율

두 함수가 비슷한데 값이 약간 차이가 난다. PERCENT_RANK는 0 부터 시작하므로 파티션내의 개수 - 1 등분하고,
CUME_DIST는 개수만큼 등분한다.

각 부서별 연봉 순위를 비율로 구해본다면

SELECT DEPTNO, ENAME, SAL,
       PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)    AS PERCENT_RANK,
    ROUND(CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC),2) AS CUME_DIST
  FROM EMP

• NTILE : 파티션내의 전체 건수를 N 등분

전체 사원을 연봉 기준으로 4 LEVEL로 급수를 매길경우

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