Oracle 분석함수

SQL 분석함수
http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1030&seq=2
http://blog.naver.com/amadeause?Redirect=Log&logNo=140019462433

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