기록지

Oracle에서 날짜/시간은 소수점으로 시간 단위를 표현합니다.

• 1 = 1일
• 1/24 = 1시간
• 1/1440 = 1분 (1 / (24 * 60))
• 1/144 = 약 10분 (1 / (24 * 6))

그러므로:

1/24/(60/10) = 1/144 = 10분(interval of 10 minutes)

SELECT ~ FOR UPDATE란

SELECT ~ FOR UPDATE로 처리

SELECT ~ FOR UPDATE 옵션 

누군가가 LOCK을 걸고 있는 상황에서의 옵션 별 차이

1. SELECT FOR UPDATE : SELECT 할 떄 LOCK을 제어할 수 없으면 무한정 기다린다. 즉, 누군가가 LOCK 중이면 무한정 내 차례를 기다린다. 

2. SELECT FOR UPDATE NOWAIT : SELECT 할 때 LOCK 을 제어할 수 없으면 에러처리한다. 즉, 누군가가 LOCK중이면 해당 행에 대한 LOCK을 바로 포기한다.

3. SELECT FOR UPDATE WAIT 5(초 단위) : SELECT 할 떄 LOCK을 제어 할 수 없으면 5초 동안 계속 LOCK을 재시도 한다. 즉 누군가가 LOCK 중이면 5초 동안 계속 재시도하고, 그 이후에 포기한다. 

Oracle Demo schema인 HR을 사용하여 테스트 

일반 GROUP BY를 사용하여 부서와 직무로 그룹화 하여 연봉의 합을 구하는 쿼리 

SQL> select DEPARTMENT_ID
     , JOB_ID
     , SUM(salary)
 from HR.EMPLOYEES
group by DEPARTMENT_ID, JOB_ID;

DEPARTMENT_ID JOB_ID     SUM(SALARY)          
------------- ---------- -------------------- 
           60 IT_PROG                   28800
          100 FI_ACCOUNT                39600
           80 SA_REP                   243500
              SA_REP                     7000
           20 MK_REP                     6000
          110 AC_ACCOUNT                 8300
           30 PU_MAN                    11000
           30 PU_CLERK                  13900
           50 ST_CLERK                  55700
           10 AD_ASST                    4400
           40 HR_REP                     6500
           50 ST_MAN                    36400
           90 AD_PRES                   24000
          100 FI_MGR                    12008
           80 SA_MAN                    61000
           50 SH_CLERK                  64300
           20 MK_MAN                    13000
           70 PR_REP                    10000
          110 AC_MGR                    12008
           90 AD_VP                     34000

ROLLUP 사용

GROUP BY 결과로 나온 값에 부서별 총 연봉 합과 총 연봉합을 추가로 나타내 준다.

SQL> select DEPARTMENT_ID
     , JOB_ID
     , SUM(salary)
 from HR.EMPLOYEES
group by ROLLUP(DEPARTMENT_ID, JOB_ID);

DEPARTMENT_ID JOB_ID     SUM(SALARY)          
------------- ---------- -------------------- 
              SA_REP                     7000
                                         7000
           10 AD_ASST                    4400
           10                            4400
           20 MK_MAN                    13000
           20 MK_REP                     6000
           20                           19000
           30 PU_MAN                    11000
           30 PU_CLERK                  13900
           30                           24900
           40 HR_REP                     6500
           40                            6500
           50 ST_MAN                    36400
           50 SH_CLERK                  64300
           50 ST_CLERK                  55700
           50                          156400
           60 IT_PROG                   28800
           60                           28800
           70 PR_REP                    10000
           70                           10000
           80 SA_MAN                    61000
           80 SA_REP                   243500
           80                          304500
           90 AD_VP                     34000
           90 AD_PRES                   24000
           90                           58000
          100 FI_MGR                    12008
          100 FI_ACCOUNT                39600
          100                           51608
          110 AC_MGR                    12008
          110 AC_ACCOUNT                 8300
          110                           20308
                                       691416

전체 급여 합만 보고 싶을 땐?

ROLLUP 으로 감싼 컬럼들을 다시한번 괄호로 감싸준다. 

SQL> select DEPARTMENT_ID
     , JOB_ID
     , SUM(salary)
 from HR.EMPLOYEES
group by ROLLUP((DEPARTMENT_ID, JOB_ID));

DEPARTMENT_ID JOB_ID     SUM(SALARY)          
------------- ---------- -------------------- 
              SA_REP                     7000
           10 AD_ASST                    4400
           20 MK_MAN                    13000
           20 MK_REP                     6000
           30 PU_MAN                    11000
           30 PU_CLERK                  13900
           40 HR_REP                     6500
           50 ST_MAN                    36400
           50 SH_CLERK                  64300
           50 ST_CLERK                  55700
           60 IT_PROG                   28800
           70 PR_REP                    10000
           80 SA_MAN                    61000
           80 SA_REP                   243500
           90 AD_VP                     34000
           90 AD_PRES                   24000
          100 FI_MGR                    12008
          100 FI_ACCOUNT                39600
          110 AC_MGR                    12008
          110 AC_ACCOUNT                 8300
                                       691416

부서급여 합만 보고 싶을땐?

DEPARTMENT_ID는 GROUP BY 뒤에 기술하고 JOB_ID만 ROLLUP 해준다.

SQL> select DEPARTMENT_ID
     , JOB_ID
     , SUM(salary)
 from HR.EMPLOYEES
group by  DEPARTMENT_ID, ROLLUP(JOB_ID);

DEPARTMENT_ID JOB_ID     SUM(SALARY)          
------------- ---------- -------------------- 
              SA_REP                     7000
                                         7000
           10 AD_ASST                    4400
           10                            4400
           20 MK_MAN                    13000
           20 MK_REP                     6000
           20                           19000
           30 PU_MAN                    11000
           30 PU_CLERK                  13900
           30                           24900
           40 HR_REP                     6500
           40                            6500
           50 ST_MAN                    36400
           50 SH_CLERK                  64300
           50 ST_CLERK                  55700
           50                          156400
           60 IT_PROG                   28800
           60                           28800
           70 PR_REP                    10000
           70                           10000
           80 SA_MAN                    61000
           80 SA_REP                   243500
           80                          304500
           90 AD_VP                     34000
           90 AD_PRES                   24000
           90                           58000
          100 FI_MGR                    12008
          100 FI_ACCOUNT                39600
          100                           51608
          110 AC_MGR                    12008
          110 AC_ACCOUNT                 8300
          110                           20308

ROLLUP의 원리

위 이미지가 ROLLUP의 원리를 전부 설명해주는데, 각 번호는 해당 칼럼으로 GROUP BY한 결과를 준다는 뜻이다. 첫번째 GROUP BY절 같은 경우는 총 4개의 GROUP을 만든다는 뜻이다. 

ROLLUP의 원리

1. ROLLUP의 인자로 들어온 컬럼을 오른쪽부터 하나씩 빼면서 GROUP을 만든다.

2. ()의미는 GROUP이 없는 즉, 전체에 대한 결과를 출력한다는 의미이다. EX(SUM함수 사용하면 전체 SUM구한다는 의미)

3. 괄호로 묶여져 있는 컬럼은 하나로 본다는 뜻

4. ROLLUP 이전에 일반 컬럼과 GROUP BY 한다면, 일반 컬럼은 끝까지 남는다. 

GROUP BY ROLLUP(A, B, C) 예시

GROUP BY ROLLUP(A, (B, C)) 예시

GROUP BY A, ROLLUP((B, C)) 예시

ROLLUP 특이 예제

여태까지는 기본적으로 ROLLUP이 어떤 원리로 실행되는지 알아봤습니다. 그럼 위에 예제 처럼 결과를 출력할 수 있을까요? 일단, 결과를 보니, 한 칼럼에서 컬럼 이외의 값이 나오기도하고, 급여 컬럼에서는 평균값과 합계값이 같이 나오는 것을 볼 수 있습니다. 어떻게 결과를 이렇게 출력할 수 있을까요? 우선 GROUPING, GROUPING_ID 그리고 숫자 GROUP 추가에 대한 내용을 알아야합니다.

GROUPING

1 2 3 4 5 6 7 8

SELECT     DEPTNO     ,GROUPING(DEPTNO) AS DG     ,JOB     ,GROUPING(JOB) AS JG     ,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);

GROUPING 함수는 ROLLUP이랑 같이 사용합니다. 해당 칼럼이 ROLLUP 되었을 때 그룹에서 빠져있다면 1을 반환합니다. 즉, GROUP BY ROLLUP 결과로 NULL이 나왔을 때 1을 반환합나다.

GROUPING_ID

GROUPING_ID 함수는 인자로 들어온 각각 칼럼의 GROUPING 함수 값을 2진수로 합쳐 해당 2진수 값을 10진수로 변환한 값을 반환해줍니다.  

숫자 GROUP

숫자가 들어간 ROLLUP은 뭘까요? 결과를 보니 마지막 2개의 줄이 같은 값이 나왔습니다. 헷갈릴 수 있지만 ROLLUP의 원리를 생각해보면 별 다를것 없습니다. JOB과 DEPTNO가 ROLLUP에서 빠지고 1만 남았을때 SUM(SAL)을 구하려고 합니다. 상수에 대한 GROUP은 없습니다. 즉, 전체 행에 대한 결과를 출력하라는 의미와 같습니다.

최종 풀이

https://myjamong.tistory.com/191

프로시저 개발 중 다음과 같은 에러 발생 

ORA-06550: line 28, column 8:
PL/SQL: ORA-02185: a token other than WORK follows COMMIT

원인 : 다음과 같이 실행 시킬 SQL문의 COMMIT 뒤에 세미콜론이 빠져서 생긴 문제

V_STMT := 'BEGIN' || CHR(13) || CREC.SQL_TEXT || CHR(13) || CHR(13) || 'COMMIT' || CHR(13) ||  'END;' ;

COMMIT 뒤에 세미콜론 추가

V_STMT := 'BEGIN' || CHR(13) || CREC.SQL_TEXT || CHR(13) || CHR(13) || 'COMMIT;' || CHR(13) ||  'END;' ;

EXECUTE IMMEDIATE 문법

    EXECUTE IMMEDIATE dynamic_sql_string
        [INTO {define_var1 [, define_var2] ... | plsql_record }]
        [USING [IN | OUT | IN OUT] bind_arg1 [,
               [IN | OUT | IN OUT] bind_arg2] ...];

- USING 절에 바인드 변수를 사용한다. 

- IN : 입력 모드, 값이 PL/SQL에서 DB서버로 전달된다. 입력 모드 변수의 값은 동적 SQL의 실행 전후에 변경이 없다. 

- OUT : 출력 모드, 값이 DB 서버에서 PL/SQL로 전달된다.

- IN OUT : 입출력 모드, 값이 양방향으로 전달된다. 동적 SQL을 실행하기전에 가지고 있던 값이 DB서러보 전되되어 참조되며, DB서버에서 변경된 값이 다시 PL/SQL 변수로 되돌려진다. 

- 다수를 사용할 경우, 변수명과 동일하지 않게 순서대로 기입된다. 

계층형 쿼리란?

테이블에 계층형 구조 즉, 수직적 구조가 존재할 때 이를 조회하기 위한 쿼리이다.

위의 테이블을 보면 EMPNO가 직원번호이고 MGR이 해당 직원의 관리자의 직원번호이다.

1번 로우의 EMPNO가 '7839' 인 KING은  회장이라 관리자란이 NULL값이다. 

2번 로우의 EMPNO가 '7698'인 BLAKE는 상사직번이 7829(KING)으로 회장님이 직접 관리하는 직원이다. 

이런식으로 한 테이블안에 수직적 구조가 존재할 때 이를 계층형 구조라고 한다.

START WITH

계층의 루트로 사용될 행을 지정한다.

서브 쿼리를 사용할 수 있다.

어떤 레코드를 최상위 레코드로 정할지 결정한다. 

관리자가 없는(최상위 관리자)부터 시작할 것이기 때문에 MGR이 NULL인 ROW를 최초 시작점으로 한다.

SELECT * 
 FROM TB_EMP
START WITH MGR IS NULL;

CONNECT BY

연결고리를 만든다.

PRIOR 연산자로 계층구조를 표현할 수 있다.

서브쿼리를 사용할 수 없다.

최초행 이 후 다음행을 어떤 조건으로 가져올 것인지 정한다.

계층형 쿼리에서 부모 노드를 참조하는 데 사용된다. 현재 행의 값을 부모 행의 값과 비교하여 계층을 형성.

SELECT 절에도 사용 가능하다.

앞서 START WITH를 통해 최상위 행을 가져왔다. 이제 최상위 행을 관리자로 갖는 다음 계층의 데이터를 가져와야한다.

최상위 행인 'KING' 데이터를 기준으로 생각하자. MGR값이 'KING'  데이터의 EMPNO와 일치하는 행을 가져오면 된다. 

- **`CONNECT BY PRIOR EMPNO = MGR `**: `PRIOR`를 사용하여 부모와 자식 관계를 정의.

현재 행의 ` MGR  `와 이전 행의 ` EMPNO `가 같을 때, 그 행을 자식으로 간주.

PRIOR은 이전행을 참조 할 때 사용

SELECT * 
 FROM TB_EMP
START WITH MGR IS NUL
CONNECT BY PRIOR EMPNO = MGR

만약 PRIOR을 반대로 선언하면 

SELECT * 
 FROM TB_EMP
START WITH MGR IS NUL
CONNECT BY PRIOR MGR = EMPNO

위와 같이 선언하면 'KING' 데이터의 MGR NULL 값이고,  NULL값을 EMPNO로 갖는 데이터는 없기 때문에 출력 결과가 나오지 않게 된다.

***CONNECT BY NOCYCLE PRIOR: NOCYCLE파라미터를 이용하여 무한 루프 방지

LEVEL

CONNECT BY 절을 쓰면 오라클이 제공해주는 컬럼 

수행 결과의 DEPTH를 표현해주는 의사 컬럼

LEVEL 1이 제일 상위값이다. 즉 ROOT의 LEVEL은 1이다.

SELECT LPAD(' ', (LEVEL -1) * 5 ) || ENAME 
 FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

레벨을 따라 들여쓰기한 쿼리로 

1레벨이면 좌측에 0개의 공백

2레벨이면 좌측에 5개의 공백

3레벨이면 좌측에 10갸의 공백이 붙어 ENAME을 출력해준다.

위와 같은 트리의 형태로 

최하단까지 찾아가면 상위로 올라가 하위단을 찾아간다.

ADAMS를 찾은 후 SCOTT으로 이동하여 하위계층이 있는지 찾아본다. SCOTT에게 더 이상 하위계층이 없으면, 한 단계 상위 단계인 JONES로 이동하여 하위단이 있는지 찾아본다. FORD가 발견되었고, 하위단 SMITH까지 찾아간다. 위의 방식으로 모든 대상을 찾을때까지 계속한다.

SIBLINGS

계층별로 정렬을 하기 위해서는 SIBLINGS 라는 명령어를 ORDER BY와 같이 사용해야 한다.

SELECT LPAD(' ', (LEVEL -1) * 5 ) || ENAME 
 FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

WHERE 

where 조건을 사용하면 모든 계층형 결과를 뽑아낸 다음 where 조건절을 필터한다. 

SELECT LPAD(' ', (LEVEL -1) * 5 ) || ENAME 
 FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
WHERE DEPT <> '10'
ORDER SIBLINGS BY ENAME;

SYS_CONNECT_BY_PATH

루트 부터 현재 까지 PAHT를 표시 한다. 

사용법 : sys_connectby_path( 컬럼명, 구분자) 

select employee_id, salary , level ,
       sys_connect_by_path( employee_id , '-->')
 from hr.EMPLOYEES a
start with manager_id is null
connect by manager_id = prior EMPLOYEE_ID ;

CONNECT_BY_ROOT

루트 노드 값 추출

사용법 : select connect_by_root 컬럼명 from ...

select employee_id, salary , level , connect_by_root salary, 
       sys_connect_by_path( employee_id , '-->')
 from hr.EMPLOYEES a
start with manager_id is null
connect by manager_id = prior EMPLOYEE_ID ;

CONNECT_BY_ISLEAF

리프 노드 여부 (1: 리프, 0: 상위 존재)

CONNECT_BY_ISCYCLE

순환(cycle) 참조가 있는지를 판단하며, 1이면 사이클이 존재함을 의미한다. NOCYCLE 옵션과 함께 사용해야 한다.

SELECT emp_name,
       CONNECT_BY_ISCYCLE
FROM org
START WITH mgr_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = mgr_id;

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 

WINDOW FUNCTION 종류

SYNTAX

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

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

그룹 내 비율 함수

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

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

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

RATIO_TO_REPORT 함수 

파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함

* 0 < 백분율 < 1, 개별 RATIO의 합 = 1 

SQL Sever 에서는 지원하지 않는 함수 

SELECT FIRST_NAME, SALARY, 
            ROUND(RATIO_TO_REPORT(SALARY) OVER (),2) AS R_R,
            SUM(SALARY) Over ( Order by JOB_ID  ) AS "Total"
FROM EMPLOYEES e  
WHERE JOB_ID='SA_REP' ;

FIRST_NAME |SALARY|R_R |Total |
-----------+------+----+------+
Peter      | 10000|0.04|250500|
David      |  9500|0.04|250500|
Peter      |  9000|0.04|250500|
Christopher|  8000|0.03|250500|
Nanette    |  7500|0.03|250500|
Oliver     |  7000|0.03|250500|
Janette    | 10000|0.04|250500|
Patrick    |  9500|0.04|250500|
Allan      |  9000|0.04|250500|
Lindsey    |  8000|0.03|250500|
Louise     |  7500|0.03|250500|
Sarath     |  7000|0.03|250500|
Clara      | 10500|0.04|250500|
Danielle   |  9500|0.04|250500|
Mattea     |  7200|0.03|250500|
David      |  6800|0.03|250500|
...

 PERCENT_RANK 

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

* 0 < 백분율 < 1 

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

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
       PERCENT_RANK() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS P_R
FROM EMPLOYEES e   ;
    
DEPARTMENT_ID|FIRST_NAME |SALARY|P_R                                       |
-------------+-----------+------+------------------------------------------+
           10|Jennifer   |  4400|                                         0|
           20|Michael    | 13000|                                         0|
           20|Pat        |  6000|                                         1|
           30|Den        | 11000|                                         0|
           30|Alexander  |  3100|                                       0.2|
           30|Shelli     |  2900|                                       0.4|
           30|Sigal      |  2800|                                       0.6|
           30|Guy        |  2600|                                       0.8|
           30|Karen      |  2500|                                         1|
           40|Susan      |  6500|                                         0|
           50|Adam       |  8200|                                         0|
...

CUME_DIST

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

* 0 < 백분율 < 1 

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

SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
       CUME_DIST() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS C_D
FROM EMPLOYEES e   ;   

DEPARTMENT_ID|FIRST_NAME |SALARY|C_D                                       |
-------------+-----------+------+------------------------------------------+
           10|Jennifer   |  4400|                                         1|
           20|Pat        |  6000|                                       0.5|
           20|Michael    | 13000|                                         1|
           30|Karen      |  2500|0.1666666666666666666666666666666666666667|
           30|Guy        |  2600|0.3333333333333333333333333333333333333333|
           30|Sigal      |  2800|                                       0.5|
           30|Shelli     |  2900|0.6666666666666666666666666666666666666667|
           30|Alexander  |  3100|0.8333333333333333333333333333333333333333|
           30|Den        | 11000|                                         1|
           40|Susan      |  6500|                                         1|
...

NFILE

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

사원들을 연봉이 높은 순으로 정렬 해서 4단계로 나눠 보여주는 예제 

SELECT FIRST_NAME, SALARY, NTILE(4) OVER (ORDER BY SALARY DESC ) AS QUAR_TILE
FROM EMPLOYEES e   ; 

FIRST_NAME |SALARY|QUAR_TILE|
-----------+------+---------+
Steven     | 24000|        1|
Neena      | 17000|        1|
Lex        | 17000|        1|
John       | 14000|        1|
Karen      | 13500|        1|
Michael    | 13000|        1|
Nancy      | 12008|        1|
Shelley    | 12008|        1|
Alberto    | 12000|        1|
Lisa       | 11500|        1|
Den        | 11000|        1|
Gerald     | 11000|        1|
Ellen      | 11000|        1|
Eleni      | 10500|        1|
Clara      | 10500|        1|
Janette    | 10000|        1|
Peter      | 10000|        1|
Hermann    | 10000|        1|
Harrison   | 10000|        1|
Tayler     |  9600|        1|
Danielle   |  9500|        1|
David      |  9500|        1|
Patrick    |  9500|        1|
Peter      |  9000|        1|
Alexander  |  9000|        1|
Allan      |  9000|        1|
Daniel     |  9000|        1|
Alyssa     |  8800|        2|
Jonathon   |  8600|        2|
Jack       |  8400|        2|
William    |  8300|        2|
Adam       |  8200|        2|
John       |  8200|        2|
Matthew    |  8000|        2|
Lindsey    |  8000|        2|
Christopher|  8000|        2|
Payam      |  7900|        2|
Jose Manuel|  7800|        2|
Ismael     |  7700|        2|
Louise     |  7500|        2|
Nanette    |  7500|        2|
William    |  7400|        2|
Elizabeth  |  7300|        2|
Mattea     |  7200|        2|
Oliver     |  7000|        2|
Kimberely  |  7000|        2|
Sarath     |  7000|        2|
Luis       |  6900|        2|
David      |  6800|        2|
Susan      |  6500|        2|
Shanta     |  6500|        2|
Sundar     |  6400|        2|
Charles    |  6200|        2|
Amit       |  6200|        2|
Sundita    |  6100|        3|
Pat        |  6000|        3|
Bruce      |  6000|        3|
Kevin      |  5800|        3|
Valli      |  4800|        3|
David      |  4800|        3|
Jennifer   |  4400|        3|
Nandita    |  4200|        3|
Diana      |  4200|        3|
Alexis     |  4100|        3|
Sarah      |  4000|        3|
Britney    |  3900|        3|
Kelly      |  3800|        3|
Jennifer   |  3600|        3|
Renske     |  3600|        3|
Trenna     |  3500|        3|
Julia      |  3400|        3|
Jason      |  3300|        3|
Laura      |  3300|        3|
Julia      |  3200|        3|
Samuel     |  3200|        3|
Winston    |  3200|        3|
Stephen    |  3200|        3|
Alexander  |  3100|        3|
Alana      |  3100|        3|
Jean       |  3100|        3|
Curtis     |  3100|        3|
Kevin      |  3000|        4|
Anthony    |  3000|        4|
Shelli     |  2900|        4|
Timothy    |  2900|        4|
Michael    |  2900|        4|
Sigal      |  2800|        4|
Vance      |  2800|        4|
Girard     |  2800|        4|
Mozhe      |  2800|        4|
John       |  2700|        4|
Irene      |  2700|        4|
Guy        |  2600|        4|
Douglas    |  2600|        4|
Donald     |  2600|        4|
Randall    |  2600|        4|
Karen      |  2500|        4|
James      |  2500|        4|
Randall    |  2500|        4|
Peter      |  2500|        4|
Martha     |  2500|        4|
Joshua     |  2500|        4|
Ki         |  2400|        4|
James      |  2400|        4|
Hazel      |  2200|        4|
Steven     |  2200|        4|
TJ         |  2100|        4|

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 

WINDOW FUNCTION 종류

SYNTAX

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

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

FIRST_VALUE

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

FIRST_VALUE는 공동 등수를 인정하지 않고 처음 나온 행만을 구한다. 

부서별로 나누어진 윈도우에서 가장 먼저 나온 사람의 이름을 구하는 예제 

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Michael    |
        202|           20|Pat        |  6000|Michael    |
        114|           30|Den        | 11000|Den        |
        115|           30|Alexander  |  3100|Den        |
        116|           30|Shelli     |  2900|Den        |
        117|           30|Sigal      |  2800|Den        |
        118|           30|Guy        |  2600|Den        |
        119|           30|Karen      |  2500|Den        |
        203|           40|Susan      |  6500|Susan      |
        120|           50|Matthew    |  8000|Matthew    |
        121|           50|Adam       |  8200|Matthew    |
        122|           50|Payam      |  7900|Matthew    |
        123|           50|Shanta     |  6500|Matthew    |
        ...

order by를 사용하여 급여로 정렬 후 같은 부서내에서 연봉이 가장 작은 사람중 한명의 이름을 구하는 예제

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       FIRST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        202|           20|Pat        |  6000|Pat        |
        201|           20|Michael    | 13000|Pat        |
        119|           30|Karen      |  2500|Karen      |
        118|           30|Guy        |  2600|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        114|           30|Den        | 11000|Karen      |
        203|           40|Susan      |  6500|Susan      |
        132|           50|TJ         |  2100|TJ         |
        136|           50|Hazel      |  2200|TJ         |
        128|           50|Steven     |  2200|TJ         |
        ...

LAST_VALUE

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

부서별로 나누어진 사원들 중 가장 나중의 사원 이름을 구하는 예

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Pat        |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        118|           30|Guy        |  2600|Karen      |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
...

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

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC
       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Pat        |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Karen      |
        115|           30|Alexander  |  3100|Karen      |
        116|           30|Shelli     |  2900|Karen      |
        117|           30|Sigal      |  2800|Karen      |
        118|           30|Guy        |  2600|Karen      |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
        ...

윈도우잉 절을 기술하지 않으면 다음과 같이 자기 자신의 로우까지 가장 마지막값으로 여긴다.

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       LAST_VALUE(FIRST_NAME) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS Fisrst_name
FROM EMPLOYEES e  ;

EMPLOYEE_ID|DEPARTMENT_ID|FIRST_NAME |SALARY|FISRST_NAME|
-----------+-------------+-----------+------+-----------+
        200|           10|Jennifer   |  4400|Jennifer   |
        201|           20|Michael    | 13000|Michael    |
        202|           20|Pat        |  6000|Pat        |
        114|           30|Den        | 11000|Den        |
        115|           30|Alexander  |  3100|Alexander  |
        116|           30|Shelli     |  2900|Shelli     |
        117|           30|Sigal      |  2800|Sigal      |
        118|           30|Guy        |  2600|Guy        |
        119|           30|Karen      |  2500|Karen      |
        203|           40|Susan      |  6500|Susan      |
        ...

NTH_VALUE

파티션 별 윈도우에서 n 번째를 지정하여 값을 가져올 수 있다. 

SELECT EMPLOYEE_ID,  DEPARTMENT_ID, FIRST_NAME, SALARY,
       NTH_VALUE(FIRST_NAME, 2) OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS Fisrst_name
FROM EMPLOYEES e  ;

LAG 

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

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

- 인수1  : 입력 컬럼

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

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

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

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|        |
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|   10000|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|    9500|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|   11000|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|    9000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|   10000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|    8000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|   11500|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|    8800|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    9500|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9000|
...

사원들을 입사일자가 빠른 기준으로 정렬하고, 본인 보다 입사일자가 두 단계 앞선 사원의 급여를 본인 급여와 함께 출력하는 예제 

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY, 2) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
 FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|        |
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|        |
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|   10000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|    9500|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|   11000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|    9000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|   10000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    8000|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|   11500|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    8800|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9500|
Louise     |2005-12-15 00:00:00.000|SA_REP|  7500|    9000|
...

* 맨 위 없는 값들에 대해 NULL 대신 0 출력
SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LAG(SALARY, 2, 0) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
     FROM EMPLOYEES e  
     WHERE JOB_ID ='SA_REP';
     
FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|       0|
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|       0|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|   10000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|    9500|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|   11000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|    9000|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|   10000|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    8000|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|   11500|
Peter      |2005-08-20 00:00:00.000|SA_REP|  9000|    8800|
Clara      |2005-11-11 00:00:00.000|SA_REP| 10500|    9500|
...

LEAD 

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

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

- 인수1  : 입력 컬럼

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

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

입사일자로 정렬 후 현재 로우 보다 입사일자가 한 단계 뒤에있는 사원의 급여를 구하는 예제 

SELECT FIRST_NAME, HIRE_DATE, JOB_ID, SALARY, LEAD(SALARY) OVER ( ORDER BY HIRE_DATE ) AS PREV_SAL
FROM EMPLOYEES e  
WHERE JOB_ID ='SA_REP';

FIRST_NAME |HIRE_DATE              |JOB_ID|SALARY|PREV_SAL|
-----------+-----------------------+------+------+--------+
Janette    |2004-01-30 00:00:00.000|SA_REP| 10000|    9500|
Patrick    |2004-03-04 00:00:00.000|SA_REP|  9500|   11000|
Ellen      |2004-05-11 00:00:00.000|SA_REP| 11000|    9000|
Allan      |2004-08-01 00:00:00.000|SA_REP|  9000|   10000|
Peter      |2005-01-30 00:00:00.000|SA_REP| 10000|    8000|
Lindsey    |2005-03-10 00:00:00.000|SA_REP|  8000|   11500|
Lisa       |2005-03-11 00:00:00.000|SA_REP| 11500|    8800|
Alyssa     |2005-03-19 00:00:00.000|SA_REP|  8800|    9500|
David      |2005-03-24 00:00:00.000|SA_REP|  9500|    9000|
...

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 

WINDOW FUNCTION 종류

SYNTAX

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

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들

SELECT empno, ename, sal , 
            SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;

UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

SUM

sum 함수를 사용하여 같은 매니저를 둔 사원들의 급여의 합을 구하는 예제

SELECT MANAGER_ID, FIRST_NAME , SALARY , 
       SUM(SALARY) OVER (PARTITION BY MANAGER_ID) MGR_SUM
FROM   EMPLOYEES e  ;

MANAGER_ID|FIRST_NAME |SALARY|MGR_SUM|
----------+-----------+------+-------+
       100|Neena      | 17000| 155400|
       100|Lex        | 17000| 155400|
       100|Den        | 11000| 155400|
       100|Matthew    |  8000| 155400|
       100|Adam       |  8200| 155400|
       100|Payam      |  7900| 155400|
       100|Shanta     |  6500| 155400|
       100|Kevin      |  5800| 155400|
       100|John       | 14000| 155400|
       100|Karen      | 13500| 155400|
       100|Alberto    | 12000| 155400|
       100|Gerald     | 11000| 155400|
       100|Eleni      | 10500| 155400|
       100|Michael    | 13000| 155400|
       101|Jennifer   |  4400|  44916|
       101|Susan      |  6500|  44916|
       101|Nancy      | 12008|  44916|
...

ROWS UNBOUNDED PRECEDING 라는 윈도우 절을 사용하여 이전 급여에 새로운 급여를 차례로 더하는 누적 값을 보여주는 예제 

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

(WINDOWING 절은 나중에 따로 자세히 다루도록 함)

SELECT MANAGER_ID, FIRST_NAME , SALARY , 
       SUM(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY SALARY ROWS UNBOUNDED PRECEDING ) MGR_SUM
FROM   EMPLOYEES e  ;

MANAGER_ID|FIRST_NAME |SALARY|MGR_SUM|
----------+-----------+------+-------+
       100|Kevin      |  5800|   5800|
       100|Shanta     |  6500|  12300|
       100|Payam      |  7900|  20200|
       100|Matthew    |  8000|  28200|
       100|Adam       |  8200|  36400|
       100|Eleni      | 10500|  46900|
       100|Den        | 11000|  57900|
       100|Gerald     | 11000|  68900|
       100|Alberto    | 12000|  80900|
       100|Michael    | 13000|  93900|
       100|Karen      | 13500| 107400|
       100|John       | 14000| 121400|
       100|Lex        | 17000| 138400|
       100|Neena      | 17000| 155400| --> 위의 MGR_SUM 컬럼과 같은 값이 되었다.
       101|Jennifer   |  4400|   4400|
       101|Susan      |  6500|  10900|
       101|Hermann    | 10000|  20900|
       101|Shelley    | 12008|  32908|
       101|Nancy      | 12008|  44916| --> 위의 MGR_SUM 컬럼과 같은 값이 되었다.
       102|Alexander  |  9000|   9000|
       ...

MAX

같은 매니저를 둔 사원들중 가장 큰 급여를 보여주는 예제 

SELECT MANAGER_ID, FIRST_NAME , SALARY , 
       MAX(SALARY) OVER (PARTITION BY MANAGER_ID) SAL_MAX
FROM   EMPLOYEES e  ; 

MANAGER_ID|FIRST_NAME |SALARY|SAL_MAX|
----------+-----------+------+-------+
       100|Neena      | 17000|  17000|
       100|Lex        | 17000|  17000|
       100|Den        | 11000|  17000|
       100|Matthew    |  8000|  17000|
       100|Adam       |  8200|  17000|
       100|Payam      |  7900|  17000|
       100|Shanta     |  6500|  17000|
       100|Kevin      |  5800|  17000|
       100|John       | 14000|  17000|
       100|Karen      | 13500|  17000|
       100|Alberto    | 12000|  17000|
       100|Gerald     | 11000|  17000|
       100|Eleni      | 10500|  17000|
       100|Michael    | 13000|  17000|
       101|Jennifer   |  4400|  12008|
       101|Susan      |  6500|  12008|
       101|Nancy      | 12008|  12008|
       101|Shelley    | 12008|  12008|
       101|Hermann    | 10000|  12008|
       102|Alexander  |  9000|   9000|

인라인 뷰를 사용해 같은 매니저를 둔 사원들중 가장 높은 급여를 받는 사원들을 조회하는 예제 

SELECT MANAGER_ID, FIRST_NAME , SALARY
FROM (
	SELECT MANAGER_ID, FIRST_NAME , SALARY , 
	       MAX(SALARY) OVER (PARTITION BY MANAGER_ID) SAL_MAX
	FROM   EMPLOYEES e  ) 
WHERE SALARY = SAL_MAX
ORDER BY MANAGER_ID;

MANAGER_ID|FIRST_NAME|SALARY|
----------+----------+------+
       100|Neena     | 17000|
       100|Lex       | 17000|
       101|Nancy     | 12008|
       101|Shelley   | 12008|
       102|Alexander |  9000|
       103|Bruce     |  6000|
...

MIN

같은 매니저를 둔 사원들 중 가장 적은 급여를 보여주는 예제

SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY , 
       MIN(SALARY) OVER (PARTITION BY MANAGER_ID ) SAL_MIN
FROM   EMPLOYEES e  ;

MANAGER_ID|FIRST_NAME |HIRE_DATE              |SALARY|SAL_MIN|
----------+-----------+-----------------------+------+-------+
       100|Neena      |2005-09-21 00:00:00.000| 17000|   5800|
       100|Lex        |2001-01-13 00:00:00.000| 17000|   5800|
       100|Den        |2002-12-07 00:00:00.000| 11000|   5800|
       100|Matthew    |2004-07-18 00:00:00.000|  8000|   5800|
       100|Adam       |2005-04-10 00:00:00.000|  8200|   5800|
       100|Payam      |2003-05-01 00:00:00.000|  7900|   5800|
       100|Shanta     |2005-10-10 00:00:00.000|  6500|   5800|
       100|Kevin      |2007-11-16 00:00:00.000|  5800|   5800|
       100|John       |2004-10-01 00:00:00.000| 14000|   5800|
       100|Karen      |2005-01-05 00:00:00.000| 13500|   5800|
       100|Alberto    |2005-03-10 00:00:00.000| 12000|   5800|
       100|Gerald     |2007-10-15 00:00:00.000| 11000|   5800|
       100|Eleni      |2008-01-29 00:00:00.000| 10500|   5800|
       100|Michael    |2004-02-17 00:00:00.000| 13000|   5800|
       101|Jennifer   |2003-09-17 00:00:00.000|  4400|   4400|
       101|Susan      |2002-06-07 00:00:00.000|  6500|   4400|
       101|Nancy      |2002-08-17 00:00:00.000| 12008|   4400|
       101|Shelley    |2002-06-07 00:00:00.000| 12008|   4400|
       101|Hermann    |2002-06-07 00:00:00.000| 10000|   4400|
       102|Alexander  |2006-01-03 00:00:00.000|  9000|   9000|
       ...

같은 매니저를 둔 사원중 입사일을 기준으로 정렬되어(ASC) 해당 로우의 입사일자를 포함 이전 날짜중에 가장 적은 급여를 보여주는 예제

SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY , 
       MIN(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE) SAL_MIN
FROM   EMPLOYEES e  ;

MANAGER_ID|FIRST_NAME |HIRE_DATE              |SALARY|SAL_MIN|
----------+-----------+-----------------------+------+-------+
       100|Lex        |2001-01-13 00:00:00.000| 17000|  17000|
       100|Den        |2002-12-07 00:00:00.000| 11000|  11000|
       100|Payam      |2003-05-01 00:00:00.000|  7900|   7900|
       100|Michael    |2004-02-17 00:00:00.000| 13000|   7900|
       100|Matthew    |2004-07-18 00:00:00.000|  8000|   7900|
       100|John       |2004-10-01 00:00:00.000| 14000|   7900|
       100|Karen      |2005-01-05 00:00:00.000| 13500|   7900|
       100|Alberto    |2005-03-10 00:00:00.000| 12000|   7900|
       100|Adam       |2005-04-10 00:00:00.000|  8200|   7900|
       100|Neena      |2005-09-21 00:00:00.000| 17000|   7900|
       100|Shanta     |2005-10-10 00:00:00.000|  6500|   6500|
       100|Gerald     |2007-10-15 00:00:00.000| 11000|   6500|
       100|Kevin      |2007-11-16 00:00:00.000|  5800|   5800|
       100|Eleni      |2008-01-29 00:00:00.000| 10500|   5800|
       101|Hermann    |2002-06-07 00:00:00.000| 10000|   6500|
       101|Shelley    |2002-06-07 00:00:00.000| 12008|   6500|
       101|Susan      |2002-06-07 00:00:00.000|  6500|   6500|
       101|Nancy      |2002-08-17 00:00:00.000| 12008|   6500|
       101|Jennifer   |2003-09-17 00:00:00.000|  4400|   4400|
       102|Alexander  |2006-01-03 00:00:00.000|  9000|   9000|
       ...

AVG

같은 매니저를 둔 사원들의 급여의 평균을 구하는 예제 ( 반올림 )

SELECT MANAGER_ID, FIRST_NAME, SALARY , 
       ROUND( AVG(SALARY) OVER (PARTITION BY MANAGER_ID )) SAL_AVG
FROM   EMPLOYEES e  ;

MANAGER_ID|FIRST_NAME |SALARY|SAL_AVG|
----------+-----------+------+-------+
       100|Neena      | 17000|  11100|
       100|Lex        | 17000|  11100|
       100|Den        | 11000|  11100|
       100|Matthew    |  8000|  11100|
       100|Adam       |  8200|  11100|
       100|Payam      |  7900|  11100|
       100|Shanta     |  6500|  11100|
       100|Kevin      |  5800|  11100|
       100|John       | 14000|  11100|
       100|Karen      | 13500|  11100|
       100|Alberto    | 12000|  11100|
       100|Gerald     | 11000|  11100|
       100|Eleni      | 10500|  11100|
       100|Michael    | 13000|  11100|
       101|Jennifer   |  4400|   8983|
       101|Susan      |  6500|   8983|
       101|Nancy      | 12008|   8983|
       101|Shelley    | 12008|   8983|
       101|Hermann    | 10000|   8983|
       102|Alexander  |  9000|   9000|

같은 매니저를 둔 사원들을 입사일로 정렬 하여 자기 자신 포함 이전 로우 하나와 다음 로우 하나의 ( 3 rows) 평균을 구하는 예제

SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY , 
       ROUND (AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )) SAL_AVG
FROM   EMPLOYEES e  ; 

MANAGER_ID|FIRST_NAME |HIRE_DATE              |SALARY|SAL_AVG|
----------+-----------+-----------------------+------+-------+
       100|Lex        |2001-01-13 00:00:00.000| 17000|  14000|
       100|Den        |2002-12-07 00:00:00.000| 11000|  11967|
       100|Payam      |2003-05-01 00:00:00.000|  7900|  10633|
       100|Michael    |2004-02-17 00:00:00.000| 13000|   9633|
       100|Matthew    |2004-07-18 00:00:00.000|  8000|  11667|
       100|John       |2004-10-01 00:00:00.000| 14000|  11833|
       100|Karen      |2005-01-05 00:00:00.000| 13500|  13167|
       100|Alberto    |2005-03-10 00:00:00.000| 12000|  11233|
       100|Adam       |2005-04-10 00:00:00.000|  8200|  12400|
       100|Neena      |2005-09-21 00:00:00.000| 17000|  10567|
       100|Shanta     |2005-10-10 00:00:00.000|  6500|  11500|
       100|Gerald     |2007-10-15 00:00:00.000| 11000|   7767|
       100|Kevin      |2007-11-16 00:00:00.000|  5800|   9100|
       100|Eleni      |2008-01-29 00:00:00.000| 10500|   8150|
       101|Hermann    |2002-06-07 00:00:00.000| 10000|  11004|
       101|Shelley    |2002-06-07 00:00:00.000| 12008|   9503|
       101|Susan      |2002-06-07 00:00:00.000|  6500|  10172|
       101|Nancy      |2002-08-17 00:00:00.000| 12008|   7636|
       101|Jennifer   |2003-09-17 00:00:00.000|  4400|   8204|
       102|Alexander  |2006-01-03 00:00:00.000|  9000|   9000|
       103|David      |2005-06-25 00:00:00.000|  4800|   4800|
       103|Valli      |2006-02-05 00:00:00.000|  4800|   4600|
       103|Diana      |2007-02-07 00:00:00.000|  4200|   5000|
       103|Bruce      |2007-05-21 00:00:00.000|  6000|   5100|
       ...

COUNT

같은 매니저를 둔 사원들의 수를 구하는 예제 

SELECT MANAGER_ID, EMPLOYEE_ID, FIRST_NAME, SALARY , 
       count(EMPLOYEE_ID) OVER (PARTITION BY MANAGER_ID ) SAL_CNT
FROM   EMPLOYEES e  ;

MANAGER_ID|EMPLOYEE_ID|FIRST_NAME |SALARY|SAL_CNT|
----------+-----------+-----------+------+-------+
       100|        101|Neena      | 17000|     14|
       100|        102|Lex        | 17000|     14|
       100|        114|Den        | 11000|     14|
       100|        120|Matthew    |  8000|     14|
       100|        121|Adam       |  8200|     14|
       100|        122|Payam      |  7900|     14|
       100|        123|Shanta     |  6500|     14|
       100|        124|Kevin      |  5800|     14|
       100|        145|John       | 14000|     14|
       100|        146|Karen      | 13500|     14|
       100|        147|Alberto    | 12000|     14|
       100|        148|Gerald     | 11000|     14|
       100|        149|Eleni      | 10500|     14|
       100|        201|Michael    | 13000|     14|
       101|        200|Jennifer   |  4400|      5|
       101|        203|Susan      |  6500|      5|
       101|        108|Nancy      | 12008|      5|
       101|        205|Shelley    | 12008|      5|
       101|        204|Hermann    | 10000|      5|
       102|        103|Alexander  |  9000|      1|
       ...

사원 급여 순으로 정렬 후 본인 월급의 -50 이하 부터 +200 이상인 월급을 가진 로우수(해당 로우 포함)를 구하는 예제 

SELECT ENAME, SAL, 
       COUNT(*) OVER (ORDER BY SAL
       RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM   EMP ;

MANAGER_ID|FIRST_NAME |SALARY|SAL_CNT|
----------+-----------+------+-------+
       121|TJ         |  2100|      3|
       120|Steven     |  2200|      4|
       122|Hazel      |  2200|      4|
       120|James      |  2400|     12|
       122|Ki         |  2400|     12|
       114|Karen      |  2500|     12|
       121|James      |  2500|     12|
       123|Joshua     |  2500|     12|
       124|Peter      |  2500|     12|
       120|Martha     |  2500|     12|
       122|Randall    |  2500|     12|
       114|Guy        |  2600|     10|
       124|Randall    |  2600|     10|
       124|Donald     |  2600|     10|
       124|Douglas    |  2600|     10|
       120|Irene      |  2700|      9|
       123|John       |  2700|      9|
       ...

1. Window Function 개요

윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다. 
 
WINDOW FUNCTION 종류

 
SYNTAX

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

-- WINDOW_FUNCTION : 윈도우함수 
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
                 order by에 종속적
                 --ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
                 --WINDOWING절은 SQL Server에서는 지원하지 않는다.

WINDOWING 절 SYNTAX 상세

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]

ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다 

WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING 

UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들
UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들 

n PRECEDING : 현재행을 기준으로 n칸 앞 
n FOLLWING  : 현재행을 기준으로 n칸 뒤

ex)
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY , 
            SUM(SALARY) OVER ( partition by JOB_ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM HR.EMPLOYEES ;

job_id 로 파티셔닝 후 현재 행 기준 이전 모든 행과 현재 행까지의 sal 의 합을 구한 후 sal로 정렬

2. 그룹 내 순위 함수 

 
순위 함수 요약

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

 
RANK 함수

• order by를 포함한 QUERY문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수
• 특정 범위(Partition) 내에서 혹은 전체 데이터에 대한 순위 구함
• 동일한 값에 대해서는 동일한 순위를 부여하고 별개의 건수로 취급한다. 
• 위의 동일한 순위가 부여가 됐다면 아래 순위는 해당 (중복 개수 -1) 만큼의 순위를 건너뛰고 순위를 부여한다.
  • ex) 3위가 3명이라면 다음 순위는 6위가 된다.

-- 사원 데이터에서 급여가 높은 순서와 job별로 급여가 높은 순서를 같이 출력
-- 동일 salary에서 같은 순위 부여


--## ORACLE
SELECT JOB_ID , FIRST_NAME, SALARY,
       rank() OVER ( ORDER BY SALARY DESC ) all_rank,
       rank() OVER ( PARTITION BY JOB_ID  ORDER BY SALARY desc) job_rank 
FROM hr.EMPLOYEES e ;

-- order by sal desc와 partition by job order by salary desc 가 충돌하여 
-- 전체 order by sal desc 기준으로 정렬

JOB_ID    |FIRST_NAME |SALARY|ALL_RANK|JOB_RANK|
----------+-----------+------+--------+--------+
AD_PRES   |Steven     | 24000|       1|       1|
AD_VP     |Neena      | 17000|       2|       1|
AD_VP     |Lex        | 17000|       2|       1|
SA_MAN    |John       | 14000|       4|       1|
SA_MAN    |Karen      | 13500|       5|       2|
MK_MAN    |Michael    | 13000|       6|       1|
FI_MGR    |Nancy      | 12008|       7|       1|
AC_MGR    |Shelley    | 12008|       7|       1|
SA_MAN    |Alberto    | 12000|       9|       3|
SA_REP    |Lisa       | 11500|      10|       1|
PU_MAN    |Den        | 11000|      11|       1|
SA_REP    |Ellen      | 11000|      11|       2|
SA_MAN    |Gerald     | 11000|      11|       4|
SA_REP    |Clara      | 10500|      14|       3|
SA_MAN    |Eleni      | 10500|      14|       5|
SA_REP    |Peter      | 10000|      16|       4|
PR_REP    |Hermann    | 10000|      16|       1|
SA_REP    |Harrison   | 10000|      16|       4|
SA_REP    |Janette    | 10000|      16|       4|
SA_REP    |Tayler     |  9600|      20|       7|
SA_REP    |Patrick    |  9500|      21|       8|
SA_REP    |Danielle   |  9500|      21|       8|
SA_REP    |David      |  9500|      21|       8|
FI_ACCOUNT|Daniel     |  9000|      24|       1|
IT_PROG   |Alexander  |  9000|      24|       1|
SA_REP    |Allan      |  9000|      24|      11|
SA_REP    |Peter      |  9000|      24|      11|
SA_REP    |Alyssa     |  8800|      28|      13|
SA_REP    |Jonathon   |  8600|      29|      14|
SA_REP    |Jack       |  8400|      30|      15|
AC_ACCOUNT|William    |  8300|      31|       1|
FI_ACCOUNT|John       |  8200|      32|       2|
ST_MAN    |Adam       |  8200|      32|       1|
SA_REP    |Christopher|  8000|      34|      16|
ST_MAN    |Matthew    |  8000|      34|       2|
SA_REP    |Lindsey    |  8000|      34|      16|
ST_MAN    |Payam      |  7900|      37|       3|
FI_ACCOUNT|Jose Manuel|  7800|      38|       3|
FI_ACCOUNT|Ismael     |  7700|      39|       4|
SA_REP    |Nanette    |  7500|      40|      18|
SA_REP    |Louise     |  7500|      40|      18|
SA_REP    |William    |  7400|      42|      20|
SA_REP    |Elizabeth  |  7300|      43|      21|
SA_REP    |Mattea     |  7200|      44|      22|
SA_REP    |Oliver     |  7000|      45|      23|
SA_REP    |Kimberely  |  7000|      45|      23|
SA_REP    |Sarath     |  7000|      45|      23|
FI_ACCOUNT|Luis       |  6900|      48|       5|
...
...

--## PostgreSQL
SELECT JOB_ID , FIRST_NAME, SALARY,
       rank() OVER ( ORDER BY SALARY DESC ) all_rank,
       rank() OVER ( PARTITION BY JOB_ID  ORDER BY SALARY desc) job_rank 
FROM hr.EMPLOYEES e ;

-- order by sal desc와 partition by job order by salary desc 가 충돌하여 partition by job 
-- 기준으로 모이고 그 그룹안에서 salary 순위로 정렬 

job_id    |first_name |salary  |all_rank|job_rank|
----------+-----------+--------+--------+--------+
AC_ACCOUNT|William    | 8300.00|      31|       1|
AC_MGR    |Shelley    |12008.00|       7|       1|
AD_ASST   |Jennifer   | 4400.00|      61|       1|
AD_PRES   |Steven     |24000.00|       1|       1|
AD_VP     |Lex        |17000.00|       2|       1|
AD_VP     |Neena      |17000.00|       2|       1|
FI_ACCOUNT|Daniel     | 9000.00|      24|       1|
FI_ACCOUNT|John       | 8200.00|      32|       2|
FI_ACCOUNT|Jose Manuel| 7800.00|      38|       3|
FI_ACCOUNT|Ismael     | 7700.00|      39|       4|
FI_ACCOUNT|Luis       | 6900.00|      48|       5|
FI_MGR    |Nancy      |12008.00|       7|       1|
HR_REP    |Susan      | 6500.00|      50|       1|
IT_PROG   |Alexander  | 9000.00|      24|       1|
IT_PROG   |Bruce      | 6000.00|      56|       2|
IT_PROG   |Valli      | 4800.00|      59|       3|
IT_PROG   |David      | 4800.00|      59|       3|
IT_PROG   |Diana      | 4200.00|      62|       5|
MK_MAN    |Michael    |13000.00|       6|       1|
MK_REP    |Pat        | 6000.00|      56|       1|
PR_REP    |Hermann    |10000.00|      16|       1|
PU_CLERK  |Alexander  | 3100.00|      78|       1|
PU_CLERK  |Shelli     | 2900.00|      84|       2|
PU_CLERK  |Sigal      | 2800.00|      87|       3|
PU_CLERK  |Guy        | 2600.00|      93|       4|
PU_CLERK  |Karen      | 2500.00|      97|       5|
PU_MAN    |Den        |11000.00|      11|       1|
SA_MAN    |John       |14000.00|       4|       1|
SA_MAN    |Karen      |13500.00|       5|       2|
SA_MAN    |Alberto    |12000.00|       9|       3|
...
...

-- 전체 SALARY 순위 구하는 ALL RANK 컬럼 제외, 업무별로 SALARY 순위를 구하는 JOB_RANK만 출력 
-- 결과적으로 JOB과 SALARY 별로 정렬 

SELECT JOB_ID , FIRST_NAME, SALARY,
       rank() OVER ( PARTITION BY JOB_ID  ORDER BY SALARY desc) job_rank 
FROM hr.EMPLOYEES e ; 

--## ORACLE
JOB_ID    |FIRST_NAME |SALARY|JOB_RANK|
----------+-----------+------+--------+
AC_ACCOUNT|William    |  8300|       1|
AC_MGR    |Shelley    | 12008|       1|
AD_ASST   |Jennifer   |  4400|       1|
AD_PRES   |Steven     | 24000|       1|
AD_VP     |Lex        | 17000|       1|
AD_VP     |Neena      | 17000|       1|
FI_ACCOUNT|Daniel     |  9000|       1|
FI_ACCOUNT|John       |  8200|       2|
FI_ACCOUNT|Jose Manuel|  7800|       3|
FI_ACCOUNT|Ismael     |  7700|       4|
FI_ACCOUNT|Luis       |  6900|       5|
FI_MGR    |Nancy      | 12008|       1|
HR_REP    |Susan      |  6500|       1|
IT_PROG   |Alexander  |  9000|       1|
IT_PROG   |Bruce      |  6000|       2|
IT_PROG   |David      |  4800|       3|
IT_PROG   |Valli      |  4800|       3|
IT_PROG   |Diana      |  4200|       5| --위에 3위가 두명이니 4위를 건너뛰고 바로 5위를 준다.
MK_MAN    |Michael    | 13000|       1|
MK_REP    |Pat        |  6000|       1|
PR_REP    |Hermann    | 10000|       1|
PU_CLERK  |Alexander  |  3100|       1|
PU_CLERK  |Shelli     |  2900|       2|
PU_CLERK  |Sigal      |  2800|       3|
PU_CLERK  |Guy        |  2600|       4|
PU_CLERK  |Karen      |  2500|       5|
PU_MAN    |Den        | 11000|       1|
SA_MAN    |John       | 14000|       1|
SA_MAN    |Karen      | 13500|       2|
SA_MAN    |Alberto    | 12000|       3|
SA_MAN    |Gerald     | 11000|       4|
SA_MAN    |Eleni      | 10500|       5|
SA_REP    |Lisa       | 11500|       1|
SA_REP    |Ellen      | 11000|       2|
SA_REP    |Clara      | 10500|       3|
SA_REP    |Janette    | 10000|       4|
SA_REP    |Peter      | 10000|       4|
SA_REP    |Harrison   | 10000|       4|
SA_REP    |Tayler     |  9600|       7| --4위가 3명이니 5,6위를 건너뛰고 7위를 부여한다.  
SA_REP    |David      |  9500|       8|
SA_REP    |Danielle   |  9500|       8|
SA_REP    |Patrick    |  9500|       8|
SA_REP    |Peter      |  9000|      11|
SA_REP    |Allan      |  9000|      11|
SA_REP    |Alyssa     |  8800|      13|
SA_REP    |Jonathon   |  8600|      14|
...
...

--## PostgreSQL 
-- ORACLE과 동일한 결과

 
DENSE_RANK 함수

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

SELECT JOB_ID , FIRST_NAME, SALARY,
       rank() OVER ( ORDER BY SALARY DESC ) RANK,
       dense_rank() OVER ( ORDER BY SALARY DESC ) DENSE_RANK 
FROM hr.EMPLOYEES e ;

--## ORACLE
JOB_ID    |FIRST_NAME |SALARY|RANK|DENSE_RANK|
----------+-----------+------+----+----------+
AD_PRES   |Steven     | 24000|   1|         1|
AD_VP     |Neena      | 17000|   2|         2|
AD_VP     |Lex        | 17000|   2|         2|
SA_MAN    |John       | 14000|   4|         3| -- DENS_RANK는 RANK와 달리 2순위를 복수로 취급하지 않는다.
SA_MAN    |Karen      | 13500|   5|         4|
MK_MAN    |Michael    | 13000|   6|         5|
FI_MGR    |Nancy      | 12008|   7|         6|
AC_MGR    |Shelley    | 12008|   7|         6|
SA_MAN    |Alberto    | 12000|   9|         7|
SA_REP    |Lisa       | 11500|  10|         8|
PU_MAN    |Den        | 11000|  11|         9|
SA_MAN    |Gerald     | 11000|  11|         9|
SA_REP    |Ellen      | 11000|  11|         9|
SA_MAN    |Eleni      | 10500|  14|        10|
SA_REP    |Clara      | 10500|  14|        10|
SA_REP    |Janette    | 10000|  16|        11|
SA_REP    |Peter      | 10000|  16|        11|
PR_REP    |Hermann    | 10000|  16|        11|
SA_REP    |Harrison   | 10000|  16|        11|
SA_REP    |Tayler     |  9600|  20|        12|
SA_REP    |Danielle   |  9500|  21|        13|
SA_REP    |David      |  9500|  21|        13|
SA_REP    |Patrick    |  9500|  21|        13|
...
...

--## PostgreSQL
-- 위와 동일

 
ROW_NUMBER

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

--## ORACLE
SELECT JOB_ID , FIRST_NAME, SALARY,
       rank() OVER ( ORDER BY SALARY DESC ) RANK,
       dense_rank() OVER ( ORDER BY SALARY DESC ) DENSE_RANK ,
       row_number() OVER ( ORDER BY SALARY DESC ) ROW_NUMBER 
FROM hr.EMPLOYEES e ;

JOB_ID    |FIRST_NAME |SALARY|RANK|DENSE_RANK|ROW_NUMBER|
----------+-----------+------+----+----------+----------+
AD_PRES   |Steven     | 24000|   1|         1|         1|
AD_VP     |Neena      | 17000|   2|         2|         2|
AD_VP     |Lex        | 17000|   2|         2|         3| -- RANK, DENSE_RANK와 다른 부분
SA_MAN    |John       | 14000|   4|         3|         4|
SA_MAN    |Karen      | 13500|   5|         4|         5|
MK_MAN    |Michael    | 13000|   6|         5|         6|
FI_MGR    |Nancy      | 12008|   7|         6|         7|
AC_MGR    |Shelley    | 12008|   7|         6|         8|
SA_MAN    |Alberto    | 12000|   9|         7|         9|
SA_REP    |Lisa       | 11500|  10|         8|        10|
PU_MAN    |Den        | 11000|  11|         9|        11|
...
...

-- ## PostgreSQL
-- 위와 동일

다음글에서는 그룹 내 집계 관련 함수에 대해 알아보겠습니다.