[SQLD] SQL 활용 2-6. 윈도우 함수

6절. 윈도우 함수

1. WINDOW FUNCTION 개요

: 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수로 분석함수나 순위함수로도 불린다

[다른 함수와 달리 중첩해서 사용 불가하지만, 서브쿼리에서 사용 가능]

 

종류

1. 그룹 내 순위 관련 함수
   1. RANK
   2. DENSE_RANK
   3. ROW_NUMBER
2. 그룹 내 집계 관련 함수
   1. SUM
   2. MAX
   3. MIN
   4. AVG
   5. COUNT
3. 그룹 내 행 순서 관련 함수
   1. FIRST_VALUE
   2. LAST_VALUE
   3. LAG
   4. LEAD
4. 그룹 내 비율 관련 함수
   1. CUME_DIST
   2. PERCENT_RANK
   3. NTILE
   4. RATIO_TO_REPORT
5. 선형 분석을 포함한 통계 분석 관련 함수 [통계 특화 함수]
   • CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

기본구조

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

PARTITION 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

2. 그룹 내 순위 함수

가. RANK 함수

특정 항목(칼럼)에 대한 순위를 구하는 함수이다.

특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위

-> 동일한 값에 대해서는 동일한 순위를 부여

 

(1) 사원 데이터에서 급여가 높은 순서와 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

업무 구분이 없는 ALL_RANK 칼럼에서 동일한 SALARY이므로 같은 순위를 부여
업무를 PARTITION으로 구분한 JOB_RANK의 경우 같은 업무 내 범위에서만 순위를 부여
ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건
-> JOB 별로는 정렬이 되지 않고, ORDER BY SAL DESC 조건으로 정렬

 

(2) 전체 SALARY 순위를 구하는 ALL_RANK 칼럼은 제외하고, 업무별로 SALARY 순서를 구하는 JOB_RANK 출력

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

업무별로 SALARY 순서를 구하는 JOB_RANK만 사용한 경우 파티션의 기준이 된 JOB과 SALARY 별로 정렬

 

나. DENSE_RANK 함수

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

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

동일한 SALARY인 경우 RANK와 DENSE_RANK 칼럼에서 모두 같은 순위를 부여
RANK와 DENSE_RANK의 차이
->동일한 값으로 같은 순위가 3등이라면, 다음 순위에 RANK는 4등, DENSE_RANK는 3등
->동일한 값으로 같은 순위가 9등이라면, 다음 순위에 RANK는 12등, DENSE_RANK는 10등

 

다. ROW_NUMBER 함수

: RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해,

동일한 값이라도 고유한 순위를 부여

(4) 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력

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

동일한 SALARY이므로 RANK는 같은 순위를 부 여했지만, ROW_NUMBER의 경우 동일한 순위를 배제하기 위해 유니크한 순위 (Oracle의 경우 rowid가 낮은행이 먼저)

동일 값에 대한 순서를 위해선 ORDER BY 절을 이용해 추가적인 정렬 기준 정의

 

3. 일반 집계 함수

가. SUM 함수

: 파티션별 윈도우의 합

 

(5) 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP; PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화

 

(6) OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력

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

: 파티션별 윈도우의 최대값

 

(7) 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값 출력

SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX FROM EMP;

 

(8) 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 ;

같은 최대값을 가지는 경우까지 WHERE SAL = IV_MAX_SAL ;으로 의해 두 건 모두 추출

 

다. MIN 함수

: 파티션별 윈도우의 최솟값

 

(9) 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최솟값 출력

SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP;

 

라. AVG 함수

: 원하는 조건에 맞는 데이터에 대한 통계값

 

(10) 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY 출력

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

: 원하는 조건에 맞는 데이터에 대한 통계

 

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

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 : 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시)

파티션이 지정되지 않았으므로 모든 건수를 대상으로 -50 ~ +150 기준에 맞는지 검사
ORDER BY SAL로 정렬이 되어 있으므로 비교 연산이 비교적 쉽다.

 

4. 그룹 내 행 순서 함수

가. FIRST_VALUE 함수

: 파티션별 윈도우에서 가장 먼저 나온 값 출력 [MIN 함수를 활용하여 같은 결과]

 

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

SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH FROM EMP; RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

같은 부서 내에 최고 급여를 받는 사람이 둘 있는 경우 -> 어느 사람이 최고 급여자로 선택될지 모른다.

공동 등수를 인정 하지 않고 처음 나온 행만을 처리 이 경우 세부 항목의 정렬을 원한다면,
별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 절에 칼럼을 추가

 

(13) 같은 값을 가진 FIRST_VALUE를 처리하기 위해 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함수

: 파티션별 윈도우에서 가장 나중에 나온 값 [MAX 함수를 활용하여 같은 결과]

 

(14) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력

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 : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정

LAST_VALUE는 다른 함수와 달리 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리

공동 등수가 있을 경우를 의도적으로 정렬을 원한다면,
-> 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 조건에 칼럼을 추가

 

다. LAG 함수

: 파티션별 윈도우에서 이전 몇 번째 행의 값

 

(15) 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력

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

(16) 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바본인보다 입사일자가 두 명 앞선 사원의 급여를 본인의 급여와 함께 출력하는데 만약 값이 없을 경우 0으로 출력. 

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으로 처리

LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능

라. LEAD 함수

: 파티션별 윈도우에서 이후 몇 번째 행의 값

 

(17) 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사 일자를 함께 출력

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

LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데, 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능

 

5. 그룹 내 비율 함수

가. RATIO_TO_REPORT 함수

: 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 출력 [>0 &<= 1 의 범위]

 

(18) JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력

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

전체 값은 RATIO_TO_REPORT 함수 연산의 분모로 사용된다.
개별 RATIO의 전체 합을 구하 면 1이 되는 것을 확인

 

나. PERCENT_RANK 함수

: 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1 [값이 아닌 행의 순서별 백분율]

 

(19) 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

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

ORDER BY SAL DESC 구문에 의해 급여가 같은 경우 같은 ORDER로 취급

 

다. CUME_DIST 함수

: 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건 수에 대한 누적백분율 [>0 &<= 1 의 범위]

 

(20) 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

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

ORDER BY SAL에 의해 SAL 이 같은 경우 같은 ORDER로 취급

다른 WINDOW 함수의 경우 동일 순서면 앞 행의 함수 결과 값을 따르는데,
CUME_DIST의 경우는 동일 순서면 뒤 행의 함수 결과값을 기준

 

라. NTILE 함수

: 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과

 

(21) 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류

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

NTILE(4)의 의미는 14명의 팀원을 4개 조로 나눈다는 의미
4개의 집합으로 나누면 몫이 3명, 나머지가 2명이 된다.
나머지 두 명은 앞의 조부터 할당 한다. 즉, 4명 + 4명 + 3명 + 3명으로 조를 나누게 된다.