오라클 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
최신 댓글