데이터베이스 질문과 답변
안녕하세요.
쿼리가 잘 안되어서 문의드립니다.
아래와 같은 샘플이 있을경우 한번 쿼리에 가능할지 문의 드립니다.
| 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 |
좋은 한주 되세요.
미리 감사드립니다.
by 마농
[2021.05.20 12:37:06]
TV 의 before 가 왜 63 이죠? 64 아닌가요?
by 도겸아빠
[2021.05.20 13:29:51]
안녕하세요.
UPDATED_NAME='JOBUSR'가 아닌 가장 최근 값입니다. RESERVE_VERSION_NO = 4번 값이 되어야 해요.
by 마농
[2021.05.20 13:44:09]
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
;
by 도겸아빠
[2021.05.20 14:29:41]
바쁘실텐데 도움 주셔서 감사드립니다.
즐거운 오후 되세요.^^
) 버튼을 클릭하여 작성 하시면 됩니다.