Analytic Functions (분석함수)
분석함수는 그룹단위로 값을 계산한다는 점에서 그룹함수와 유사하지만,
그룹마다가 아니라 행마다 1개의 행을 반환한다는 점에서 그룹함수와 상당한 차이가 있다.
(분석함수는 쉽게 생각해서, 그룹을 계산해서 각 행마다 결과를 반환하는 것이다.)
분석함수에서의 그룹을 윈도우(window)라고 부르며,
analytic_clause에서 정의한다.(analytic_clause는 아래에서 설명)
조회되는 각 행마다, 분석함수 값을 계산하기 위한 윈도우를 정의한다. 윈도우의 크기는 행의 갯수나
시간간격을 계산하여 정의할 수 있다. 분석함수는 order by절을 제외하고는 쿼리에서 가장 나중에
실행된다. 따라서 select-list나 order by절에만 사용할 수 있다.
분석함수의 사용법은 아래와 같다.
* 아래의 Syntax Diagram을 읽는 방법은 다음 페이지를 참고한다.
- http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1020&seq=8
analytic_function::=
analytic_function은 위와 같이 4부분으로 구성된다.
1. analytic_function - 함수명
2. arguments - 파라미터, 0~3개의 파라미터를 가질 수 있다.
3. over - 키워드, 분석함수임을 나타낸다.
4. analytic_clause - 아래에서 설명한다.
analytic_function > analytic_clause::=
분석함수는 analytic_clause를 사용하며, select-list나 order by절에 나올 수 있다.
analytic_clause는 위와 같이 3부분으로 구성된다.
* 아래에서 그림과 함께 자세하게 설명한다.
1. query_partition_clause
2. order_by_clause
3. windowing_clause
analytic_function > analytic_clause > query_partition_clause::=
query_partition_clause는 위와 같이 사용하며, 그룹함수를 사용할 때의 group by 절에 해당한다.
emp 테이블에서 아래 쿼리를 실행하면서 partition by(즉, query_partition_clause)의 기능을 알아보자.
SELECT emp.*
, SUM (sal) OVER (PARTITION BY deptno) sum_sal
FROM emp
위의 쿼리를 실행하면, 먼저 아래와 같이 내부적으로 deptno에 따라 그룹을 생성한다.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
7369 SMITH CLERK 7902 1980-12-17 800 20
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7900 JAMES CLERK 7698 1981-12-03 950 30
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
그리고 그룹마다 SUM (sal) 값을 계산해서 행마다 반환한다.
따라서, 같은 그룹의 SUM (sal)은 같은 값을 갖는다. 아래에서 확인하자.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SUM_SAL
--------------------------------------------------------------------------------
7782 CLARK MANAGER 7839 1981-06-09 2450 10 8750
7839 KING PRESIDENT 1981-11-17 5000 10 8750
7934 MILLER CLERK 7782 1982-01-23 1300 10 8750
7369 SMITH CLERK 7902 1980-12-17 800 20 10875
7876 ADAMS CLERK 7788 1987-05-23 1100 20 10875
7902 FORD ANALYST 7566 1981-12-03 3000 20 10875
7788 SCOTT ANALYST 7566 1987-04-19 3000 20 10875
7566 JONES MANAGER 7839 1981-04-02 2975 20 10875
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 9400
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 9400
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 9400
7900 JAMES CLERK 7698 1981-12-03 950 30 9400
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 9400
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 9400
어떤가? 쉽지 않은가?
(이해가 가지 않는 사람은 댓글로 남겨주시기 바란다.)
analytic_function > analytic_clause > order_by_clause::=
분석함수에서의 order by절은 그룹함수의 order by와 기능이 같다.
즉, 분석함수의 값을 계산하기 위해서 내부적으로 정렬을 해주는 것이다.
partition by로 그룹을 만들고 order by로 그 그룹안의 해당값으로 정렬을 하는 것이다.
partition by절이 없을 경우 전체 레코드를 하나의 그룹으로 잡고 정렬한다.
분석함수에는 order by절을 필요로 하지 않는 것도 있다.
예를 들어, sum을 할 때, 정렬을 할 필요는 없는 것이다.
분석함수인 row_number 를 가지고 order by절의 예를 살펴보자.
row_number 함수는 정렬한 순서에 따라 1부터 차례대로 순번을 매겨주는 함수이다.
위의 partition by절 예제와 같이 보면서 아래 쿼리와 결과를 이해해 보자.
(정렬하는 값이 같은 값을 가질때는 임의의 순서로 정렬한다.)
SELECT emp.*
, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal) rnum
FROM emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RNUM
--------------------------------------------------------------------------------
7934 MILLER CLERK 7782 1982-01-23 1300 10 1
7782 CLARK MANAGER 7839 1981-06-09 2450 10 2
7839 KING PRESIDENT 1981-11-17 5000 10 3
7369 SMITH CLERK 7902 1980-12-17 800 20 1
7876 ADAMS CLERK 7788 1987-05-23 1100 20 2
7566 JONES MANAGER 7839 1981-04-02 2975 20 3
7788 SCOTT ANALYST 7566 1987-04-19 3000 20 4
7902 FORD ANALYST 7566 1981-12-03 3000 20 5
7900 JAMES CLERK 7698 1981-12-03 950 30 1
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 3
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 4
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 5
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 6
analytic_function > analytic_clause > windowing_clause ::=
windowing clause는 분석함수를 내부적으로 그룹으로 분리하고 정렬한 후에(order by절은 필수),
현재 행을 기준으로, 그룹에서 어떤 행까지를 함수의 계산에 포함할지를 필터링하는 부분이다.
크게 두가지 rows, range를 사용할 수 있는데,
rows는 현재 행을 기준으로 몇개의 행을 포함하는지를,
range는 현재 행을 기준으로 어떤 값의 범위를 포함하는지를 명시한다.
References
1. Oracle(R) Database SQL Reference 10g Release 2 (10.2) - SQL Functions
- http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm
Document History
최초등록: 2006-04-19
1차 수정: 2006-04-19
2차 수정: 2006-07-17