본문 바로가기
Oracle/SQL

[SQL] 윈도우 함수(Window Function)의 종류(3) - 행 순서 관련 함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD)

호루 2023. 4. 17.
728x90

1. Window Function 개요

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

 

WINDOW FUNCTION 종류

구분 함수 지원 정보 
그룹 내 순위(RANK) 관련 함수 RANK, DENSE_RANK, ROW_NUMBER  ANSI/ISO SQL 표준과 Oracle, SQL Sever등 대부분의 DBMS에서 지원 
그룹 내 집계(AGGREGATE) 관련 함수  SUM ,MAX, MIN, AVG, COUNT ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원

SQL Server 의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지 않음
그룹 내 행 순서 관련 함수  FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE에서만 지원 
그룹 내 비율 관련 함수


 CUME_DIST, PERCENT_RANK  ANSI/ISO SQL 표준과 Oracle, MS SQL에서 지원
NTILE ORACLE, MS SQL에서 지원
RATIO_TO_REPORT ORACLE 에서만 지원

 

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|
...

 

728x90
반응형

'Oracle > SQL' 카테고리의 다른 글

[SQL] 계층형 쿼리 ( CONNECT BY )  (0) 2024.03.05
[SQL] 윈도우 함수(Window Function)의 종류(4)- 비율 관련 함수(CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT)  (0) 2023.04.18
[SQL] 윈도우 함수(Window Function)의 종류(2) - 집계 관련 함수(SUM, MAX, MIN, AVG, COUNT)  (0) 2023.04.13
[SQL] 윈도우 함수(Window Function)의 종류(1) - 순위 관련 함수(RANK, DENSE_RANK, ROW_NUMBER)  (0) 2023.04.05
[SQL] NULL 처리  (0) 2023.04.03

'Oracle/SQL' Related Articles

티스토리툴바