데이터베이스 질문과 답변

조건에 따른 max값 가져오기 질문입니다. 0 11 12,005

by 커피요쿠르트d [SQL Query] 조인 max row_number [2013.04.24 18:59:15]

수고하십니다.

작업 중 막힘이 있는데.. 뚫지를 못하고 있어. 질문드립니다.

두 테이블을 조인하는데..
한 테이블에서 특정 칼럼의 값이 가장 큰 값의 row만 가져오는 내용입니다.
문제는...
다른 칼럼의 값 중 특정값일 경우는 2단계 아래 row를 가져오게 되서요. ㅠㅠ

처음 작업한 쿼리는 아래와 같습니다.


ㅇ
 

with tbl1 as (
select '0001' code, '1' seq, '131' changeCode, '메롱' status from dual union all
select '0001' code, '2' seq, '201' changeCode, '정상' status from dual union all 
select '0001' code, '3' seq, '202' changeCode, '정상' status from dual union all
select '0001' code, '4' seq, '603' changeCode, '메롱' status from dual union all
select '0002' code, '1' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '3' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '4' seq, '100' changeCode, '정상' status from dual union all
select '0003' code, '1' seq, '100' changeCode, '메롱' status from dual union all
select '0003' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0003' code, '3' seq, '100' changeCode, '정상' status from dual
), tbl2 as (
select '0001' code, '1' status from dual union all
select '0002' code, '1' status from dual union all
select '0003' code, '3' status from dual
)select code, changeCode, status from 
(select a.code,
 changecode,
 a.status,
 row_number() over (partition by a.code order by seq desc) as rn
from tbl1 a,
 tbl2 b
where a.code = b.code
and b.status = '1'
)where rn = 1
;

ㅇ
그냥 row_number()를 이용해서 max값가져오는 평범한 쿼리입니다. 

위 쿼리의 결과는 아래와 같습니다.

0

그런데 
문제가.. 예외상황이 있습니다. 

seq의 값이 가장 큰 row의 changeCode중 ..
그 값이  603인 경우는 seq 순서 중 2단계 아래 row인
- code=1 인 row 중 max(seq)가 4이므로 seq=2 인 - row를 
가져오길 바랍니다.

아래와 같이요..




row_number(), max() keep(), lag()
이것저것 시도해보는데.. 잘 안되네요. ㅠㅠ


혹시 좋은 방법을 알고계시면 도움 주시면 감사하겠습니다.

그럼 편안한 밤되셔요.. ㅠㅠ
by 아린 [2013.04.24 19:50:36]
LAG 함수 사용하시면 될듯한데요.  

(2단계 아래 데이터가 없으면 어떻게 할건가요? )

SELECT code
     , DECODE(changecode, '603', NVL(changecode_603,changecode)
            , changecode) changecode
     , DECODE(changecode, '603', NVL(status_603,status)
            , status) status
  FROM (SELECT a.code, a.seq, a.changecode, a.status
             , ROW_NUMBER()  OVER(PARTITION BY a.code ORDER BY a.seq DESC) rn
             , LAG(a.status, 2) OVER(PARTITION BY a.code ORDER BY a.seq) status_603
             , LAG(a.changecode, 2) OVER(PARTITION BY a.code ORDER BY a.seq) 
               changecode_603
          FROM tbl1 a, tbl2 b
         WHERE a.code = b.code
           AND b.status = '1'
        )
 WHERE rn = '1'
by 커피요쿠르트d [2013.04.25 01:43:29]
설명이 좀 모자랐네요.

단계를 거쳐서 진행되는 seq라.. 603의 경우는 2단계위가 항상 존재합니다.

그리고 중복값도 없구요..

음.. 

아.. 아예 가져올 때 2단계 이전 값들을 함꼐 가져와서 처리하는군요..
제가 lag() 함수 사용이 미숙했네요.  ㅜㅜ

좋은 가르침 감사합니다~!!!


by 손님 [2013.04.25 09:35:09]

select code, changeCode, status from
(select a.code,
 changecode,
 a.status,
 row_number() over (partition by a.code order by (case when seq='2' then '5' else seq end) desc) as rn
from tbl1 a,
 tbl2 b
where a.code = b.code
and b.status = '1'
)where rn = 1
;
by 커피요쿠르트d [2013.04.25 16:36:32]
//손님.
손님님의 쿼리는 음..
changeCode값으로 row를 구분하지 못하네요.
무조건 seq의 2의 값을 가져오네요..


제가 설명이 미진했나봅니다. 죄송해요;;

by 신이만든짝퉁 [2013.04.25 09:39:38]
저는 그냥 쿼리를 두번 돌려봤습니다.
select code, changecode, status
  from (
        select a.code
             , a.changecode
             , a.status            
             , dense_rank() over(partition by a.code order by seq desc) rn
          from tbl1 a
             , tbl2 b
         where a.code = b.code
           and b.status = '1'
           and a.changecode <> '603'
        )       
 where rn = 1
 union all
select code, changecode, status
  from (
        select a.code
             , a.changecode
             , a.status
             , dense_rank() over(partition by a.code order by seq desc) rn
          from tbl1 a
             , tbl2 b
         where a.code = b.code
           and b.status = '1'         
           and a.changecode = '603'
        )       
  where rn = 3
 ;
by 커피요쿠르트d [2013.04.25 16:37:07]


//신이만든짝퉁
조건에 해당되는 row와 아닌 row를 구분해서 가져오네요
이 아이디어도 좋은거 같아요!!

그런데.. 2단계 아래 처리가 필요한데..
603이 아닌 경우에서 max(seq)의 바로 아래 row를 가져오네요.. 흑흑.
실제 쿼리를 약간 수정해서 실행해보면 ..
603이 아닌 row만 조회되네요.. 
잘 하면 수정할 수 있을거같은데 지금은 시간이 없어서요. ㅠㅠ

ㅇ

 
 
 
 with tbl1 as (
select '0001' code, '1' seq, '131' changeCode, '메롱' status from dual union all
select '0001' code, '2' seq, '201' changeCode, '정상' status from dual union all
select '0001' code, '3' seq, '202' changeCode, '헤롱' status from dual union all
select '0001' code, '4' seq, '603' changeCode, '메롱' status from dual union all
select '0002' code, '1' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '3' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '4' seq, '100' changeCode, '정상' status from dual union all
select '0003' code, '1' seq, '100' changeCode, '메롱' status from dual union all
select '0003' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0003' code, '3' seq, '100' changeCode, '정상' status from dual
), tbl2 as (
select '0001' code, '1' status from dual union all
select '0002' code, '1' status from dual union all
select '0003' code, '3' status from dual
)select gb,code, changecode, status
 from (
  select 'non' gb
    , a.code
    , a.changecode
    , a.status    
    , dense_rank() over(partition by a.code order by seq desc) rn
   from tbl1 a
    , tbl2 b
   where a.code = b.code
   and b.status = '1'
   and a.changecode <> '603'
  )  
 where rn = 1
 union all 
select gb, code, changecode, status
 from (
  select 'is' gb
    , a.code
    , a.changecode
    , a.status
    , dense_rank() over(partition by a.code order by seq desc) rn
   from tbl1 a
    , tbl2 b
   where a.code = b.code
   and b.status = '1'   
   and a.changecode = '603'
  )  
 where rn = 3
 ;

ㅇ
gb값을 쿼리에 추가했는데요..
603이 아닌 row만 조회한 후 처리되네요.
실제 union all의 아래 쿼리는 rn=3인 row가 조회되진 않네요..
흠.. 더 고민을 해야하는데.. 일에 치여서..



by 디케이 [2013.04.25 15:35:06] 
 
-- Oracle 11g의 LISTAGG 로 해봤어요..
SELECT CODE
   , regexp_substr(c_list, '[^,]+', 1, DECODE(INSTR(c_list, '603'), 1, 3, 1)) changeCode
   , regexp_substr(s_list, '[^,]+', 1, DECODE(INSTR(c_list, '603'), 1, 3, 1)) status
 FROM (SELECT A.CODE
      , LISTAGG(a.changeCode, ',') WITHIN GROUP(ORDER BY SEQ DESC) c_list
      , LISTAGG(a.status,   ',') WITHIN GROUP(ORDER BY SEQ DESC) s_list
     FROM TBL1 A
      , TBL2 B
    WHERE A.CODE = B.CODE
     AND B.STATUS = '1'
    GROUP BY A.CODE
   )
by 커피요쿠르트d [2013.04.25 16:37:26]



//디케이

눈이 호강을 했네요. ㅋ
실제 테스트 해볼 수 없어 아쉽네요. 좋은 쿼리 잘 봤습니다. 감사합니다!!
by 커피요쿠르트d [2013.04.25 16:35:16]
오.. 이렇게 다양한 방법들이.. ㅠㅠ
저는 왜 이런 생각이 안났을까요? 흑흑흑

모두 감사합니다~!!
by 이재현 [2013.04.26 09:41:02] 
--나만 별문제 아니라고 보는건가??;;
with tbl1 as ( 
select '0001' code, '1' seq, '131' changeCode, '메롱' status from dual union all
select '0001' code, '2' seq, '201' changeCode, '정상' status from dual union all 
select '0001' code, '3' seq, '202' changeCode, '정상' status from dual union all
select '0001' code, '4' seq, '603' changeCode, '메롱' status from dual union all
select '0002' code, '1' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '3' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '4' seq, '100' changeCode, '정상' status from dual union all
select '0003' code, '1' seq, '100' changeCode, '메롱' status from dual union all
select '0003' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0003' code, '3' seq, '100' changeCode, '정상' status from dual 
), tbl2 as ( 
select '0001' code, '1' status from dual union all
select '0002' code, '1' status from dual union all
select '0003' code, '3' status from dual 
) 
SELECT A.CODE, MIN(changeCode) AS changeCode, A.status
 FROM TBL1 A
  , TBL2 B
 WHERE A.CODE = B.CODE
 AND B.STATUS = '1'
 AND A.status = '정상'
GROUP BY A.CODE, A.STATUS
by 손님 [2013.04.26 13:54:05] 
 
with tbl1 as (
select '0001' code, '1' seq, '131' changeCode, '메롱' status from dual union all
select '0001' code, '2' seq, '201' changeCode, '정상' status from dual union all
select '0001' code, '3' seq, '202' changeCode, '정상' status from dual union all
select '0001' code, '4' seq, '603' changeCode, '메롱' status from dual union all
select '0002' code, '1' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '3' seq, '100' changeCode, '헤롱' status from dual union all
select '0002' code, '4' seq, '100' changeCode, '정상' status from dual union all
select '0003' code, '1' seq, '100' changeCode, '메롱' status from dual union all
select '0003' code, '2' seq, '100' changeCode, '헤롱' status from dual union all
select '0003' code, '3' seq, '100' changeCode, '정상' status from dual
), tbl2 as (
select '0001' code, '1' status from dual union all
select '0002' code, '1' status from dual union all
select '0003' code, '3' status from dual
)
Select * From (
select rn, code, changeCode, status, 
    Case When changeCode = '603' Then
        Lead(status,2) OVER(PARTITION BY code ORDER BY seq desc)
      Else
        status
    End status2
 from
(select a.code, changecode, a.status, seq,
    row_number() over (partition by a.code order by seq desc) as rn
  from tbl1 a, tbl2 b
 where a.code = b.code
  and b.status = '1' 
)   
)where rn = 1
;
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입