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

윈도우 함수 (WINDOW FUNCTION)

- 서로 다른 행의 비교나 연산을 위해 만든 함수

- GROUP BY 를 쓰지 않고 그룹 연산 가능

- LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK

 

# 문법

SELECT 윈도우함수([대상]) OVER 
([PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC|DESC]
[ROWS|RANGE BETWEEN A AND B]);

 

- PARTITON BY 절 :

출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼

 

- ORDER BY 절 :

RANK의 경우 필수(정렬 컬럼 및 정렬 순서에 따라 순위 변화), SUM,AVG,MIN,MAX,COUNT 등은 누적값 출력 시 사용

 

- ROWS|RANGE BETWEEN A AND B : 

연산 범위 결정, ORDER BY 절 필수

 

1. ROWS, RANGE 차이

- ROWS : 값이 같더라도 각 행씩 연산

- RANGE : 값은 값의 경우 하나의 RANGE 로 묶어서 동시 연산(DEFAULT)

 

2. BETWEEN A AND B

A) 시작점 정의

- CURRENT ROW : 현재행부터

- UNBOUNDED PRECEDING : 처음부터(DEFAULT)

- N PRECEDING : N 이전부터

 

B) 마지막 시점 정의

- CURRENT ROW : 현재행까지(DEFAULT)

- UNBOUNDED FOLLOWING : 마지막까지

- N FOLLOWING : N 이후까지

 

※ PARTITON BY, ORDER BY, ROWS... 절 전달 순서 중요(ORDER BY 를 PARTITION BY 전에 사용 불가)

---

윈도우 함수 종류

구분	함수	비고
순위 함수	RANK, DENSE_RANK, ROW_NUMBER
일반 집계 함수	SUM, MAX, MIN, AVG, COUNT	SQL서버에서는 OVER 절 내에서 ORDER BY 지원하지 않음
그룹 내 행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD	SQL서버에서는 지원하지 않음
그룹 내 비율 함수	RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE	NTILE 제외 SQL서버에서는 지원하지 않음

 

# 순위 함수

- RANK ( ) : 동일한 값에 동일한 순위를 부여하지만, 다음 순위는 중복된 값의 수만큼 건너 뜀

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

결과:
JOB	ENAME	SAL	JOB_RANK
ANALYST	FORD	3000	1
ANALYST	SCOTT	3000	1
CLERK	MILLER	1300	1
CLERK	ADAMS	1300	1
CLERK	JAMES	950	3
CLERK	SMITH	800	4

 

 

- DENSE_RANK ( ) : 동일한 값에 동일한 순위를 부여하지만, 다음 순위는 중복된 값의 수와 관계없이 순차적으로 증가

SELECT JOB, ENAME, SAL, 
       DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_DENSE_RANK 
FROM EMP;

결과:
JOB	ENAME	SAL	JOB_DENSE_RANK
ANALYST	FORD	3000	1
ANALYST	SCOTT	3000	1
CLERK	MILLER	1300	1
CLERK	ADAMS	1300	1
CLERK	JAMES	950	2
CLERK	SMITH	800	3

 

 

- ROW_NUMBER ( ) : 각 행에 고유한 순번을 부여, 동일한 순번이 존재하지 않

SELECT JOB, ENAME, SAL, 
       ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_ROW_NUM 
FROM EMP;

결과:
JOB	ENAME	SAL	JOB_ROW_NUM
ANALYST	FORD	3000	1
ANALYST	SCOTT	3000	2
CLERK	MILLER	1300	1
CLERK	ADAMS	1300	2
CLERK	JAMES	950	3
CLERK	SMITH	800	4

# 일반 집계 함수

- SUM : 합계

- MIN : 최소값

- MAX : 최대값

- AVG : 평균

- COUNT : 조건에 해당하는 컬럼 숫자 출력

 

# 그룹 내 행 순서 함수

- FIRST_VALUE ( ) : 윈도우 또는 그룹 내에서 첫 번째 값을 반환 (공동 등수를 인정하지 않고 MIN 함수를 쓰는 것과 결과가 동일)

SELECT JOB, ENAME, SAL, 
       FIRST_VALUE(SAL) OVER (PARTITION BY JOB ORDER BY SAL) AS FIRST_SAL 
FROM EMP;

결과 :
JOB	ENAME	SAL	FIRST_SAL
ANALYST	SCOTT	3000	3000
ANALYST	FORD	3000	3000
CLERK	SMITH	800	800
CLERK	JAMES	950	800
CLERK	ADAMS	1300	800
CLERK	MILLER	1300	800

 

 

- LAST_VALUE ( ) : 윈도우 또는 그룹 내에서 마지막 값을 반환 (공동 등수를 인정하지 않고 MAX 함수를 쓰는 것과 결과가 동일)

SELECT JOB, ENAME, SAL, 
       LAST_VALUE(SAL) OVER (PARTITION BY JOB ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_SAL 
FROM EMP;

결과 :
JOB	ENAME	SAL	LAST_SAL
ANALYST	SCOTT	3000	3000
ANALYST	FORD	3000	3000
CLERK	SMITH	800	1300
CLERK	JAMES	950	1300
CLERK	ADAMS	1300	1300
CLERK	MILLER	1300	1300

 

 

- LAG ( ) : 현재 행의 이전 행 값을 반환. 기본적으로 LAG(column, offset, default) 형식

- column : 참조할 열

- offset : 몇 행 이전의 값을 참조할지 지정 (기본값은 1)

- default : 참조할 행이 없을 때 반환할 기본값 (기본값은 NULL)

SELECT JOB, ENAME, SAL, 
       LAG(SAL, 1, 0) OVER (PARTITION BY JOB ORDER BY SAL) AS PREV_SAL 
FROM EMP;

결과 :
JOB	ENAME	SAL	PREV_SAL
ANALYST	SCOTT	3000	0
ANALYST	FORD	3000	3000
CLERK	SMITH	800	0
CLERK	JAMES	950	800
CLERK	ADAMS	1300	950
CLERK	MILLER	1300	1300

 

 

- LEAD ( ) : 현재 행의 다음 행 값을 반환. LAG ( ) 와 동일한 형식

SELECT JOB, ENAME, SAL, 
       LEAD(SAL, 1, 0) OVER (PARTITION BY JOB ORDER BY SAL) AS NEXT_SAL 
FROM EMP;

결과 :
JOB	ENAME	SAL	NEXT_SAL
ANALYST	SCOTT	3000	3000
ANALYST	FORD	3000	0
CLERK	SMITH	800	950
CLERK	JAMES	950	1300
CLERK	ADAMS	1300	1300
CLERK	MILLER	1300	0

 

# 그룹 내 비율 함수

- RATIO_TO_REPORT ( ) : 그룹 내에서 각 행의 값이 전체 합에서 차지하는 비율을 반환

SELECT JOB, ENAME, SAL,
       RATIO_TO_REPORT(SAL) OVER (PARTITION BY JOB) AS SAL_RATIO
FROM EMP;

결과 :
JOB	ENAME	SAL	SAL_RATIO
ANALYST	FORD	3000	0.50
ANALYST	SCOTT	3000	0.50
CLERK	MILLER	1300	0.325
CLERK	ADAMS	1300	0.325
CLERK	JAMES	950	0.2375
CLERK	SMITH	800	0.20

 

 

- PERCENT_RANK ( ) : 현재 행의 백분위 순위를 반환 (백분위 순위는 (순위 - 1) / (전체 행 수  - 1)로 계산됨)

SELECT JOB, ENAME, SAL,
       PERCENT_RANK() OVER (PARTITION BY JOB ORDER BY SAL) AS PCT_RANK
FROM EMP;

결과:
JOB	ENAME	SAL	PCT_RANK
ANALYST	FORD	3000	0.0
ANALYST	SCOTT	3000	0.0
CLERK	SMITH	800	0.0
CLERK	JAMES	950	0.25
CLERK	ADAMS	1300	0.75
CLERK	MILLER	1300	0.75

 

 

- CUME_DIST ( ) : 현재 행의 누적 백분위 비율을 반환 (누적 백분위 비율은 현재 행을 포함하여 이전의 값들이 전체 값에서 차지하는 비율을 나타냄)

SELECT JOB, ENAME, SAL,
       CUME_DIST() OVER (PARTITION BY JOB ORDER BY SAL) AS CUM_DIST
FROM EMP;

결과:
JOB	ENAME	SAL	CUM_DIST
ANALYST	FORD	3000	1.0
ANALYST	SCOTT	3000	1.0
CLERK	SMITH	800	0.25
CLERK	JAMES	950	0.50
CLERK	ADAMS	1300	1.0
CLERK	MILLER	1300	1.0

 

 

- NTILE ( ) : 결과 집합을 지정된 개수의 그룹으로 나눔 (NTILE(n)은 결과 집합을 n개의 그룹으로 나눔)

SELECT JOB, ENAME, SAL, 
       NTILE(2) OVER (PARTITION BY JOB ORDER BY SAL DESC) AS NTILE_RANK
FROM EMP;

결과:
JOB	ENAME	SAL	NTILE_RANK
ANALYST	FORD	3000	1
ANALYST	SCOTT	3000	2
CLERK	MILLER	1300	1
CLERK	ADAMS	1300	2
CLERK	JAMES	950	1
CLERK	SMITH	800	2
MANAGER	JONES	2975	1
MANAGER	BLAKE	2850	1
MANAGER	CLARK	2450	2

# 여기서 NTILE(2)는 각 JOB 그룹을 급여를 기준으로 2개의 그룹으로 나눕니다. 
# 같은 급여를 가진 행은 같은 그룹에 속할 수 있습니다.