SQL 심화 2 (그룹함수, 윈도우함수, 순위함수, PIVOT, UNPIVOT절)

그룹함수

데이터를  GROUP BY 하여 데이터를 구하는 함수이다. 집계함수와 총계함수로 나눌 수 있다.

집계 함수	COUNT, SUM, AVG, MAX, MIN 등
소계(총계) 함수	ROLLUP, CUBE, GROUPING SETS 등

 

ROLLUP 소그룹 간의 소계 및 총계를 계산하는 함수

ROLLUP	소그룹 간의 소계 및 총계를 계산하는 함수
ROLLUP (A)	A 로 그룹핑 총합계
ROLLUP (A, B)	A, B로 그룹핑 A 로 그룹핑 총합계
ROLLUP (A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A 로 그룹핑 총합계

 

ROLLUP은 그룹핑 순서에 따라 소계와 총계를 계산하며 중간 단계의 조합은 포함하지 않는다

 

CUBE

소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수 조합할 수 있는 모든 그룹에 대한 소계를 집계한다

CUBE	조합할 수 있는 모든 그룹에 대한 소계를 집계
CUBE (A)	A로 그룹핑 총합계
CUBE (A, B)	A, B로 그룹핑 A로 그룹핑 B로 그룹핑 총합계
CUBE (A, B, C)	A, B, C로 그룹핑 A, B로 그룹핑 A, C로 그룹핑 B, C로 그룹핑 A로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

 

CUBE는 모든 가능한 그룹 조합을 포함하여 더 세부적인 결과를 제공한다

 

GROUPING SETS 특정 항목에 대한 소계를 계산하는 함수. 인자값으로 ROLLUP 이나 CUBE 를 사용할 수 있다

GROUPING SETS (A, B)	A로 그룹핑 B로 그룹핑
GROUPING SETS (A, B, ())	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, ROLLUP(B))	A로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, ROLLUP(B, C))	A로 그룹핑 B, C로 그룹핑 B로 그룹핑 총합계
GROUPING SETS (A, B, ROLLUP(C))	A 로 그룹핑 B로 그룹핑 C로 그룹핑 총합계

 

윈도우 함수

OVER 키워드와 함께 사용된다.

역할에 따라 나눌수 있다.

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MAX, MIN, AVG, COUNT
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

 

순위 함수

순위함수	결과	설명
RANK	1, 2, 2, 4, 5, 5, 7	순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너 띈다
DENSE_RANK	1, 2, 2, 3, 4, 4, 5	같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다
ROW_NUMBER	1, 2, 3, 4, 5, 6, 7	순위를 매길때 같은 값이라도 각기 다른 순위를 부여한다

 

윈도우 함수 사용 옵션

WINDOWING 절을 이용해 데이터의 범위를 지정할 수 있다.

RANGE ROWS	BETWEEN UNBOUNDED PRECEDING AND n PRECEDING
	BETWEEN UNBOUNDED AND CURRENT ROW
	BETWEEN UNBOUNDED PRECEDING AND n FOLLOWING
	BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	BETWEEN n PRECEDING AND n PRECEDING
	BETWEEN n PRECEDING AND CURRENT ROW
	BETWEEN n PRECEDING AND n FOLLOWING
	BETWEEN n PRECEDING AND UNBOUNDED FOLLOWING
	BETWEEN CURRENT ROW AND n FOLLOWING
	BETWEEN CURRENT ROW AND n FOLLOWING
	UNBOUNDED PRECEDING (default 값임)
	n PRECEDING
	CURRENT ROW

 

범위	역할
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLOWING	아래쪽 끝 행
CURRENT ROW	현재 행
n PRECEDING	현재 행에서 위로 n 만큼 이동
n FOLLOWING	현재 행에서 아래로 n 만큼 이동

기준	의미
ROWS	행 자체가 기준이 된다
RANGE	행이 가지고 있는 데이터 값이 기준이 된다

 

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

위쪽 끝 행 부터 현재 행까지

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW

현재 행이 가지고 있는 값보다 10 만큼 적은 행부터 현재 행까지

ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

현재 행부터 끝까지

ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING

현재 행부터 아래로 5만큼 이동한 행까지

 

행 순서 함수

FIRST_VALUE	파티션별 가장 선두에 위치한 데이터를 구하는 함수
LAST_VALUE	파티션별 가장 끝에 위치한 데이터를 구하는 함수
LAG	파티션별로 특정 수만큼 앞선 데이터를 구하는 함수
LEAD	파티션별로 특정 수만큼 뒤에 있는 데이터를 구하는 함수

 

비율 함수

비율함수
RATIO_TO_REPORT	파티션별 합계에서 차지하는 비율을 구하는 함수
PERCENT_RANK	해당 파티션의 맨 위 맨 끝 행을 0, 맨 아래 행을 1로 놓고 현재 행이 위치하는 백분위 순위값을 구하는 함수
CUME_DIST	해당 파티션에서의 누적 백분율을 구하는 함수
NTILE	주어진 수만큼 행들을 n 등분한 후 현재 행에 해당하는 등급을 구하는 함수

 

Top-N 쿼리

ROWNUM	수도컬럼을 생성하는 함수

 

셀프조인

LEVEL	현재의 DEPTH 를 반환. 루트노드는 1이 된다
SYS_CONNECT_BY_PATH (컬럼, 구분자)	루트노드부터 현재 노드까지의 경로를 출력해주는 함수
START WITH	경로가 되는 루트 노드를 생성해주는 절
CONNECT BY	루트로부터 자식노드를 생성해주는 절이다
PRIOR	바로 앞에 있는 부모 노드의 값을 반환

 

PIVOT 절

PIVOT 절의 구성요소	설명
집계 함수	결과 데이터에 표시할 집계 데이터를 정의
FOR 절	PIVOT 할 컬럼을 지정
IN 절	PIVOT 할 컬럼 값을 지정

 

UNPIVOT 절

UNPIVOT 절의 구성 요소	설명
UNPIVOT 컬럼	UNPIVOT 된 값이 들어갈 컬럼을 지정
FOR 절	UNPIVOT 된 값에 대한 설명이 들어갈 컬럼을 지정
IN 절	FOR 절에서 생성한 컬럼에 표시될 데이터 값을 지정