그룹 내 행 순서 함수

그룹 내 행 순서 함수

 

 

 

* FIRST_VALUE 함수

   

 - 파티션별 윈도우에서 가장 먼저 나온 값을 구합니다. MIN 함수를 활용하여 같은 결과를 얻을 수도 있습니다.

   SQL Server에서는 지원하지 않는 함수입니다.

 

 EX)

 

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

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

 

DEPTNO	ENAME	SAL	DEPT_RICH
10	KING	5000	KING
20	SCOTT	3000	SCOTT
20	FORD	3000	SCOTT
20	JONES	2975	SCOTT
20	ADAMS	1100	SCOTT
20	SMITH	800	SCOTT

 

* SCOTT과 FORD 중에서 어느 사람이 최고 급여자로 선택될지는 FIRST_VALUE 함수로 판단할 수 없습니다.

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

  공동 등수가 있을 경우에 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER() 내의 ORDER BY 절에 컬럼을 추가

  해야 합니다.

 

 EX) 같은 값을 지닌 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 DEPT_RICH
FROM EMP;

DEPTNO	ENAME	SAL	DEPT_RICH
10	KING	5000	KING
20	FORD	3000	FORD
20	SCOTT	3000	FORD
20	JONES	2975	FORD
20	ADAMS	1100	FORD
20	SMITH	800	FORD

 

* 두 번째 정렬 조건을 추가하여 반영된 결과입니다. ENAME이 ASCII 코드 값으로 정렬되어 DEPT_RICH가 변경된 것을 확인할 수 있습니다.

 

 

* LAST_VALUE 함수

   

 - 파티션별 윈도우에서 가장 나중에 나온 값을 구합니다. SQL Server에서는 지원하지 않는 함수입니다. MAX 함수를 활용하여 같은 결과를 얻을 수도 

   있습니다.

 

 EX)

 

SELECT DEPTNO
     , ENAME
     , SAL
     , LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
       AS DEPT_ROOR
FROM EMP;

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

 

 

DEPTNO	ENAME	SAL	DEPT_POOR
10	MILLER	1300	MILLER
20	SCOTT	3000	SMITH
20	FORD	3000	SMITH
20	JONES	2975	SMITH
20	ADAMS	1100	SMITH
20	SMITH	800	SMITH

 

LAST_VALUE는 다른 함수와 달리 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리합니다.

공동 등수가 있을 경우 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 조건에 컬럼을 추가해야 합니다.

 

 

* LAG 함수

   

 - 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다. SQL Server에서는 지원하지 않는 함수입니다.

 

 EX)

 

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

 

LAG(SAL,2,0)는 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리합니다.

LAG(SAL)은 한 행 앞의 SALARY를 가져옵니다.

 

 

* LEAD 함수

   

 - 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있습니다. SQL Server에서는 지원하지 않는 함수입니다.

 

 EX)

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

 

LAG의 반대 개념의 함수입니다. 

LEAD(HIREDATE,2,0)은 두 행 뒤의 데이터를 가져오고 3번째 인자는 NULL값을 지정한 값으로 바꾸어 줍니다. NVL이나 ISNULL과 기능이 같습니다.