[SQLD] 윈도우 함수 : SQL 기본 및 활용 / SQL 활용

SQLD 시험 내용
(a) 데이터 모델링의 이해 (출제 비중 20% : 10문제, 20점)
- 데이터 모델링의 이해 : 데이터모델의 이해 / 엔터티 / 속성 / 관계 / 식별자
- 데이터 모델과 성능 : 정규화와 성능 / 반정규화와 성능 / 대용량 데이터에 따른 성능 / DB 구조와 성능 / 분산 DB 데이터에 따른 성능
(b) SQL 기본 및 활용 (출제 비중 80% : 40문제, 80점)
- SQL 기본 : 정보 요구 사항 / DDL / DML / TCL / WHERE / FUNCTION / GROUP BY, HAVING / ORDER BY / JOIN
- SQL 활용 : 표준 조인 / 집합 연산자 / 계층형 질의 / 서브쿼리 / 그룹함수 / 윈도우 함수 / DCL / 절차형 SQL
- SQL 최적화 기본 원리 : 옵티마이저와 실행 계획 / 인덱스 기본 / 조인 수행 원리

---

1. 윈도우 함수

분석 함수(Analytic Function) = 윈도우 함수 (Window Function) : 

- 개별 행을 유지한 채 집계 값을 계산할 수 있는 함수이다. 원본 값과 집계 값을 함께 분석할 수 있다. 

- 분석 함수 실행 시 대상이 되는 행의 범위를 윈도우라고 하며, analytic_clause에 의해 각 행 별로 윈도우가 정의된다.

analytic_function ( ) OVER ( analytic_clause)     *OVER은 필수

analytic_clause : [PARTITON BY절] [ORDER BY 절 [WINDOWING절]]

- 모든 join, where절, group by절, having절의 수행은 분석 함수 실행 전에 완료된다. 따라서 분석함수는 select절, order by 절 내에서만 사용할 수 있다. 

---

2. 윈도우 함수 종류

- 쿼리 파티션 : PARTITION BY 절을 사용해 GROUP BY 절과 유사하게 논리적인 행의 그룹을 생성

SELECT a.empno, a.ename, SUM(a.sal) OVER (PARTITION BY a.deptno) AS SUM_SAL
FROM emp a;

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100	200	deptno = 1인 파티션
2	banana	1	100	200	deptno = 1인 파티션
3	candy	2	100	300	deptno = 2인 파티션
4	desk	2	200	300	deptno = 2인 파티션
5	ears	3	400	400	deptno = 3인 파티션

* 집계 함수 (GROUP BY)와의 차이점 :

집계 함수는 각 행 그룹 별로 단일 행을 리턴하기 때문에 데이터 집합이 변경된다. (집계 기준 컬럼과 집계 값만 조회할 수 있다.)

 

분석 함수는 물리적으로 행을 합치지 않고, 눈에 보이지 않는 윈도우를 정해 값을 계산하며 행은 그대로고 집계 값이 반복되어 보인다. (개별 컬럼과 집계 값을 동시에 조회할 수 있다.)

 

DEPTNO	SUM_SAL
1	200
2	300
3	400

 

- 분석 함수의 활용 (누적 집계) : 파티션 내에서 ORDER BY 절에 기술한 순서대로 현재 행까지의 값을 누적 집계 할 수 있다.

* PARTITON BY 절이 생략되면 전체 행을 대상으로 누적 집계 값 계산

SELECT a.empno, a.ename, a.deptno, a.sal, 
SUM(a.sal) OVER(PARTITION BY a.deptno ORDER BY a.sal) AS SUM_SAL
FROM emp a;

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100	200	deptno = 1인 파티션
2	banana	1	200	300	deptno = 1인 파티션
3	candy	1	300	600	deptno = 1인 파티션
4	desk	2	400	400	deptno = 2인 파티션
5	ears	2	500	900	deptno = 2인 파티션

 

- WINDOWING 절 : 각 행마다 분석 함수의 실행 대상이 되는 행의 범위(윈도우)를 세밀하게 지정한다. 물리적인 행의 범위를 지정하거나 논리적인 값의 범위를 지정할 수 있다. 

* analytic_clause 내 order by 절 컬럼 또는 표현식이 윈도우의 기준이 된다.

ROWS/RANGE BETWEEN start_point and end_point

start_point & end_point	ROWS (시작 행 / 종료 행)	RANGE (값의 범위)
UNBOUNDED PRECEDING	파티션의 첫 번재 행의 위치	파티션의 첫 번째 행의 값
N PRECEDING	현재 행을 기준으로 이전 N번째 행의 위치	현재 행의 값 - N
CUREENT ROW	현재 행의 위치	현재 행의 값
N FOLLOWING	현재 행을 기준으로 이후 N번째 행의 위치	현재 행의 값 + N
UNBOUNDED FOLLOWING	파티션의 마지막 행의 위치	파티션의 마지막 행의 값

SUM(a.sal) OVER 
(PARTITION BY a.deptno ORDER BY a.sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_AL

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100		UNBOUNDED PRECEDING
2	banana	1	200
3	candy	1	300	100+200+300=600	CURRENT ROW
4	desk	1	400

SUM(a.sal) OVER 
(PARTITION BY a.deptno ORDER BY a.sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS SUM_AL

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100
2	banana	1	200		이전 1번째 행(1 PRECEDING)
3	candy	1	300	200+300+400 = 900	현재 행
4	desk	1	400		이후 1번째 행(1 FOLLOWING)

SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100		UNBOUNDED PRECEDING = 100
2	banana	1	200
3	candy	1	300		CURRENT ROW = 300
4	desk	1	400

RANGE 값의 범위 = 100~300

WINDOW (deptno = 1) 안에서 100~300 에 해당하는 SAL들의 합 (100 + 200 + 300 = 600)

SUM(a.sal) OVER (PARTITION BY a.deptno ORDER BY a.sal
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)

EMPNO	ENAME	DEPTNO	SAL	SUM_SAL
1	apple	1	100
2	banana	1	200
3	candy	1	300		CURRENT ROW
4	desk	1	400

CURRENT ROW 's SAL = 300  / 1 PRECEDING = 300 - 1 = 299 / 1 FOLLOWING = 300 + 1 = 301

RANGE = 299~301

같은 PARTITION인 deptno 1 중에서 SAL 값이 299~301에 해당하는 것의 합 = 300

---