Oracle 행 함수

쿼리를 다루다보면 특정 행에 대해 다뤄야 할 때가 있다.

 

종류로는

1. FIRST_VALUE

2. LAST_VALUE
3. LAG

4. LEAD

 

위 4가지가 있다.

1. FIRST_VALUE : 파티션에서 가장 처음에 나오는 값을 구한다.(그룹함수 MIN과 같은 결과를 구할 수 있음)

select deptno, sal,
       first_value(sal) over () as A,
       first_value(sal) over (partition by DEPTNO) as B,
       first_value(sal) over (partition by DEPTNO order by sal) as C,
       first_value(sal) over (partition by DEPTNO
                              order by sal desc rows unbounded preceding ) as D
from emp order by sal

위 쿼리를 실행해보면 아래와 같은 결과가 나온다.

쿼리를 기본적으로 sal을 기준으로 오름차순으로 정렬되어 있다.

그리고 파티션은 C에선 오름차순, D 에선 내림차순으로 정렬되어 있다.

A 컬럼은 파티션이 따로 적용이 안되어 있으므로 메인쿼리의 첫 행인 800이 될 것이다.

B 컬럼은 부서별 첫 행인 최저값이 적용되어 있다.

C 컬럼도 마찬가지이지만 만약 order by를 삭제한다면 D의 파티션 기준에 따라갈 것이다.

first_value(sal) over (partition by DEPTNO) as C

D 컬럼은 급여 기준 내림차순 파티션에서 각 부서별 첫행을 구해온다.(메인쿼리의 영향을 안받음)

 

2. LAST_VALUE : 파티션에서 가장 나중에 나오는 값을 구한다.(그룹함수 MAX와 같은 결과를 구할 수 있음)

select deptno, sal,
       last_value(sal) over () as A,
       last_value(sal) over (partition by DEPTNO) as B,
       last_value(sal) over (partition by DEPTNO order by sal) as C,
       last_value(sal) over (partition by deptno
                              order by sal desc rows
                              between current row and unbounded following) as E
from emp order by sal

위 쿼리를 실행하면 아래와 같이 나온다.

A컬럼은 오름차순된 메인쿼리 결과에서 마지막 값(max)이 적용된 것이고

B컬럼은 오름차순된 메인쿼리에서 부서별 마지막 값(max)가 적용된 것이다.

C컬럼은 ?

D컬럼은 부서별 내림차순된 것에서 현재행부터 마지막 행(min)까지를 의미하는데 마지막 값을 뽑아내니까 최저값이 나온다.

 

3. LAG : 이전 행을 구해 온다.

select deptno, ename, sal, lag(sal) over (order by sal desc) as pre_sal
from emp

4. LEAD : 특정 위치의 행 값을 구한다. 기본은 1이다.

select deptno, ename, sal, lead(sal, 2) over (order by sal desc) as pre_sal
from emp

위 쿼리를 실행하면 아래와 같은 결과가 나온다.

해석하자면 실행되는 해당 row를 기준으로 2번째 행에 위치하는 값을 가져오는 것이다.

1번 로우의 경우 3번 로우의 sal 값을 가져오는 것이다.

lead(sal, 0)  // 가능
lead(sal, -2) // 불가능