함수 (그룹 함수, 윈도우 함수)
그룹 함수
- 그룹 별 소계 및 중계 등 중간 합계 분석 데이터를 산출 하기 위해 사용하는 함수이다.
- GROUP BY 절이 필수로 요구되며, GROUP BY 절 뒤에 위치하게 된다.
- 하나 이상의 칼럼을 값에 다라 그룹화 하여 그룹 별로 출력하는 함수이다.
ROLL UP
SELECT 그룹화칼럼1, 그룸화칼럼2, SUM(SALRARY) FROM 테이블
GROUP BY ROLLUP(그룹화칼럼1, 그룸화칼럼2);
- ROLL UP 은 GROUP BY 칼럼에 대해서 SubTotal을 만들어 준다.
- GROUP BY 구에 칼럼이 두 개 이상 왔을 때 순서에 따라 결과가 달라진다.
GROUPING
SELECT 그룹화칼럼1,GROUPING(그룹화칼럼1),
그룸화칼럼2,GROUPING(그룹화칼럼2),SUM(SALRARY)
FROM 테이블
GROUP BY ROLLUP(그룹화칼럼1, 그룸화칼럼2);
- GROUPING은 다른 그룹함수를 통해 나온 합계값을 어떤 칼럼별로 계산되었는지를 구분하기 위해 만들어진 함수이다.
- 출력시 그룹핑 항목이 출력되며 해당 칼럼에 대한 집계일 경우 1로서 표시된다.
- 1로서 표시되는 이 특성에 DECODE 함수를 사용하여 좀 더 시각성을 높일 수 있다.
SELECT 그룹화칼럼1,DECODE(GROUPING(그룹화칼럼1),1,'전체합계') 별칭
그룸화칼럼2,DECODE(GROUPING(그룹화칼럼2),1,'부분합계') 별칭, SUM(SALRARY)
FROM 테이블
GROUP BY ROLLUP(그룹화칼럼1, 그룸화칼럼2);
GROUPING SETS
- GROUPING SETS 는 GROUP BY에 나오는 칼럼의 순서와 관계 없이 다양한 소계를 만들 수 있다.
- GROUPING SETS 는 GROUP BY에 나오는 칼럼의 순서와 관계 없이 개별적으로 모두 처리한다.
- ROLLUP과 다르게 DIY로 집계 구조를 자유롭게 설정 가능하다.
SELECT 그룹화칼럼1, 그룸화칼럼2, SUM(SALRARY) FROM 테이블
GROUP BY GROUPING SETS(그룹화칼럼1, 그룸화칼럼2);
CUBE
- CUBE는 CUBE 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.
- 다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있게 한다.
- 조합할 수 있는 경우의 수가 모두 조합되는 것이다.
SELECT 그룹화칼럼1, 그룸화칼럼2, SUM(SALRARY) FROM 테이블
GROUP BY CUBE (그룹화칼럼1, 그룸화칼럼2);
윈도우 함수
- 윈도우 함수는 마치 창문을 통해 세상의 한 부분만 보듯이 테이블 전체가 아닌 특정 부분만을 사용해 데이터를 조회하는 함수이다.
- 윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다.
- GROUPBY 절과 함께 사용할 수 없다. -> PARTITION BY 사용
- 윈도우 함수를 사용하여 순위, 합계, 평균, 팽 위치 등을 조작할 수 있다.
기본 구조
SELECT 윈도우함수(인수)
OVER(PARTITION BY 칼럼
ORDER BY WINDOWING 절)
FROM 테이블명 ;
윈도우 함수 구조
| 구조 |
설명 |
| ARGUMENTS(인수) |
윈도우 함수에 따라 0~N개의 인수를 설정 |
| PARTITION BY |
전체 집한을 기준에 의해 소그룹으로 나눔 |
| ORDER BY |
정렬 |
| WINDOWING |
행 기준의 범위를 정함 |
WINDOWING
| 구조 |
설명 |
| ROWS |
부분집합인 왼도우 크기를 물리적 단위로 행의 지합을 지정 |
| RANGE |
논리적인 주소에 의해 행 집합을 지정 |
| BETWEEN ~AND |
윈도우의 시작과 끝을 정의 |
| UNBOUNDED PRECEDING |
윈도우의 시작 위치가 첫 번째 행임을 의미 |
| UNBOUNDED FOLLOWING |
윈도우의 마지막 위치가 마지막 행임을 의미 |
| CURRENT ROW |
윈도우 시작 위치가 현재 행임을 의미 |
SELECT 칼럼명,칼럼명,
SUM(칼럼명) OVER (ORDER BY 칼럼명
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) 별칭
FROM 테이블;
-- 첫번째 행을 뜻하는 UNBOUNDED PRECEDING 에서
-- 마지막 행을 뜻하는 UNBOUNDED FOLLOWING 의 값을 가져온다.
-- 누적합계
SELECT 칼럼명,칼럼명,
SUM(칼럼명) OVER (ORDER BY 칼럼명
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) 별칭
FROM 테이블;
-- 첫 번째 행부터 현재행까지의 합계를 구함으로서 누적 합계를 구현한다.
순위 함수
- 윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.
순위 관련 윈도우 함수
| 순위 함수 |
설명 |
| RANK |
순위를 계산하며 동일 순위는 동일 값을 부여한다. |
| DENSE_RANK |
동일한 순위를 하나의 건수로 계산한다. |
| ROW_NUMBER |
동일한 순위에 고유의 순위를 부여한다. |
집계 함수
| 집계 함수 |
설명 |
| SUM |
합계 계산 |
| AVG |
평균 계산 |
| COUNT |
행 수 계산 |
| MAX & MIN |
최댓값과 최솟값 계산 |
행 순서 관련 함수
- 행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있다.
- 특정 위치의 행을 출력할 수 있다.
| 행 순서 |
설명 |
| FIRST_VALUE |
-파티션에서 가장 처음에 나오는 값을 구한다.
-MIN 함수와 같은 결과 |
| LAST_VALUE |
-파티션에서 가장 나중에 나오는 값을 구한다.
-MAX 함수와 같은 결과 |
| LAG |
이전 행 |
| LEAD |
특정 위치 행을 가져옴 |
비율 관련 함수
- 비율 관련 함수는 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있다.
| 비율 함수 |
설명 |
| CUME_DIST |
현재 행 이하인 건수에 대한 누적 백분율을 조회
누적 분포상에 0~1의 값을 가짐 |
| PERCENT_RANK |
제일먼저 나온 것을 0 제일 늦게 나온 것을 1 로 하여 값이 아닌 행의 순서별 백분율 조회 |
| NTILE |
전체 건수를 ARGUMENT 값으로 N 등분한 결과 조회 |
| RATIO_TO_REPORT |
전체 SUM에 대한 행 별 칼럼값의 백분율을 소수점까지 조회한다. |
테이블 파티션
- 파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장
- 물리적으로 분리된 데이터 파일에 저장함으로서 입력, 수정, 삭제, 조회 성능 향상
- 파티션 별로 백업 복구도 가능하며 인덱스도 생성 가능하다. 즉 독립적임
- 테이블 스페이스 간에 이동 가능
- 조회 범위를 줄일 수 있음
RANGE PARTITION
- 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것
EX) 연봉이 3000만원이하인 사람과 초과인 사람으로 파티션을 나눔
LIST PARTITION
EX) 부서가 개발부서인 사람들과 인사과인 사람들로 파티션을 나눔
HASH PARTITION
- DBMS가 내부적으로 해시 함수를 사용해서 데이터를 분할함
- 즉 사용자가 아닌 DBMS가 알아서 분할하고 관리하는 것
파티션 인덱스
| 구분 |
주요 내용 |
| Globla index |
여러개의 파티션에서 하나의 인덱스를 사용 |
| Local index |
해당 파티션 별로 각자의 인덱스 사용 |
| Prefixed index |
파티션 키와 인덱스 키 동일 |
| Non prefixed index |
파티션 키와 인덱스 키 다름 |
댓글남기기