window 함수, 계층형 조회

window함수: 행간 구분을 지어서 그 안에서의 관계를 쉽게 정의하기 위한 함수.

구조 : select window_function (arguments) over ([partition by 컬럼명][order by 컬럼명][windowing]) from 테이블명;

ex) select 매장코드, 지역명, 메뉴명, 판매량, dense_rank() over (partition by 지역명 order by 판매량 desc) as 판매순위

from 판매

 

window_function : 윈도우 함수

argument : 인수 ( 컬럼명 등 함수의 작업이 이뤄지는 대상)

partition by : 테이블의 레코드들을 쪼개는 기준

order by : 쪼개진 레코드들 내에서 혹은 전체 테이블에서 레코드들을 어떤 기준으로 정렬할지

windowing: 함수의 연산 대상이 되는 레코드의 범위를 정함

 

# window_function 종류

- 그룹 내 집계함수 : count, sum, min, max, avg 등

- 그룹 내 순위(rank) 함수 (자주 나옴)

- 그룹 내 비율 관련 함수

- 그룹 내 행 순서 함수

 

window 함수 - 그룹 내 rank 함수

rank : 동일한 순위에 대해 동일한 순위를 부여한다. 동일한 순위를 하나의 건수로 계산하지 않는다.

dense_rank : 동일한 순위에 대해 동일한 순위를 부여한다. 동일한 순위는 하나의 건수로 계산한다.

row_number : 동일한 순위에 대해 고유한 순위를 부여한다.

 

window 함수 - 그룹 내 비율 함수

percent_rank : 값이 아닌, 순서를 대상으로, 파티션 내에서의 순서별 백분율을 조회함

ntile(n) :  파티션별로 전체 건수를 n등분한 값을 반환한다. n=4이면 4등분한 것 내에서 몇인지 조회.

cume_dist : 파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대한 누적 백분율을 조회, 누적 분포 상에 0~1값을 가짐.

 

window 함수 - 그룹 내 행 순서 함수

first_value : 파티션 내에서 가장 처음 나오는 값 반환, min과 동일한 결과

last_value : 파티션 내에서 가장 마지막에 나오는 값 반환, max와 동일한 결과

lag(컬럼명, 레코드위치차이값) : 이전 행을 가져온다.

lead(컬럼명, 레코드위치차이값, null일 경우 대체값) : 다음(특정 위치의) 행을 가져온다. default는 1이다.

 

windowing 함수의 종류

range : 범위를 지정해줄 때 사용. ex)range between 100 and 200

between a and b : a부터 b까지 윈도우가 적용됨

unbounded preceding : 윈도우의 시작 위치 = 첫 번째 행

unbounded following : 윈도우의 마지막 위치 = 마지막 행

current row : 윈도우 시작 위치가 현재 행

 

테이블 파티션 : db를 사용할 때도 파티션을 사용한다. 대용량의 테이블을 여러개의 데이터 파일에 분리하여 저장하는 것. 물리적으로 분리된 데이터 파일에 저장되어 입력/수정/삭제/조회 성능이 향상되고, 독립적 관리가 가능. 조회 범위를 줄이는 효과가 있어 성능이 향상됨.

 

3가지 방식

range partition : 값의 범위를 기준으로 파티션을 나눠 저장하는 방법. ex) 매출액이 50000이상인 레코드와 미만인 레코드를 별도로 저장. 

list partition : 특정 값을 기준으로 분할. ex) 특정 값이 100일 때와 250일때 각각 다른 데이터파일에 저장

hash partition: 데이터베이스 관리 시스템이 자체적으로 해시함수를 사용해 분할하고 관리하는 방식.

 

계층형 조회:

구조 select col3 from 조직구조 start with col1 is null connect by prior col1 = col2 order siblings by col 

 

위 내용은 복습을 위한 작성 포스트입니다.

출처 : https://www.youtube.com/watch?v=8uP_E6SyiuM&t=16111s