Analytic Function
제1회 Analytic Function의 소개 및 수행원리
Analytic function의 소개
초기 대부분의 RDBMS는 집합적인 개념에 충실하여 만들어 졌기 때문에 집합적인 개념에 위배되는 처리는 표준 SQL로 처리가 불가능 하였다. 그래서 이러한 작업은 프로그램 로직으로 처리 하거나 데이터의 복제 등 다양한 응용 고난도 SQL( 대용량 데이터베이스 2권 참조)을 활용하여 처리 해야만 했다.
그러나 프로그램에서 로직으로 처리할 경우 집합 개념의 RDB시스템에서 심각한 성능저하를 유발할 수 있다는 점을 ”고성능 DB구축을 위한 핵심요소의 이해”를 읽으신 분들은 이해하실 수 있을 것이다. 즉 고가의 고성능 RDB를 구입해 놓고 DBMS를 단지 Data 저장소로만 사용하는 방법이다. 프로그램에서 IF..then..else 를 이용하여 필요하면 그때그때 Data저장소(RDB)에서 Data를 읽어와 상황에 따라 처리 및 판단을 한 후 조건에 맞으면 저장해 놓고 마지막에 모든 값을 조합하여 최종 결과를 Return하는 형식이 될 것이다.
특히 비즈니스 분석작업에서 위와 같은 방법으로 처리 할 경우 어떤 결과를 초래 하겠는가 ? 분석작업에 필요한 Data라면 범위가 일부분에만 국한되지는 않을 것이다. 때에 따라서 범위를 정하여 분석하는 경우도 있고, 전체를 대상으로 분석하는 경우도 발생하겠지만 거의 대부분은 넓은 범위의 Data가 대상이 된다는 점이다. 이런 넓은 범위의 다량의 Data를 반복적으로 읽어와서 프로그램 로직으로 처리할 경우 이미 성능을 논할 단계는 넘어간 것이다.
가령 100만 건의 고객 Data를 직업별로 분류하고 연봉이 높은 순으로 몇 개의 Level을 정하여 각 직업별 Level별 평균 연봉 및 고객 수 및 각 직업별 평균연봉 및 고객수 를 보고자 한다고 100만건을 건별로 읽어서 직업이 무엇인지 구분하고 연봉에 해당 고객의 연봉을 저장하고 어느 level에 속하는지 구분한 후, 다시 한건의 고객을 읽은 후 분류 및 기존에 이미 읽은 Data에 합계를 수행하고, 건수를 기록하고 다리 또 한건의 고객을 읽는 작업을 100만번 수행해야 한다. 그 다음에 각 직업별로 연봉은 비교하여 순위를 정한 후 이 순위에 따라 차례대로 나열하는 작업을 수행해야 한다. 만약에 연봉이 아니라 한 고객 당 평균 약 10회의 매출을 발생시킨 상황에서 매출 금액이 높은 직업별로 보고자 한다면 또 어떻게 할 것인가. 위와 같은 처리가 다시 고객 건별로 약 10회씩 반복 처리를 하게 될 것이다. 즉 답을 구하는 데는 문제가 없지만 효율성 측면에서는 재고(再考)해야만 하는 상황이 되는 것이다.
그러면 성능 개선을 위해 RDB의 집합개념을 이용하여 고성능 SQL로 해결하면 어떻게 되는가 ? 해당하는 전체 집합을 정한 후, 이 전체 집합을 보고자 하는 Level로 Group by를 하여 나누어 주고 Decode를 이용하여 분리한 통계를 만들고 다시 직업별 통계를 구하기 위해 원 집합을 복제하여 직업별로 Grouping 작업을 수행한 후 두 집합을 결합하는 하나의 SQL을 만들어 DBMS 에게 수행하라고 명령(Query)하는 것이다. 이 경우 DBMS가 해당하는 대상이 되는 Data들을 읽어서 Group by를 수행하고 조합한 수 다시 순위를 매겨 최종 결과만을 프로그램에 Return하게 된다. - 여기선 비교적 간단한 분석/통계작업의 예를 들었지만 위의 예에다가 Group(직업)별 순위를 매기로 순위에 따라 고객 신용등급을 다르게 적용하는 등 몇 가지 응용이 추가된다면 Group Serial등 다양한 기법의 고난도 SQL을 적용하게 될 것 이다. -
실제 하는 일은 DBMS가 했는가, DBMS에서 읽어와서 프로그램의 로직에서 처리했는가 만의 차이가 있을 뿐 하고자 한 행위는 동일하다. 그러나 첫번째 방법은 System의 성능에 지대한 영향을 미칠 것이 자명하고, 두 번째 방법은 SQL을 구사하는데 고난도의 기술이 필요하며 유지보수를 하는데 있어서도 개발자가 적용한 SQL의 정확한 개념을 이해 해야만 원활하게 작업할 수 있을 것이다.
그러면 이두가지 문제를 통시에 해결할 수는 없을까 ? 그룹 내 순위를 정하기 위해 복잡하게 SQL을 구사한 부분을 하나의 명령으로 DBMS가 처리해 줄 수는 없을까 ? 집합개념에서는 처리가 되지 않는 각 Row 간의 값을 비교할 수는 없을까 ? 즉 사용자의 비즈니스 요구사항이 더욱 더 복잡해 지면서 구현이 용이하고 성능도 향상시킬 수 있는 강력한 SQL의 필요성이 절실하게 요구되었고 이러한 필요에 따라 Red Brick은 DATA ANALYSIS나 DSS(DECISION-SUPPORT SYSTEM)에 적합한 다양하고 강력한 기능을 가진 SQL을 제안하였는데, 새로운 제안에는 집합적 개념인 표준 SQL에서 처리가 어려워 절차적으로 처리를 할 수 밖에 없었던 비즈니스 분석 요구를 수용하기 위해 cume, MovingAvg(n), MovingSum(s), Rank....When, RatioToReport, Tertile, Create Macro와 같은 많은 functions들의 지원을 포함하고 있으며 이는 집합개념에서 수용하지 못하였던 포인터(Pointer)와 오프셋(offset)의 개념을 추가 시킨 것으로 이 SQL을 RISQL(Red Brick intelligent SQL) 이라 한다. 여기서 추가된 개념들이 바로 Analytic Function이다.
Analytic function을 지원하는 RDBMS를 사용하는 경우 Self-join 또는 클라이언트 프로그램의 절차적 로직으로 표현한 것 또는 SQL로 표현하기 위해 고난도의 여러 기법을 적용하였던 것을 native SQL에서 하나의 명령어로 바로 적용할 수 있으므로 조인이나 클라이언트 프로그램의 overhead를 줄임으로써 Query 속도를 향상시킬 수 있고, 개발자가 명백하고 간결한 SQL로 복잡한 분석작업을 수행할 수 있으며, 개발 및 유지보수가 편하기 때문에 생산력을 향상시킬 수 있다.
또한 기존 SQL syntax를 그대로 따르기 때문에 기존 Standard SQL을 사용하던 개발자/운영자의 이해가 빠르며 적용하기 쉽고 ANSI SQL 채택으로 향후 다양한 소프트웨어에 적용이 될 것이므로 때문에 표준화에도 유리한 장점이 있으므로 그 활용 정도가 점차 확대될 것이다.
현재 모든 상용 DBMS가 Analytic Function을 제공하는 것은 아니다. 본 연재에서는 Oracle 사의 DBMS가 Support하는 Analytic Function을 기준으로 간단한 수행원리 및 각 기능별 활용사례를 독자여러분이 이해하기 쉽도록 실제 Site에서 적용한 사례를 들어 아래와 같이 4회에 걸쳐 연재하고자 한다.
1회 : Analytic Function의 소개 및 수행원리 ( 본 글 )
2회 : Ranking Family의 소개 및 활용사례
3회 : Aggregate Family( Reporting ) 의 소개 및 활용사례
4회 : Lead/Lag Family의 소개 및 활용사례
Analytic Function 수행원리
analytic function의 수행절차(processing order)
Analytic function을 사용한 query processing은 크게 세단계로 수행된다.
1단계 ( general query processing ) : join, where, group by and having 등의 기존 query processing 수행단계로서 기존 Standard SQL이 수행되는 동일한 원리에 의해서 대상 집합을 추출하는 단계이다.
2단계 ( analytic function applying ) : 1단계 결과를 가지고 실제 analytic function이 적용되어 필요한 계산을 행하는 단계, 즉 대상집합을 필요한 몇 개의 Group 으로 분리하고 순위를 결정하며 그룹 순위를 기준으로 명령된 계산을 수행하는 단계이다.
이단계에서 내부적으로 적용되는 세부 메카니즘은 다음과 같이 수행된다.
1) 대상집합을 Analytic Function이 적용되어야 할 각 Group으로 나눈다(Partitioning )
2) 각 Partition내의 집합에 속한 개체에 조건에 따른 순위를 결정한다.
3) Pointer와 Off-set개념을 적용하여 각 Row간에 필요한 계산을 수행한다.
3단계 (order by processing - optional) : query에 order by 절이 있다면 최종결과에 대한 ordering을 행하는 단계
[그림 2-1] Analytic function의 수행절차
Analytic function의 3요소
Result Set Partitions : query processing with analytic function의 1단계 수행결과를 column이나 expression을 기준으로 grouping한 것, 1단계 수행결과 전체가 하나의 partition에 속할 수도 있고, 적은 rows를 가진 여러 개의 작은 partition으로 쪼개질 수도 있다. 그러나, 한 row는 반드시 하나의 partition에 속한다.
(Sliding) Window : current row에 대한 analytic calculation 수행의 대상이 되는 row의 범위(range), window는 current row를 기준으로 하나의 partition 내에서 sliding하며, 반드시 starting row와 ending row를 가진다.window size는 partition 전체가 될 수도 있고 partition의 부분범위가 될 수도 있으나 하나의 partition을 넘을 수는 없다.partition의 부분범위로서 window size를 정할 때는 physical number of rows로 정할 수도 있고 logical interval로 정할 수도 있다.
Current Row : 모든 analytic function의 적용은 항상 partition 내의 current row를 기준으로 수행된다.
current row는 항상 window의 start와 end를 결정하는 기준(reference point)으로서 역할을 하므로 current row가 없는 window는 존재하지 않는다.
[그림 2-2] sliding window와 current row
Analytic function의 종류
Analytic Function은 그 성격에 따라 다음과 같이 4개의 Family Group 으로 구분된다.
Ranking family
대상 집합에 대하여 특정 컬럼(들) 기준으로 순위나 등급을 매기는 analytic function 류로서 다음과 같은 종류가 있다.
RANK(), DENSE_RANK(), CUME_DIST(), PERCENT_RANK(), NTILE(), ROW_NUMBER()
Window aggregate family
현재 로우(current row)를 기준으로 지정된 윈도우(window) 내의 로우들을 대상으로 집단화(aggregation)를 수행하여 여러가지 유용한 집계정보(running summary, moving average 등)를 구하는 analytic function 류이며 다음과 같은 종류가 있다.
SUM, AVG, MIN, MAX, STDDEV, VARIANCE, COUNT, FIRST_VALUE, LAST_VALUE
Reporting Aggregate family
서로 다른 두 가지의 aggregation level을 비교하고자 하는 목적으로 사용하는 analytic function 으로서 다음과 같은 종류가 있다. SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE
LEAD/LAG family
서로 다른 두 로우값을 비교하기 위한 analytic function. LEAD()와 LAG()
다음에 연재될 내용은 각 Analytic Function의 Family별로 표준적인 사용방법 및 실제 Project에서 어떻게 응용되어 사용되고 있는지 실 사례를 제시할 것이다. 각 사례에 따라서는 해당 그룹에 속하는 Family만으로 구성된 사례가 있는가 하면 다른 그룹의 Family를 복합적으로 이용하여 활용한 사례도 소개될 것이다. 실제 업무에서는 본 글을 읽으시는 독자분들이 더 잘 아시겠지만 한 가지 Function만을 이용하여 Application을 작성하는 것 보다는 여러 Analytic Function 및 기타 아이디어가 복합적으로 들어갈 때 진짜 실용가치가 있는 Application이 탄생함을 알고 있을 것이다. 그 중에서 몇 가지 사례를 추출하여 제공하고자 한다.
이번 회에서는 지난 회에 연재된 Analytic Function 또는 OLAP function의 한 종류인 Ranking Family의 소개 및 활용사례를 다루고자 한다. 활용 사례는 Ranking Family를 위주로 하여 추후로 소개될 예정인 다른 Analytic Function들과 함께 사용되어 실무에서 쉽게 응용할 수 있는 것들을 위주로 하고 있다
1. Ranking Family의 특징
Ranking Family에 속하는 Analytic Function 은 대상 집합에 대하여 특정 컬럼(들)을 기준으로 순위나 등급을 부여하는 것으로 다음과 같은 특징을 지니고 있다.
- 오름차순 또는 내림차순으로 순위나 등급을 부여할 수 있다
- 오름차순 내림차순과 관계없이 NULL은 순위의 가장 처음 또는 마지막으로 강제 처리 가능하다.
- Rank functions은 각 파티션마다 초기화된다.
- 순위 또는 등급은 GROUP BY CUBE와 ROLLUP 절마다 초기화된다.
2. Ranking Family의 종류
1) RANK()
RANK 함수는 각 로우마다 순위를 매겨주는 함수로 각 PARTITION 내에서 ORDER BY절에 명시된 대로 정렬한 후의 순위를 의미하고 1부터 시작하여 동일한 값은 동일한 순위를 가지며, 동일한 순위의 수만큼 다음 순위는 건너뛴다.
[그림 1]은 “판매실적에 의한 제품의 순위를 각 지역별과 전체 지역에서 각각 부여하라”는 RANK()함수의 활용한 예로 GROUP BY절과 RANK()함수가 같이 사용되었다
SELECT r_regionkey, p_productkey, sum(s_amount),
RANK() OVER (PARTITION BY r_regionkey ORDER BY sum(s_amount) DESC)
AS rank_of_product_per_region,
RANK() OVER (ORDER BY sum(s_amount) DESC)
AS rank_of_product_total
FROM product, region, sales
WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey
GROUP BY r_regionkey, p_productkey;
[예제 1] RANK() 활용
[그림 1]에서 동일 영업실적에 대해서는 동일한 순위를 부여하고 있고 다음 영업실적은 동일 순위의 수만큼을 건너 뛴 순위가 부여되었음을 확인할 수 있다.
2) DENSE_RANK()DENSE_RANK()는 Rank()와 유사한 함수로ORDER BY절에 사용된 컬럼이나 표현식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다.
[그림 2]는 RANK()와 DENSE_RANK()가 어떻게 다른 것인지를 보여주고 있다.
[그림 2] RANK()와 DENSE_RANK()의 비교
3) CUME_DIST() : Cumulative Distribution Function
PARTITION BY에 의해 나누어진 그룹별로 각 row를 ORDER BY절에 명시된 순서로 정렬한 후 그룹별 상대적인 위치(누적된 분산정도)를 구한다. 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 ROW수를 그룹내 총 ROW 수로 나눈 것을 의미하며 결과 값의 범위는 0보다 크고 1보다 작거나 같다.
SELECT r_regionkey, p_productkey, SUM(s_amount) as s_amount,
CUME_DIST() OVER (PARTITION BY r_regionkey ORDER BY SUM(s_amount))
AS cume_dist_per_region
FROM region, product, sales
WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey
| EAST GROUP의 7개 ROW중 2/7 |
GROUP BY r_regionkey, p_productkey ORDER BY r_regionkey, s_amount DESC;
[그림 3] CUME_DIST() 활용
4) PERCENT_RANK()
CUME_DIST와 유사한 함수이나 PARTITION별 각 row의 순위 –1 / PARTITION내의 ROW의 수를 결과값으로 하며, 결과값 범위는 0 <= 결과값 <= 1이고 집합의 첫번째 row의 PERCENT_RANK는 항상 0이 된다.
| VALUE | RANK() | DENSE_RANK() | CUME_DIST() | PERCENT_RANK |
| 10 | 1 | 1 | 0.25 | 0 => (1-1)/(4-1) |
| 20 | 2 | 2 | 0.75 | 0.33 => (2-1)/(4-1) |
| 20 | 2 | 2 | 0.75 | 0.33 => (2-1)/(4-1) |
| 30 | 4 | 3 | 1 | 1 => (4-1)/(4-1) |
[그림 4] CUME_DIST()와 PERCENT_RANK의 비교5) NTILE() PARTITION을BUCKET이라 불리는 그룹별로 나누고 PARTITION내의 각 ROW등을 BUCKET에 배치하는 함수로 각 BECKET에는 동일한 수의 ROW가 배치된다. 예를 들어 PARTITION내에 100개의 ROW를 가지고 있고 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면 1개의 BUCKET당 25개의 ROW가 배정된다. 만일 각 PARTION의 수가 정확하게 분배되지 않을 경우 근사치로 배분한 후 남는 값에 대하여 최초 PARTITION부터 한 개씩 배분한다. 즉, 만일 103개의 ROW에 대하여 NTILE(5)를 적용하면 첫번째 BUCKET부터 세번째까지는 21개의 ROW가, 나머지는 20개의 ROW가 배치된다.
[그림 5]는 “제품별 판매량을 구하고, 이를 4등급으로 나누어, 다시 등급 내에서 순위를 매겨라”는 질의를 NTILE()함수를 이용하여 구현한 것이다.
SELECT p_productkey, sum(s_amount) AS sum_s_amount,
NTILE(4) over (ORDER BY sum(s_amount) DESC) as 4_tile,
RANK() OVER (PARTITION BY NTILE(4) over (ORDER BY sum(s_amount) DESC) ORDER BY sum_s_amount DESC) AS rank_in_quartile
FROM product, sales
WHERE p_productkey = s_productkey
GROUP BY p_productkey);
[그림 4-5] NTILE() 활용
6) ROW_NUMBER()ROW_NUMBER()는 각 PARTITION내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다. [그림 6]은 “제품별 판매량의 내림차순으로 unique한 일련번호를 부여하라”는 질의에 ROW_NUMBER()를 활용한 예이다.
SELECT p_productkey, sum(s_amount),
ROW_NUMBER() (ORDER BY sum(s_amount) DESC NULLS LAST) AS srnum
FROM product, sales
WHERE p_productkey = s_productkeyGROUP BY p_productkey;
[그림 6] ROW_NUMBER() 활용
3. Ranking Family의 활용 사례
이 절에서는 위에서 소개된 Ranking Family Analytic Function의 실제업무에 이용 가능한 활용사례에 대해 살펴보고자 한다. 아래에 소개된 예들은 ORACLE 8i Version을 중심으로 작성되었으나 UNIX Version의 IBM UDB 7에서도 OLAP Function이라하여 동일한 문법의 함수를 지원하므로 사용 가능할 것으로 보여진다. 다만 DBMS별로 지원하는 함수의 종류에는 차이가 있으면 RANK 계열 함수의 경우 UDB 7의 경우 RANK, DENSE_RANK, ROW_NUMBER가 지원됨을 확인하였다. 다른 DBMS에서도 Analytic Function이 SQL 1999의 표준으로 채택된 만큼 이미 사용 가능하거나 아닐지라도 가까운 시일내에 지원되리라고 보여진다.
1) 달력상의 주별로 순번을 부여하는 SQL을 작성하시오. (ROW_NUMBER활용사례 1)
아래 그림과 같은 결과가 나오도록 SQL을 작성하시오. 여기에서 주의할 점은 달(월)이 바뀐 경우이다. 즉 달력상의 1 주(일 ~ 월)를 하나의 Grouping 으로 해야 한다. 또한 달이 바뀔 경우에는 이를 무시하고 다시 순번을 시작해야 한다.
| 일자 | 순번 | 일자 | 순번 |
| 20020101 | 1 | 20020201 | 1 |
| 20020104 | 2 | 20020203 | 1 |
| 20020105 | 3 | 20020207 | 2 |
| 20020106 | 1 | 20020209 | 3 |
| 20020107 | 2 | 20020210 | 1 |
| 20020111 | 3 | 20020211 | 2 |
| 20020112 | 4 | 20020221 | 1 |
| 20020113 | 1 | 20020223 | 2 |
| 20020114 | 2 | 20020224 | 1 |
| 20020117 | 3 | 20020225 | 2 |
| … | … | … | … |
SQL) SELECT 일자,
ROW_NUMBER() OVER (PARTITION BY substr(일자,1,6)||
to_char(to_date(일자,'yyyymmdd') -
to_char(to_date(일자,'yyyymmdd'),'d') + 1,'yyyymmdd')
ORDER BY 일자) 순번
FROM SAMPLE1;
2. 아래 그림의 월별 급여 집계테이블에서 2001년 1월의 각 부서별 급여액순으로 상위 2위에 해당 하는 사원 1명의 이름, 급여액을 구하는 SQL을 작성하시오. 단, 급여액이 동일할 경우 성명 순으로 한다.( 즉, 동률의 급여금액이 존재하도라도 부서별 급여금액 역순, 이름순으로 정렬하여 그 순서에 따른 2위(두번째) 를 의미) (ROW_NUMBER활용사례 2)[월별 급여집계 테이블]
| 부서 | 성 명 | 년월 | 급여액 |
| 총무부 | 홍길동 | 200101 | 200 |
| 총무부 | 임꺽정 | 200101 | 250 |
| 총무부 | 장길산 | 200101 | 100 |
| 총무부 | 최고봉 | 200101 | 200 |
| 총무부 | 황비홍 | 200101 | 120 |
| 영업부 | 궁해 | 200101 | 300 |
| 영업부 | 왕곤 | 200101 | 310 |
| 영업부 | 견온 | 200101 | 220 |
| 생산부 | 박임자 | 200101 | 350 |
| 생산부 | 전본인 | 200101 | 300 |
| 생산부 | 노친구 | 200101 | 300 |
| 생산부 | 김갱제 | 200101 | 230 |
[결과 집합]
| 부 서 | 성명 | 급여액 |
| 총무부 | 최고봉 | 200 |
| 영업부 | 궁해 | 300 |
| 생산부 | 노친구 | 300 |
SQL) SELECT 부서, 성명, 급여액
FROM (SELECT
부서, 성명, 급여액,
ROW_NUMBER() OVER (PARTITION BY 부서 ORDER BY 급여액 DESC, 성명) SEQ FROM 월별급여테이블
WHERE 년월 = '200101' )
WHERE SEQ = 2;
※ 이 경우 Analytic Function중 RANK 또는 DENSE_RANK를 사용하면 문제에서 원하는 금액역순
+ 성명의 순서중 2번째를 구하는데 1위에 해당하는 동률이 있을 경우 답이 틀리게 됩니다.3. 아래 테이블의 데이터를 참조하여 결과집합을구하는 SQL을 작성하시오. CO001T는 코드 테이블로 하나의 MAJOR 코드에 대하여
최대 3개의 MINOR코드를 가질 수 있다.우리는 보고자 하는 것은 각 MAJOR 코드에 대하여 MINOR
코드 를 가로로 정렬하여 보고자 한다. 이때 MINOR 코드가3개가 아닌 경우에 나머지는 공백으로 둔다. (RANK 활용사례 1)[코드 테이블인 CO001T의 데이터 구조 ]
| Major | Minor |
| 영업 | 가마니 |
| 영업 | 나오미 |
| 영업 | 마동탁 |
| 물류 | 사시미 |
| 물류 | 아사달 |
| 회계 | 자몽돌 |
| 회계 | 차이나 |
| 회계 | 카이로 |
| 전산 | 파김치 |
∴ 결과 집합
| MAJOR | MINOR1 | MINOR2 | MINOR3 |
| 물류 | 사시미 | 아사달 | |
| 영업 | 가마니 | 나오미 | 마동탁 |
| 전산 | 파김치 | ||
| 회계 | 자몽돌 | 차이나 | 카이로 |
SQL) SELECT t1.major,
max(decode(no, 1, minor)) minor1,
max(decode(no, 2, minor)) minor2,
max(decode(no, 3, minor)) minor3
FROM (
SELECT major,minor,
RANK() OVER (PARTITION BY major ORDER BY minor ) as no
FROM co001t
GROUP BY major,minor
) t1
GROUP BY t1.major
4. 두 개의 상품이력 테이블을 선분 BETWEEN 조인과 데이터의 복제 방법을 활용하여
아래의 결과 집합 을 구하는 SQL을 작성하시오.
(주의사항: 결과집합에는 가입계약별로 할인전화번호가 반드시 3개가 존재해야만 한다.
따라서 상품이력에는 존재하고 상품 별 할인전화번호가 없는 경우는 계약별 상품이력에
있는 전화번 호로 3개의 전화번호를 생성 해야만 한다.)
(ROW_NUMBER 활용사례 3)- 계약번호별 상품이력
| 계약번호 | 상품코드 | 전화번호 | 사용시작일 | 사용종료일 |
| 200100001 | FAMILY01 | 01201111212 | 20010107 | 20010502 |
| 200100002 | FAMILY02 | 01201112020 | 20010301 | 20010707 |
| 200100003 | COUPLE01 | 01208711212 | 20010601 | 99991231 |
| 200100002 | FAMILY01 | 01208721212 | 20010707 | 99991231 |
| 200100001 | COUPLE02 | 01208731212 | 20010502 | 99991231 |
- 상품별 할인전화번호 이력
| 계약번호 | 등록전화번호 | 사용시작일 | 사용종료일 |
| 200100001 | 00022341212 | 20010107 | 99991231 |
| 200100001 | 01190661868 | 20010107 | 99991231 |
| 200100001 | 01608711313 | 20010107 | 20010502 |
| 200100001 | 00024527979 | 20010502 | 99991231 |
| 200100002 | 00025441868 | 20010301 | 99991231 |
| 200100002 | 01720661868 | 20010301 | 99991231 |
| 200100002 | 01608713313 | 20010301 | 99991231 |
| 200100003 | 00322340101 | 20010601 | 99991231 |
| 200100003 | 01198705868 | 20010601 | 99991231 |
| . | . | . | . |
∴
결과 집합
| 계약번호 | 상품명 | 순번 | 등록전화번호 | 사용시작일 | 사용종료일 |
| 200100001 | FAMILY01 | 1 | 00022341212 | 20010107 | 20010502 |
| 200100001 | FAMILY01 | 2 | 01190661868 | 20010107 | 20010502 |
| 200100001 | FAMILY01 | 3 | 01608711313 | 20010107 | 20010502 |
| 200100001 | COUPLE02 | 1 | 00024527979 | 20010502 | 99991231 |
| 200100001 | COUPLE02 | 2 | 01190661868 | 20010502 | 99991231 |
| 200100001 | COUPLE02 | 3 | 00024527979 | 20010502 | 99991231 |
| 200100002 | FAMILY02 | 1 | 00025441868 | 20010301 | 20010707 |
| 200100002 | FAMILY02 | 2 | 01720661868 | 20010301 | 20010707 |
| 200100002 | FAMILY02 | 3 | 01608713313 | 20010301 | 20010707 |
| 200100002 | FAMILY01 | 1 | 00025441868 | 20010107 | 99991231 |
| 200100002 | FAMILY01 | 2 | 01720661868 | 20010107 | 99991231 |
| 200100002 | FAMILY01 | 3 | 01608713313 | 20010107 | 99991231 |
| 200100003 | COUPLE01 | 1 | 00322340101 | 20010601 | 99991231 |
| 200100003 | COUPLE01 | 2 | 01198705868 | 20010601 | 99991231 |
| 200100003 | COUPLE01 | 3 | 01208711212 | 20010601 | 99991231 |
| . | . | . | . | . | . |
SQL) SELECT 계약번호, 상품번호,
순번+NO-1 순번, decode(sign(1-no),-1,전화번호,등록전화번호) 등록전화번호, GREATEST(X_SDATE, Y_SDATE) 사용시작일, LEAST(X_EDATE, Y_EDATE) 사용종료일 FROM (SELECT x,계약번호, x.상품번호, x.전화번호, row_number() over (partition by x.계약번호,x.상품명 order by x.등록전화번호) As 순번,count(*) over (partition by x.계약번호,x.상품명 order by x.등록전화번호) As c_no,nvl(y.등록전화번호,x.전화번호) 등록전화번호,x.사용시작일 X_SDATE, x.사용종료일 X_EDATE,
nvl(y.사용시작일,'10000101') Y_SDATE,
nvl(y.사용종료일,'99991231') Y_EDATE FROM 계약별상품이력 x, 상품별할인전화번호이력 y WHERE y.계약번호(+) =x.계약번호 AND y.시작일(+) <=x.종료일 --- 선분 Between 조인 AND y.종료일(+) > x.시작일 --- 선분이력이 양편 넣기일 때 ) a, copy_t b --- 복제용 Dummy TableWHERE no <= decode(c_no||순번,'11',3,'22',2,'21',1,'31',1','32',1,'33',1,0)
앞서 소개된 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 》
{SUM|AVG|MAX|MIN|COUNT|STDDEV|VARIANCE|RATIO_TO_REPORT}
([ALL | DISTINCT] {<value_expr1> | *})
OVER ([PARTITION BY <value_expr2>[,...] ]
[ORDER BY <value_expr3>[,...] ] [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 | 0020305 | 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 | 100 | 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 한 경우 처리 결과가 다름을 알 수 있다.
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에 대해 살펴보기로 한다.
analytic function의 Lag/Lead family에 대한 소개 및 그 활용 사례를 살펴보기로 한다.
1. Lag/Lead Family의 특징
Lag/Lead Family는 특정 로우가 속한 파티션([엔코아 이창수 수석]제1회 Analytic Function의 소개 글의 Analytic function의 3요소 중 Result Set Partitions
참조) 내에서 상대적 상하 위치에 있는 특정 로우의 컬럼 값을 참조하거나 상호 비교하고자 할 때 사용할 수 있는 function들의 집합으로, 그 특징은 다음과 같다.
- 오름차순 또는 내림차순으로 정렬된 파티션 내에서 상대적으로 상위 또는 하위에 위치하고 있는 특정 로우의 컬럼 값을 offset 지정에 의해 참조할 수 있다.
- 파티션 내에서 참조할 로우가 없을 경우 지정한 값(default = NULL)으로 출력한다.
- Order by에 기술된 컬럼의 값이 NULL인 경우 오름차순 또는 내림차순과 관계없이 순서 상 가장 처음 또는 마지막으로 강제 처리 가능하다.
2. Lag/Lead Family의 종류
1) Lag()
Lag 함수는 파티션 내에서 offset에 지정된 값(default = 1) 만큼 상대적으로 상위에 위치한 로우(오름차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우)를 참조하기 위해 사용된다.
Syntax :
- Offset
파티션 내에서 참조하고자 하는 로우를 지정하기 위한 대한 상대 주소이다. 지정 하지 않으면 1이다.
- efault
Offset에 의해 참조되는 로우가 파티션 내에 없을 경우, 즉 파티션의 범위를 벗어나는 경우에 출력되는 값을 지정한다. 지정하지 않으면 null이다.
- Analytic clause
파티션의 크기를 지정하며 파티션 내 로우들에 대한 정렬 순서를 결정한다.
다음 테이블은 신용카드의 마일리지 포인트에 대한 포인트 적립 또는 포인트 사용 이력을 표현한 것이다.
위의 테이블의 각 로우에 대해 동일 카드번호의 종전 마일리지 포인트적립사용일자 및 적립사용포인트를 보고자 할 경우 다음과 같이 lag()를 사용하여 간단히 그 결과를 볼 수 있다.
SQL :
select 카드번호,
포인트적립사용일자,
순번,
적립사용포인트,
lag(포인트적립사용일자) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 종전포인트적립사용일자,
lag(적립사용포인트) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 적립사용포인트
from 마일리지테이블;
결과 :
2) Lead()
Lead()는 Lag()와 유사한 함수로 offset에 지정된 값(default = 1) 만큼 상대적으로 하위에 위치한 로우(오름차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우)를 참조하기 위해 사용된다는 점만 다르다.
Syntax :
Lag() 예제와는 반대로 마일리지테이블의 각 로우에 대해 동일 카드번호의 바로 다음 적립 또는 사용한 일자와 포인트를 보고자 할 경우 다음과 같이 lead()를 사용하여 그 결과를 볼 수 있다.
SQL :
select 카드번호,
포인트적립사용일자, 순번, 적립사용포인트,
lead(포인트적립사용일자) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 종전포인트적립사용일자,
lead(적립사용포인트) over (partition by 카드번호
order by 포인트적립사용일자, 순번) as 적립사용포인트
from 마일리지테이블;
결과 :
3. Lag/Lead Family의 활용 사례
이 절에서는 위에서 소개된 Lag/Lead Family의 실제업무에 이용 가능한 활용사례에 대해 살펴보고자 한다. 아래에 소개된 예들은 ORACLE 8i Version 이상에서 테스트 되었다.
1) 신용카드 마일리지 포인트 적립 또는 사용 이력의 선분화
Lag/Lead family사용 예에서 언급한 바 있는 신용카드 마일리지 포인트 적립 또는 사용에 관한 이력을 선분화 해보자. 아래의 마일리지테이블은 마일리지 포인트가 적립되거나 사용된 일자와 해당 포인트를 관리하고 있다.
대상 테이블 :
일반적으로, 운영 시스템은 마일리지 포인트가 적립되거나 사용된 business event를 중시하여 데이터화하기 때문에 event가 발생한 일자(마일리지 포인트 적립 또는 사용일자)와 event 결과(적립 또는 사용된 마일리지 포인트)에만 관심을 갖는다. 그러나 이러한 데이터 모델은 신용카드별 특정 시점의 사용 가능한 마일리지 포인트를 조회하고자 하는 요구사항에 대응하기 어렵다. 이와 같은 요구사항에 쉽게 대처하기 위해서 카드번호, 유효시작일자, 유효종료일자, 사용가능포인트 컬럼으로 구성된 사용가능포인트테이블을 설계하고 기존 마일리지테이블로부터 데이터를 추출하여 로딩하는 SQL을 Lead()를 활용하여 작성해보자. 사용가능포인트테이블의 유효시작일자는 마일리지 포인트를 적립 또는 사용한 일자이며, 유효종료일자는 그 다음에 발생한 마일리지 포인트 적립 또는 사용한 일자 – 1일로 설정한다. 또한, 사용가능포인트는 신용카드별로 현재까지 적립 또는 사용한 마일리지 포인트를 합산한다.
SQL :
create table 사용가능포인트테이블
as
select 카드번호 as 카드번호,
포인트적립사용일자 as 유효시작일자,
nvl(to_char(to_date(lead(포인트적립사용일자)
over (partition by 카드번호 order by 포인트적립사용일자),
'YYYYMMDD') - 1, 'YYYYMMDD'), '99991231') as 유효종료일자,
sum(sum(적립사용포인트)) over (partition by 카드번호
order by 포인트적립사용일자) as 사용가능포인트
from 마일리지테이블
group by 카드번호,
포인트적립사용일자;
결과 테이블(사용가능포인트테이블) :
2) 비어 있는 순번 찾기
위에서 예로 들었던 마일리지테이블의 순번은 신용카드별 포인트적립사용일자별 순번이다.
즉, 특정 신용카드에 대해 동일 일자에 마일리지 포인트를 적립 또는 사용한 횟수가 다수인 경우 순번을 1부터 차례로 증가시킨다. 신용카드의 마일리지 포인트에 대한 적립 또는 사용 이력에 대한 삭제가 가능해서 중간에 비어 있는 순번이 존재하고 이를 찾아서 조회해야 하는 요구를 만족하는 SQL을 작성해야 한다고 가정해보자.
우선 대상 마일리지테이블의 데이터는 다음과 같다.
대상 테이블 :
위의 테이블에 대해 비어 있는 순번을 카드번호와 포인트적립사용일자별로 출력하는 SQL은 다음과 같다.
SQL :
select a.카드번호,
a.포인트적립사용일자,
b.no
from (select 카드번호, 포인트적립사용일자, 순번,
lag(순번, 1, 0) over (partition by 카드번호,
포인트적립사용일자 order by 순번) as 이전순번
from 마일리지테이블) a,
copy_t b -- 비어 있는 순번 재생을 위한 복제용 테이블
where a.순번 > a.이전순번 + 1
and b.no between a.이전순번 + 1 and a.순번 - 1;
결과 집합 :
4. 맺음말
총 4회에 걸쳐서 Oracle 8i 이상의 버전에서 지원하는 각종의 analytic family에 대해 고찰하였다. 연재를 통해 살펴 본 바와 같이 analytic function은 관계형 데이터베이스의 기본 개념에 pointer와 offset의 개념을 부분적으로 도입함으로써 고난이도의 SQL 활용 수준을 요구하던 종래의 복잡한 analytic task를 효율적으로 단순화해서 처리할 수 있도록 했다. 향후에도 Oracle을 비롯한 여러 DBMS 개발업체는 analytic function과 같은 개발생산성 및 성능 향상에 도움이 되는 다양한 기능들을 양산할 것이며 이에 대한 활용도 및 기존 방법에 대한 대체 효과 등을 심층 연구하여 기회가 닿는 대로 기고할 것이다.
읽어주신 독자께 감사의 글을 전하며 조금이나마 기술적 진보에 도움이 되길 바라는 마음으로 총 4회에 걸친 analytic function에 대한 연재를 마치고자 한다.