[펌]오라클 분석 함수

6. Analytic 함수
 6-1) AVG* 함수
 6-2) CORR* CORR* 함수
 6-3) COUNT* 함수
 6-4) COVAR_SAMP 함수
 6-5) CUME_DIST 함수
 6-6) DENSE_RANK 함수
 6-7) FIRST 함수
 6-8) FIRST_VALUE 함수
 6-9) LAG 함수
 6-10) LAST_VALUE 함수
 6-11) LEAD 함수
 6-12) NTILE 함수
 6-13) RATIO_TO_REPORT 함수
 6-14) ROW_NUMBER 함수

 

6-1) AVG* 함수
--------------------------------------------------------------------------------

조건을 만족하는 행(row)의 평균을 값을 반환하며,
 aggregate 함수나 analytic 함수로 사용된다.

【형식】
AVG( [DISTINCT | ALL] 컬럼명)
   [ [OVER] (analytic 절)]

【예제】aggregate 예
SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

SQL> select avg(salary) from emp;

AVG(SALARY)
-----------
        240

【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
  2  from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)
-----------------------------------------
                               236.666667
                               236.666667
                               236.666667
                                      250

SQL> select avg(salary) over(partition by bonus order by id
    2 rows between 1 preceding and 1 following) as avg  from emp;

       AVG
----------
       245
236.666667
       235
       250

6-2) CORR* CORR* 함수
--------------------------------------------------------------------------------

집합 쌍의 상관관계 계수를 반환한다.

【형식】
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】
SQL> select corr(avg(bonus),max(bonus))
  2  from employees
  3  group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))
---------------------------
                          1

 

6-3) COUNT* 함수
--------------------------------------------------------------------------------

 쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】
SQL> select count(*) from emp;

  COUNT(*)
----------
         4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)
----------------------
                     2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)
-----------------
                4

SQL> select salary,count(*)
  2  over (order by salary)
  3  from emp;

    SALARY COUNT(*)OVER(ORDERBYSALARY)
---------- ---------------------------
       220                           1
       240                           2
       250                           4
       250                           4

6-4) COVAR_SAMP 함수
--------------------------------------------------------------------------------

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】
SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)
------------------------
              83.3333333

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

6-5) CUME_DIST 함수
--------------------------------------------------------------------------------

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
     expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】
SQL> select cume_dist(230) within group
  2  (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)
----------------------------------------
                                      .4

SQL> select * from emp;

        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 

6-6) DENSE_RANK 함수
--------------------------------------------------------------------------------

그룹 내에서 순위를 반환한다.

【예제】
SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

SQL> select dense_rank(230, .05) within group
  2  (order by salary, bonus) "Dense Rank"
  3  from employees;

Dense Rank
----------
         2

 

6-7) FIRST 함수
--------------------------------------------------------------------------------

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-8) FIRST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 첫 번째를 출력한다.

【형식】
FIRST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,first_value(name)
  2  over (order by salary asc)
  3  from (select * from employees
  4        where dept_no = 20
  5        order by salary);

    SALARY FIRST_VALU
---------- ----------
       220 jijoe
       240 jijoe

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-9) LAG 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서,
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LAG ( value_expr [,offset] [,default] )
     OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LAG(salary,1,0)    
  2   OVER (ORDER BY salary) FROM employees;

NAME           SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- ----------------------------------
jijoe             220                                  0
Joe               240                                220
Cho               250                                240
kim               250                                250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------

이 함수는 서열화된 값에서 마지막 번째를 출력한다.

【형식】
LAST_VALUE ( expr ) OVER ( analytic_절)

【예제】
SQL> select salary,last_value(name)
  2  over (order by salary asc)
  3  from (select * from employees
  4        where dept_no = 20
  5        order by salary);

    SALARY LAST_VALUE
---------- ----------
       220 jijoe
       240 Joe

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-11) LEAD 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, self join하지 않고
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】
LEAD ( value_expr [,offset] [,default] )
     OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,LEAD(salary,1,0)   
  2   OVER (ORDER BY salary) FROM  employees;

NAME           SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- -----------------------------------
jijoe             220                                 240
Joe               240                                 250
Cho               250                                 250
kim               250                                   0

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-12) NTILE 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.

【형식】
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
  2  FROM  employees;

NAME           SALARY NTILE(3)OVER(ORDERBYSALARYDESC)
---------- ---------- -------------------------------
Cho               250                               1
kim               250                               1
Joe               240                               2
jijoe             220                               3

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.

【형식】
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])

【예제】
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
  2   FROM  employees;

NAME           SALARY RATIO_TO_REPORT(SALARY)OVER()
---------- ---------- -----------------------------
Cho               250                    .260416667
Joe               240                           .25
kim               250                    .260416667
jijoe             220                    .229166667

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

 

6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------

이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.

【형식】
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )

【예제】
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
  2    FROM  employees;

ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME      
----------------------------------- ----------
                                  1 Cho       
                                  2 kim       
                                  3 Joe       
                                  4 jijoe     

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

[출처]http://huikyun.tistory.com