오라클 first_value(), last_value()

오라클의 그룹함수 중에 first_value(), last_value() 가 있다.

first_value() : 집합 중 첫번째 값을 가져온다.
last_value() : 집합 중 마지막 값을 가져온다.

예제를 보면 쉽게 이해할 수 있다.

단, last_value() 경우 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 의
사용여부에 따라 결과 값이 달라지므로 주의하도록 한다.
( ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : 정렬결과의 처음과 끝을 대상으로 함
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 정렬결과 처음부터 현재 행까지를 대상으로 함)

-- first_value : 1월부터 4월의 집합 중 1월 job_cd를 가져온다.
with t as 
(
select 1 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 2 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 3 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 4 as wol, 1001 as sabeon, 'A0' as job_cd from dual
union all select 5 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 6 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 7 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 8 as wol, 1001 as sabeon, 'A3' as job_cd from dual
)
select wol
     , sabeon
     , first_value(job_cd) over(partition by sabeon order by wol) job_cd
 from t
where wol between 1 and 4
;

       WOL     SABEON JO
---------- ---------- --
         1       1001 A1
         2       1001 A1
         3       1001 A1
         4       1001 A1


-- last_value : 1월부터 4월의 집합 중 4월 job_cd를 가져온다. 
with t as 
(
select 1 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 2 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 3 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 4 as wol, 1001 as sabeon, 'A0' as job_cd from dual
union all select 5 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 6 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 7 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 8 as wol, 1001 as sabeon, 'A3' as job_cd from dual
)
select wol
     , sabeon
     , last_value(job_cd) over(partition by sabeon order by wol 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) job_cd
 from t
where wol between 1 and 4
;

       WOL     SABEON JO
---------- ---------- --
         1       1001 A0
         2       1001 A0
         3       1001 A0
         4       1001 A0

-- last_value : 1월인 경우 1월 1개행을 대상으로 마지막 job_cd를 가져오고(여기서는 A1),
--  2월인 경우 1~2월 2개행을 대상으로 마지막 job_cd를 가져온다(여기서는 A2)
--  3월과 4월의 경우에도 위와 같은 방식으로 집합을 달리해 가며 마지막 job_cd 를 가져온다.
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 명시하지 않아도 같은 결과를 보여줌(default)
-- 
with t as 
(
select 1 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 2 as wol, 1001 as sabeon, 'A1' as job_cd from dual
union all select 3 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 4 as wol, 1001 as sabeon, 'A0' as job_cd from dual
union all select 5 as wol, 1001 as sabeon, 'A2' as job_cd from dual
union all select 6 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 7 as wol, 1001 as sabeon, 'A3' as job_cd from dual
union all select 8 as wol, 1001 as sabeon, 'A3' as job_cd from dual
)
select wol
     , sabeon
     , last_value(job_cd) over(partition by sabeon order by wol 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) job_cd
 from t
where wol between 1 and 4
;

       WOL     SABEON JO
---------- ---------- --
         1       1001 A1
         2       1001 A1
         3       1001 A2
         4       1001 A0

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다