Oracle 분석함수 사용 방법

Oracle분석함수.doc

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	SELECT empno, ename, job, mgr, sal,     RANK() over (partition by job order by sal desc)     as sal_rank_by_job     FROM emp
Result Set

 

Example 2 - Ranking with different boundaries in a single query
SQL	SELECT empno, ename, job, mgr, sal,         RANK() over (partition by job order by sal desc) as by_job,         RANK() over (order by sal desc)  as over_all     FROM emp
Result Set

　

Example 3 - NTILE Function
SQL	SELECT empno, ename, job, mgr, sal,          NTILE(4) over (order by sal desc) as ntile_4     FROM emp
Result Set

　

Example 4 - ROW_NUMBER Function
SQL	SELECT ROW_NUMBER() over (order by sal desc) as num,         empno, ename, job, mgr, sal     FROM emp
Result Set

　

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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   FROM ( SELECT s_productkey, s_regionkey, SUM(s_amount) as sum_s_amount,                   MAX(SUM(s_amount)) OVER (PARTITION BY s_productkey) as max_sum_s_amount               FROM sales                GROUP BY s_productkey, s_regionkey )     WHERE sum_s_amount = max_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) 2000년 10월 10일 - 14일 사이의 각 판매량과 전날(LAG), 다음날(LEAD)과 비교

 

 

 

 

 

 

 

 

 

 

 

 

 

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) 2000년 10월 10일에 남성이 구입한 제품 채널별 판매량과 제품 가격 간의 관계

 

 

* 위의 제공 함수를 이용하여 분산분석표의 SSR, SSE, SST, DF, MSR, MSE, F 값을 구하였다.

 

EX) [분산분석표 : 제곱합 (SSR, SSE, SST), 자유도, 평균제곱합 (MSR, MSE), F 검정통계량]

 

 

 

 

 

 

EX) 표준오차, 결정계수, 수정된 결정계수, 독립변수 평균, 종속변수 평균

 

 

EX) 모수추정 : 절편, 기울기, 표준오차, 결정계수, 수정된 결정계수, 독립변수 평균, 종속변수 평균

 

 

히스토그램 생성 (CASE 문 사용)  -- 사용자 정의 단위

 

EX) Marshal도시에 살고 있는 고객의 신용 제한액을 4개 구간으로 분류. (히스토그램 표현 과정 - 계급구간, 도수)