[SQLD 요약] II. SQL 기본 및 활용 - 02. SQL 활용 (4) - 윈도우함수, 테이블파티션

 

 

SQLD 요약 / SQL Developer 요약

🔑 윈도우함수/ ARGUMENTS/ PARTITION BY/ ORDER BY/ WINDOWING/ ROWS/ RANGE/

       순위함수/ RANK/ DENSE_RANK/ ROW_NUMBER/ 집계함수/ SUM/ AVG/ COUNT/ MAX/ MIN/ 

       행순서관련/ FIRST_VALUE/ LAST_VALUE/ LAG/ LEAD/

       비율관련/ CUME_DIST/ PERCENT_RANK/ NTILE/ RATIO_TO_REPORT/ 테이블파티션

 

II. SQL 기본 및 활용

   02. SQL 활용

          [1] 조인 (JOIN)

          [2] 계층형 조회 (Connect by)

          [3] 서브쿼리 (Subquery)

          [4] 그룹함수 (Group Function)

          [5] 윈도우 함수 (Window Function)

          [6] 테이블 파티션 (Table Partition)

 

 

---

---

 

02. SQL 활용

 

[5] 윈도우 함수 (Window Function)

(1) 윈도우 함수 | 행과 행 사이의 관계를 정의하기 위한 함수

• 순위, 합계, 평균, 행위치 등을 조작
  
  
• 윈도우 함수 구조: ARGUMENTS, PARTITION BY, ORDER BY, WINDOWING

SELECT WINDOW_FUNCTION(ARGUMENTS)                                                                                                   OVER (PARTITION BY 칼럼                                                                                                                 ORDER BY WINDOWING절)                                                                                      FROM 테이블;
ARGUMENTS(인수)	PARTITION BY	ORDER BY	WINDOWING
0~N개의 인수 설정	전체 집합을 기준에 따라서 소그룹으로 나눔	어떤 항목에 대해 정렬함 (오름차순, 내림차순)	행 기준의 범위 설정 (ROWS, RANGE)

 

 

• WINDOWING 구조

WINDOWING 구조	기능	WINDOWING 구조	기능
ROWS	물리적 단위로 행집합 지정	UNBOUNDED PRECEDING	윈도우 시작위치 = 첫번째 행
RANGE	논리적 주소로 행집합 지정	UNBOUNDED FOLLOWING	윈도우 시작위치 = 마지막 행
BETWEEN ~ AND	윈도우 시작, 끝 위치 지정	CURRENT ROW	윈도우 시작위치 = 현재 행

# TOTSAL: SAL의 전체합계를 조회하는 칼럼
SELECT EMPNO, ENAME, SAL, 
  SUM(SAL) OVER (ORDER BY SAL		                # SAL 칼럼의 합계(SUM)
                 ROWS BETWEEN UNBOUNDED PRECEDING	# 첫번째~마지막행까지 WINDOWING
                 AND UNBOUNDED FOLLOWING) TOTSAL	# 칼럼이름은 TOTSAL
  FROM EMP;
  
  
# CUMSAL: SAL의 누적합계를 조회하는 칼럼
SELECT EMPNO, ENAME, SAL,
  SUM(SAL) OVER (ORDER BY SAL
                 ROWS BETWEEN UNBOUNDED PRECEDING	# 첫번째~현재행까지 WINDOWING
                 AND CURRENT ROW) CUMSAL            # 칼럼이름은 CUMSAL
  FROM EMP;
  
  
# LEFTSAL: SAL에서 현재행~마지막행까지의 합계를 조회하는 칼럼
SELECT EMPNO, ENAME, SAL,
  SUM(SAL) OVER (ORDER BY SAL
                 ROWS BETWEEN CURRENT ROW           # 현재~마지막행까지 WINDOWING
                 AND UNBOUNDED FOLLOWING) LEFTSAL   # 칼럼이름은 LEFTSAL
  FROM EMP;

 

---

 

 

(2) 순위 함수 | 특정 항목, 파티션에 대해 순위를 계산함

• 순위 관련 윈도우 함수: RANK, DENSE_RANK, ROW_NUMBER

RANK	DENSE_RANK	ROW_NUMBER
동일한 순위에 동일한 값 부여	동일한 순위는 하나의 건수로 계산	동일한 순위에 고유의 순위 부여

# RANK() 예시: 동일순위 동일한값
SELECT ENAME, SAL, 
       RANK() OVER (ORDER BY SAL DESC) ALL RANK,   # SAL 순위 조회 (ALL_RANK)
       RANK() OVER (PARTITION BY JOB               # 직업별로 파티션 나누고
                    ORDER BY SAL DESC) JOB_RANK    # 직업별 순위 조회 (JOB_RANK)
  FROM EMP;

 

---

 

 

(3) 집계 함수 | 특정 항목, 파티션에 대해 집계 (AGGREGATE)

• 집계 관련 윈도우 함수: SUM, AVG, COUNT, MAX, MIN

SUM	AVG	COUNT	MAX	MIN
파티션별 합계 계산	평균 계산	행 개수 계산	최댓값 계산	최솟값 계산

# SUM 예시: 관리자(MGR)마다 파티션 생성 >> 파티션마다 합계 계산
SELECT ENAME, SAL,
  SUM(SAL) OVER (PARTITION BY MGR) SUM_MGR
  FROM EMP;

 

---

 

 

(4) 행 순서 관련 함수 | 특정한 행을 출력

• 행 순서 관련 함수의 기능
  - 상위 행의 값을 하위에 출력
  - 하위 행의 값을 상위에 출력
  - 특정 위치의 행을 출력
  
  
• 행 순서 관련 윈도우 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD

FIRST_VALUE	LAST_VALUE	LAG	LEAD
파티션에서 가장 처음의 값	파티션에서 가장 나중의 값	이전 행을 가지고 옴	이후 행을 가지고 옴
MIN 함수와 같은 결과	MAX 함수와 같은 결과	없는 경우는 NULL	특정 위치의 행 (기본값 = 1)

# FIRST_VALUE 예시: 첫번째 행 조회 >> 부서 내에서 가장 급여 높은 사람
SELECT DEPTNO, ENAME, SAL, 
  FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO       # 부서별로 파티션
                           ORDER BY SAL DESC         # 급여 내림차순 정렬
                           ROWS UNBOUNDED PRECEDING) AS DEPT_F
  FROM EMP;


# LAST_VALUE 예시: 마지막 행 조회 >> 부서 내에서 가장 급여 낮은 사람
# LAST_VALUE 예시
SELECT DEPTNO, ENAME, SAL,
  LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO        # 부서별로 파티션 
                          ORDER BY SAL DESC          # 급여 내림차순 정렬
                          ROWS BETWEEN CURRENT ROW   # 현재~마지막 행까지
                          AND UNBOUNDED FOLLOWING) AS DEPT_L
  FROM EMP;
  

# LAG, LEAD 예시
SELECT DEPTNO, ENAME, SAL,
  LAG(SAL) OVER (ORDER BY SAL DESC) AS PRE_SAL       # 현재행 기준으로 이전 행의 값
  LEAD(SAL) OVER (ORDER BY SAL DESC) AS POST_SAL     # 현재행 기준으로 이후 행의 값
  FROM EMP;

 

---

 

 

(5) 비율 관련 함수 | 백분율, 파티션을 분할한 결과 등을 조회

• 비율 관련 윈도우 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

CUME_DIST	PERCENT_RANK	NTILE	RATIO_TO_REPORT
파티션 내 전체 건수 중에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율	파티션에서 가장 처음 값은 0 가장 나중의 값은 1 으로 행의 순서별 백분율 조회	파티션별로 전체 행을 N등분한 결과를 조회 (N은 ARGUMENT로 입력)	파티션 내 전체 SUM(칼럼)에 대한 행별 칼럼값의 백분율을 소수점까지 조회

# PERCENT_RANK 예시: 부서 내 급여 퍼센트(등수) 조회
SELECT DEPTNO, ENAME, SAL, 
  PERCENT_RANK() OVER (PARTITION BY DEPTNO            # 부서별로 파티션
                       ORDER BY SAL DESC) AS PER_SAL  # 급여 내림차순
  FROM EMP;
  
  
# NTILE 예시: 급여 높은 순서로 4등분
SELECT DEPTNO, ENAME, SAL,
  NTILE(4) OVER (ORDER BY SAL DESC) AS N_TILE         # 4등분 >> 1,2,3,4 할당
  FROM EMP;

 

---

 

 

[6] 테이블 파티션 (Table Partition)

(1) 파티션 기능 | 대용량 테이블을 여러 개의 데이터파일에 분리하여 저장

• 테이블 파티션 특징

파티션 특징/기능	파티션 장점
- 테이블의 데이터를 물리적으로 분리된 데이터파일에 저장	- 데이터 조회 시 범위를 줄임
- 각 파티션별로 독립적 관리: 백업, 복구, 전용 인덱스 생성	- 데이터 입력, 수정, 삭제, 조회 성능 향상
- 테이블 스페이스간 이동 가능

 

 

• 파티션 기법: Range, List, Hash, Composite
  - Composite Partition: 여러 개의 파티션 기법을 조합

Range Partition	List Partition	Hash Partition
칼럼 값의 범위를 기준으로 파티션 분할	특정한 값을 기준으로 파티션 분할	시스템 내부의 해시함수를 사용하여 분할
(ex) - 파티션1: SAL 값 2000~4000          - 파티션2: SAL 값 5000~7000	(ex) - 파티션1: 부서번호 11인 행들          - 파티션2: 부서번호 12인 행들	데이터베이스 관리 시스템이 자동으로 분할 및 관리함

 

 

• 파티션 인덱스: Global, Local, Prefixed, Non Prefixed
  - Oracle 데이터베이스는 Global Non-Prefixed를 지원하지 않음

Global Index	Local Index	Prefixed Index	Non Prefixed Index
여러 개 파티션에서 하나의 인덱스 사용	각각의 파티션 별로 각각의 인덱스 사용	파티션 키와 인덱스 키가 동일함 (파티션 키 = 인덱스 키)	파티션 키와 인덱스 키가 다름 (파티션 키 ≠ 인덱스 키)

 

---

 

참고도서: SQL 개발자 이론서+기출문제_이기적, 2020