13. 윈도우 함수 (SQLD 자격증 공부)

윈도우 함수 (Window Function) 

• 순위, 합계, 평균, 행 위치 등을 조작 가능
• 행과 행 간의 관계를 정의하기 위해 제공되는 함수

-- 사용 예시
SELECT WINDOW FUNCTION(ARGUMENTS)
	OVER(PARTITION BY 칼럼 ORDER BY WINDOWING절)
FROM 테이블;

 

 ▷ 함수 구조

1. ARGUMENTS(인수) : 함수에 따라 0~N개 인수 설정
2. PARTITION BY : 기준에 의해 소그룹으로 나눔
3. ORDER BY : 정렬
4. WINDOWING ROWS는 물리적 결과의 행 수, RANGE는 논리적 값에 의한 범위

 ▷ WINDOWING

• ROWS : 물리적 단위로 행의 집합
• RANGE : 논리적 주소에 의해 행 집합
• BETWEEN ~ AND : 시작과 끝의 위치
• UNBOUNDED PRECEDING : 시작 위치가 첫 번째 행
• UNBOUNDED FOLLOWING : 마지막 위치가 마지막 행
• CURRENT ROW : 시작위치가 현재 행

-- WINDWING 사용 예시
SELECT ENAME, SAL
	SUM(SAL) OVER(ORDER BY SAL 
    		ROWS BETWEEN UNBOUNDED PRECENDING
           	AND UNBOUNDED FOLLOWING) AS T_SAL
FROM EMP;

위와 같이 SQL문을 실행시 T_SAL의 경우 SAL의 전체 합계( 처음부터 끝까지의 합계)를 조회한다.

 

 

 ▷ 순위 함수(Rank Function)

• RANK : 동일한 순위는 동일한 값이 부여 / EX:) 1, 2, 2, 4 ... 
• DENSE_RANK : 동일한 순위를 하나의 건수로 계산 / EX:) 1, 2, 2, 3  .....
• ROW_NUMBER : 고유의 순위를 부여 / EX:) 1, 2, 3, 4, 5....

-- 순위함수 사용예시
SELECT ENAME, SAL
	RANK() OVER(ORDER BY SAL DESC) AS ALL_R
FROM EMP;

 ▷ 집계 함수(Aggregate Function)

• SUM : 파티션 별로 합계
• AVG : 파티션 별 평균
• COUNT : 파티션 별 행 수 계산
• MAX, MIN : 파티션 별 최대값, 최소값 계산

-- 집계 함수 사용 예시
SELECT ENAME, SAL
	SUM(SAL) OVER(PARTITION BY JOB) AS SUM_J		-- 직업별 급여 합계를 나타낸다.
FROM EMP;

 ▷ 행 순서 관련 함수

• FIRST_VALUE : 가장 처음 나오는 값 / MIN 함수를 사용해 같은 결과를 구할 수 있다.
• LAST_VALUE : 가장 나중에 나온 값 / MAX를 사용해 같은 결과 출력 가능
• LAG : 이전 행
• LEAD : 특정 위치의 행 / 기본값 = 1

 ▷ 비율 관련 함수

• CUME_DIST : 현재 행보다 ㅈ작거나 같은 건수에 대한 누적 백분율을 조회하며 0~1사이의 값을 출력
• PERCENT_RANK : 값이 아닌 행의 순서별 백분율 조회
• NTILE : 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과 조회
• RATIO_TO_REPORT : SUM에 대한 행 별 칼럼값의 백분율을 소수점까지 조회

---

테이블 파티션(Table Partition)

 ▷ Partition 기능

• 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장
• 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 서능 향상
• 파티션 별로 독립적으로 관리 가능
• 데이터의 범위를 줄여 성능을 향상

 ▷ Partition 종류

1. Range Partition : 칼럼 중에서 값의 범위를 기준으로 여러개의 파티션으로 나누어 저장
2. List Partition : 특정 값을 기준으로 분할
3. Hash Partition : 내부적으로 해시 함수를 사용해서 분할
4. Composite Partition : 여러개의 파티션 기법을 조합해서 사용

 ▷ 파티션 인덱스(Partition Index)

• GLOBAL INDEX : 여러개의 파티션에서 하나의 인덱스를 사용
• LOCAL INDEX : 파티션 별 각자의 인덱스 사용
• PREFIXED INDEX : 파티션 키와 인덱스 키는 동일
• NON PREFIXED INDEX : 파티션 키와 인덱스 키는 다름

---

파티션 기법 중 특정값을 기준으로 데이터를 파티션 하는 방법은? (답 : 1)
① LIST PARTITION
② RANGE PARTITION
③ COMPOSITE PARTITION
④ HASH PARTITION

---