SQL 개발자 (SQLD) 자격증 따기 Part.3 섹션 0.

1. GROUP 함수(ROLL UP, GROUPING, GROUPING SETS, CUBE)에 대한 설명

1) ROLL UP

- ROLL UP은 GROUP BY의 칼럼에 대해 Subtotal을 만들어준다. GROUP BY구에 칼럼이 두 개 이상이면 순서에 따라 결과가 달라진다

* DECODE문과 함께 유용하게 활용가능

2) GROUPING

- GROUPING 함수는 ROLL UP, CUBE, GROUPING SETS에서 생성되는 합계 값을 구분하기 위해 만들어진 함수. 소계, 합계 등이 계산되면 GROUPING은 1을 반환하고 그렇지 않은 경우 0을 반환해 합계 값을 식별할 수 있다

+ DECODE 함수를 사용해, 결과가 1일 경우 합계를 출력, 그렇지 않으면 NULL을 반환하게 할 수 있다

: SELECT DEPTNO, DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, JOB, DECODE(GROUPING(JOB), 1, '부서합계') T_DEPT, SUM(SAL) FROM EMP GROUP BY ROLL UP(DEPTNO, JOB);

3) GRUOPING SETS

- GROUPING SETS 함수는 GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있다. 순서와 관계없이 개별적으로 모두 처리한다 ~ 조회결과 간 관계가 없다

4) CUBE

- CUBE는 함수에 제시한 칼럼에 대해 결합 가능한 모든 집계를 계산. 다양하게 데이터를 분석할 수 있다

2. WINDOW 함수(WINDOW FUNCTION)

1) 윈도우 함수 : 윈도우 함수는 행과 행 간 관계를 정의하기 위해 제공되는 함수

~ 순위, 합계, 평균, 행 위치 등을 조작할 수 있다

- 구조

AGUMENTS 인수

PARTITION BY 전체 집합을 기준에 의해 소그룹으로 나눈다

ORDER BY 정렬

WINDOWING 행 기준의 범위를 정한다. ROWS는 물리적 결과의 행 수이고 RANGE는 논리 값에 의한 범위

- WINDOWING

UNBOUNDED PRECENDING : 윈도우의 시작 위치가 첫번째 행

UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 행

CURRENT ROW : 윈도우 시작 위치가 현재 행

ex) SELECT empno, ename, sal SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRNET ROW) TOTSAL FROM EMP; ~ 처음부터 CURRENT ROW까지 합계, CURRENT ROW는 데이터가 인출된 현재 행을 의미

 

2) 순위 함수 : 특정 항목 및 파티션에 대한 순위를 계산할 수 있는 함수

RANK, DENSE_RANK, ROW_NUMBER 함수가 있다

(1) RANK

RANK 함수는 순위를 계산하며, 동일 순위에 같은 순위가 부여된다

(2) DENSE_RANK

동일 순위를 하나의 건수로 인식해서 조회

(3) ROW NUMBER ~ 잘 안씀

ROW_NUMBER 함수는 동일 순위에 대해 고유 순위를 부여

 

3) 집계 함수

집계 함수 관련 윈도우 함수

SUM

AVG

COUNT

MAX, MIN

 

4) 행 순서 관련 함수 - 빈출!

ex)

SELECT DEPTNO, ENAME, SAL

LAST_VALUE || FIRST_VALUE || LAG || LEAD (ENAME) OVER (PARTTITION BY DEPTNO

ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

AS DEPT A FROM EMP;

FIRST_VALUE 함수는 파티션에서 조회된 행 중에서 첫 번째 행의 값을 가지고 온다

LAST_VALUE : 파티션의 가장 나중 값

BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING의 의미는 현재 행에서 마지막행까지의 파티션을 의미

LAG : 이전 행, 이전 값을 가져오는 것이다 ~ ex) 행 1 value 2000, 행 2 value 3000일 때, 행 1의 lag가 3000

LEAD : 윈도우에서 특정 위치의 행을 가지고 온다

 

5) 비율 관련 함수 : 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회

CUME_DIST : 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회, 0 ~ 1사이의 값

PERCENT_RANK : 파티션에서 제일 먼저 나온 것을 0으로 나중에 나오는 것을 1로 해 값이 아닌 행의 순서별 백분율 조회

NTILE : 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회

RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회

6. 테이블 파티션(레인지, 리스트 위주)

1) 파티션 기능

- 파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다

- 테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상

- 파티션 별로 관리가 가능하다

- 파티션은 Oracle 데이터베이스의 논리적 관리 단위인 스페이스 간 이동이 가능. 데이터를 조회할 때 데이터의 범위를 줄여 성능을 향상

 

2) 레인지 파티션(Range Partition)

- 테이블의 칼럼 중 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것

 

3) 리스트 파티션(List Partition)

- 특정 값을 기준으로 분할하는 방법

 

4) 해시 파티션

- 데이터베이스 관리시스템이 내부적으로 해시 함수를 사용해 데이터를 분할

 

+ 컴포지트 파티션

시험 : 다음 중 파티션의 종류로 부적절한 것은?

 

+ 파티션 인덱스 ~ Oracle은 Global Non-Prefixed를 지원하지 않는다