데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

앞서 소개된 Analytic Function의 Ranking Family의 소개 및 활용사례에 이어 본 연재에서는 Aggregate Family(Reporting)에 대한 소개 및 활용사례에 대해 설명할 것이다. 활용사례에서는 Analytic Function을 사용해서 쿼리를 한 경우와 사용하지 않고 쿼리를 한 경우를 통해 사용하지 않은 경우 어떤 비효율이 발생되고 사용했을 경우 어떤 장점이 있는지를 살펴볼 기회를 가질 수 있을 것이다.

1. Window Aggregate Family (Reporting) 개념

① 윈도우 집계 유형 (Window Aggregate Family)

윈도우 집계 함수는 윈도우를 근간으로 하여 정렬된 로우들의 집합과 그 각각의 로우들에 대한 집계 값을 반환한다. 이 함수들은 집계 함수(SUM, COUNT, MAX, MIN 등등)의 파티션에 속하는 로우들의 Sliding Window에 대한 계산을 수행하기 위해 윈도우 문법(Window Syntax)을 추가하여 확장한 형태이다.

② 보고용 집계 유형 (Reporting Aggregate Family)

한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해 분석작업을 하고자 하는 경우가 많다.

예를 들면, 한 사원의 급여와 해당 부서의 평균 급여를 비교하고자 하는 경우나, 그 사원의 급여를 제외한 해당 부서의 평균 급여를 알고자 할 때, 보고용 집계 유형은 셀프 조인을 할 필요 없이 다른 집합 레벨에 대한 집계 값을 계산하여 반환한다.

한 그룹에 대해 하나의 집계 값을 반환하는 집계 함수와 다르게 보고용 집계 함수 (Reporting Aggregate Function)는 윈도우 레벨에서 작업한다. 이 함수는 윈도우안의 모든 로우에 대해 같은 집계 값을 반환한다.

보고용 집계 함수는 전체 윈도우에 대한 집계 값을 반환하거나 해당 로우를 제외한 전체 윈도우의 집계 값을 계산하여 반환한다.

이 함수들의 대부분은 윈도우 집계 함수(Window Aggregate Function)와 유사하고, 비슷한 기능을 수행한다.

2. Window Aggregate Function (Reporting)의 Syntax 설명

여기서는 Window Aggregate Function의 기본적인 문법에 대한 설명을 한다. 먼저 Analytic Function의 집계함수 종류를 살펴보면 기존에 사용하였던 모든 집계 함수(SUM,COUNT,AVG,MIN,MAX,STDDEV,VARIANCE) 들을 윈도우 집계 함수(Window Aggregate Function)로 사용할 수 있다.

새로운 기하 함수 (Regression Function - VAR_SAMP,VAR_POP,STDDEV_SAMP,STDDEV_POP 등등) 들을 사용할 수 있다. 좀 더 많은 Analytic 함수는 Oracle Manual을 참고하기 바란다.

《 Syntax 》

([ALL | DISTINCT] { | *})

OVER ([PARTITION BY [,...] ]

[ORDER BY [,...] ] [Windowing_Clause])

① Analytic Function

<value_expr1> 에는 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있다. Analytic Function의 아규먼트는 0에서 3개까지 사용 가능하고 Asterisk(*)는 COUNT(*)에서만 허용되며 DISTINCT는 해당 집계 함수가 허용할 때만 지원된다.

② OVER analytic_clause

해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로써 FROM, WHERE, GROUP BY와 HAVING구 이후에 계산되어 진다. SELECT 구 또는 ORDER BY 구에 Analytic Function을 사용할 수 있다.

ⓐ PARTITION BY 구

<value_expr2> 에는 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식에 의한 그룹으로 쿼리의 결과를 파티션한다. 이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다.

ⓑ ORDER BY 구

<value_expr3> 에는 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.

Windowing 구의 예약어

CURRENT ROW - 윈도우의 시작 위치 또는 마지막 위치가 현재 로우임을 지시하는 예약어

UNBOUNDED PRECEDING - 윈도우의 시작 위치가 Partition의 첫 번째 로우임을 지시하는 예약어

UNBOUNDED FOLLOWING - 윈도우의 마지막 위치가 Partition의 마지막 로우임을 지시하는 예약어

◎ Physical Window : Physical Window Size는 rows로 환산하여 표현한다.

◎ Logical Window

Time Interval : Logical Window Size는 Time Interval로 환산하여 표현한다.

Value Range : Logical Window Size는 정렬된 순서에서 Current Value와 이전 value들과의 차로 환산하여 표현한다.

좀 더 다양한 윈도우 구(Windowing_Clause)의 의미는 다음장에서 살펴보기로 한다.

3. Windowing 구의 분류 및 의미

각 윈도우의 크기는 해당 파티션의 크기를 넘을 수 없고, 윈도우 구(Windowing_Clause)에 따라 해당 파티션 내에서 윈도우의 크기가 유기적으로 결정된다. 여기서 Physical Window와 Logical Window(Time Interval, Value Range)에 의한 분류 및 윈도우 구에 의해 유기적인 크기가 정해지는 Cumulative, Moving, Centered 형태의 분류에 따른 결과의 차이에 대해 살펴볼 것이다. 끝으로 Logical Window의 경우 PARTITION BY 구와 ORDER BY 구의 표현식의 결합에 의해 로우가 정렬되어 질때 정렬이 Unique한 정렬인지 Non Unique한 정렬인지에 따라 결과가 어떻게 다른지를 살펴볼 것이다.

<주1> 아래의 도표는 위의 내용들을 설명하기 위해 사용되는 데이터임.

고객번호

판매일자

판매금액

100

20020301

1000

100

20020302

130

100

20020303

1500

100

20020304

900

100

20020304

300

100

20020305

2300

200

20020301

500

200

20020302

250

200

20020303

1000

200

20020304

1500

200

20020305

3500

200

20020305

200

① Cumulative Aggregate Function

각 파티션의 시작 위치가 윈도우의 시작 위치가 되고 현재 로우의 물리적 또는 논리적 위치가 윈도우의 종료 위치가 되어 여기에 해당 집계함수를 적용하는 형태.

SELECT CUSTCODE, SALEDATE, SALE_AMT,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT1,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT2,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE,'YYYYMMDD')

RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '0' DAY FOLLOWING) ACC_AMT3,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE) ACC_AMT4

FROM SALE_TBL;

위의 쿼리문에서 파란색의 글자가 Windowing_Clause를 의미하고 ACC_AMT1은 Physical 윈도우에 의해, ACC_AMT2는 Logical 윈도우중에서 Value Range에 의해, ACC_AMT3은 Logical 윈도우 중에서 Time Interval에 의해, ACC_AMT4는 windowing_clause를 생략했지만 ACC_AMT2와 같은 의미를 가진다.

고객번호

판매일자

판매금액

ACC_AMT1

ACC_AMT2

ACC_AMT3

ACC_AMT4

100

20020301

1000

100

20020302

130

1130

100

20020303

1500

2630

100

20020304

900

3530

3830

100

20020304

300

3830

100

20020305

2300

6130

200

20020301

500

200

20020302

250

750

200

20020303

1000

1750

200

20020304

1500

3250

200

20020305

3500

6750

6950

200

20020305

200

6950

위의 도표에서 고객번호가 '100'이고 판매일자가 '20020304', 판매금액이 900원인 로우를 기준으로 보면 검은 화살표는 파티션, 빨간 화살표는 물리적인 윈도우 크기(Physical Window Size), 파란 화살표는 논리적인 윈도우 크기(Logical Window Size)를 의미한다. 여기서 주의해서 봐야할 것은 논리적인 윈도우의 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 고객번호, 판매일자 순으로 정렬되므로 고객번호 '100', 판매일자 '20020304' 기준으로 보면 판매액이 900원 300원이 발생했지만 정렬순위가 같으므로 논리적으로는 1200원으로 처리된다.

② Moving Aggregate Function

각 파티션내의 윈도우의 종료위치가 현재 로우의 물리적 또는 논리적 위치가 윈도우의 종료 위치가 되고 이 종료 위치를 기준으로 OFFSET을 적용하여 시작위치가 결정되고 여기에 해당 집계함수를 적용하는 형태.

SELECT CUSTCODE, SALEDATE, SALE_AMT,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT1,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE,'YYYYMMDD')

RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT2,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE,'YYYYMMDD')

RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW) ACC_AMT3

FROM SALE_TBL;

위의 쿼리문에서 파란색의 글자가 Windowing_Clause를 의미하고 ACC_AMT1은 Physical 윈도우에 의해, ACC_AMT2는 Logical 윈도우중에서 Value Range에 의해, ACC_AMT3은 Logical 윈도우 중에서 Time Interval에 대한 의미를 가진다.

고객번호

판매일자

판매금액

ACC_AMT1

ACC_AMT2

ACC_AMT3

100

20020301

1000

100

20020302

130

1130

100

20020303

1500

1630

100

20020304

900

2400

2700

100

20020304

300

1200

2700

100

20020305

2300

2600

3500

200

20020301

500

200

20020302

250

750

200

20020303

1000

1250

200

20020304

1500

2500

200

20020305

3500

5000

5200

200

20020305

200

3700

5200

③ Centered Aggregate function

각 파티션내의 윈도우의 크기가 현재 로우의 물리적 또는 논리적 위치를 기준으로 주어진 오프셋(Offset)을 적용하여 윈도우의 시작위치와 종료위치가 결정되고 여기에 해당 집계함수를 적용하는 형태.

SELECT CUSTCODE, SALEDATE, SALE_AMT,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT1,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE,'YYYYMMDD')

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT2,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE,'YYYYMMDD')

RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) ACC_AMT3

FROM SALE_TBL;

고객번호

판매일자

판매금액

ACC_AMT1

ACC_AMT2

ACC_AMT3

100

20020301

1000

1130

100

20020302

130

2630

100

20020303

1500

2530

2830

100

20020304

900

2700

5000

100

20020304

300

3500

5000

100

20020305

2300

2600

3500

200

20020301

500

750

200

20020302

250

1750

200

20020303

1000

2750

200

20020304

1500

6000

6200

200

20020305

3500

5200

200

20020305

200

3700

5200

④ Window Size가 Logical Offset으로 결정될 경우 Ordering이 Unique할때와 Non Unique할 때의 차이점.

SELECT CUSTCODE, SALEDATE, SALE_AMT,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT1,

SUM(SALE_AMT) OVER (PARTITION BY CUSTCODE ORDER BY SALEDATE, SALE_AMT

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT2

FROM SALE_TBL;

위의 쿼리문에서 빨간색의 글자가 데이터 정렬에 기준이 되는 PARTITION BY와 ORDER BY구이고 파란색의 글자가 Windowing_Clause를 의미하고 ACC_AMT1과 ACC_AMT2은 둘 다 Value Range에 의한 논리적 윈도우의 결과를 반환하지만, ORDER BY의 표현식을 다르게 하였다. ACC_AMT1의 데이터 정렬은 CUSTCODE, SALEDATE 기준으로 수행되고 데이터와 비교해 보면 정렬의 순서가 같은 데이터 들이 있다. 그러나 ACC_AMT2는 데이터 정렬이 CUSTCODE, SALEDATE, SALE_AMT 기준으로 수행되고 데이터와 비교해 보면 정렬 순서가 Unique하게 됨을 알 수 있다.

고객번호

판매일자

판매금액

ACC_AMT1

ACC_AMT2

100

20020301

1000

100

20020302

130

1130

100

20020303

1500

2630

100

20020304

300

3830

2930

100

20020304

900

3830

100

20020305

2300

6130

200

20020301

500

200

20020302

250

750

200

20020303

1000

1750

200

20020304

1500

3250

200

20020305

200

6950

3450

200

20020305

3500

6950

위의 도표에서 고객번호가 '100'이고 판매일자가 '20020304', 판매금액이 300원인 로우를 기준으로 보면 검은 화살표는 파티션, 빨간 화살표는 ACC_AMT1의 논리적인 윈도우 크기, 파란 화살표는 ACC_AMT2의 논리적인 윈도우 크기를 (Logical Window Size)를 의미한다.

여기서 주의해서 봐야할 것은 ACC_AMT1의 논리적인 윈도우의 크기가 ACC_AMT2의 논리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 ACC_AMT1의 경우 데이터가 고객번호, 판매일자 순으로 정렬되므로 고객번호 '100', 판매일자 '20020304' 기준으로 보면 판매액이 300원 900원이 발생했지만 정렬순위가 같으므로 논리적으로는 1200원으로 처리된다. 그러나 ACC_AMT2의 경우 데이터가 고객번호, 판매일자, 판매금액 순으로 정렬되므로 고객번호 '100', 판매일자 '20020304' 이고 판매액이 300원인 경우가 판매액이 900원인 경우 보다 정렬우선 순위가 앞서므로 각각의 로우가 별개로 처리됨을 알 수 있다.

위의 도표의 결과에서 알 수 있듯이 논리적 윈도우 구를 지정하게 되면 정렬의 기준이 되는 PARTITION BY와 ORDER BY구에 의한 정렬의 기준이 Unique한 경우와 Non Unique 한 경우 처리 결과가 다름을 알 수 있다.

다음 시간엔 Aggregate Family(Reporting)의 소개 및 활용사례 두 번째 내용을 살펴보도록 하겠습니다. - 편집자-

이번 기사는 지난 [엔코아 송해창 책임]제3회 Aggregate Family(Reporting)의 소개 및 활용사례(1)에 이어 두번째로 활용사례 중심으로 알아보자.

4. Window Aggregate 및 Reporting Aggregate Function의 활용사례

먼저 Window Aggregate Family의 활용사례 두 가지와 Reporting Aggregate Family 활용사례 두 가지에 대해 살펴보기로 한다

① Window Aggregate Function 활용사례

1) Window Aggregate Family ? SUM()

다음과 같이 데이터가 발생하였을 때 일별 판매액 누계를 구하는 쿼리문을 Analytic Function을 사용하지 않은 경우와 사용한 경우를 살펴보자.

<주2> 도표에서 회색으로 보이는 부분은 원래 데이터이고 노란색으로 보이는 부분은 원하는 결과임.

판매일자

판매액

판매누계액

20020301

1000

20020302

130

1130

20020303

1500

2630

20020304

900

3530

20020305

2300

5830

◎ Analytic Function을 사용하지 않은 경우.

먼저, 일별 판매액 누계를 구하고자 하면 다음과 같이

2002년03월01일 기준 : 2002년03월01일 판매액

2002년03월02일 기준 : 2002년03월01일 판매액 + 2002년03월02일 판매액

2002년03월03일 기준 : 2002년03월01일 판매액 + 2002년03월02일 판매액 + 2002년03월03일 판매액

형태의 데이터가 필요하다.

이런 형태의 중간집합을 만들고자 할 때 데이터 복제(COPY_T 이용-대용량 데이타베이스2권 참조)를 통하여 필요한 집합을 만들고 이 집합에 대하여 GROUPING을 하여 최종 결과를 도출한다. 그러나 이 경우는 데이터 복제가 발생하고 그룹핑에 대한 비효율이 존재한다.

즉, 아래와 같은 쿼리문을 작성하여 일자별 판매액 누계를 구할 수 있다.

SELECT MIN(DECODE(T1.NO,V1.RCNT,V1.SALEDATE)) SALEDATE,

SUM(V1.SALE_AMT) SALE_AMT

FROM (SELECT SALEDATE*-1||'' SALEDATE,

SALE_AMT,

ROWNUM RCNT

FROM (SELECT SALEDATE*-1 SALEDATE,

SUM(SALE_AMT) SALE_AMT

FROM SALE_TBL

GROUP BY SALEDATE*-1)) V1, COPY_T T1

WHERE V1.RCNT >= T1.NO

GROUP BY T1.NO

ORDER BY SALEDATE;

◎ Analytic Function을 사용한 경우.

SELECT SALEDATE,

SUM(SALE_AMT) OVER (ORDER BY SALEDATE) SALE_AMT

FROM SALE_TBL;

2) Window Aggregate Family ? MAX()

다음과 같이 데이터가 발생하였을 때 상태가 ‘신규’,’명의변경’,’기기변경’인 경우의 시작일과 상태코드를 상태변경일과 상태변경코드로 관리하고 그 이외의 상태는 ‘신규’, ‘명의변경’,’기기변경’의 상태 변경일과 상태변경코드로 상속을 받는 형태의 결과를 구하는 쿼리문을 Analytic Function을 사용하지 않은 경우와 사용한 경우를 살펴보자.

<주3> 도표에서 회색으로 보이는 부분은 원래 데이터이고 노란색으로 보이는 부분은 원하는 결과임.

고객번호

이름

시작일

종료일

상태코드

상태명

상태변경일

상태변경코드

100

홍길동

19980102

19990302

개통

19980102

100

홍길동

19990302

19990421

정지

19980102

100

홍길동

19990421

20000823

복구

19980102

100

차인표

20000823

20011002

명의변경

20000823

100

홍길동

20001002

99991231

명의변경

20001002

200

이순신

20000401

20000523

개통

20000401

200

이순신

20000523

20000823

정지

20000401

200

이순신

20000823

20010418

복구

20000401

200

김유신

20010418

20010827

명의변경

20010418

200

김유신

20010827

20010911

정지

20010418

200

김유신

20010911

20011020

복구

20010418

200

김유신

20011020

20011215

기기변경

20011020

200

이순신

20011215

99991231

명의변경

20011215

◎ Analytic Function을 사용하지 않은 경우.

‘신규’, ‘명의변경’,’기기변경’인 경우만 선택하여 다음과 같이 선분이력을 먼저 만든다.

고객번호

시작일

종료일

상태코드

100

19980102

20000823

100

20000823

20001002

100

20001002

99991231

200

20000401

20010418

200

20010418

20011020

200

20011020

20011215

200

20011215

99991231

그런 후 아래의 쿼리문과 같이 위의 도표와 같이 만들어진 중간 집합으로 원래의 테이블과 BETWEEN JOIN을 하여 상태 변경일을 상속 받는다. 아래 쿼리문에서 알 수 있듯이 같은 테이블을 여러번 Access하고 그룹핑을 한 후 자기 자신과 조인을 해야 하는 비효율이 발생한다.

<주4> 아래 보라색으로 칠해진 쿼리가 위의 도표처럼 해당 상태에 대한 선분이력을 만드는 쿼리임.

SELECT T1.CONTNO, T1.NAME, T1.SDATE, T1.EDATE, T1.STATE_CODE, T1.STATE_NAME,

V3.SDATE CHG_DATE, V3.STATE_CODE CHG_CODE

FROM (SELECT /*+ ORDERED USE_MERGE(V1 V2) */

V1.CONTNO, V1.SDATE,

DECODE(V2.SDATE,NULL,'99991231',V2.SDATE) EDATE,

V1.STATE_CODE

FROM (SELECT CONTNO, SDATE, STATE_CODE, ROWNUM RCNT

FROM (SELECT CONTNO, SDATE, STATE_CODE

FROM CONT_TBL

WHERE STATE_CODE IN ('30','20','10')

GROUP BY CONTNO, SDATE, STATE_CODE)) V1,

(SELECT CONTNO, SDATE, STATE_CODE, ROWNUM-1 RCNT

FROM (SELECT CONTNO, SDATE, STATE_CODE

FROM CONT_TBL

WHERE STATE_CODE IN ('30','20','10')

GROUP BY CONTNO, SDATE, STATE_CODE)) V2

WHERE V2.CONTNO(+) = V1.CONTNO

AND V2.RCNT(+) = V1.RCNT) V3, CONT_TBL T1

WHERE T1.CONTNO = V3.CONTNO

AND T1.SDATE >= V3.SDATE

AND T1.SDATE < V3.EDATE;

◎ Analytic Function을 사용한 경우.

Analytic Function을 활용하면 간단하게 해결할 수 있지만 약간의 데이터 조작이 필요하다. 상태변경일의 경우는 변경일이 계속 커지는 경우이기 때문에 ‘신규’, ‘명의변경’,’기기변경’의 경우에만 시작일을 그대로 보존하고 나머지 상태는 NULL로 치환한 후 MAX()함수를 가지고 Analytic Function을 적용하면 원하는 결과를 얻을 수 있지만, 상태 코드의 경우는 크기가 서로 혼재되어 있으므로 약간의 조작이 더 필요하다. 후자의 경우는 ROW_NUMBER()를 이용하여 MAX()에 대한 크기를 보정해 주어야 한다. 이해를 돕기 위해 아래 데이터에 대한 도표를 참고 하기 바란다.

고객번호

이름

시작일

종료일

상태코드

상태명

상태변경일

상태변경코드

T_RANK

100

홍길동

19980102

19990302

개통

19980102

100

홍길동

19990302

19990421

정지

100

홍길동

19990421

20000823

복구

100

차인표

20000823

20011002

명의변경

20000823

100

홍길동

20001002

99991231

명의변경

20001002

200

이순신

20000401

20000523

개통

20000401

200

이순신

20000523

20000823

정지

200

이순신

20000823

20010418

복구

200

김유신

20010418

20010827

명의변경

20010418

200

김유신

20010827

20010911

정지

200

김유신

20010911

20011020

복구

200

김유신

20011020

20011215

기기변경

20011020

200

이순신

20011215

99991231

명의변경

20011215

<주5> 아래 파란색으로 칠해진 쿼리가 위의 도표와 같은 결과를 만드는 부분이고 보라색으로 칠해진 쿼리가 상태변경일 및 상태코드를 상속받을 수 있도록 보정하는 쿼리임.

SELECT CONTNO, NAME, SDATE, EDATE, STATE_CODE, STATE_NAME,

MAX(CHG_DATE) OVER (PARTITION BY CONTNO ORDER BY SDATE) CHG_DATE,

SUBSTR(MAX(DECODE(CHG_CODE,NULL,NULL,

TO_CHAR(T_RANK,'B99999999'))||CHG_CODE)

OVER (PARTITION BY CONTNO ORDER BY SDATE),-2) CHG_CODE

FROM (SELECT CONTNO, NAME, SDATE, EDATE, STATE_CODE, STATE_NAME,

DECODE(STATE_CODE,'10',SDATE,'20',SDATE,

'30',SDATE) CHG_DATE,

DECODE(STATE_CODE,'10',STATE_CODE,'20',STATE_CODE,

'30',STATE_CODE) CHG_CODE,

ROW_NUMBER()

OVER (PARTITION BY CONTNO ORDER BY SDATE) T_RANK

FROM CONT_TBL);

② Reporting Aggregate Function의 활용사례.

다음은 아래의 EMP 테이블을 이용하여 Reporting Aggregate Function의 활용사례에 대해 설명할 것이다.

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

1980-12-17

800

7499

ALLEN

SALESMAN

7698

1981-02-20

1600

300

7521

WARD

SALESMAN

7698

1981-02-22

1250

500

7566

JONES

MANAGER

7839

1981-04-02

2975

7654

MARTIN

SALESMAN

7698

1981-09-28

1250

1400

7698

BLAKE

MANAGER

7839

1981-05-01

2850

7782

CLARK

MANAGER

7839

1981-06-09

2450

7788

SCOTT

ANALYST

7566

1982-12-09

3000

7839

KING

PRESIDENT

1981-11-17

5000

7844

TURNER

SALESMAN

7698

1981-09-08

1500

7876

ADAMS

CLERK

7788

1983-01-12

1100

7900

JAMES

CLERK

7698

1981-12-03

950

7902

FORD

ANALYST

7566

1981-12-03

3000

7934

MILLER

CLERK

7782

1982-01-23

1300

1) Reporting Aggregate Family - SUM()

위의 도표와 같이 데이타가 발생하였을 때 각각의 부서별로, 총 판매액이 가장 많은 직무를 알아보는 경우에 대해 Analytic Function을 사용한 경우와 사용하지 않은 경우를 살펴보자.

<주6> 아래의 도표가 원하는 결과임.

DEPTNO

JOB

SUM_SAL

MAX_SUM_SAL

PRESIDENT

5000

ANALYST

6000

SALESMAN

5600

◎ Analytic Function을 사용하지 않은 경우.

아래 쿼리문에서 알 수 있듯이 같은 테이블을 두 번 Access하고 서로 다른 집합 레벨로 인하여 여러 번의 그룹핑을 한 후 서로 조인을 해야 하는 비효율이 발생한다.

SELECT V2.DEPTNO, V2.JOB, V2.SUM_SAL, V1.MAX_SUM_SAL

FROM (SELECT DEPTNO, MAX(SUM_SAL) MAX_SUM_SAL

FROM (SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL

FROM EMP

GROUP BY DEPTNO, JOB)

GROUP BY DEPTNO) V1,

(SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL

FROM EMP

GROUP BY DEPTNO, JOB) V2

WHERE V2.DEPTNO = V1.DEPTNO

AND V2.SUM_SAL = V1.MAX_SUM_SAL;

◎ Analytic Function을 사용한 경우.

SELECT DEPTNO, JOB, SUM_SAL, MAX_SUM_SAL

FROM (SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL,

MAX(SUM(SAL)) OVER (PARTITION BY DEPTNO) MAX_SUM_SAL

FROM EMP

GROUP BY DEPTNO, JOB)

WHERE SUM_SAL = MAX_SUM_SAL;

2) Reporting Aggregate Family - RATIO_TO_REPORT()

각각의 부서 총 판매액에 대한 부서별, 직무별 총 판매액의 비율을 알고자 할 경우 Analytic Function을 사용한 경우와 사용하지 않은 경우를 살펴보자. 참고적으로 RATIO_TO_REPORT() 함수는 윈도우내의 합계에 대한 비율을 계산하는 함수이다.

<주7> 아래의 도표가 원하는 결과임.

DEPTNO JOB            SUM_SAL  SUM_TOTAL  RATIO_TO_SUM 
10          CLERK        1300          8750               0.148571428571429 
10          MANAGER   2450          8750               0.28
10          PRESIDENT 5000          8750               0.571428571428571
20          ANALYST    6000          10875             0.551724137931034
20          CLERK        1900          10875             0.174712643678161
20          MANAGER   2975          10875             0.273563218390805
30          CLERK        950            9400              0.101063829787234
30          MANAGER   2850          9400              0.303191489361702
30          SALESMAN 5600          9400              0.595744680851064

◎ Analytic Function을 사용하지 않은 경우.

아래 쿼리문에서 알 수 있듯이 같은 테이블을 두 번 Access하고 서로 다른 집합 레벨로 인하여 두 번의 그룹핑을 한 후 서로 조인을 해야 하는 비효율이 발생한다.

SELECT    V2.DEPTNO, V2.JOB, V2.SUM_SAL, V1.SUM_TOTAL,
          V2.SUM_SAL/V1.SUM_TOTAL RATIO_TO_SUM
  FROM    (SELECT    DEPTNO, SUM(SAL) SUM_TOTAL
             FROM    EMP
            GROUP BY DEPTNO) V1,
          (SELECT    DEPTNO, JOB, SUM(SAL) AS SUM_SAL
             FROM    EMP
            GROUP BY DEPTNO, JOB) V2
 WHERE   V2.DEPTNO = V1.DEPTNO;

◎ Analytic Function을 사용한 경우.

SELECT    DEPTNO, JOB, SUM(SAL) SUM_SAL,
          SUM(SUM(SAL)) OVER (PARTITION BY DEPTNO) SUM_TOTAL,
          RATIO_TO_REPORT(SUM(SAL)) OVER (PARTITION BY DEPTNO) RATIO_TO_SUM
  FROM    EMP
 GROUP BY DEPTNO, JOB;

이상으로 Analytic Function중 Window Aggregation Family와 Reporting Aggregation Family에 대한 개념, 문법설명, 특징, 활용사례에 대해 살펴보았다. 다음 연재에서는 Analytic Function LAG/LEAD Family에 대해 살펴보기로 한다.

[Top]

No.	제목	작성자	작성일	조회
504	Open Source for the Enterprise	정재익	2002-08-12	16087
477	Storing XML in Databases	정재익	2002-07-30	4488
447	제4회 Lag/Lead family에 대한 소개 및 활용 사례	정재익	2002-07-13	5402
446	제3회 Aggregate Family(Reporting)의 소개 및 활용사례	정재익	2002-07-12	5078
445	제2회 Analytic Function의 소개 및 활용사례	정재익	2002-07-12	8319
444	제1회 Analytic Function의 소개	정재익	2002-07-12	5313
443	이력관리 Data Model - 선분이력 [1]	정재익	2002-07-12	8016

작업시간: 0.045초, 이곳 서비스는
PostgreSQL v16.1로 자료를 관리합니다