SQLD(5)-SQL활용2

• 그룹함수

ROLLUP - GROUP BY 칼럼에 대해서 Subtotal 만듬 

             ex) GROUP BY ROLLUP(COLUMN) 열이 두개이상 가능 순서에 따라 결과가 달라짐

 

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

                 소계, 합계등이 계산되면 1을 반환 그렇지 않으면 0을 반환

 

GROUPING SETS - GROUP BY 에서 나오는 칼럼의 순서와 관계없이 개별적으로 처리

 

CUBE - 함수에 제시한 칼럼에 대해서 결합이 가능한 모든 집계계산 ( 조합할 수 있는 모든 가짓수 계산)

select year, semester, count(course_id) as count
from takes
group by rollup(year, semester)

select year, semester, count(course_id) as count
from takes
group by grouping sets(year, semester)

select year, semester, count(course_id) as count
from takes
group by CUBE(year, semester)

ROLLUP, GROUPING SETS, CUBE

• 윈도우함수 - 행 간의 관계를 정의하기 위해 제공되는 함수

* SELECT WINDOW_FUNCTION(ARGUMENTS) OVER(PARTITION BY COLUMN ORDER BY WINDOWING) FROM TABLE;

PARTITION BY - 전체 집합을 칼럼에 의해 소그룹으로 나눔

 

WINDOWING

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

select *, 
sum(salary) over(order by salary 
		rows between unbounded preceding 
		and current row) total
from instructor

현재행 까지 누적 합계 구하기

---

WINDOW FUNCTION

• 순위 함수 - 특정항목과 파티션에 대해서 순위를 계산

RANK - 순위 계산, 동일한 순위는 동일한 값 부여

DENSE_RANK - 동일한 순위를 하나의 건수로 계산

ROW_NUMBER - 동일한 순위에 고유의 순위 부여

select *, 
RANK() over(order by salary DESC) total
from instructor

select *, 
DENSE_RANK() over(order by salary DESC) total
from instructor

select *, 
ROW_NUMBER() over(order by salary DESC) total
from instructor

RANK, DENSE_RANK, ROW_NUMBER

• 집계 함수 - SUM, AVG, COUNT, MAX, MIN
• 행 순서 관련 함수 - 특정 위치의 행을 출력

FIRST_VALUE - 파티션에서 처음 나오는 행의 열의 값, MIN함수로 같은 결과 구할 수 있음

LAST_VALUE - 파티션에서 마지막에 나오는 행의 열의 값, MAX함수로 같은 결과 구할 수 있음

LAG - 이전 행을 가져옴

LEAD - 특정 위치의 행을 가지고 옴 Default : 1

 

• 비율 관련 함수 - 누적백분율, 순서별백분율, 파티션을 분할

CUME_DIST - 파티션에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회

PERCENT_RANK - 행의 순서별 백분율 조회, 첫번째 것을 0 마지막을 1로 설정 

NTILE - 파티션별 전체 건수를 ARGUMENT값으로 N등분한 결과 조회, 등수매기기?

RATIO_TO_REPORT - SUM에 대한 행 별 칼럼값의 백분율을 소수점까지 조회

 

• 테이블 파티션

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

                - 입력 수정 삭제 조회 성능 향상

                - 파티션별로 독립적으로 관리, 파티션별 백업 복구 인덱스관리

 

Range Partition - 테이블의 칼럼중 값의 범위를 기준으로 나눔

List Partition - 특정 값을 기준으로 분할

Hash Partition - DBMS 내부적으로 해시함수를 이용해서 분할

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

 

Partition Index

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