Analytic Function

***() OVER()함수들 모음

NTILE

순서화된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket 번호를 할당, 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력하는 함수

 

USAGE

NTILE ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT first_name,salary,NTILE(3) OVER (ORDER BY salary DESC)
FROM employees
WHERE job_id = 'IT_PROG';

RESULT

FIRST NAME           SALARY         NTILE(3) OVER (ORDER BY salary DESC)
-------------------------------------------------------------------
Alexander               9000                1
Bruce                     6000                1
David                     4800                2
Valli                       4800                2
Diana                     4200                3

 

COUNT OVER

조건을 만족하는 행의 수를 반환합니다.

 

USAGE

COUNT ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT employee_id,salary,COUNT(*) OVER (ORDER BY salary) AS count
FROM employees
WHERE department_id = 50;

SUM OVER

조건을 만족하는 행의 합을 반환합니다.

USAGE

SUM ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT employee_id,last_name,salary,
SUM(salary) OVER (ORDER BY employee_id) AS acc_salary
FROM employees

 

DECODE

CASE나 IF-THEN-ELSE문처럼 조건부 결과를 반환

USAGE

DECODE(칼럼이름, 비교값, 입력값,
                [비교값2, 입력값2..]
      ,기본값)

EXAMPLE

SELECT last_name,job_id,salary,
DECODE(job_id, 'IT_PROG',1.10*Salary,
        'ST_CLERK',1.15*Salary,
             'SA_REP', 1.20*Salary,
       salary) as Recieve
FROM employees

 

RANK

값의 그룹에서 해당값의 절대순위를 계산
1위 - 공동2위 - 공동2위- 공동2위 - 공동 5위 - 공동 5위 - 7위 순서

USAGE

RANK (expr) WITH GROUP(ORDER BY expr)
RANK () OVER(query_partition_clause order_by_clause);

EXAMPLE

SELECT RANK(15500, .05)
WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Rank"
FROM employees;

RESULT

Rank
----------
4

DENSE RANK

값의 그룹에서 해당값의 상대순위를 계산
1위 - 공동2위 - 공동2위- 공동2위 - 공동 3위 - 공동 3위 - 4위 순서

USAGE

DENSE_RANK (expr) WITH GROUP(ORDER BY expr)
DENSE_RANK () OVER(query_partition_clause order_by_clause);

EXAMPLE

SELECT DENSE_RANK(15500, .05)
WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Rank"
FROM employees;

RESULT

Rank
----------
3

PERCENT_RANK

그룹 수에 대한 값의 순위 퍼센트를 반환.
최고 순위 0, 최대 순위를 1로 놓고 해당 비율을 반환

EXAMPLE

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) AS Percent-Rank
FROM employees;

RESULT

Percent-Rank
-------------
.028037383

FIRST_VALUE

LAST_VALUE

FIRST_VALUE 함수는 받아온 데이터에서 가장 첫번째 데이터를 반환한다.
LAST_VALUE 함수는 반대로 받아온 데이터에서 가장 첫번째 데이터를 반환한다.
오름차순 정렬후 FIRST_VALUE함수로 얻은 데이터는
내림차순 정렬후 LAST_VALUE함수로 얻은 데이터와 같다. (반대의 경우도 마찬가지)

USAGE

FIRST_VALUE(expr) OVER (analyic_clause)
LAST_VALUE(expr) OVER (analyic_clause)

EXAMPLE

SELECT employee_id,salary, FIRST_VALUE(salary)
OVER (PARTITION BY department_id ORDER BY salary DESC) as "Highsal"
FROM employees;

RESULT

employee_id  salary                 Highsal
---------------------------------------------------------
200                     4400                    4400
201                     13000                   13000

 

NVL

변수 1의 값을 체크하여 변수 1이 NULL이면 변수 2의 값을 반환하고
그렇지 않다면 변수1의 값을 반환한다.

USAGE

NVL(expr1, expr2)

EXAMPLE

SELECT last_name, nvl(manager_id, 0) AS Manager_id
FROM employees
WHERE employee_id BETWEEN 100 AND 105;

RESULT

LAST_NAME              manager_id
-----------------------------------
King                     0
Kochhar                  100

 

WIDTH_BUCKET

어떤 값의 최소에서부터 최대값을 설정하고 bucket(구간)을 지정하여 임의의 값이
지정된 범위 내에서 어느 위치에 있는지를 반환한다. <등급 지정>

USAGE

WIDTH_BUCKET(expr, min_value, max_value, num_buckets)

EXAMPLE

SELECT salary, WIDTH_BUCKET(salary, 0, 20000, 5)
FROM employees
ORDER BY salary desc

LAG

SELF JOIN하지 않고 하나의 테이블에서 동시에 한 행 이상을 접근 할 수 있게 함 - 분석용 함수로써, 이전 레코드를 참조(동일한 테이블에 있는 다른 행의 값을 참조하는 함수)

USAGE

LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

EXAMPLE 

SELECT last_name, salary, LAG(salary, 1, 0)
OVER (ORDER BY hire_date) AS prev_sal
 FROM employees
 WHERE job_id = 'PU_CLERK';

RESULT

last_name       salary      LAG(salary,1,0)
---------------------------------------------
Grant              3200        2600
Himuro            2600        3200
Colmenares     2500        2600

 

 

LEAD

SELF JOIN하지 않고 하나의 테이블에서 동시에 한 행 이상을 접근 할 수 있게 함
  - LAG와는 반대로 하나 앞의 레코드 값 참조

USAGE

LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

EXAMPLE

SELECT last_name, salary,LEAD(salary, 1, 0)
OVER (ORDER BY hire_date) AS NextHire
FROM employees
WHERE job_id = 'PU_CLERK';

RESULT

last_name       salary      LEAD(salary,1,0)
---------------------------------------------
Grant              3200        2600
Himuro            2600        2500
Colmenares     2500        3300