Analytic Function 2

Analytic functions2

 

 

 

 

RISQL, SQL 확장,Intelligent SQL, Analytic functions

   

SQL이 여러 분야에서 다양하게 사용되긴 하지만, 분석작업을 위한 강력한 기능을 가지고 있지는 않다.

때문에 MOVING AVERAGES, RANKINGS, AND LEAD/LAG COMPARISONS 같은 기본적인 BUSINESS INTELLIGENCE CALCULATION 을 수행하기 위해서는 표준 SQL외에 과도한 프로그램밍 작업을 요구하게 된다.

뿐만 아니라 이러한 작업은 간혹 시스템 성능에 치명적인 장애가 될 수도 있다.

ORACLE8i 8.1.6 에서는 이러한 요구들을 다루기 위해 BUSINESS INTELLIGENCE CALCULATION에 대한 새로운 FUNCTIONS들을 제공하게 된다.

이 FUNCTIONS 들은 분석적인 작업에 유용하기 때문에 ANALYTIC FUNCTIONS 이라고 하며, 현재 SQL 표준에 추가하기 위하여 ANSI에 의하여

검토 중이다.

 

1.RANKING FUNCTIONS RESULT SET에서 VALUE에 대한 RANKS, PERCENTILES, AND N-TILES 등을 계산한다.

 

2.WINDOWING CUMULATIVE AND MOVING AVERAGES를 계산하며 SUM, AVG, MIN, MAX, COUNT, VARIANCE,STDDEV, FIRST_VALUE, LAST_VALUE, AND NEW STATISTICAL FUNCTIONS 과 함께 사용이 가능하다.

 

3.REPORTING MARKET SHARE와 같은 SHARES를 계산하며 SUM, AVG, MIN, MAX, COUNT (with/without DISTINCT), VARIANCE, STDDEV, RATIO_TO_REPORT, AND NEW STATISTICAL FUNCTIONS과 함께 사용이 가능하다.

 

4.LAG/LEAD CURRENT ROW로부터 임의의 위치에 있는 ROW의 VALUE를 찾는 함수로 다른 시간대에 있는 값들을 서로 비교할 때 유리하다.

이들 함수는 Self-join을 사용하지 않아도 동시에 한 ROW 이상을 액세스 할 수 있는 기능을 제공한다.

LAG Function은 현 위치에서 주어진 offset 이전에 있는 ROW에 대한 ACCESS를 제공하고 LEAD Function은 현 위치에서 주어진 offset 이후에 있는 ROW에 대한 ACCESS를 제공한다.

 

5.STATISTICS LINEAR REGRESSION 과 SLOPE,INTERCEPT와 같은 통계정보를 계산한다.

 

 

VERSION : ORACLE 8.1.6 이상

 

ORACLE은 ANALYTIC FUNCTION을 수행하기 위해 기존 SQL PROCESSING에 몇 가지 단계를 추가하여 유연하고 강력한 계산능력을 가질 수 있게 하였다. 그러면 ORACLE이 어떤 순서로 ANALYTIC FUNCTION을 구현하는지 알아보도록 하자.

 

첫째 JOINS, WHERE, GROUP BY 그리고 HAVING 절이 수행된다.

둘째 ANALYTIC FUNCTION이 이용할 수 있는 RESULT SET이 만들어 진다.

셋째 만약 ORDER BY 절을 가지고 있다면, ORDER BY 절이 수행된다.

 

 

1.Result Set Partitions

Analytic Function은 Partition이라고 하는 Rows의 Group으로 Result Set을 나누고 그 partition을 대상으로 연산을 수행하게 된다.

여기에서 Partition이라는 개념은 Table의 Partition과는 다른 의미이며, Analytic Function이 수행되는 집합 단위를 말한다.

Partition은 첫 번째 단계가 모두 수행된 후 생성되며, 하나 이상의 Partition을 가질 수 있다.

 

2.Window Partition에서는 현재의 row에 대해 수행할 연산의 범위를 결정하기 위해 각각의 row를 대상으로 data sliding window를 구성할 수 있다.

이때 window의 크기는 물리적인 row의 수나 논리적인 시간간격에 따라 결정될 수 있다.

Window는 starting row와 ending row를 가지며, 한쪽 혹은 양쪽의 ending row로 이동할 수 있다. 예를 들어 cumulative sum function을 위해 정의된 window는 partition의 첫 번째 row를 starting row로 사용하며,ending row는 partition의 첫 번째 row에서 마지막 row까지 차례로 취하게 된다. 그러나 moving average는 starting 과 ending point가 둘다 이동하기 때문에 일정한 범위가 관리되어야 한다.

 

3.Current Row

Current row는 window의 start와 end를 결정하는 reference point를 말하며,Analytic Function과 함께 수행되는 각각의 연산은 partition의 current row에 그 근거를 두고 있다.

 

 

ANALYTIC FUNCTION, RANK, DENSE_RANK

 

RANK와 DENSE_RANK는 어떤 한 GROUP에서 항목들의 순위를 매길 수 있는 기능을 제공한다.

SYNTAX :

RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )

DENSE_RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )

 

1.RANK의 특징

• 정렬 순서는 ascending이 default이며 descending으로 지정이 가능하다.
• PARTITION BY는 선택항목이며 QUERY의 RESULT SET을 RANK FUNCTION을 적용할 GROUP으로 나눈다.다시 말해 RANK는 GROUP이 바뀔 때 마다 새로운 SET에 대하여 연산을 수행하게 된다.
• PARTITION BY 절이 생략되면 RANK는 QUERY의 전체 RESULT SET에 적용된다.
• ORDER BY는 순위가 매겨지는 기준을 정의하는 절이다.
• NULLS FIRST나 NULL LAST는 ORDER SEQUENCE에서 NULL의 위치를 나타낸다.

RANK와 DENSE_RANK의 차이는 순위가 같을 때 RANK는 ranking sequence에 gap을 허용하지만 DENSE_RANK는 gap을 허용하지 않는다.

 

2.RANKING ORDER

다음은 [ASC | DESC]이 어떻게 ranking order를 변화시키는지를 보여주는 사례이다.

 

 

SELECT deptno,empno, sal,
RANK() OVER (ORDER BY sal) AS asc_rank,
RANK() OVER (ORDER BY sal desc nulls last) as desc_rank
FROM emp;
 

DEPTNO	EMPNO	SAL	ASC_RANK	DESC_RANK
10	7839	5000	14	1
20	7788	3000	12	2
20	7902	3000	12	2
20	7566	2975	11	4
30	7698	2850	10	5
10	7782	2450	9	6
30	7499	1600	8	7
30	7844	1500	7	8
10	7934	1300	6	9
30	7521	1250	4	10
30	7654	1250	4	10
20	7876	1100	3	12
30	7900	950	2	13
20	7369	800	1	14

 

3.Ranking on multiple Expressions

Result Set에서 Ranking을 매기고자 하는 value가 동률을 이룰 때 이것을 해결하기 위해 다른 expression을 사용할 수 있다.

SELECT deptno,empno, sal,  
       RANK() OVER (ORDER BY sal desc,empno nulls last) as desc_rank  
  FROM emp
 WHERE DEPTNO = 30;
 

DEPTNO	EMPNO	SAL	DESC_RANK
30	7698	2850	1
30	7499	1600	2
30	7844	1500	3
30	7521	1250	4
30	7654	1250	5
30	7900	950	6

 

4.Group Ranking

PARTITION BY 옵션에 Group expression을 지정하면 dataset 은 group으로 나누어져 Group 이내에서 Ranking Function이 수행된다.

SELECT deptno,empno, sal,  
       RANK() OVER (PARTITION BY deptno
                    ORDER BY sal desc,empno nulls last) as desc_rank  
  FROM emp;
 

DEPTNO	EMPNO	SAL	DESC_RANK
10	7839	5000	1
10	7782	2450	2
10	7934	1300	3
20	7788	3000	1
20	7902	3000	2
20	7566	2975	3
20	7876	1100	4
20	7369	800	5
30	7698	2850	1
30	7499	1600	2
30	7844	1500	3
30	7521	1250	4
30	7654	1250	5
30	7900	950	6

 

5.CUME_DIST Function

일련의 값에 대하여 지정된 값이 가지는 위치를 계산하는 FUNCTION으로 백분율로 표시되며 값의 범위는 0보다 크고 1보다 같거나 작은 값들로 표시된다.

CUME_DIST(x) =

number of values (different from, or equal to, x) in S coming before x in the specified order/ N

여기에서 N : 집합 S의 크기

Its syntax is:

CUME_DIST() OVER
([PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

SELECT deptno,empno, sal,
CUME_DIST() OVER (PARTITION BY deptno
ORDER BY sal nulls last) as desc_rank
FROM emp;

 

DEPTNO	EMPNO	SAL	RANK
10	7934	1300	0.33
10	7782	2450	0.66
10	7839	5000	1
20	7369	800	0.2
20	7876	1100	0.4
20	7566	2975	0.6
20	7788	3000	1
20	7902	3000	1
30	7900	950	0.16
30	7521	1250	0.5
30	7654	1250	0.5
30	7844	1500	0.66
30	7499	1600	0.83
30	7698	2850	1

 

6.PERCENT_RANK

CUME_DIST Function과 매우 유사하지만 row counts를 사용하지 않고 rank value를 사용한다.

PERCENT_RANK of a row is calculated as:
(rank of row in its partition - 1) / (number of rows in the partition - 1)

PERCENT_RANK returns values in the range zero to one. The first row will have a PERCENT_RANK of zero.

Its syntax is:
PERCENT_RANK() OVER
([PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

7.ROW_NUMBER

Partition 이내에서 각각의 Row에 대해 unique 한 숫자를 부여한다.

syntax:
ROW_NUMBER() OVER
([PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])

SELECT deptno,empno, sal,
row_number() OVER (PARTITION BY deptno
ORDER BY sal nulls last) as rank
FROM emp;

 

DEPTNO	EMPNO	SAL	RANK
10	7934	1300	1
10	7782	2450	2
10	7839	5000	3
20	7369	800	1
20	7876	1100	2
20	7566	2975	3
20	7788	3000	4
20	7902	3000	5
30	7900	950	1
30	7521	1250	2
30	7654	1250	3
30	7844	1500	4
30	7499	1600	5
30	7698	2850	6

 

8.기타

위에서 기술한 Function 외에 RANK 분류에서 지원되는 기능은 TOP_N, BOTTOM_N, NTILE 등이 있으므로 참고하기 바란다.

 

 

 

[ Analytic Function : Windowing ] 

 

 

ANALYTIC FUNCTION,PARTITION, WINDOW, CURRENT ROW,Lag, Lead, RATIO

 

기존 RDBMS에서는 다른 시간대에 있는 값들을 참조하기 위한 포인터를 가지고 있지 않다. 이것은 집합에서 모든 요소가 동등한 자격과 위치를 가지며 서로를 참조하기 위한 포인터를 가지고 있지 않기 때문이다.

Analytic Function의 Lag와 Lead는 다른 시간대에 있는 값들을 비교하기 위한 포인터를 제공하기 때문에 이러한 연산을 수행할 경우 아주 유리하다.

(<value expression1>, [<offset> [, <default>]]) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])

<offset> 은 옵션이며 지정하지 않으면 1 이 default로 사용된다.

다음 SQL은 Lag와 Lead Function을 사용하여 자신이 소속한 부서에서 자기보다 월급이 많은 사원의 급여와 자기보다 월급이 적은 사원의 급여를 참조하기 위한 것이다.

select deptno,empno,sal,
       lag(sal,1) over (order by detpno,sal) as sal_lag,
       lead(sal,1) over (order by deptno,sal) as sal_lead
  from emp

<< Result Set >>

DEPTNO	EMPNO	SAL	SAL_LAG	SAL_LEAD
10	7934	100		2450
10	7782	2450	100	5000
10	7839	5000	2450	800
20	7369	800	5000	1100
20	7876	1100	800	2975
20	7566	2975	1100	3000
20	7788	3000	2975	3000
20	7902	3000	3000	950
30	7900	950	3000	1250
30	7521	1250	950	1250
30	7654	1250	1250	1500
30	7844	1500	1250	1600
30	7499	1600	1500	2850
30	7698	2850	1600

 

 

ANALYTIC FUNCTION,PARTITION, WINDOW, CURRENT ROW,Reporting,RATIO

 

Query가 처리된 후 한 컬럼의 평균값이나 결과 집합의 수를 구하는 집계 값은 한 partition 이내에서는 쉽게 계산될 수 있을 뿐 아니라 다른 reporting functions에서 이용할 수도 있다. Reporting functions은 한 partition 이내의 모든 row에 대하여 같은 집계 값을 반환한다.

([ALL | DISTINCT] {<value expression1> | *})
OVER ([PARTITION BY <value expression2>[,...]])
 

• asterisk(*)는 count(*)에서만 허용된다
• distinct는 해당하는 aggregate function이 허용할 때만 지원된다.
• <value expression1>과 <value expression2>는 컬럼 참조 혹은 집계를 포함하는 유효한 표현식이 사용될 수 있다.
• partition by 절은 다른 analytic function과 같은 의미를 갖는다.

Reporting function은 SELECT절 혹은 ORDER BY절에만 사용할 수 있으며 한 데이터 블록에서 데이터의 다중 통행이 가능하다.

다음은 각 부서별로 가장 많은 월급이 지급되는 직업을 구하기 위하여 reporting function을 사용한 사례이다.

select *
  from (
         select deptno,job,sum(sal) as sum_sal,
                max(sum(sal)) over(partition by deptno) as max_sum_sal
           from emp a
          group by deptno,job
       )
 where sum_sal = max_sum_sal

<< RESULT SET >>

Deptno	Job	SUM_SAL	MAX_SUM_SAL
10	PRESIDENT	5000	5000
20	ANALYST	6000	6000
30	SALESMAN	4350	4350

 

RATIO_TO_REPORT

어떤 한 집합의 합계에 대하여 각각의 비율을 구하는 report function으로 다른 report function과 syntax와 의미가 비슷하다.

RATIO_TO_REPORT (<value expression1>) OVER ([PARTITION BY <value expression2>[,...]])
 

• <value expression1>과 <value expression2>는 컬럼 참조 혹은 집계를 포함하는 유효한 표현식이 사용될 수 있다.
• partition by 절은 다른 analytic function과 같은 의미를 갖는다.

다음은 부서 전체 월급에 대하여 직업별 비율을 구하기 위하여 RATIO_TO_REPORT를 사용한 사례이다.

select deptno,job,sum(sal) as sum_sal,
       sum(sum(sal)) over(partition by deptno) sum_total,
       ratio_to_report(sum(sal)) over(partition by deptno) as max_sum_sal
  from emp a
 group by deptno,job

<< RESULT SET >>

DEPTNO	JOB	SUM_SAL	SUM_TOTAL	MAX_SUM_SAL
10	CLERK	1300	8750	0.15
10	MANAGER	2450	8750	0.28
10	PRESIDENT	5000	8750	0.57
20	ANALYST	6000	10875	0.55
20	CLERK	1900	10875	0.18
20	MANAGER	2975	10875	0.27
30	CLERK	950	9400	0.10
30	MANAGER	4100	9400	0.44
30	SALESMAN	4350	9400	0.46