[SQLD] 시험준비 4주 1일차 - SQL 활용2

시험 4일남았다

빡세게 진도빼자

 

 

POINT4. 그룹함수

• ROLLUP

- ROLLUP은 GROUP BY 의 칼럼에 대해서 Subtotal을 만들어 준다

- ROLLUP을 할 때 GROUP BY 구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라진다.

rollup 사용

- 위의 예는 deptno에 대해서 group by 로 급여 합계를 계산하고 부서별 합계를 추가해서 계산했다. 즉, ROLLUP은 DEPTNO에 대해서 기존 GROUP BY 와는 다르게 부서별 전체 합계를 계산하게 된다.

- DECODE 문은 전체합계를 조회할 때 '전체합계' 라는 문자를 출력하기 위해서 사용된다. DECODE문을 사용해서 DEPTNO 가 NULL 과 같으면 '전체합계' 라는 문자를 출력하고 그렇지 않으면 부서번호(DEPTNO)를 출력한다.

부서별(DEPTNO), 직업별(JOB) ROLLUP 실행

- 부서별, 직업별 ROLLUP을 실행하면 부서별 합계, 직업별 합계, 전체합계가 모두 조회된다.

- ROLLUP으로 실행되는 칼럼별로 Subtotal을 만들어 준다.

 

 

 

• GROUPING 함수

- GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS 에서 생성되는 합계값을 구분하기 위해서 만들어진 함수이다.

- 예를 들어 소계, 합계 등이 계산되면 GROUPING 함수는 1을 반환하고 그렇지 않으면 0을 반환해서 합계값을 식별할 수 있다.

GROUPING함수

- 위의 SQL문을 보면 소계와 합계가 계산된 데이터는 GROUPING 함수에서 '1' 이 출력된 것을 알 수 있다.

- GROUPING의 반환값을 DECODE 혹은 CASE 문으로 식별해서 SELECT문으로 '소계', '합계' 를 구분하는 것이다.

 

전체합계와 부서합계 표시

- 위의 예를 보면 DECODE 함수를 사용해서 GROUPING 함수 결과가 '1'이면 '전체합계' 혹은 '부서합계' 를 출력하고 그렇지 않으면 NULL을 반환한다.

- GROUPING 함수의 기능을 사용하면 사용자가 필요로 하는 데이터를 SELECT문으로 작성하여 제공할 수 있다.

 

 

 

• GROUPING SETS 함수

- GROUPING SETS 함수는 GROUP BY 에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있다.

- GROUPING SETS 함수는 GROUP BY 에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리한다.

직업별 합계와 부서별 합계 표시

- 위의 예를 보면 GROUPING SETS 함수로 DEPTNO 와 JOB 을 실행했다.

- 그 결과 DEPTNO 합계와 JOB 합계가 개별적으로 조회되었다. 즉, 서로 관계 없다는 것이다.

 

 

 

 

• CUBE 함수

- CUBE는 CUBE함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.

- 다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있게 한다.

- 예를 들어 부서와 직업을 CUBE로 사용하면 부서별합계, 직업별 합계, 부서별 직업별 합계, 전체합계가 조회된다.

- 즉, 조합할 수 있는 경우의 수가 모두 조합되는 것이다.

CUBE 사용

- 위의 예에서는 CUBE로 부서코드와 직업을 실행했다. 그 결과 전체합계, 직업별 합계, 부서별 합계, 부서별 직업별 합계가 조회됐다.

 

 

 

POINT5. 윈도우 함수 (Window Function)

• 윈도우 함수

- 윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다.

- 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

윈도우 함수 구조

윈도우 함수 구조

구조	설명
ARGUMENTS(인수)	윈도우 함수에 따라서 0~N 개의 인수를 설정한다.
PARTITION BY	전체 집합을 기준에 의해 소그룹으로 나눈다.
ORDER BY	어떤 항목에 대해서 정렬한다.
WINDOWING	- 행 기준의 범위를 정한다. - ROWS는 물리적 결과의 행 수 이고 RANGE는 논리적인 값에 의한 범위이다.

 

WINDOWING

구조	설명
ROWS	부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다.
RANGE	논리적인 주소에 의해 행 집합을 지정한다.
BETWEEN~AND	윈도우의 시작과 끝의 위치를 지정한다.
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행임을 의미한다.
UNBOUNDED FOLLOWING	윈도우 마지막 위치가 마지막 행임을 의미한다.
CURRENT ROW	윈도우 시작 위치가 현재 행임을 의미한다.

 

WINDOWING 사용(처음부터 마지막까지 합계 계산)

- UNBOUNDED PRECEDING은 처음 행을 의미하며, UNBOUNDED FOLLOWING은 마지막 행을 의미한다. 그러므로 TOTSAL 에 처음부터 마지막까지의 합계(SUM(SAL))를 계산한 것이다.

 

처음부터 현재 행까지의 합계 계산

- 행 별로 누적 합계를 구한다.

- 위의 SQL 문은 처음부터 CURRNET ROW 까지의 합계를 계산한다. 결과적으로 누적합계가 된다. 즉, 첫 번재 행의 SAL은 800이고 두번째 행은 950이다. 그러므로 두번째 행의 TOTSAL은 800+900=1750이 된다. 세번째 행은 다시 1100+1750=2850이 된다. 

- CURRENT ROW 는 데이터가 인출된 현재 행을 의미한다.

 

현재행 부터 마지막 행까지 합계 계산

- 위의 SQL 문은 현재 행(CURRENT ROW) 부터 마지막행(UNBOUNDED FOLLOWING)까지의 합계를 계산한다.

- 첫 번째 행의 SAL이 800이므로 800부터 긑까지의 합계를 TOTSAL 에 계산한다. 결과적으로 전체 합계가 된다.

- 그 다음은 950부터 마지막까지이므로 첫 800이 제외된 합계가 된다.

 

 

 

• 순위 함수(RANK Function)

- 윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.

- 순위 함수는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다.

 

순위(RANK) 관련 윈도우 함수

순위 함수	설명
RANK	- 특정항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다.
DENSE_RANK	동일한 순위를 하나의 건수로 계산한다.
ROW_NUMBER	동일한 순위에 대해서 고유의 순위를 부여한다.

 

RANK() 함수 사용

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

- RANK() OVER (ORDER BY SAL DESC)는 SAL로 등수를 계산하고 내림차순으로 조회하게 한다.

- RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) 는 JOB으로 파티션을 만들고 JOB별 순위를 조회하게 한다.

 

DENSE_RANK 함수

- DENSE_RANK 는 동일한 순위를 하나의 건수로 인식해서 조회한다.

- 즉 공동순위 밑으로 순번이 밀리지 않음

 

ROW_NUMBER 함수

- ROW_NUMBER 함수는 동일한 순윙 대해서 고유의 순위를 부여한다.

 

 

 

 

• 집계 함수(AGGREGATE Funtion)

- 윈도우 함수를 제공한다.

집계 관련 윈도우 함수

집계 함수	설명
SUM	파티션 별로 합계를 계산한다.
AVG	파티션 별로 평균을 계산한다.
COUNT	파티션 별로 행 수를 계산한다.
MAX와 MIN	파티션 별로 최댓값과 최솟값을 계산한다.

SUM 함수

- 같은 관리자(MGR)에 파티션을 만들고 합계를 계산한다.

- 같은 관리자의 급여 합계를 보여준다.

 

 

 

• 행 순서 관련 함수

- 행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있다.

- 특정 위치의 행을 출력할 수 있다.

 

행 순서 관련 윈도우 함수

행 순서	설명
FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다.
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구한다. - MAX 함수를 사용해서 같은 결과를 구할 수 있다.
LAG	이전 행을 가지고 온다.
LEAD	- 윈도우에서 특정 위치의 행을 가지고 온다. - 기본값은 1이다.

 

FIRST_VALUE 사용

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

- 위의 예에서 TEST7과 TEST14가 조회되었다. 그중에서 TEST7이 첫 번째 행이므로 첫째행의 TEST7을 가지고 온다.

- 단, SAL 내림차순으로 조회했기 때문에 의미상으로는 부서 내에 가장 급여가 많은 사원이 된다.

 

LAST_VALUE 사용

- LAST_VALUE 함수는 파티션에서 마지막 행을 가지고 온다. 그래서 FIRST_VALUE 와 다르게 TEST14가 출력된다.

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

 

LAG 함수

- LAG 함수는 이전 값을 가지고 오는 것이다. 예를 들어 PRE_SAL 의 5000값은 SAL의 이전 데이터이다.

 

LEAD 함수

- LEAD 함수는 지정된 행의 값을 가지고 오는 것이다. 위의 예는 SAL에서 2번째 행의 값을 가지고 온다.

- LEAD의 기본값은 1이며, 첫 번째 행의 값을 가지고 오는 것이다.

 

 

 

 

• 비율 관련 함수

- 비율 관련 함수는 누적백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있다.

 

비율 관련 윈도우 함수

비율 함수	설명
CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상에 위치를 0~1 사이의 값을 가진다.
PRECENT_RANK	파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다.
NTILE	파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회한다.
RATIO_TO_REPORT	파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회한다.

 

PERCENT_RANK 함수

- 같은 부서에서 자신의 급여 퍼센트(등수)를 구한다.

 

NTILE 함수

- NTILE(4) 는 4등분으로 분할하라는 의미로 위의 예에서는 급여가 높은 순으로 1~4등분으로 분할한다.

- 급여가 높은 순으로 4개로 등분한다.

 

 

 

 

POINT6. 테이블 파티션 (Table Partition)

• Partition 기능

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

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

- 파티션은 각각의 파티션 별로 독립적으로 관리될 수 있다. 즉, 파티션 별로 백업하고 복구가 가능하면 파티션 전용 인덱스 생성도 가능하다.

- 파티션은 Oracle db의 논리적 관리 단위인 테이블 스페이스 간에 이동이 가능하다.

- 데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향상시킨다.

 

 

• Range Partition

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

Range Partition

- 위의 예에서는 sal 값 2000~4000은 Datafile1.dbf 파일에 저장하고 그 이상 값은 Datafile2.dbf 에 저장한다.

 

 

• List partition

- List partition 은 특정 값을 기준으로 분할하는 방법이다.

List partition

- 위의 예에서 DEPTNO 가 10번인 것은 Datafile1.dbf 에 저장하고 20번인 것은 Datafile2.dbf 에 저장한다.

 

 

 

• Hash Partition

- Hash Partition 은 dbms가 내부적으로 해시ㅣ 함수를 사용해서 데이터를 분할한다.

- 결과적으로 dbms가 알아서 분할하고 관리하는 것이다.

 

hash partition

- hash partition 이외에도 composite partition 이 있는데, composite partition 은 여러 개의 파티션 기법을 조합해서 사용하는 것이다.

 

 

 

• 파티션 인덱스

- 파티션 인덱스는 4가지 유형의 인덱스를 제공한다. 즉, 파티션 키를 사용해서 인덱스를 만드는 prefixed index와 해당 파티션만 사용하는 local index 등으로 나누어진다.

- Oracle db는 global non-prefixed를 지원하지 않는다.

 

파티션 인덱스

구분	주요 내용
Global Index	여러 개의 파티션에서 하나의 인덱스를 사용한다.
Local Index	해당 파티션 별로 각자의 인덱스를 사용한다.
Prefixed Index	파티션 키와 인덱스 키가 동일하다.
Non Prefixed Index	파티션 키와 인덱스 키가 다르다.