[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #6

<목차>

• 그룹 내 순위 함수

  • RANK 함수

  • DENSE_RANK 함수

  • ROW_NUMBER 함수

• 일반 집계 함수

  • SUM 함수

  • MAX 함수

  • MIN 함수

  • AVG 함수

  • COUNT 함수

• 그룹 내 행 순서 함수

  • FIRST_VALUE 함수

  • LAST_VALUE 함수

  • LAG 함수

  • LEAD 함수

• 그룹 내 비율 함수

  • RATIO_TO_REPORT 함수

  • PERCENT_RANK 함수

  • CUME_DIST 함수

  • NTILE 함수

---

<< 윈도우 함수 >>

* 기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉽지만, 행과 행간의 관계를 정의하거나, 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어렵다.

---> 이를 해결하기 위해 만든 함수가 윈도우 함수(WINDOW FUNCTION)이다.

 

A. 특징

• 데이터 웨어하우스에서 발전한 기능

• 다른 함수와는 달리 중첩해서 사용할 수는 없지만, 서브쿼리에서는 사용할 수 있다.

 

B. 종류

• 그룹 내 순위 관련 함수

  * RANK, DENSE_RANK, ROW_NUMBER

• 그룹 내 집계 관련 함수

  * SUM, MAX, MIN, AVG, COUNT

• 그룹 내 행 순서 관련 함수

  * FIRST_VALUE, LAST_VALUE, LAG, LEAD

• 그룹 내 비율 관련 함수

  * CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

• 선형 분석을 포함한 통계 분석 관련 함수

 

C. SYNTAX : 윈도우 함수에는 OVER 키워드가 필수적으로 포함된다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) 
FROM 테이블 명;

- WINDOW_FUNCTION

   : 기존에 사용하던 함수도 있고, 새롭게 윈도우 함수용으로 추가된 함수도 있다.

- ARGUMENTS

   : 함수에 따라 0 ~ N개의 인수가 지정된다.

- PARTITION BY 절

   : 전체 집합을 기준에 의해 소그룹으로 나눈다.

- ORDER BY 절

   : 어떤 항목에 대해 순위를 지정할 지 기술한다.

- WINDOWING 절

   : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정한다. ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를 나타낸다. 둘 중 하나를 선택해서  사용할 수 있다.

 

 

* BETWEEN 사용 타입

1. ROWS | 2. RANGE

BETWEEN

1. UNBOUNDED PRECEDING | 2. CURRENT ROW | 3. VALUE_EXPR PRECEDING/FOLLOWING

AND

1. UNBOUNDED FOLLOWING | 2. CURRENT ROW | 3. VALUE_EXPR PRECEDING/FOLLOWING

 

* BETWEEN 미사용 타입

1. ROWS | 2. RANGE

1. UNBOUNDED PRECEDING | 2. CURRENT ROW | 3. VALUE_EXPR PRECEDING

 

- PRECEDING : 앞

- FOLLOWING : 뒤

- ROWS : 행 기준

- RANGE : 값의 범위 기준

- CURRENT ROW : 현재 행부터

- UNBOUNDED : 제일 앞/뒤 행까지

---

 

1. 그룹 내 순위 함수

1-1. RANK 함수

* ORDER BY 를 포함한 SQL 문에서 특정 칼럼에 대한 순위를 구하는 함수이다.

* 특정 범위를 지정할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있다.

* 동일한 값에 대해서는 동일한 순위를 부여한다.

 

* 예시 - 문제

   : 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력하라.

 

* 예시 - 결과

SELECT JOB, ENAME, SAL, 
    RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, 
    RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 
FROM EMP;
 
/*
JOB        ENAME  SAL   ALL_RANK JOB_RANK 
---------- ------ ----- -------- -------- 
PRESIDENT  KING   5000   1        1 
ANALYST    FORD   3000   2        1 
ANALYST    SCOTT  3000   2        1 
MANAGER    JONES  2975   4        1 
MANAGER    BLAKE  2850   5        2 
MANAGER    CLARK  2450   6        3 
SALESMAN   ALLEN  1600   7        1 
SALESMAN   TURNER 1500   8        2 
CLERK      MILLER 1300   9        1 
SALESMAN   WARD   1250   10       3 
SALESMAN   MARTIN 1250   10       3 
CLERK      ADAMS  1100   12       2 
CLERK      JAMES  950    13       3 
CLERK      SMITH  800    14       4 
14개의 행이 선택되었다.
*/

---> ORDER BY SAL DESC 조건으로 정렬이 되었다.

---> 전체 급여에 대한 순위는 제외하고 JOB 별 급여 순위를 구하는 SQL문을 만들어본다.

SELECT JOB, ENAME, SAL, 
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 
FROM EMP;
 
/*
JOB        ENAME  SAL   JOB_RANK 
---------- ------ ----- --------
ANALYST    FORD   3000   1 
ANALYST    SCOTT  3000   1 
CLERK      MILLER 1300   1 
CLERK      ADAMS  1100   2 
CLERK      JAMES  950    3 
CLERK      SMITH  800    4 
MANAGER    JONES  2975   1 
MANAGER    BLAKE  2850   2
MANAGER    CLARK  2450   3 
PRESIDENT  KING   5000   1 
SALESMAN   ALLEN  1600   1 
SALESMAN   TURNER 1500   2 
SALESMAN   MARTIN 1250   3 
SALESMAN   WARD   1250   3 
14개의 행이 선택되었다.
*/

---> PARTITION BY 의 기준이 된 JOB과 SAL 별로 정렬이 되었다.

 

1-2. DENSE_RANK 함수

* RANK 함수와 유사하나, 동일한 순위의 데이터들을 각각 카운트하는 RANK와는 달리 하나의 건수로 취급하는 차이점이 있다.

 

* 예시 - 문제

   : 사원 테이블에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력하라.

 

* 예시 - 결과

SELECT JOB, ENAME, SAL, 
    RANK( ) OVER (ORDER BY SAL DESC) RANK, 
    DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK 
FROM EMP;
 
/*
JOB           ENAME  SAL   RANK  DENSE_RANK 
------------- ------ ----- ----- ---------- 
PRESIDENT     KING   5000   1     1 
ANALYST       FORD   3000   2     2 
ANALYST       SCOTT  3000   2     2 
MANAGER       JONES  2975   4     3 
MANAGER       BLAKE  2850   5     4 
MANAGER       CLARK  2450   6     5 
SALESMAN      ALLEN  1600   7     6
SALESMAN      TURNER 1500   8     7 
CLERK         MILLER 1300   9     8 
SALESMAN      WARD   1250   10    9 
SALESMAN      MARTIN 1250   10    9 
CLERK         ADAMS  1100   12    10 
CLERK         JAMES  950    13    11 
CLERK         SMITH  800    14    12 
14개의 행이 선택되었다.
*/

---> line10, 11을 보면 같은 2등이지만 line13의 데이터는 RANK일 때 4등, DENSE_RANK일 때 3등으로 처리된다. (2등인 2개의 데이터를 하나의 건으로 보기 때문에 다음 등수는 3등인 셈이다.)

 

1-3. ROW_NUMBER 함수

* RANK나 DENSE_RANK가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여하기 때문에 다른 순위를 가진다.

 

* 예시 - 문제

   : 사원 테이블에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 함께 출력하라.

 

* 예시 - 결과

SELECT JOB, ENAME, SAL, 
    RANK() OVER (ORDER BY SAL DESC) RANK, 
    ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER 
FROM EMP;
 
/*
JOB           ENAME  SAL   RANK  ROW_NUMBER 
------------- ------ ----- ----- ---------- 
PRESIDENT     KING   5000   1     1 
ANALYST       FORD   3000   2     2 
ANALYST       SCOTT  3000   2     3 
MANAGER       JONES  2975   4     4 
MANAGER       BLAKE  2850   5     5 
MANAGER       CLARK  2450   6     6 
SALESMAN      ALLEN  1600   7     7
SALESMAN      TURNER 1500   8     8 
CLERK         MILLER 1300   9     9 
SALESMAN      WARD   1250   10    10 
SALESMAN      MARTIN 1250   10    11
CLERK         ADAMS  1100   12    12 
CLERK         JAMES  950    13    13 
CLERK         SMITH  800    14    14 
14개의 행이 선택되었다.
*/

 

 

2. 일반 집계 함수

2-1. SUM 함수

* SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.

 

* 예시 - 문제

   : 사원들의 급여, 같은 매니저를 두고 있는 사원들의 급여의 합을 출력하라.

 

* 예시 - 결과

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM 
FROM EMP;
 
/*
MGR  ENAME  SAL  MGR_SUM 
---- ------ ---- ------- 
7566 FORD   3000 6000 
7566 SCOTT  3000 6000 
7698 JAMES  950  6550 
7698 ALLEN  1600 6550 
7698 WARD   1250 6550 
7698 TURNER 1500 6550 
7698 MARTIN 1250 6550 
7782 MILLER 1300 1300 
7788 ADAMS  1100 1100 
7839 BLAKE  2850 8275 
7839 JONES  2975 8275 
7839 CLARK  2450 8275 
7902 SMITH  800  800 
     KING   5000 5000 
14개의 행이 선택되었다.
*/

---> OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 급여 데이터까지의 누적 값을 출력한다.

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM 
FROM EMP;
 
/*
MGR  ENAME  SAL  MGR_SUM 
---- ------ ---- ------- 
7566 SCOTT  3000 6000 
7566 FORD   3000 6000 
7698 JAMES  950  950 
7698 WARD   1250 3450 << 950 + 1250 + 1250
7698 MARTIN 1250 3450 << 950 + 1250 + 1250
7698 TURNER 1500 4950 << 950 + 1250 + 1250 + 1500
7698 ALLEN  1600 6550 
7782 MILLER 1300 1300 
7788 ADAMS  1100 1100 
7839 CLARK  2450 2450 
7839 BLAKE  2850 5300
7839 JONES  2975 8275
7902 SMITH  800  800 
     KING   5000 5000 
14개의 행이 선택되었다.
*/

---> line10, 11은 같은 SAL 값을 가지므로 같은 ORDER로 취급하여 950+1250+1250=3450의 값이 되었다.

 

2-2. MAX 함수

* 파티션별 윈도우의 최대값을 구할 수 있다.

 

* 예시 - 문제

   : 사원들의 급여, 같은 매니저를 두고 있는 사원들의 급여 중 최대값을 같이 구한다.

 

* 예시 - 결과

SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX 
FROM EMP;
 
/*
MGR  ENAME  SAL  MGR_MAX
---- ------ ---- ------- 
7566 FORD   3000 3000 
7566 SCOTT  3000 3000 
7698 JAMES  950  1600 
7698 ALLEN  1600 1600 
7698 WARD   1250 1600 
7698 TURNER 1500 1600 
7698 MARTIN 1250 1600 
7782 MILLER 1300 1300 
7788 ADAMS  1100 1100 
7839 BLAKE  2850 2975 
7839 JONES  2975 2975 
7839 CLARK  2450 2975 
7902 SMITH  800  800 
     KING   5000 5000 
14개의 행이 선택되었다.
*/

---> INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출할 수도 있다. (같은 매니저를 가지는 사원들 중 최대 급여가 같은 사원이 있으면 같이 출력됨)

SELECT MGR, ENAME, SAL 
FROM (
    SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL 
    FROM EMP
    ) 
WHERE SAL = IV_MAX_SAL ;
 
/*
MGR  ENAME  SAL 
---- ------ ----- 
7566 FORD   3000 
7566 SCOTT  3000 
7698 ALLEN  1600 
7782 MILLER 1300 
7788 ADAMS  1100 
7839 JONES  2975 
7902 SMITH  800 
     KING   5000 
8개의 행이 선택되었다.
*/

 

2-3. MIN 함수

* 파티션별 윈도우의 최소값을 구할 수 있다.

 

* 예시 - 문제

   : 사원들의 급여, 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 오름차순 정렬하고, 급여의 최소값을 같이 출력하라.

 

* 예시 - 결과

SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN 
FROM EMP;
 
/*
MGR  ENAME  HIREDATE   SAL  MGR_MIN 
---- ------ ---------- ---- ------- 
7566 FORD   1981-12-03 3000 3000 
7566 SCOTT  1987-07-13 3000 3000 
7698 ALLEN  1981-02-20 1600 1600 
7698 WARD   1981-02-22 1250 1250 
7698 TURNER 1981-09-08 1500 1250 
7698 MARTIN 1981-09-28 1250 1250 
7698 JAMES  1981-12-03 950  950 
7782 MILLER 1982-01-23 1300 1300 
7788 ADAMS  1987-07-13 1100 1100 
7839 JONES  1981-04-02 2975 2975 
7839 BLAKE  1981-05-01 2850 2850 
7839 CLARK  1981-06-09 2450 2450 
7902 SMITH  1980-12-17 800  800 
     KING   1981-11-17 5000 5000 
14개의 행이 선택되었다.
*/

 

2-4. AVG 함수

* AVG함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다.

 

* 예시 - 문제

   : EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 급여를 구하는데, 조건이 같은 매니저 내에서 자기 바로 앞 사번과 뒷 사번의 직원만을 대상으로 한다.

 

* 예시 - 결과

SELECT MGR, ENAME, HIREDATE, SAL, 
    ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG 
FROM EMP;
 
/*
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 
    : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정. (ROWS는 현재 행의 앞뒤 건수를 말하는 것)
*/
 
/*
MGR  ENAME  HIREDATE   SAL  MGR_AVG 
---- ------ ---------- ---- ------- 
7566 FORD   1981-12-03 3000 3000 
7566 SCOTT  1987-07-13 3000 3000 
7698 ALLEN  1981-02-20 1600 1425 <
7698 WARD   1981-02-22 1250 1450 
7698 TURNER 1981-09-08 1500 1333 <
7698 MARTIN 1981-09-28 1250 1233 
7698 JAMES  1981-12-03 950  1100 
7782 MILLER 1982-01-23 1300 1300 
7788 ADAMS  1987-07-13 1100 1100 
7839 JONES  1981-04-02 2975 2913 
7839 BLAKE  1981-05-01 2850 2758 
7839 CLARK  1981-06-09 2450 2650 
7902 SMITH  1980-12-17 800  800 
     KING   1981-11-17 5000 5000 
14개의 행이 선택되었다.
*/

---> line18을 기준으로 MGR이 7698로 동일한 앞의 행, 뒤의 행 하나씩을 범위로 평균을 구한다.

     즉 (1250+1500+1250) / 3 = 1333.

---> 첫 값/마지막 값에서는 뒤의 행/앞의 행 하나만을 범위로 둔다. (ex. line16 : 첫 값)

     즉 (1600+1250) / 2 = 1425.

 

2-5. COUNT 함수

* COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다.

 

* 예시 - 문제

   : 사원들을 급여 기준으로 오름차순 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 함께 출력하라.

 

* 예시 - 결과

SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT 
FROM EMP;
 
/*
    RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING 
    : 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다.
    (RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)
*/
 
/*
NAME   SAL  SIM_CNT  ( 범위값 ) 
------ ---- -------- --------- 
SMITH  800  2        (750~ 950) 
JAMES  950  2        (900~1100) 
ADAMS  1100 3        (1050~1250) <
WARD   1250 3        (1200~1400) 
MARTIN 1250 3        (1200~1400) 
MILLER 1300 3        (1250~1450) 
TURNER 1500 2        (1450~1650) 
ALLEN  1600 1        (1550~1750) 
CLARK  2450 1        (2400~2600) 
BLAKE  2850 4        (2800~3000) 
JONES  2975 3        (2925~3125) 
SCOTT  3000 3        (2950~3100) 
FORD   3000 3        (2950~3100) 
KING   5000 1        (4950~5100) 
14개의 행이 선택되었다.
*/

---> 파티션이 지정되지 않았으므로 모든 건수를 대상으로 각 SAL이 기준이 되는 SAL의 -50 ~ +150 기준에 맞는지 검사하게 된다.

 

---> line15의 데이터를 예로, 기준 SAL=1100이며, RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING의 범위는 (1050 ~ 1250)이다. 모든 건수를 대상으로 해당 범위에 포함되는 건수는 1100, 1250, 1250으로 3건이므로 COUNT 값은 3이다.

 

 

3. 그룹 내 행 순서 함수

3-1. FIRST_VALUE 함수

* 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

* MIN 함수를 활용하여 같은 결과를 얻을 수 있다.

* SQL Server 에서는 지원하지 않는 함수이다.

 

* 예시 - 문제

   : 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.

 

* 예시 - 결과

SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH 
FROM EMP; 
 
/*
    ROWS UNBOUNDED PRECEDING
    현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
*/
 
/*
DEPTNO ENAME  SAL  DEPT_RICH 
------ ------ ---- --------- 
10     KING   5000 KING 
10     CLARK  2450 KING 
10     MILLER 1300 KING 
20     SCOTT  3000 SCOTT <
20     FORD   3000 SCOTT <
20     JONES  2975 SCOTT 
20     ADAMS  1100 SCOTT 
20     SMITH  800  SCOTT 
30     BLAKE  2850 BLAKE 
30     ALLEN  1600 BLAKE 
30     TURNER 1500 BLAKE 
30     MARTIN 1250 BLAKE 
30     WARD   1250 BLAKE 
30     JAMES  950  BLAKE 
14개의 행이 선택되었다.
*/

---> line16, 17을 보면 SAL이 같지만 SCOTT으로 첫 값이 지정되어 있다. 즉 가장 높은 연봉을 받는 사원이 SCOTT이라는 뜻인데, 실제로는 FORD와 같은 연봉을 갖는다. 공동 등수가 있는 경우 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 절에 다른 칼럼을 추가해야 한다.

 

* ORDER BY 정렬 조건을 추가하여 나타내어 본다.

SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP 
FROM EMP;
 
/*
    ROWS UNBOUNDED PRECEDING
    현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
*/
 
/*
DEPTNO ENAME  SAL  RICH_EMP 
------ ------ ---- --------- 
10     KING   5000 KING 
10     CLARK  2450 KING 
10     MILLER 1300 KING 
20     FORD   3000 FORD <
20     SCOTT  3000 FORD <
20     JONES  2975 FORD <
20     ADAMS  1100 FORD <
20     SMITH  800  FORD <
30     BLAKE  2850 BLAKE 
30     ALLEN  1600 BLAKE 
30     TURNER 1500 BLAKE 
30     MARTIN 1250 BLAKE 
30     WARD   1250 BLAKE 
30     JAMES  950  BLAKE 
14개의 행이 선택되었다.
*/

---> ORDER BY에 SAL이 같은 경우 ENAME을 오름차순으로 정렬하는 조건을 추가했다. SCOTT보다 FORD가 사전순으로 앞에 있으므로 FORD가 가장 먼저 출력되는 값이 되었다.

 

3-2. LAST_VALUE 함수

* 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.

* MAX 함수를 활용하여 같은 결과를 얻을 수 있다.

* SQL Server 에서는 지원하지 않는 함수이다.

 

* 예시 - 문제

   : 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.

 

* 예시 - 결과

SELECT DEPTNO, ENAME, SAL, 
    LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR 
FROM EMP;
 
/*
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 
    현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.
*/
 
/*
DEPTNO ENAME   SAL  DEPT_POOR 
------ ------- ---- --------- 
10     KING    5000 MILLER 
10     CLARK   2450 MILLER 
10     MILLER  1300 MILLER 
20     SCOTT   3000 SMITH 
20     FORD    3000 SMITH 
20     JONES   2975 SMITH 
20     ADAMS   1100 SMITH 
20     SMITH   800  SMITH 
30     BLAKE   2850 JAMES 
30     ALLEN   1600 JAMES 
30     TURNER  1500 JAMES 
30     MARTIN  1250 JAMES 
30     WARD    1250 JAMES 
30     JAMES   950  JAMES 
14개의 행이 선택되었다.
*/

---> FIRST_VALUE와 마찬가지로 공동 등수가 있는 경우 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 절에 다른 칼럼을 추가해야 한다.

3-3. LAG 함수

* 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

* SQL Server 에서는 지원하지 않는 함수이다.

* 3개의 Arguments 까지 사용할 수 있다.

• Arg2 : 몇 번째 앞의 행을 가져올지 결정하는 인자 (Default 는 1이다.)
• Arg3 : 이전 행의 데이터를 가져오는데 가져올 데이터가 NULL 인 경우, 대체할 값을 설정한다. (NVL, ISNULL과 기능이 같다)
  

 

* 예시 - 문제

   : JOB이 'SALESMAN'인 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력하라.

 

* 예시 - 결과

SELECT ENAME, HIREDATE, SAL, 
    LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL 
FROM EMP 
WHERE JOB = 'SALESMAN' ;
 
/*
ENAME   HIREDATE   SAL  PREV_SAL 
------- ---------- ---- ------- 
ALLEN   1981-02-20 1600 
WARD    1981-02-22 1250 1600 
TURNER  1981-09-08 1500 1250 
MARTIN  1981-09-28 1250 1500 
4개의 행이 선택되었다.
*/

---> 바로 이전 행이 아닌 두 번째 전의 행의 급여를 가져오고, 값이 NULL 인 경우 0으로 처리하자.

SELECT ENAME, HIREDATE, SAL, 
    LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL 
FROM EMP 
WHERE JOB = 'SALESMAN' ;
 
/*
ENAME   HIREDATE   SAL  PREV_SAL 
------- ---------- ---- ------- 
ALLEN   1981-02-20 1600 0 
WARD    1981-02-22 1250 0 
TURNER  1981-09-08 1500 1600 
MARTIN  1981-09-28 1250 1250 
4개의 행이 선택되었다.
*/

---> LAG의 Arg2 = 2 (두 번째 전의 행), Arg3 = 0 (NULL 값을 0으로 처리)

 

3-4. LEAD 함수

* 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

* SQL Server 에서는 지원하지 않는 함수이다.

* 3개의 Arguments 까지 사용할 수 있다.

• Arg2 : 몇 번째 후의 행을 가져올지 결정하는 인자 (Default 는 1이다.)
• Arg3 : 이후 행의 데이터를 가져오는데 가져올 데이터가 NULL 인 경우, 대체할 값을 설정한다. (NVL, ISNULL과 기능이 같다)
  

 

* 예시 - 문제

   : 직원들을 입사일자가 빠른 기준으로 정렬하고, 바로 다음에 입사한 사원의 입사일자를 함께 출력하라. 단, 데이터가 없는 경우 '없음'으로 처리하라.

 

* 예시 - 결과

SELECT ENAME, HIREDATE, 
    LEAD(HIREDATE, 1, '없음') OVER (ORDER BY HIREDATE) as "NEXTHIRED" 
FROM EMP;
 
/*
ENAME  HIREDATE   NEXTHIRED 
------ ---------- --------- 
ALLEN  1981-02-20 1981-02-22 
WARD   1981-02-22 1981-04-02 
TURNER 1981-09-08 1981-09-28 
MARTIN 1981-09-28 없음
4개의 행이 선택되었다.
*/

 

 

4. 그룹 내 비율 함수

4-1. RATIO_TO_REPORT 함수

* 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.

* 개별 RATIO의 합은 1이다.

* SQL Server에서는 지원하지 않는 함수이다.

 

* 예시 - 문제

   : JOB이 'SALESMAN'인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력하라.

 

* 예시 - 결과

SELECT ENAME, SAL, 
    ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as "R_R" 
FROM EMP
WHERE JOB = 'SALESMAN';
 
/*
ENAME  SAL  R_R 
------ ---- ---- 
ALLEN  1600 0.29 (1600 / 5600) 
WARD   1250 0.22 (1250 / 5600) 
MARTIN 1250 0.22 (1250 / 5600) 
TURNER 1500 0.27 (1500 / 5600) 
4개의 행이 선택되었다.
*/

 

4-2. PERCENT_RANK 함수

* 파티션 내 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다.

* SQL Server에서는 지원하지 않는 함수이다.

 

* 예시 - 문제

   : 같은 부서 소속 사원들의 집합에서 본인의 급여가 많은 순서대로 순서상 몇 번째 위치 쯤에 있는지 0과 1 사이의 값으로 출력한다.

 

* 예시 - 결과

SELECT DEPTNO, ENAME, SAL, 
    PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R 
FROM EMP;
 
/*
DEPTNO ENAME  SAL  P_R 
------ ------ ---- ---- 
10     KING   5000 0 
10     CLARK  2450 0.5 
10     MILLER 1300 1 
20     SCOTT  3000 0 
20     FORD   3000 0 <
20     JONES  2975 0.5 
20     ADAMS  1100 0.75 
20     SMITH  800  1 
30     BLAKE  2850 0 
30     ALLEN  1600 0.2 
30     TURNER 1500 0.4 
30     MARTIN 1250 0.6 
30     WARD   1250 0.6 <
30     JAMES  950  1 
14개의 행이 선택되었다.
*/

---> line12, 20의 RERCENT_RANK는 잘못된 값이 아니다. 같은 SAL 값을 가지기 때문에 같은 ORDER로 취급되었기 때문이다.

 

4-3. CUME_DIST 함수

* 파티션 내 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.

* SQL Server에서는 지원하지 않는 함수이다.

 

* 예시 - 문제

   : 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치 쯤에 있는지 0과 1사이로 출력하라.

 

* 예시 - 결과

SELECT DEPTNO, ENAME, SAL, 
    CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM EMP;
 
/*
DEPTNO ENAME  SAL  CUME_DIST 
------ ------ ---- ---------
10     KING   5000 0.3333 
10     CLARK  2450 0.6667 
10     MILLER 1300 1.0000 
20     SCOTT  3000 0.4000 <
20     FORD   3000 0.4000 <
20     JONES  2975 0.6000 
20     ADAMS  1100 0.8000 
20     SMITH  800  1.0000 
30     BLAKE  2850 0.1667 
30     ALLEN  1600 0.3333 
30     TURNER 1500 0.5000 
30     MARTIN 1250 0.8333 <<
30     WARD   1250 0.8333 <<
30     JAMES   950 1.0000 
14개의 행이 선택되었다.
*/

---> line11, 12와 line19, 20의 결과를 보면 PERCENT_RANK함수와 마찬가지로 SAL 값이 같아 동일한 ORDER를 가지기 때문이다.

 

4-4. NTILE 함수

* 파티션 내 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다.

 

* 예시 - 문제

   : 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.

 

* 예시 - 결과

SELECT ENAME, SAL, 
    NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE 
FROM EMP
 
/*
DEPTNO ENAME   SAL  QUAR_TILE 
------ ------- ---- -------- 
10     KING    5000 1 
10     FORD    3000 1 
10     SCOTT   3000 1  
20     JONES   2975 1 
20     BLAKE   2850 2 
20     CLARK   2450 2 
20     ALLEN   1600 2 
20     TURNER  1500 2 
30     MILLER  1300 3 
30     WARD    1250 3 
30     MARTIN  1250 3 
30     ADAMS   1100 4 
30     JAMES   950  4 
30     SMITH   800  4 
14개의 행이 선택되었다.
*/

---> NTILE(4)는 14명을 4개의 조로 나눈다는 의미 : 4명 4명 3명 3명

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148205&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

데이터 전문가 지식포털 DBGuide.net