원본출처 : http://korea.internet.com/channel/content.asp?kid=3&cid=436&nid=20230
앞서 소개된 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> 에는 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.
ⓒ Windwing 구
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
1000
1000
1000
1000
100
20020302
130
1130
1130
1130
1130
100
20020303
1500
2630
2630
2630
2630
100
20020304
900
3530
3830
3830
3830
100
20020304
300
3830
3830
3830
3830
100
20020305
2300
6130
6130
6130
6130
200
20020301
500
500
500
500
500
200
20020302
250
750
750
750
750
200
20020303
1000
1750
1750
1750
1750
200
20020304
1500
3250
3250
3250
3250
200
20020305
3500
6750
6950
6950
6950
200
20020305
200
6950
6950
6950
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
1000
1000
1000
100
20020302
130
1130
1130
1130
100
20020303
1500
1630
1630
1630
100
20020304
900
2400
2700
2700
100
20020304
300
1200
2700
2700
100
20020305
2300
2600
3500
3500
200
20020301
500
500
500
500
200
20020302
250
750
750
750
200
20020303
1000
1250
1250
1250
200
20020304
1500
2500
2500
2500
200
20020305
3500
5000
5200
5200
200
20020305
200
3700
5200
5200
위의 도표에서 고객번호가 '100'이고 판매일자가 '20020304', 판매금액이 900원인 로우를 기준으로 보면 검은 화살표는 파티션, 빨간 화살표는 물리적인 윈도우 크기(Physical Window Size), 파란 화살표는 논리적인 윈도우 크기(Logical Window Size)를 의미한다. 여기서 주의해서 봐야할 것은 논리적인 윈도우의 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 고객번호, 판매일자 순으로 정렬되므로 고객번호 '100', 판매일자 '20020304' 기준으로 보면 판매액이 900원 300원이 발생했지만 정렬순위가 같으므로 논리적으로는 1200원으로 처리된다.
③ 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;
위의 쿼리문에서 파란색의 글자가 Windowing_Clause를 의미하고 ACC_AMT1은 Physical 윈도우에 의해, ACC_AMT2는 Logical 윈도우중에서 Value Range에 의해, ACC_AMT3은 Logical 윈도우 중에서 Time Interval에 대한 의미를 가진다.
고객번호
판매일자
판매금액
ACC_AMT1
ACC_AMT2
ACC_AMT3
100
20020301
1000
1130
1130
1130
100
20020302
130
2630
2630
2630
100
20020303
1500
2530
2830
2830
100
20020304
900
2700
5000
5000
100
20020304
300
3500
5000
5000
100
20020305
2300
2600
3500
3500
200
20020301
500
750
750
750
200
20020302
250
1750
1750
1750
200
20020303
1000
2750
2750
2750
200
20020304
1500
6000
6200
6200
200
20020305
3500
5200
5200
5200
200
20020305
200
3700
5200
5200
위의 도표에서 고객번호가 '100'이고 판매일자가 '20020304', 판매금액이 900원인 로우를 기준으로 보면 검은 화살표는 파티션, 빨간 화살표는 물리적인 윈도우 크기(Physical Window Size), 파란 화살표는 논리적인 윈도우 크기(Logical Window Size)를 의미한다. 여기서 주의해서 봐야할 것은 논리적인 윈도우의 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 고객번호, 판매일자 순으로 정렬되므로 고객번호 '100', 판매일자 '20020304' 기준으로 보면 판매액이 900원 300원이 발생했지만 정렬순위가 같으므로 논리적으로는 1200원으로 처리된다.
④ 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
1000
1000
100
20020302
130
1130
1130
100
20020303
1500
2630
2630
100
20020304
300
3830
2930
100
20020304
900
3830
3830
100
20020305
2300
6130
6130
200
20020301
500
500
500
200
20020302
250
750
750
200
20020303
1000
1750
1750
200
20020304
1500
3250
3250
200
20020305
200
6950
3450
200
20020305
3500
6950
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
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
30
개통
19980102
30
100
홍길동
19990302
19990421
70
정지
19980102
30
100
홍길동
19990421
20000823
50
복구
19980102
30
100
차인표
20000823
20011002
10
명의변경
20000823
10
100
홍길동
20001002
99991231
10
명의변경
20001002
10
200
이순신
20000401
20000523
30
개통
20000401
30
200
이순신
20000523
20000823
70
정지
20000401
30
200
이순신
20000823
20010418
50
복구
20000401
30
200
김유신
20010418
20010827
10
명의변경
20010418
10
200
김유신
20010827
20010911
70
정지
20010418
10
200
김유신
20010911
20011020
50
복구
20010418
10
200
김유신
20011020
20011215
20
기기변경
20011020
20
200
이순신
20011215
99991231
10
명의변경
20011215
10
◎ Analytic Function을 사용하지 않은 경우.
‘신규’, ‘명의변경’,’기기변경’인 경우만 선택하여 다음과 같이 선분이력을 먼저 만든다.
고객번호
시작일
종료일
상태코드
100
19980102
20000823
30
100
20000823
20001002
10
100
20001002
99991231
10
200
20000401
20010418
30
200
20010418
20011020
10
200
20011020
20011215
20
200
20011215
99991231
10
그런 후 아래의 쿼리문과 같이 위의 도표와 같이 만들어진 중간 집합으로 원래의 테이블과 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
30
개통
19980102
30
1
100
홍길동
19990302
19990421
70
정지
2
100
홍길동
19990421
20000823
50
복구
3
100
차인표
20000823
20011002
10
명의변경
20000823
10
4
100
홍길동
20001002
99991231
10
명의변경
20001002
10
5
200
이순신
20000401
20000523
30
개통
20000401
30
1
200
이순신
20000523
20000823
70
정지
2
200
이순신
20000823
20010418
50
복구
3
200
김유신
20010418
20010827
10
명의변경
20010418
10
4
200
김유신
20010827
20010911
70
정지
5
200
김유신
20010911
20011020
50
복구
6
200
김유신
20011020
20011215
20
기기변경
20011020
20
7
200
이순신
20011215
99991231
10
명의변경
20011215
10
8
<주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
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600
300
30
7521
WARD
SALESMAN
7698
1981-02-22
1250
500
30
7566
JONES
MANAGER
7839
1981-04-02
2975
20
7654
MARTIN
SALESMAN
7698
1981-09-28
1250
1400
30
7698
BLAKE
MANAGER
7839
1981-05-01
2850
30
7782
CLARK
MANAGER
7839
1981-06-09
2450
10
7788
SCOTT
ANALYST
7566
1982-12-09
3000
20
7839
KING
PRESIDENT
1981-11-17
5000
10
7844
TURNER
SALESMAN
7698
1981-09-08
1500
0
30
7876
ADAMS
CLERK
7788
1983-01-12
1100
20
7900
JAMES
CLERK
7698
1981-12-03
950
30
7902
FORD
ANALYST
7566
1981-12-03
3000
20
7934
MILLER
CLERK
7782
1982-01-23
1300
10
1) Reporting Aggregate Family - SUM()
위의 도표와 같이 데이타가 발생하였을 때 각각의 부서별로, 총 판매액이 가장 많은 직무를 알아보는 경우에 대해 Analytic Function을 사용한 경우와 사용하지 않은 경우를 살펴보자.
<주6> 아래의 도표가 원하는 결과임.
DEPTNO
JOB
SUM_SAL
MAX_SUM_SAL
10
PRESIDENT
5000
5000
20
ANALYST
6000
6000
30
SALESMAN
5600
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에 대해 살펴보기로 한다.
|