LAG문의 드립니다. 1 4 625

by 도겸아빠 [SQL Query] [2021.05.20 11:08:53]


안녕하세요.

쿼리가 잘 안되어서 문의드립니다.

아래와 같은 샘플이 있을경우 한번 쿼리에 가능할지 문의 드립니다.

 

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]

바쁘실텐데 도움 주셔서 감사드립니다.

즐거운 오후 되세요.^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입