꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 질문 드립니다.

by 만세 [SQL Query] 쿼리질문 [2013.11.29 13:11:29]

안녕하세요.
쿼리 작성에 어려움이 있어서 질문 드립니다. 고수님들의 많은 관심 부탁드립니다.
오라클 버젼은 10g 입니다.

아래 temp 테이블에서 아래의 결과를 얻고 싶습니다.

with temp as(
 SELECT 1 as SEQ, 1 as ORDER_NUM, 'james' as A_ID, 'ibatis' as B_ID, '' as C_ID, '1' as GUB_CD, '0' as TP_CD from dual union all
 SELECT 2, 2, 'jhone', 'ibatis', '', '1', '0' from dual union all
 SELECT 3, 3, 'shally', 'ibatis', '', '1', '0' from dual union all
 SELECT 4, 1, 'smith', 'oracleLove', '', '1', '1' from dual union all
 SELECT 5, 2, 'michael', 'oracleLove', '', '1', '1' from dual union all
 SELECT 6, 1, 'obama', 'start1', '', '1', '2' from dual union all
 SELECT 7, 2, 'oldman', 'start1', '', '1', '2' from dual union all
 SELECT 8, 3, 'youngman', 'start1', '', '1', '2' from dual union all
 SELECT 9, 4, 'spring', 'start1', '', '1', '2' from dual union all
 SELECT 10, 1, 'james', 'javadev', '', '1', '0' from dual union all
 SELECT 11, 2, 'jhone', 'javadev', '', '1', '0' from dual union all
 SELECT 12, 3, 'shally', 'javadev', 'uncletom', '1', '0' from dual union all
 SELECT 13, 4, 'lima', 'javadev', '', '1', '0' from dual
)

결과예시 입니다.

조회조건: A_ID = shally
 B_ID GUB_CD TP_CD count_ordernum preA_ID C_ID LAST_YN SEQ ORDER_NUM
========================================================================================
 ibatis  1 0  3 jhone NULL  Y 3  3
 javadev  1 0  4 jhone uncletom  N 12  3

조회 조건: A_ID = jhone
 B_ID GUB_CD TP_CD count_ordernum preA_ID C_ID LAST_YN SEQ ORDER_NUM
=======================================================================================
 ibatis  1 0  3 james NULL   N 2  2
 javadev  1 0  4 james NULL  N 11  2

조회조건: A_ID = michael
 B_ID GUB_CD TP_CD count_ordernum preA_ID C_ID LAST_YN SEQ ORDER_NUM
========================================================================================
 oracleLove 1 1  2 smith NULL   Y 5  2

조회조건: A_ID = obama
 B_ID GUB_CD TP_CD count_ordernum preA_ID C_ID LAST_YN SEQ ORDER_NUM
========================================================================================
start1  1 2  4 NULL NULL    N 6  1

설명을 좀 드리자면 A_ID 조건에 해당하는 B_ID, GUB_CD, TP_CD 별 row갯수(count_ordernum)와 ORDER_NUM 상 하나전 A_ID(preA_ID), 그리고 C_ID 및 ORDER_NUM상 마지막인 경우 LAST_YN 'Y' 아닌 경우 'N'를 표시 하고 싶습니다.
SEQ와 ORDER_NUM도 포함해서요

머리싸매고 고민해도 답이 안나와서 질문드립니다. 감사합니다.

by 아발란체 [2013.11.29 13:52:27]

--결과만 나오게 했습니다 ~ :)
--참고만 하시면 될 것 같습니다.
SELECT
  b_id, gub_cd, tp_cd,
  last_order,
  prra_id,
  c_id,
  DECODE(last_order, MAX(count_order) OVER(), 'Y', 'N') AS last_yn,
  seq,
  order_num
FROM (
  SELECT
    b_id, gub_cd, tp_cd, a_id, c_id, seq, order_num,
    LAG(a_id) OVER(PARTITION BY b_id ORDER BY SEQ) AS prra_id,
    ROW_NUMBER() OVER(PARTITION BY b_id ORDER BY b_id asc) AS count_order,
    MAX(ORDER_NUM) OVER(PARTITION BY b_id) AS last_order
  FROM
    TEMP
)
WHERE
  a_id = 'shally'

by 만세 [2013.11.29 14:35:44]

아빌란체님 정말 감사합니다. 원하던 결과가 나오네요. ^^

by 사랑초 [2013.11.29 14:03:09]

음? 설명 주신 내용에 대해서 좀 보충해주셔야 할 것 같은데...

결과값이라고 보여주신 값을 따라야 하는지 설명해 주신 내용을 따라야 할지

예를 들자면 B_ID 값이 start1 일 경우 아래 SQL문으로 풀 수 있는데 NULL이 아니라 1로 나와야 할것 같은데요..

select b_id, gub_cd, tp_cd, count(*) as cnt from temp

WHERE A_ID = 'obama'

group by b_id, gub_cd, tp_cd;

ORDER_NUM 상 하나전 A_ID 라고 하심은 A_ID가 1일 경우 예시값과 설명이 일치하지 않아
다소 이해가 되지 않고;

음.... 조금 더 보충이나 설명을 수정해 주셔야 할 것 같아보이는데...

제가 잘못 짚은 부분이 있으면 말씀 부탁드려요~

by 만세 [2013.11.29 14:31:32]

관심 가져주셔서 감사합니다.
제가 글 제주가 없어서 설명이 좀 부족한것 같습니다.
조회 조건으로 A_ID 값을 사용하여
각 조건값을 주었을때 제가 보여드린 결과 예시들 처럼 나오기를 바라는 것 입니다.
ORDER_NUM 상 하나전 A_ID라는 말의 뜻은 예를 들어 조회 조건에 A_ID가 shally인 것을 조회 하면 B_ID, GUB_CD, TP_CD 별 A_ID가 shally인 row가 두개 나올텐데요. 결과중 count_ordernum은 B_ID, GUB_CD, TP_CD 별 A_ID중 shally를 포함하는 row의 갯수를 뜻합니다.
이때 각 row의 shally의 ORDER_NUM을 보면 3, 3 으로 둘다 3이구요
B_ID, GUB_CD, TP_CD 별 ORDER_NUM이 2인 ROW의 A_ID를 표시 하고 자 함이었습니다.
하~~ 추가 설명이 더 복잡하네요. 이해 안돼시는게 당연하겠습니다. 죄송합니다.
아빌란체님이 제가 원하는 결과의 쿼리를 작성해 주신것 같습니다.
감사합니다.

by 마농 [2013.11.29 17:05:47]

SELECT *
  FROM (SELECT a_id
             , b_id
             , gub_cd
             , tp_cd
             , COUNT(*) OVER(PARTITION BY b_id) count_ordernum
             , LAG(a_id) OVER(PARTITION BY b_id ORDER BY order_num) prea_id
             , c_id
             , DECODE(order_num, MAX(order_num) OVER(PARTITION BY b_id), 'Y', 'N') last_yn
             , seq
             , order_num
          FROM temp
        )
 WHERE a_id = 'shally'
;

by 만세 [2013.11.30 10:49:08]

마농님 정말 감사합니다. ^^
작성해 주신 쿼리 찬찬히 뜯어 보며 연구해 보겠습니다.
아빌란체님 것도요.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변