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이 탄생함을 알고 있을 것이다. 그 중에서 몇 가지 사례를 추출하여 제공하고자 한다.