안녕하세요.
쿼리가 잘 안되어서 문의드립니다.
아래와 같은 샘플이 있을경우 한번 쿼리에 가능할지 문의 드립니다.
RESERVE_VERSION_NO | PRODUCT_NAME | UPDATED_NAME | CGO_PC |
1 | 반도체 | ICOPORWSUSR | 2 |
2 | 반도체 | ICOPORWSUSR | 1 |
1 | TV | ICOPORWSUSR | 60 |
2 | TV | ICOPORWSUSR | 61 |
3 | TV | 1001276 | 62 |
4 | TV | 1001276 | 63 |
5 | TV | JOBUSR | 64 |
6 | TV | JOBUSR | 65 |
7 | TV | 137550 | 66 |
with tmp1 as ( select 1 as RESERVE_VERSION_NO, '반도체' as PRODUCT_NAME, 'ICOPORWSUSR' as UPDATED_NAME, 2 as CGO_PC from dual union all select 2 as RESERVE_VERSION_NO, '반도체' as PRODUCT_NAME, 'ICOPORWSUSR' as UPDATED_NAME, 1 as CGO_PC from dual union all select 1 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, 'ICOPORWSUSR' as UPDATED_NAME, 60 as CGO_PC from dual union all select 2 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, 'ICOPORWSUSR' as UPDATED_NAME, 61 as CGO_PC from dual union all select 3 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, '1001276' as UPDATED_NAME, 62 as CGO_PC from dual union all select 4 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, '1001276' as UPDATED_NAME, 63 as CGO_PC from dual union all select 5 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, 'JOBUSR' as UPDATED_NAME, 64 as CGO_PC from dual union all select 6 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, 'JOBUSR' as UPDATED_NAME, 65 as CGO_PC from dual union all select 7 as RESERVE_VERSION_NO, 'TV' as PRODUCT_NAME, '137550' as UPDATED_NAME, 66 as CGO_PC from dual ) select * From tmp1
before cgo_pc : UPDATED_NAME='JOBUSR'인 경우 JOBUSR 이전 마지막값
만약 UPDATED_NAME='JOBUSR'가 없는 경우 NULL
current cgo_pc: UPDATED_NAME='JOBUSR'이고 그 중에 마지막 값
만약 UPDATED_NAME='JOBUSR'가 없는 경우 PRODUCT_NAME, RESERVE_VERSION_NO기준으로 마지막값
결과:
PRODUCT_NAME | before cgo_pc | current cgo_pc |
반도체 | null | 1 |
TV | 63 | 65 |
좋은 한주 되세요.
미리 감사드립니다.
WITH tmp1 AS ( SELECT 1 reserve_version_no, '반도체' product_name, 'ICOPORWSUSR' updated_name, 2 cgo_pc FROM dual UNION ALL SELECT 2, '반도체', 'ICOPORWSUSR', 1 FROM dual UNION ALL SELECT 1, 'TV' , 'ICOPORWSUSR', 60 FROM dual UNION ALL SELECT 2, 'TV' , 'ICOPORWSUSR', 61 FROM dual UNION ALL SELECT 3, 'TV' , '1001276' , 62 FROM dual UNION ALL SELECT 4, 'TV' , '1001276' , 63 FROM dual UNION ALL SELECT 5, 'TV' , 'JOBUSR' , 64 FROM dual UNION ALL SELECT 6, 'TV' , 'JOBUSR' , 65 FROM dual UNION ALL SELECT 7, 'TV' , '137550' , 66 FROM dual ) SELECT product_name , before_cgo_pc , current_cgo_pc FROM (SELECT reserve_version_no , product_name , updated_name , cgo_pc current_cgo_pc , DECODE(updated_name, 'JOBUSR' , LAG(DECODE(updated_name, 'JOBUSR', null, cgo_pc)) IGNORE NULLS OVER(PARTITION BY product_name ORDER BY reserve_version_no) ) before_cgo_pc , ROW_NUMBER() OVER(PARTITION BY product_name ORDER BY DECODE(updated_name, 'JOBUSR', 1, 2), reserve_version_no DESC) rn FROM tmp1 ) WHERE rn = 1 ;