Analytic Function

분석 함수는 Aggregate Function의 계산을 지정하는 행 그릅을 기반으로 계산하여 각 그룹에 대해 여러 행을 반환 할 수 있는 Function을 의미한다. 일반적으로 누적 계산, 집계 및 보고용 결과를 질의 할 때 유용하게 사용 할 수 있으며 복잡한 질의를 보다 간편하고 빠르게 실행 할 수 있게 도와준다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , SUM(sal) AS dept_tot

       FROM emp;

ERROR at line 1:

ORA-00937: not a single-group group function

위의 쿼리는 SUM 이라는 Aggregate Function(Group Function)을 GROUP BY 절 없이 일반 컬럼들과 함께 사용해서 발생한 오류이다.

다음 쿼리는를 보면,

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , SUM(sal) OVER(PARTITION BY deptno) AS dept_tot

       FROM emp;

EMPNO ENAME  SAL  DEPNO DEPT_TOT

----- ------ ---- ----- --------

7782  CLARK  2450 10        8750

7839  KING   5000 10        8750

7934  MILLER 1300 10        8750

7566  HONES  2975 20       10875

7902  FORD   3000 20       10875

7876  ADAMS  1100 20       10875

7369  SMITH   800 20       10875

7788  SCOTT  3000 20       10875

7521  WARD   1250 30        9400

7844  TURNER 1500 30        9400

7499  ALLEN  1600 30        9400

7900  JAMES   950 30        9400

7698  BLAKE  2850 30        9400

7654  MARTIN 2500 30        9400

각 그룹당 동일한 Function의 결과를 반복 출력하며 오류 없이 실행 가능하다.

만약, 위와 같은 결과를 확인 하고자 했을 때 분석 함수를 사용하지 않는다면 아래와 같은 쿼리를 사용해야 한다.

SQL> SELECT a.empno

          , a.ename

          , a.sal

          , a.deptno

          , b.dept_tot

       FROM emp a

          , (SELECT deptno, SUM(sal) as dept_tot FROM emp GROUP BY deptno) b

      WHERE a.deptno = b.deptno;

결과는 동일하게 나온다.

그러나 불필요한 서브쿼리를 사용해야 하며, 원본 집합인 emp 테이블을 반복적으로 액세스하는 등 비효율적인 문장이 된다.

분석 함수는 원하는 결과를 가져다 주는 쿼리를 보다 쉽게 만들 수 있도록 도와주며, 성능 향상을 시켜주므로 그 쓰임새를 익혀둘 필요가 있다.

분석 함수 정의

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , SUM(sal) OVER(PARTITION BY deptno) AS dept_tot

       FROM emp;

분석 함수는 Aggregate Function 뒤에 Analytic Clause (OVER 절)을 통해 행 그룹의 정의를 지정하고, 각 그룹당 결과 값을 반복하여 출력한다. 여기서 행 그룹의 범위를 WINDOW라 부른다. 하나의 WINDOW가 계산을 수행하는데 사용되는 행들의 집합을 결정하게 되며 PARTITION BY, ORDER BY, WINDOWING을 통해 조절하게 된다.

또한, 분석 함수는 Join 문장, WHERE, GROUP BY, HAVING 등과 함께 쓰일 떄 가장 마지막에 연산(집계)을 진행하며 SELECT 절과 ORDER BY 절에서만 사용이 가능하다.

매뉴얼 상의 문법을 먼저 확인하면 다음과 같다.

Database SQL Reference

분석 함수 사용 시 OVER 절의 사용법만 정확히 이해한다면 나머지는 Aggregate Function들의 특징 몇가지만 이해하면 된다.

우선 OVER 절을 살펴보자.

PARTITION BY 절은 GROUP BY 절과 동일한 역할을 진행한다. 단, GROUP BY 절을 사용하지 않고 필요한 집합으로 (WINDOW) 행들을 그룹화한다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , AVG(sal) OVER() AS avg_sal

       FROM emp;

EMPNO ENAME  SAL  AVG_SAL

----- ------ ---- --------

7369  SMITH   800 2073.21429

7499  ALLEN  1600 2073.21429

7521  WARD   1250 2073.21429

7566  JONES  2975 2073.21429

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , job

          , AVG(sal) OVER(PARTITION BY deptno, job) AS avg_sal

       FROM emp; -- 부서별, 업무별 평균 급여

EMPNO ENAME  SAL  DEPTNO JOB       AVG_SAL

----- ------ ---- ------ --------- --------

7934  MILLER 1300 10     CLERK     1300

7782  CLARK  2450 10     MANAGER   2450

7839  KING   5000 10     PRESIDENT 5000

7788  SCOTT  3000 20     ALANYST   3000

SQL> SELECT d.deptno

          , d.dname

          , e.ename

          , e.sal

          , AVG(e.sal) OVER(PARTITION BY d.deptno) AS avg_sal

       FROM emp e

          , dept d

      WHERE d.deptno = e.deptno; -- 조인 후 부서별 평균 급여

DEPTNO DNAME      ENAME  SAL    AVG_SAL

------ ---------- ------ ------ ----------

10     ACCOUNTING CLARK  2450   2916.66667

10     ACCOUNTING KING   5000   2916.66667

10     ACCOUNTING MILLER 1300   2916.66667

20     RESEARCH   JONES  2975   2175

20     RESEARCH   FORD   3000   2175

PARTITION BY 절을 사용 함으로 GROUP BY 절 없이 다양한 Grouping 집합의 집계 결과들을 함께 출력 할 수 있다,

ORDER BY 절은 PARTITION BY 절로 정의 된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다.

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , ROW_NUMBER() OVER(ORDER BY sal ASC) AS rnum

       FROM emp; -- 전체 급여를 오름차순으로 정렬 했을 경우 Row Number

EMPNO ENAME  SAL    DEPTNO RNUM

----- ------ ------ ------ ----

7369  SMITH   800   20     1

7900  JAMES   950   30     2

7876  ADAMS  1100   20     3

7521  WARD   1250   30     4

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rnum

       FROM emp; -- 부서별 급여를 내림차순으로 정렬 했을 경우 Row Number

EMPNO ENAME  SAL    DEPTNO RNUM

----- ------ ------ ------ ----

7839  KING   5000   10     1

7782  CLARK  2450   10     2

7934  MILLER 1300   10     3

7788  SCOTT  3000   20     1

7902  FORD   3000   20     2

부서번호가 바뀔 때 Row Number는 새로 시작 되는 것을 확인 할 수 있다.

SQL> SELECT empno

          , ename

          , sal

          , comm

          , DENSE_RANK() OVER(ORDER BY comm ASC) AS rnum

       FROM emp

      WHERE deptno = 30; -- NULL 값은 정렬 시 가장 큰 값으로 인식(기본 설정)

EMPNO ENAME  SAL    COMM   RNUM

----- ------ ------ ------ ----

7844  TURNE  1500      0   1

7499  ALLEN  1600    300   2

7521  WARD   1250    500   3

7654  MARTIN 1250   1400   4

7900  JAMES   950          5

7698  BLAKE  2850          5

7782  CLARK  2450          5

SQL> SELECT empno

          , ename

          , sal

          , comm

          , DENSE_RANK() OVER(ORDER BY comm ASC NULL FIRST) AS rnum

       FROM emp

      WHERE deptno = 30; -- NULL 값을 가장 작은 값으로 설정

EMPNO ENAME  SAL    COMM   RNUM

----- ------ ------ ------ ----

7900  JAMES   950          1

7698  BLAKE  2850          1

7782  CLARK  2450          1

7844  TURNE  1500      0   2

7499  ALLEN  1600    300   3

7521  WARD   1250    500   4

7654  MARTIN 1250   1400   5

ORDER BY 절은 PARTITION BY 절에 의해 그룹화 된 행들의 정렬 순서를 결정하며, NULL 값을 가지고 있는 행이 있을 경우 NULL에 대한 값을 FIRST, LAST로 보낼 수 있도록 조절 가능하다.

RANK(), DENSE_RANK(), ROW_NUMBER()의 차이점

SQL> SELECT empno

          , ename

          , deptno

          , sal

          , RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rank

          , DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS d_rank

          , ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rnum

       FROM emp

      WHERE deptno IN (10, 20);

EMPNO ENAME  DEPTNO SAL    RANK   D_RANK RNUM

----- ------ ------ ------ ------ ------ ------

7839  KING   10     5000   1      1      1

7782  CLARK  10     2450   2      2      2

7934  MILLER 10     1300   3      3      3

7788  SCOTT  20     3000   1      1      1

7902  FORD   20     3000   1      1      2

7566  JONES  20     2975   3      2      3

7876  ADAMS  20     1100   4      3      4

7369  SMITH  20      800   5      4      5

현재 결과는 부서별 급여를 기준으로 내림차순으로 정렬 하였을 때 순번을 보여준다. 이때 RANK()는 동등 순위 발생 시 중복된 값만큼 증가 시킨 3이 표시되며, DENSE_RANK()는 동등 순위 번호는 같게 표시하며 다음 순위를 2로 표시한다.

즉, 중복된 값 만큼의 증가치는 없다는 것이며 ROW_NUMER()는 동등 순위 자체를 인식하지 않고 매번 증가되는 번호를 표시한다.

WINDOW 절은 일부 Aggregate Function과 함께 쓰일 수 있으며 행들의 그룹을 물리적, 논리적으로 조절하여 Function이 적용될 WINDOW 절을 정의한다. 즉, PARTITION BY 절은 컬럼에 같은 값을 기준으로만 그룹화를 시키지만, WINDOWING 절은 ROWS와 RANGE를 이용하여 하나의 WINDOW를 결정하는 범위를 보다 자유롭게 조정할 수 있다.

ROWS: 물리적인 단위 결정

SQL> SELECT empno

          , ename

          , sal

          , SUM(sal) OVER(ORDER BY empno

                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_sal

       FROM emp

      WHERE deptno IN (10, 20);

EMPNO ENAME  SAL    SUM_SAL

----- ------ ------ -------

7369  SMITH   800     800

7566  JONES  2975    3775   <-   800 + 2975

7782  CLARK  2450    6225   <-  3775 + 2450

7788  SCOTT  3000    9225   <- ...

7839  KING   5000   14225   <- ...

7876  ADAMS  1100   15325   <- ...

7902  FORD   3000   18325   <- ...

7934  MILLER 1300   19625   <- 18325 + 1300

ROWS는 WINDOW 절의 범위를 정의 할 떄 물리적인 행을 지정하는 부분이다. 어떤 행에서 시작해서 어떤 행까지 하나의 WINDOW 영역으로 정의 할지 범위를 BETWEEN을 통하여 정의 할 수 있다. UNBOUNDED PRECEDING은 첫 번째 행을 가리키며 UNBOUNDED FOLLOWING은 마지막 행을 의미한다. CURRENT ROW는 현재 행을 의미한다.

그럼 위의 결과는 empno 순으로 오름차순 정렬한 집합에서 첫 번째 행부터 현재 행까지의 SUM(sal)을 계산하는 누적 집계를 보여준다.

아래의 문장은 동일한 결과를 보여준다.

SQL> SELECT empno

          , ename

          , sal

          , SUM(sal) OVER(ORDER BY empno

                                ROWS BETWEEN UNBOUNDED PRECEDING) AS sum_sal

       FROM emp

      WHERE deptno IN (10, 20);

또는

SQL> SELECT empno

          , ename

          , sal

          , SUM(sal) OVER(ORDER BY empno) AS sum_sal

       FROM emp

      WHERE deptno IN (10, 20);

즉, BETWEEN을 사용하지 않고 시작되는 행만을 지정하면 종료 행은 CURRENT ROW가 지정되게 된다.

BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 사용하면 첫 번째 행부터 마지막 행까지 하나의 WINDOW로 정의 할 수 있다.

SQL> SELECT empno

          , ename

          , sal

          , SUM(sal) OVER(ORDER BY empno

                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_sal

       FROM emp

      WHERE deptno IN (10, 20);

EMPNO ENAME  SAL    SUM_SAL

----- ------ ------ -------

7369  SMITH   800     800

7566  JONES  2975    3775   <-   800 + 2975

7782  CLARK  2450    5425   <-  2975 + 2450

7788  SCOTT  3000    5450   <- ...

7839  KING   5000    8000   <- ...

7876  ADAMS  1100    6100   <- ...

7902  FORD   3000    4100   <- ...

7934  MILLER 1300    4300   <-  3000 + 1300

시작 행을 이전 행 (1 PRECEDING)으로 설정하였으므로 현재 행과 이전 행만의 합계를 구하는 문장이 된다.

RANGE는 논리적인 값을 근거로 WINDOW 범위를 설정 가능하게 된다.

SQL> SELECT ename

          , TO_CHAR(hiredate, 'YYYY/MM/DD') hiredate

          , sal

          , SUM(sal) OVER(ORDER BY hiredate

                                RANGE BETWEEN INTERVAL '3' MONTH PRECEDING

                                AND INTERVAL '3' MONTH FOLLOWING) AS sum_sal

       FROM emp

      WHERE deptno IN (10, 20);

ENAME  HIREDATE   SAL     SUM_SAL

------ ---------- ------- -------

SMITH  1980/12/17  800     800    <- 전후 3개월에 해당되는 값 없음

JONES  1981/04/02 2975    5425    <- 2975 + 2450 (04/02 ~ 06/09)

CLARK  1981/06/09 2450    5425    <- 2975 + 2450 (04/02 ~ 06/09)

KING   1981/11/17 5000    9300    <- 5000 + 3000 + 1300 (11/17 ~ 01/23)

FORD   1981/12/03 3000    9300    <- 5000 + 3000 + 1300 (11/17 ~ 01/23)

MILLER 1982/01/23 1300    9300    <- 5000 + 3000 + 1300 (11/17 ~ 01/23)

SCOTT  1987/04/19 3000    4100    <- 3000 + 1100 (04/19 ~ 05/23)

ADAMS  1987/05/23 1100    4100    <- 3000 + 1100 (04/19 ~ 05/23)

입사일자를 기준으로 정렬을 한 경우 현재 행이 가지고 있는 입사일자 전후로 3개월씩, 6개월 사이의 합계를 구한다. RANGE는 논리적인 평가를 통해 시작 행과 종료 행을 지정한다.

분석 함수 종류 (10g)

FIRST / LAST

각 WINDOW 영역에서 FIRST/LAST 행의 하나의 행만을 추출 하려는 경우 사용 가능하다. ORDER BY 절을 이용하여 WINDOW 내의 정렬을 진행하고 DENSE_RANK FIRST/LAST로 그들 중 하나의 행을 선택할 수 있다. 이때 동일한 Ranking을 가지고 있는 집합들 중 Aggregate Function의 결과를 보여준다.

-- 급여를 가장 많이 받는 사원과 가장 적게 받는 사원 이름

SQL> SELECT MIN(ename) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_ename

          , MIN(ename) KEEP(DENSE_RANK LAST ORDER BY sal DESC) min_ename

       FROM emp;

MAX_ENAME MIN_NAME

--------- --------

KING      SMITH

-- 부서별 급여를 가장 많이, 적게 받는 사원들의 이름

SQL> SELECT deptno

          , MIN(ename) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_ename

          , MIN(ename) KEEP(DENSE_RANK LAST ORDER BY sal DESC) min_ename

       FROM emp

      GROUP BY deptno;

DEPTNO MAX_ENAME MIN_NAME

------ --------- --------

10     KING      MILLER

20     FORD      SMITH

30     BLAKE     JAMES

NTILE

WINDOW 그룹의 행을 정렬 후 지정한 개수의 범위(등급)로 나눈 후 각 값이 가지고 잇는 등급 값을 보여준다.

-- 사원들의 급여를 많이 받는 순서대로 5개의 등급으로 나눠 표시

SQL> SELECT empno

          , ename

          , sal

          , NTILE(5) OVER(ORDER BY sal DESC) AS grade

       FROM emp;

EMPNO  ENAME     SAL      GRADE

------ --------- -------- -----

7839   KING      5000     1

7902   FORD      3000     1

7788   SCOTT     3000     1

7566   JONES     2975     2

7698   BLAKE     2850     2

7782   CLARK     2450     2

7499   ALLEN     1600     3

7844   TURNER    1500     3

7521   WARD      1250     4

7654   MARTIN    1250     4

7900   JAMES      950     5

7369   SMITH      800     5

CUME_DIST(Cumulative distribution) / PERCENT_RANK

두 함수는 계산식에 차이가 있으나 WINDOW 그룹 내에서 누적 분포를 계산할 때 사용 가능하다. 값의 범위는 0 ~ 1까지 사용되며 PERCENT_RANK는 항상 시작 값이 0부터 시작된다.

-- 급여를 내림차순 기준으로 정렬하여 각 사원의 누적 분포 계산

SQL> SELECT empno

          , ename

          , sal

          , ROUND(PERCENT_RANK() OVER(ORDER BY sal DESC), 2) AS per_rank

          , ROUND(CUME_DIST() OVER(ORDER BY sal DESC), 2) AS cume_dist

          , RANK() OVER(ORDER BY sal DESC) AS rank

          , ROW_NUMBER() OVER(ORDER BY sal DESC) AS row_num

       FROM emp;

EMPNO  ENAME     SAL    PER_RANK CUME_DIST RANK ROW_NUM

------ --------- ------ -------- --------- ---- -------

7839   KING      5000   0        .07       1    1

7902   FORD      3000   .08      .21       2    2

7788   SCOTT     3000   .08      .21       2    3

7566   JONES     2975   .23      .29       4    4

7698   BLAKE     2850   .31      .36       5    5

7782   CLARK     2450   .38      .43       6    6

7499   ALLEN     1600   .46      .5        7    7

7844   TURNER    1500   .54      .57       8    8

7521   WARD      1250   .69      .79       9    9

7654   MARTIN    1250   .69      .79       9    10

PERCENT_RANK: (RANK - 1) / (COUNT(*) - 1)

CUME_DIST: (RANK or ROW_NUMBER) / COUNT(*)

동등 순위의 RANK 발생 시 해당 RANK의 마지막 ROW_NUMBER 사용

SQL> SELECT ROUND(1 - 1) / (14 - 1), 2) AS per, ROUND(1 / 14, 2) cume FROM dual UNION ALL

     SELECT ROUND(2 - 1) / (14 - 1), 2) AS per, ROUND(3 / 14, 2) cume FROM dual UNION ALL

     SELECT ROUND(2 - 1) / (14 - 1), 2) AS per, ROUND(3 / 14, 2) cume FROM dual UNION ALL

     SELECT ROUND(4 - 1) / (14 - 1), 2) AS per, ROUND(4 / 14, 2) cume FROM dual UNION ALL

     SELECT ROUND(5 - 1) / (14 - 1), 2) AS per, ROUND(5 / 14, 2) cume FROM dual;

PER CUME

--- ----

  0 .07

.08 .21

.08 .21

.23. 29

.31 .36

PERCENTILE_CONT / PERCENTILE_DISC

CUM_DIST, PERCENT_RANK의 결과가 누적 분포도를 계산 한다면 PERCENTILE_CONT, PERCENTILE_DISC는 분포도 값(지정되는 백분율)을 역으로 계산하여 실제의 값을 가져온다.

-- 30번 부서번호를 가지고 있는 사원들의 급여을 기준으로 내림차순 정렬

SQL> SELECT empno

          , ename

          , sal

          , deptno

          , ROUND(CUME_DIST() OVER (ORDER BY sal DISC), 2) AS cume_dist

       FROM emp

      WHERE deptno = 30;

EMPNO ENAME  SAL  DEPTNO CUME_DIST

----- -----  ---- ------ ---------

7698  BLAKE  2850 30     .17

7499  ALLEN  1600 30     .33

7844  TURNER 1500 30     .5

7521  WARD   1250 30     .83

7654  MARTIN 1250 30     .83

7900  JAMES  950  30     1

위의 결과에서 1500의 급여가 전체 WINDOW 중의 50% 범위에 해당 하는 것을 확인할 수 있다.

-- 30번 부서번호를 가지고 있는 사원들 중 급여를 기준으로 내립차순 정렬을 하였을 때 50% 범위에 해당하는 급여는 얼마인가?

SQL> SELECT PERSENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC) AS CONT

          , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY sal DESC) AS DISC

       FROM emp

      WHERE deptno = 30;

CONT DISC

---- ----

1375 1500

PERCENTILE_CONT는 선형 보간법을 이용하여 평균에 근거하는 결과를 보여주므로 실제의 값을 보여주는 PERCENTILE_DISC 보다는 정확한 값을 보여주지는 않을 수 있다.

자세한 계산 공식은 메뉴얼을 참고한다(MEDIAN 함수도 같은 결과를 확인 할 수 있다).

SQL> SELECT MEDIAN(sal) AS CONT

       FROM emp

      WHERE deptno = 30;

CONT

----

1375

Hypothetical Functions

가정에 근거하여 각 함수에 맞는 값을 확인 가능하다.

-- 급여가 2000이라면 각각 순위 및 백분율은 얼마인가?

SQL> SELECT RANK(2000) WITHIN GROUP (ORDER BY sal DESC) AS rank

          , DENSE_RANK(2000) WITHIN GROUP (ORDER BY sal DESC) AS dense_rank

          , CUME_DIST(2000) WITHIN GROUP (ORDER BY sal DESC) AS cume_dist

          , PERCENT_RANK(2000) WITHIN GROUP (ORDER BY sal DESC) AS per_rank

       FROM emp;

RANK DENSE_RANK CUME_DIST PER_RANK

---- ---------- --------- ----------

7    6          .46666667 .428571429

FIRST_VALUE / LAST_VALUE

FIRST / LAST와 비슷하게 WINDOW 내의 처음과 마지막 행의 값을 가져 올 수 있으며, WINDOWING 절을 지정하여 원하는 WINDOW의 정의가 가능하다. 또한 NULL을 제외하고 작업이 가능하다(FIRST / LAST는 WINDOWING 절 사용이 불가능하며 NULL이 포함되어 계산됨).

SQL> SELECT ename

          , sal

          , comm

          , FIRST_VALUE(comm IGNORE NULLS) OVER (ORDER BY comm DESC ROWS BETWEEN UNBOUNDING AND UNBOUNDING FOLLOWING) AS fv_c1

          , FIRST_VALUE(comm) OVER (ORDER BY comm DESC ROWS BETWEEN UNBOUNDED PRRECEDING AND UNBOUNDED FOLLOWING) AS fv_c2

          , FIRST_VALUE(comm IGNORE NULLS) OVER (ORDER BY comm DESC) AS fv_c3

          , LAST_VALUE(comm IGNORE NULLS) OVER (ORDER BY comm DESC ROWS BETWEEN UNBOUNDED PRRECEDING AND UNBOUNDED FOLLOWING) AS lv

       FROM emp

      WHERE deptno = 30;

ENAME  SAL  COMM FV_C1 FV_C2 FV_C3 LV

------ ---- ---- ----- ----- ----- --

BLAKE  2850      1400              0

JAMES   950      1400              0

MARTIN 1250 1400 1400        1400  0

WARD   1250  500 1400        1400  0

ALLEN  1600  300 1400        1400  0

TURNER 1500    0 1400        1400  0

6개 행의 기초 값을 확인하면 comm 컬럼 값에는 NULL 값이 2개 존재하며, 0 ~ 1400 범위이다.

OVER 절의 ORDER BY를 이용하여 comm 컬럼을 기준으로 내림차순 정렬한다. 단, ROWS를 사용하여 WINDOW의 범위를 전체로 한 경우와 fv_c3는 WINDOWING 절이 생략되었으므로 첫 번째 행부터 현재 행까지 계산한다.

이렇게 FIRST_VALUE와 LAST_VALUE는 ROWS ? RANGE를 이용하여 WINDOW를 직접 조절할 수 있다는 특징이 있다. 때문에 FIRST_VALUE / LAST_VALUE 사용 시 인라인 뷰를 사용해서 어떤 행이 첫 번째 행으로 하느냐를 조절할 필요도 존재한다.

자세한 내용은 메뉴얼을 참고한다.

LEAD / LAG

지정된 개수의 이전, 이후 행의 값을 가져온다.

WINDOWING 절을 지정하지 못하며, NULL 값을 대체하는 값을 지정할 수 있다(NVL 필요없음).

-- 30번 부서의 사원을 이름순으로 정렬하여 이전, 다음 행의 급여를 함께 표시

SQL> SELECT empno

          , ename

          , sal

          , LAG (sal, 1, 0) OVER (ORDER BY ename) AS prev_sal

          , LEAD (sal, 1, 0) OVER (ORDER BY ename) AS next_sal

       FROM emp

      WHERE deptno = 30;

EMPNO ENAME  SAL  PREV_SAL NEXT_SAL

----- ------ ---- -------- --------

7499  ALLEN  1600    0     2850

7698  BLAKE  2850 1600      950

7900  JAMES   950 2850     1250

7654  MARTIN 1250  950     1500

7844  TURNER 1500 1250     1250

7521  WARD   1250 1500        0

RATIO_TO_REPORT

WINDOW 영역의 합계 내에서 현재 값이 차지하는 백분율 값을 가져온다. 별도의 WINDOWING 절을 설정하는 것은 안된다.

-- 사원 정보를 출력하면서 부서별 급여의 합계 중 해당 사원이 받는 급여의 백분율을 표시하고 부서별 급여의 합계도 함계 출력

SQL> BREAK ON deptno SKIP 1;

SQL> COMPUTE sum LABEL 'total' OF sal ON deptno;

SQL> SELECT deptno

          , ename

          , ROUND(RATIO_TO_REPORT (sal) OVER (PARTITION BY deptno), 2) AS raio

          , sal

       FROM emp;

SQL> CLEAR COMPUTE;

SQL> CLEAR BREAK;

DEPTNO ENAME  RATIO SAL

------ ------ ----- -----

10     CLARK  .28    2450

       KING   .57    5000

       MILLER .15    1300

******              -----

total                8750

20     JONES  .27    2975

       FORD   .28    3000

       ADAMS  .1     1100

       SMITH  .07     800

       SCOTT  .28    3000

******              -----

total               10875

sqlplus의 몇몇 계산 명령어를 이용하면 원하는 결과를 쉽게 가져 올 수 있다.

출처: Oracle과 BigData