Oracle DBMS에서 제공하는 분석함수
1. 순위(Ranking)
- 레코드의 순위와 다른 레코드들의 순위와의 비교 계산 (순위, 백분위수, n-분위수 계산)
- 대표적 분석 예 : 지역별 판매실적 상위 5명의 영업사원
[RANK] : RANK () OVER ( [query_partition_clause] order_by_clause)
=> An ordered ranking of rows starting with a rank of one
EX.) 2000년 9월과 10월 미국에서의 채널별 판매액 순위 (판매액의 오름차순, 내림차순)
상위 3 채널 (where 문에 CUSTOM_RANK <= 3; 추가 )
[RANK와 DENSE_RANK]
: RANK () OVER ( [query_partition_clause] order_by_clause)
DENSE_RANK () OVER ( [query_partition_clause] order_by_clause)
=> It handles tie values Next rank after a tie, using DENSE_RANK = tied rank + 1
EX) 채널별, 월별(2000년 9월과 10월) 판매액 순위(일반순위, 공백수가 없는 순위)
[Cube Group RANKING] --> 분할표에 이용
PERCENT_RANK -- 그룹 수에 대한 값의 순위 퍼센트를 계산, 범위 : 0 < PERCENT_RANK < 1
PERCENT_RANK () OVER ([query_partition_clause] order_by_clause )
=> It's very similar to CUME_DIST, but it uses rank values rather than row counts in its numerator
EX) 2000년 7, 8, 9월의 월별 결과 중 판매액의 순위 퍼센트
CUME_DIST -- 누적 분포 (전체 중 특정한 값의 위치를 계산), 범위 : 0 < CUME_DIST < 1
CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )
=> Computes the relative position of a value relative to the other values in its partition
EX) 2000년 7, 8, 9월의 월별 결과 중 판매액의 위치
NTILE -- 분위수 계산
NTILE (expr) OVER ( [query_partition_clause] order_by_clause )
=> It allows easy calculation of tertiles, quartiles, deciles and other common summary statistics
EX) [ 1999년도 남성의 월별 판매액 사분위수 (N=4) ]
ROW_NUMBER -- 행의 수 계산 (파티션 내에서 각 행에 대해 1로 시작하여 정렬되어 정의되는 유일한 수를 할당)
ROW_NUMBER () OVER ( [query_partition_clause] order_by_clause )
=> It assigns a number, in the sequence defined by the ORDER BY clause, to each row within the partition
EX) 채널별, 2000년 9월과 10월의 월별 내림차순 판매액의 행 수
Example 1 - Rank Function | |
SQL |
|
Result |
|
Example 2 - Ranking with different boundaries in a single query | |
SQL |
|
Result |
|
Example 3 - NTILE Function | |
SQL |
|
Result |
|
Example 4 - ROW_NUMBER Function | |
SQL |
|
Result |
|
2. 윈도우(Window)
- 조건에 따른 행들의 집합(Window)에 대한 연산을 수행하고 각 행에 대한 값 반환
(누적, 이동, 중심 집합들 계산)
SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, ROWS, RANGE, BETWEEN AND
- 각각의 함수는 Window사이즈에 따라 최적화된 연산을 수행
- 각 Window 함수는 Ranking Family와 같은 내부적인 순위 연산절을 가진다.
Define Window Size
사용자의 필요에 따라 Window를 정의하는 몇가지 방법이 있다
- Physical : 행들의 항목으로 표현 ( ex. 현재의 행에서 뒤로 10개의 행까지..)
- Time Interval : 시간의 구간을 이용
- Value Range : 자료의 값을 이용
- 특정 주식의 13주 이동평균 주가
[누적합] : ROWS UNBOUNDED PRECEDING
EX) [고객 ID가 6380, 6510인 1999년 분기별 총 판매량과 누적 판매량]
[이동평균] : ROWS 2 PRECEDING (시간에 근거 - 시계열)
EX) 현재 달과 지난 두 달간의 판매량 이동평균
[중심평균] : 현재 행이 중심이 된다. (예. 전날과 다음날과의 평균)
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING
Ex) 1999년 6380, 6510인 고객별 전날부터 다음날까지의 판매량 평균
Ex) 각 부서별 평균급여와 한 직원의 급여를 비교
Example 7 - Reporting Aggregate function |
select s_productkey, s_regionkey, sum_s_amount |
3. 리포팅(REPORTING)
- 공유 계산 (계산된 결과에서 다시 계산.) (전체 Window에 대한 연산이나 현재 행을 제외한 연산 수행)
- 전체 Window에 대한 연산이나 현재 행을 제외한 연산을 할 수 있게 하므로 Join처리비용을 절감
- 전국 매출 대비 특정 지역 매출의 비중
RATIO_TO_REPORT : RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )
- 비율값을 출력
EX) 채널별 평균급여와 각 채널별 평균급여 비율
4. 비교(LAG/LEAD)
- 현재 행으로부터 지정한 수 만큼 다른 행에 접근하여 값 검색
- 이를 이용하면 Self Join의 사용이 불필요하다 -> 상당한 Performance 향상
- 전년 대비 지역별 올해 매출
LAG : 현재 위치보다 더 앞선 행 접근
LEAD함수는 현재 위치 이후 주어진 행 접근
{LAG | LEAD} ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
EX)
5. 역 백분위수(Inverse Percentile Functions)
- 데이터 집합 안에서 특정한 백분위수에 해당하는 값 계산
- 부서별 상위 30%에 해당하는 월급 액수
[CUME DIST] : RANK () OVER ( [query_partition_clause] order_by_clause) --> 누적분포
EX) ['Marshal'도시에 사는 고객의 신용카드 제한액에 대한 누적분포함수 ]
5. 가상순위와 분포(Hypothetical Rank and Distribution)
- 특정한 데이터집합에 삽입된 행의 순위 또는 백분위수 계산
- 5만불 정도면 연봉 순위가 얼마가 되나
RANK(50) -
PERCENT_RANK(50) -
CUME_DIST(50) -
EX) 50달러인 Sweater 제품의 범주별 순위, 퍼센트 순위, 누적순위 - 50달러이면 제품 가격 순위가 어떻게 되나?
6. 선형회귀분석(Linear Regression)
- 선형 회귀식과 다른 통계량들(기울기, 절편 등) 계산
- 근무년수와 월급의 상관 관계
§ REGR_SLOPE(Y, X) : 회귀직선의 기울기 계산
§ REGR_INTERCEPT : 회귀직선의 절편 계산
§ REGR_COUNT : 회귀선 적합에 사용되는 수
§ REGR_R2 : 결정계수 (R-Square)
§ REGR_AVGX : X의 평균
§ REGR_AVGY : Y의 평균
§ REGR_SXX, REGR_SXY, REGR_SYY : Sxx, Sxy, Syy
EX)
* 위의 제공 함수를 이용하여 분산분석표의 SSR, SSE, SST, DF, MSR, MSE, F 값을 구하였다.
EX) [분산분석표 : 제곱합 (SSR, SSE, SST), 자유도, 평균제곱합 (MSR, MSE), F 검정통계량]
EX) 표준오차, 결정계수, 수정된 결정계수, 독립변수 평균, 종속변수 평균
EX) 모수추정 : 절편, 기울기, 표준오차, 결정계수, 수정된 결정계수, 독립변수 평균, 종속변수 평균
히스토그램 생성 (CASE 문 사용) -- 사용자 정의 단위
EX) Marshal도시에 살고 있는 고객의 신용 제한액을 4개 구간으로 분류. (히스토그램 표현 과정 - 계급구간, 도수)