[SQLD] 25. 윈도우 함수 (WINDOW FUNCTION)

1. WINDOW FUNCTION 개요

• 행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수를 이름
• 분석 함수(ANALYTIC FUNCTION)이나 순위 함수(RANK FUNCTION)로도 알려짐
• 기존 집계 함수, 새로이 WINDOW 함수 전용 존재
• 서브쿼리 사용 가능, 중첩(NEST)해서 사용 불가

1.1. WINDOW FUNCTION 종류

1. 그룹 내 순위(RANK) 관련 함수

• RANK, DENSE_RANK, ROW_NUMBER
• ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원

2. 그룹 내 집계(AGGREGATE) 관련 함수

• SUM ,MAX, MIN, AVG, COUNT
• ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원
• SQL Server 의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지 않음

3. 그룹 내 행 순서 관련 함수

• FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수
• ORACLE 에서만 지원

4. 그룹 내 비율 관련 함수

• CUME_DIST, PERCENT_RANK - ANSI/ISO SQL 표준과 Oracle DBMS 에서 지원
  [ 정정. RATIO_TO_REPORT - ORACLE 만 지원, PRECENT_RANK, CUME_DIS, NTILE 모두 MS SQL 지원 확인 ]
• NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server 에서 지원
• RATIO_TO_REPORT - Oracle 에서만 지원

5. 선형 분석을 포함한 분석관련 함수 - 생략

1.2. WINDOW FUNCTION SYNTAX

WINDOW FUNCTION SYNTAX

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

• WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.
• ARGUMENTS (인수) : 함수에 따라 0 ~ N 개의 인수가 지정
• OVER : WINDOW 함수에서 OVER 문구는 필수 키워드 !!!

• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술
• WINDOWING 절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
  ROWS 는 물리적인 결과 행의 수, RANGE 는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
  WINDOWING 절은 SQL Server 에서는 지원하지 않는다.

- BETWEEN 사용 타입 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING - BETWEEN 미사용 타입 ROW | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

2. 그룹 내 순위 함수

• 순위 함수 요약

RANK : 1 2 2 4 DENSE_RANK : 1 2 2 3 ROW_NUMBER : 1 2 3 4

가. RANK 함수

• ORDER BY 를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
• 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구함
• 동일한 값에 대해서는 동일한 순위를 부여함

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

* 동일 SALARY 에 대해서 같은 순위 부여

* ORDER BY SAL DESC 와 PARTITION BY JOB ORDER BY SAL DESC 가 충돌 하여 ORDER BY SAL DESC 기준으로 정렬

SQL>

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 ; 

* 전체 SALARY 순위 구하는 ALL_RANK 칼럼 제외, 업무별로 SALARY 순서를 구하는 JOB_RANK 만 출력

* 결과적으로 JOB 과 SALARY 별로 정렬

SQL>

SELECT JOB, ENAME, SAL, 
       RANK() OVER ( PARTITION BY JOB ORDER BY SAL DESC ) JOB_RANK 
FROM EMP ; 

나. DENSE_RANK 함수

• RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급
• 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구함
• 동일한 값에 대해서는 동일한 순위를 부여함

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

SQL>

SELECT JOB, ENAME, SAL, 
       RANK() OVER ( ORDER BY SAL DESC ) RANK, 
       DENSE_RANK() OVER ( ORDER BY SAL DESC ) DENSE_RANK 
FROM EMP ;

 

다. ROW_NUMBER 함수

• RANK 나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는대 반해,
• 동일한 값이라도 고유한 순위를 부여함

* 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력 *

같은 SALARY 에서는 어떤 순서가 정해질지 알수 없다.(Oracle 의 경우 rowid 가 적은 행이 먼저 나옴 )

* DBMS(Oracle rowid) 에 의해 정렬을 원지 않는다면, 명시적으로 ORDER BY 추가 할 것

SQL>

SELECT JOB, ENAME, SAL, 
       RANK() OVER ( ORDER BY SAL DESC ) RANK, 
       ROW_NUMBER() OVER ( ORDER BY SAL DESC ) ROW_NUMBER 
FROM EMP ;

 

3. 일반 집계 함수

가. SUM 함수

• SUM 함수를 이용해 파티션별로 윈도우의 합 구하기

* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구함

* PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화

SQL>

SELECT MGR, ENAME, SAL, SUM(SAL) OVER ( PARTITION BY MGR ) MGR_SUM 
FROM EMP ;

 

 * OVER 절 내의 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력

( <<< ORDER BY SAL 추가 )

* SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.( !!!! )

* RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

* 동일 순위의 모든 값에 대한 총합계로 누적됨 ( 아래 << 950 + 1250 + 1250 = 3450 )

SQL>

SELECT MGR, ENAME, SAL, 
       SUM(SAL) OVER ( PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING ) AS MGR_SUM 
FROM EMP ;

 * SQL Server 의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.( !!!! ) ==>

MS SQL 2012 에서 수행 결과 에러 없이, ORDER BY 되어서 결과가 정상적으로 출력됨

SQL>

SELECT MGR, ENAME, SAL, 
       SUM(SAL) OVER ( PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING ) AS MGR_SUM 
FROM EMP ;

 

 

나. MAX 함수

• MAX 함수를 이용해 파티션별로 윈도우의 최대값 구하기

* 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값 구함

SQL>

SELECT MGR, ENAME, SAL, MAX(SAL) OVER ( PARTITION BY MGR) AS MGR_MAX 
FROM EMP ;

 

 * 추가로, INLINE VIEW 를 이용해 파티션별로 최대값을 가진 행만 추출

SQL>

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 ;

 

다. MIN 함수

• MAX 함수를 이용해 파티션별로 윈도우의 최소값 구하기

* 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자 기준으로 정렬하고, SALARY 최소값을 구함

SQL>

SELECT MGR, ENAME, HIREDATE, SAL,
       MIN(SAL) OVER ( PARTITION BY MGR ORDER BY HIREDATE ) AS MIN_SAL
FROM EMP ;

 

라. AVG 함수

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

* EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY 구함

* 평균 SALARY 의 제한 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 지원만을 대상으로 함

* ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한건, 현재 행, 뒤의 한 건을 범위로 지정

* ROWS 는 현재 행의 앞뒤 ROWS 를 말함

SQL>

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 ;

 

마. COUNT 함수

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

* 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받은 인원수를 출력

* RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행(Row)의 급여값을 기준으로 급여가 -50 에서 +150 의 범위 내에 포함된 모든 행이 대상

* RANGE 는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시

SQL>

SELECT ENAME, SAL, 
       COUNT(*) OVER ( ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ) 
           AS SIM_CNT, TO_CHAR(SAL-50)||'->'||TO_CHAR(SAL+150) AS "RANGE" 
FROM EMP ;

 

4. 그룹 내 행 순서 함수

가. FIRST_VALUE 함수

• FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
• SQL Server 에서는 지원하지 않는 함수 ==> MS SQL 2012 지원됨 ( 하단 참조 )
• MIN 을 활용해도 같은 결과 얻음

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

* ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

* FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리함

* DEPTNO = 20 파티션의 최고 연봉은 3000 이고, SCOTT 과 FORD 가 대상 ==> 이중 하나인 SCOTT 만 처리됨

* 명시적인 정렬을 위해서 INLINE VIEW 나, OVER () 내의 ORDER BY 절에 칼럼 추가 할 것

SQL>

SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING ) 
       AS DEPT_RICH 
FROM EMP ; 

* MSSQL 2012 [ 이젠 지원됨 확인 ]

SQL>

SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING ) 
    AS DEPT_RICH 
FROM EMP ;

* FIRST_VALUE 를 처리하기 위해 ORDER BY 정렬 조건을 추가

SQL>

SELECT DEPTNO, ENAME, SAL, 
   FIRST_VALUE(ENAME) OVER ( 
      PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC 
      -- 임의의 SCOTT 이 아닌 FORD 를 추출 하기 위해 ASC( F > S ) 정렬 필요함 
      ROWS UNBOUNDED PRECEDING ) AS DEPT_RICH 
FROM EMP ;

 

나. LAST_VALUE 함수

• LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
• SQL Server 에서도 지원
• MAX 을 활용해도 같은 결과 얻음

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

* ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정

* 공동 등수를 인정하기 않고 가장 나중에 나온 행만 처리

* 명시적인 정렬을 위해서 INLINDE VIEW 나 OVER() 내의 ORDER BY 조건 컬럼 명시

SQL>

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 ;

* MS SQL 2012 ( 지원됨 )

SQL>

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 ; 

다. LAG 함수

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

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

SQL>

SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER ( 
   ORDER BY HIREDATE ) AS PREV_SAL 
FROM EMP 
WHERE JOB ='SALESMAN';

 * LAG(인수1,인수2,인수3 )

* 인수1 - 입력 칼럼

* 인수2 - 몇 번째 앞의 행을 가져올지 결정(DEFAULT 1)

* 인수3 - 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력

> MS SQL 2012

SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER ( 
   ORDER BY HIREDATE ) AS PREV_SAL 
FROM EMP 
WHERE JOB ='SALESMAN';

 * 2 행 앞의 SALARY 가져오기, 가져올 값이 없으면 0으로 처리

SQL>

SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER ( 
  ORDER BY HIREDATE ) AS PREV_SAL 
FROM EMP 
WHERE JOB ='SALESMAN'; 

라. LEAD 함수

• 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져 올 수 있다.
• SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )

SQL>

SELECT ENAME, HIREDATE, LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED" 
FROM EMP ;

 

* LEAD(인수1,인수2,인수3 )

* 인수1 - 입력 칼럼

* 인수2 - 몇 번째 후의 행을 가져올지 결정(DEFAULT 1)

* 인수3 - 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력

> MS SQL 2012

SELECT ENAME, HIREDATE, LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED" 
FROM EMP ;

5. 그룹 내 비율 함수

* 칼럼 값에 대한 백분률 -> RATIO_TO_REPORT

* 행의 순서에 대한 (0부터 1사이 값) 백분률 -> PERCENT_RANK

* 1/(파티션)전체 건수로 표현하는 백분률 -> CUME_DIST

가. RATIO_TO_REPORT 함수

• 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함
• 0 < 백분율 < 1, 개별 RATIO 의 합 = 1
• SQL Server 에서는 지원하지 않는 함수

* JOB 이 SALESMAN 인 사람들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력

SQL>

SELECT ENAME, SAL, 
       ROUND(RATIO_TO_REPORT(SAL) OVER (),2) AS R_R, 
       SUM(SAL) Over ( Order by JOB ) AS "Total" 
FROM EMP
WHERE JOB='SALESMAN' ;

 

나. PERCENT_RANK 함수

• 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것으로 1로하여
• 값이 아닌 행의 순서별 백분율을 구함
• 0 < 백분율 < 1
• SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )

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

SQL>

SELECT DEPTNO, ENAME, SAL, 
       PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R 
FROM EMP ;

 

>MS SQL 2012

SELECT DEPTNO, ENAME, SAL, 
       PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R 
FROM EMP ;

 

다. CUME_DIST 함수

• 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한 누적백분율 구함
• 0 < 백분율 < 1
• SQL Server 에서는 지원하지 않는 함수 ( ==> MS SQL 2012 지원됨 )

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

SQL>

SELECT DEPTNO, ENAME, SAL, 
       CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A 
FROM EMP ;

> MS SQL 2012

SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER ( PARTITION BY DEPTNO ORDER BY SAL ) AS A 
FROM EMP ; 

 

라. NTILE 함수

• 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 구함

 SQL>

SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC ) AS QUAR_TILE 
FROM EMP ;

 

정리

------1. WINDOW FUNCTION개요
/*
기존 관계형 데이터베이스는 칼럼과 컬럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운반면,
행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것을 매우 어려웠다.
이를 위한 함수가 WINDOW FUNCTION이다. 윈도우 함수는 기존에 사용하던 집계함수도 있고, 새로운 기능도있다.
윈도우 함수는 다른 함수와는 달리 중첩(NEST)해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다.

----WINDOW FUNCTION 종류
윈도우 함수는 크게 다섯 개의 그룹으로 분류할 수 있다.
1. 그룹 내 순위 관련 함수: RNAK, DENSE_RANK, ROW_NUMBER
2. 그룹 내 집계 관련 함수: SUM, MAX, MIN, AVG, COUNT
3. 그룹 내 순서 관련 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 그룹 내 비율 관련 함수: CUME_DIST, PERCENT_RANK, NTILE,  RATIO_TO_REPORT
5. 통계 분석 관련 함수: 생략

----WINDOW FUNCTION 구문
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 컬럼] [ORDER BY절] [WINDOWING절] )
FROM 테이블명;

-WINDOW_FUNCTION: 기존에 사용하던 함수나 WINDOW함수 작성
-ARGUMENTS(인수): 함수에 따라 0~N개의 인수가 지정
-PARTITION BY절: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
-ORDER BY절: 어떤 항목에 대해 순위를 지정할 지 ORDER BY절을 기술
-WINDOWING절: WINDOWING절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정한다.
*/
/*
------2. 그룹 내 순위 함수
----가. RANK함수
RANK함수는 ORDER BY를 포함한 쿼리문에서 특정 항목에 대한 순위를 구하는 함수이다. 
이때 특정 범위(PARTITION)내에서 순위를 구할 수 있고, 전체 데이터에 대한 순위를 구할 수도 있다.
또한 동일한 값에 대해서는 동일한 순위를 부여하게 된다.
*/
--예제: 사원 데이터에서 급여가 높은 순서와 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;
/*
업무 구분이 없는 ALL_RANK컬럼에서 FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY이므로 같은 순위를 부여한다.
하나의 SQL문장에 ORDER BY SAL DESC조건과 PARTITION BY JOB 조건이 충돌이 났기 때문에 JOB별로는 정렬이 되지 않고,
ORDER BY SAL DESC조건으로 정렬이 되었다.

앞의 SQL문의 결과는 JOB과 SALARY 기준으로 정렬이 되어있지 않다. 새로운 SQL에서는 전체 SALARY순의를 구하는
ALL_RANK 컬럼은 제외하고, 업무별로 SALARY순서를 구하는 JOB_RANK만 알아보자.
*/
SELECT JOB, ENAME, SAL, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
--업무별로 SALARY순서를 구하는 JOB_RANK만 사용한 경우 파티션이 기준이 된 JOB과 SALARY별로 정렬이 되어있다.

/*
----나. DENSE_RANK함수
DENSE_RANK함수는 RANK함수와 흡사하나, 동일한 순위를 하나의 건수로 취급한다.

사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과를 확인하자.
*/
SELECT JOB, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) RANK,
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
/*
FORD와 SCOTT, WARD와 MARTIN은 동일한 SALARY이므로 RANK와 DENSE_RANK컬럼에서 모두 같은 순위를 부여한다.
그러나 RANK와 DENSE_RANK차이를 알 수 있는 데이터는 다음 순위인 JONES의 경우 RANK는 4등으로,
DENSE_RANK는 3등으로 표시되어 있다. 
*/

/*
----다. ROW_NUMBER 함수
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;
/*
SALARY가 같은 사원은 RANK는 같은 순위를 부여했지만, ROW_NUMBER의 경우 동일한 순위를 배제하기 위해 
유니크한 순위를 정한다. 
*/

/*
------3. 일반 집계 함수
----가. SUM함수
SUM함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.

예제: 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY합을 구한다.
*/
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

--예제: OVER절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력한다.
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING)
    AS MGR_SUM
FROM EMP;
/*
RANGE는 현재 행을 기준으로 어떤 값의 범위를 포함하는지를 명시.
UNBOUNDED: 한계를 두지 않고, 해당 파티션의 끝까지를 의미
PRECEDING과 FOLLOWING: 현재 행에서 앞쪽인지 뒤쪽인지 방향성을 나타냄
3 PRECEDING이면 현재 행부터 앞쪽 3행을 범위로 해서 함수가 적용
즉 RANGE UNBOUNDED PRECEDING 은 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
*/

----나. MAX 함수
--MAX 함수를 이용해 파티션별 윈도우의 최대값을 구할 수 있다.
--예제: 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY중 최대값을 같이 구한다.
SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) AS MAR_MAX
FROM EMP;
--실행 결과를 확인하면 파티션 내의 최대값을 파티션 내 모든 행에서 MGR_MAX라는 컬럼값으로 가질 수 있다.
--예제: 추가로 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;

----다. MIN함수
/*
MIN함수를 이용해 파티션별 윈도우의 최소값을 구할 수 있다.

예제: 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, 
SALARY 최소값을 같이 구한다.
*/
SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) AS MGR_MIN
FROM EMP;

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

예제: EMP테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서
자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
*/
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는 현재 행의 앞뒤 건수를 말한다.

----마. 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;
--현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다.
--RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시한다.


------4. 그룹 내 행 순서 함수
----가. FIRST_VALUE 함수
/*
FIRST_VALUE함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다. MIN함수를 이용해도 같은 결과를 얻을 수 있다.

예제: 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.
*/
SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;
/*
실행 결과를 보면 부서 내에 최고 급여를 받는 사람이 둘 있는 경우, 즉 부서번호 20의 SCOTT과 FORD중에서 
어느 사람이 최고 급여자로 선택될지는 이 SQL문만 가지고 판단할 수 없다. FIRST_VALUE는 다른 함수와 달리
공동 등수를 인정하지 않고 처음 나론 행만을 처리한다. 위처럼 공동 등수가 있을 경우에 의도적으로 세부 항목을 정렬하고
싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER() 내의 ORDER BY 절에 컬럼을 추가해야 한다.

예제: 앞의 SQL문장에서 같은 값을 가진 FIRST_VALUE를 처리하기 위해 ORDER BY 정렬 조건을 추가한다.
*/
SELECT DEPTNO, ENAME, SAL, 
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO
                             ORDER BY SAL DESC, ENAME ASC
                             ROWS UNBOUNDED PRECEDING) AS RICH_RMP
FROM EMP;
/*
SQL에서 같은 부서 내에 최고 급여를 받는 사람이 둘 있는 경우를 대비해서 이름을 두 번째 정렬 조건으로 추가한다.
실행 결과를 확인해보면 부서번호 20의 최고 급여자가 이전의 SCOTT값에서 아스키코드가 적은 값인 FORD로 변경된 것을
확인할 수 있다.
*/

----나. LAST_VALUE함수
/*
LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
MAX함수를 활용하여 같은 결과를 얻을 수 있다.

예제: 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.
*/
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: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정


----다. LAG함수
/*
LAG함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

예제: 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와
함께 출력한다.
*/
SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

/*
예제: LAG함수는 3개의 ARGUMENTS까지 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고
(DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL값이 들어오는데 이 경우
다른 값으로 바꿔 줄 수 있다.
결과적으로 NVL이나 ISNULL 기능과 같다.
*/
SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
--LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우 0으로 처리한다.


------5. 그룹 내 비율 함수
----가. RATIO_TO_REPORT함수
/*
RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(컬럼)값에 대한 행별 컬럼 값의 백분율을 소수점으로
구할 수 있다. 결과 값은 0 < 결과 <= 1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 된다.

예제: JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.
*/
SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER(), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';
--실행결과 R_R의 값을 모두 더하면 1이 되는 것을 확인할 수 있다.

----나. PERCENT_RANK 함수
/*
PERCENT_RANK함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로하여,
값이 아닌 행의 순서별 백분율을 구한다. 결과 값의 범위는 0 < ~ <= 1 이다.

예제: 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력한다.
*/
SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R
FROM EMP;
/*
DEPTNO 10의 경우 3건이므로 구간은 2개가 된다. 0과 1사이를 2개의 구간으로 나누면 0, 0.5, 1이 된다.
DEPTNO 20인 경우 5건이므로 구간은 4개가 된다.
이때 이름이 같은 행들은 같은 순위로 취급된다.
*/

----다. CUME_DIST 함수
/*
CUME_DIST 함수를 이용해 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
결과 값의 범위는 0 <= ~ <= 1 이다.ㅏ

예제: 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력한다.
*/
SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;
/*
DEPTNO 10인 경우 윈도우가 전체 3건이므로 0.3333단위의 간격을 가진다. 
이름이 같은 행은 같은 순위로 취급된다. 다만 다른 점은 다른 WINDOW 함수의 경우 동일 순서면 앞 행의 함수 결과 값을
따르는데, CUME_DIST는 동일 순서면 뒤 행의 함수 결과 값을 따른다.
*/

----라. NTILE 함수
/*
NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT값으로 N등분한 결과를 구할 수 있다.

예제: 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.
*/
SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;
--위 예제에서 NTILE(4)의 의미는 14명의 팀원을 4개 조로 나눈다는 의미이다. 

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27427796