[SQLD] SQL 활용 - 그룹 함수, 윈도우 함수, 테이블 파티션

< 그룹함수 >

1. ROLLUP

- GROUP BY 칼럼에 대해 Subtotal을 만들어준다.

SELECT DECODE(DEPTNO,NULL,'전체합계',DEPTNO),SUM(SAL) // DEPTNO가 NULL이면 '전체합계'
FROM EMP2
GROUP BY ROLLUP(DEPTNO); // 부서별&총 합계산

GROUP BY ROLLUP(컬럼명);

 

SELECT DEPTNO, JOB , SUM(SAL)
FROM EMP2
GROUP BY ROLLUP(DEPTNO,JOB);

- 부서별 합계, 직업별 합계, 전체합계 모두 조회

 

 

 

2. GROUPING 함수

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

ex) 소계, 합계 등이 계산되면 GROUPING 함수는 1을 반환하고, 그렇지 않으면 0을 반환해서 합계값 식별

 

SELECT DEPTNO,GROUPING(DEPTNO), JOB,GROUPING(JOB) , SUM(SAL)
FROM EMP2
GROUP BY ROLLUP(DEPTNO,JOB);

GROUPING(컬럼명)

 

SELECT DEPTNO,
DECODE(GROUPING(DEPTNO),1,'전체 합계',' ') TOT, // GROUPING 함수 결과가 1이면 '전체 합계'
JOB,
DECODE(GROUPING(JOB),1,'부서별 합계',' ') T_DEPT , // GROUPING 함수 결과가 1이면 '부서별 합계'
SUM(SAL)
FROM EMP2
GROUP BY ROLLUP(DEPTNO,JOB);

 

 

3. GROUPING SETS 함수

- GROUP BY에 나오는 컬럼의 순서와 상관없이 다양한 소계를 만들 수 있음

- GROUP BY에 나오는 컬럼에 순서와 관계없이 개별적으로 모두 처리

 

SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP2
GROUP BY GROUPING SETS(DEPTNO,JOB);

GROUP BY GROUPING SETS(컬럼명);

- DEPTNO 합계와 JOB 합계가 개별적으로 조회됨 (서로 관계 X)

 

 

4. CUBE 함수

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

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

ex) 부서와 직업을 CUBE로 사용하면 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체 합계가 조회됨

- 즉, 조합할 수 있는 경우의 수가 모두 조합됨

 

SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP2
GROUP BY CUBE(DEPTNO,JOB);

GROUP BY CUBE(컬럼명);

 

 

< 윈도우 함수 >

1. 윈도우 함수

- 행과 행간의 관계를 정의하기 위해서 제공되는 함수

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

 

- 윈도우 함수 구조

SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (PARTITION BY 칼럼 ORDER BY WINDOWING 절)
FROM 테이블명;

 

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

 

- WINDOWING

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

 

SELECT EMPNO, ENAME, SAL,
	SUM(SAL) OVER(ORDER BY SAL
    			ROWS BETWEEN UNBOUNDED PRECEDING // 처음행을 의미
                AND UNBOUNDED FOLLOWING) TOTSAL // 마지막행을 의미
FROM EMP2;

- TOTSAL에 처음부터 마지막까지의 합계를 계산

 

SELECT EMPNO, ENAME, SAL,
	SUM(SAL) OVER(ORDER BY SAL
    			ROWS BETWEEN UNBOUNDED PRECEDING // 처음행을 의미
                AND CURRENT ROW) TOTSAL // 현재행을 의미
FROM EMP2;

- 현재 행까지의 누적 합계를 계산

* CURRENT ROW는 데이터가 인출된 현재 행을 의미

 

SELECT EMPNO,ENAME, SAL,
    SUM(SAL) OVER (ORDER BY SAL
                ROWS BETWEEN CURRENT ROW // 현재 행을 의미
                AND UNBOUNDED FOLLOWING) TOTSAL // 마지막 행을 의미
FROM EMP2;

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

* 누적합의 역순이나 마찬가지

 

 

2. 순위 함수

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

- RANK, DENSE_RANK,ROW_NUMBER 함수가 있음

 

- RANK 관련 윈도우 함수

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

 

- RANK() 함수 사용

SELECT ENAME SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK, // 급여로 순위를 계산
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK // 직업별로 급여 순위를 계산
FROM EMP2;

- RANK 함수는 동일한 순위에 같은 순위가 부여됨

* PARTITION BY JOB으로 JOB으로 파티션을 만들고, JOB별 순위를 조회하게 함

 

 

 

- DENSE_RANK() 함수 사용

SELECT ENAME SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK, // RANK()함수로 순위 계산
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENESE_RANK // DESE_RANK() 함수로 순위 계산
FROM EMP2;

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

* RANK 함수를 쓴 ALL_RANK에는 2위가 2명이라 3위가 없지만 DENSE_RANK 함수를 쓴 DENSE_RANK는 3위가 있음

 

 

 

- ROW_NUMBER() 함수사용

SELECT ENAME, SAL,
    RANK() OVER(ORDER BY SAL DESC) ALL_RANK, // RANK() 함수로 순위 계산
    ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUM // ROW_NUMBER() 함수로 순위 계산
FROM EMP2;

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

* 같은 순위여도 다른 순위를 부여함

 

 

3. 집계 함수

- 윈도우 함수를 제공

 

- 집계(AGGREGATE) 관련 윈도우 함수

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

 

SELECT ENAME SAL,
SUM(SAL) OVER(PARTITION BY MGR) SUM_MGR //같은 관리자(MGR)에 파티션을 만들고 합계(SUM) 계산
FROM EMP2;

같은 관리자의 급여 합계

 

 

4. 행 순서 관련 함수

- 상위 행의 값을 하위에 출력하거나 하위행의 값을 상위 행에 출력할 수 있음

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

 

- 행 순서 관련 윈도우 함수

행 순서	설명
FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구함 (MIN 함수 결과와 동일)
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구함 (MAX 함수 결과와 동일)
LAG	이전 행을 가지고 옴
LEAD	윈도우에서 특정 위치의 행을 가져옴 (DEFAULT 1)

 

- FIRST_VALUE 사용

SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO // 부서별로 파티션을 만듬
                    ORDER BY SAL DESC  // 급여를 내림차순 (높은순) 정렬
                    ROWS UNBOUNDED PRECEDING) DEPT_A // 첫번째행부터 시작
FROM EMP2;

DEPT_A는 부서내 가장 급여가 많은 사원과 동일

FIRST_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) DEPT_A
                    // 현재 행부터 마지막 행까지의 파티션
FROM EMP2;

DEPT_A는 부서내 가장 급여가 적은 사원과 동일

LAST_VALUE 함수는 파티션에서 조회된 행 중 마지막 행의 값을 가져옴

 

 

 

- LAG 사용

SELECT DEPTNO, ENAME, SAL,
LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL 
FROM EMP2;

LAG 함수는 이전의 값을 가져옴

 

 

- LEAD 사용

SELECT DEPTNO, ENAME, SAL,
LEAD(SAL,2) OVER(ORDER BY SAL DESC) PRE_SAL // SAL에서 2번째 행의 값을 가지고 옴
FROM EMP2;

SAL에서 2번째 행의 값을 가지고 옴

LEAD 함수는 지정된 위치의 값을 가지고 옴

 

 

5. 비율 관련 함수

- 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있음

 

- 비율 관련 윈도우 함수

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

 

 

- PERCENT_RANK 함수

SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER(PARTITION BY DEPTNO // 부서 내의 등수를 백분율로 구함
    ORDER BY SAL DESC) AS PERCENT_SAL
FROM EMP2;

같은 부서에서 자신의 급여 퍼센트(등수)를 구함

 

 

- NTILE 함수

SELECT DEPTNO, ENAME,SAL,
NTILE(4) OVER (ORDER BY SAL DESC) AS N_TILE // 4등분으로 분할
FROM EMP2;

급여가 높은 순으로 4등급으로 분할함

 

 

 

< 테이블 파티션 >

1. Partition 기능

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

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

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

- 파티션은 Oracle 데이터베이스 논리적 관리 단위인 테이블 스페이스간에 이동이 가능함

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

 

2. Range Partition

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

ex) 급여의 범위에 따라 분류

* Range : 범위

 

3. List Partition

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

ex) 부서번호 값에 따라 분류

 

4. Hash Partition

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

- 즉, 데이터베이스 관리 시스템이 알아서 분할하고 관리하는 것 

 

5. Composite Partition

- 여러개의 파티션 기법을 조합해 사용

 

6. 파티션 인덱스

- 파티션 인덱스는 4가지 유형의 인덱스를 제공

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

* Oracle 데이터베이스는 Global Non-Prefixed를 지원하지 않음