[Oracle] 윈도우 함수(Window Funtion)_그룹 내 행 순서 함수

윈도우 함수 종류

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

 - RANK, DENSE_RANK, ROW_NUMBER 함수

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

 - SUM, MAX, MIN, AVG, COUNT 함수 

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

 - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수 

4.그룹 내 비율 관련 함수

 - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수

윈도우 함수 문법 

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절])

  FROM 테이블명; 

  

WINDOW_FUNCTION

 - 함수명

ARGUMENTS (인수) 

 - 함수에 따라 0 ~ N개의 인수가 지정될 수 있습니다.

PARTITION BY 절

 - 전체 집합을 기준에 의해 소그룹으로 나눌 수 있습니다.

ORDER BY 절

 - 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술합니다.

WINDOWING 절

 - 함수의 대상이 되는 행 기준의 범위를 지정할 수 있습니다.

 

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;

  

ROWS UNBOUNDED PRECEDIN

 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정합니다.

 

 실행 결과를 보면 같은 부서 내에 최고 급여를 받는 사람들이 둘 있는 경우, 부서번호 20의 SCOTT과 FORD 중에서 어느 사람이 최고 급여자로 선택될지는 위의 SQL 문만 가지고는 판단할 수 없습니다.

 FIRST_VALUE는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리합니다. 위처럼 공동 등수가 있을 경우에 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER() 내의 ORDER BY 절에 컬럼을 추가해야 합니다.

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

 

  SQL에서 같은 부서 내에 최고 급여를 받는 사람이 둘 있는 경우를 대비해서 이름을 두 번째 정렬 조건으로 추가합니다.

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

 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정합니다.  

 LAST_VALUE는 다른 함수와 달리 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리합니다. 만일 공동 등수가 있을 경우를 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER() 내의 ORDER BY 조건을 컬럼에 추가해야 합니다.

 

LAG 함수 

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

SELECT ENAME
     , HIREDATE
     , SAL
     , LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
  FROM EMP;

 LAG 함수는 3개의 인자까지 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고(DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 변경할 수 있습니다.

SELECT ENAME
     , HIREDATE
     , LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
  FROM EMP;  

 

LEAD 함수 

 LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있습니다.

SELECT ENAME
     , HIREDATE
     , LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS NEXTDATE
  FROM EMP; 

 LEAD 함수는 3개의 인자까지 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고(DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 변경할 수 있습니다. 

SELECT ENAME
     , HIREDATE
     , LEAD(HIREDATE, 2, SYSDATE) OVER (ORDER BY HIREDATE) AS NEXTDATE
  FROM EMP;