데이터베이스 질문과 답변

일부 데이터만 PIVOT으로 만들어 붙이는 쿼리좀.. 0 4 2,442

by 창조의날개 [2015.06.09 11:19:06]

 

여러가지로 생각을 하다가 좋은 방법을 알고 계시는 분들이 있을거 같아..

질문을 드립니다.

 

원본데이터


WITH TT(TERM_ID,UP_TERM_ID,TERM_NM,TERM_CONT_TYPE_CD,TERM_CONT) AS (
    SELECT 'TM001', 'TOP', '정보수집및이용동의', '100', 'NULL' FROM DUAL UNION ALL
    SELECT 'TM002', 'TM001', '모든 채널 수신에 동의합니다.', '100', 'NULL' FROM DUAL UNION ALL
    SELECT 'TM003', 'TM002', 'E-MAIL', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM004', 'TM002', 'SMS', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM005', 'TM002', 'TM', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM006', 'TM002', 'DM', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM007', 'TM001', '개인정보 이용 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM008', 'TM001', '고유식별정보 처리 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM009', 'TM001', '통신사 이용약관 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM010', 'TM001', '서비스 이용약관 동의(필수)', '200', '내용' FROM DUAL
)
SELECT *
FROM TT
;

 

출력형태

LV TERM_ID UP_TERM_ID TERM_NM TERM_CONT_TYPE_CD TERM_CONT E-MAIL SMS TM DM
1 TM001 TOP 정보수집및이용동의 100 　 0 0 0 0
2 TM002 TM001 모든 채널 수신에 동의합니다. 100 　 1 1 1 1
2 TM007 TM001 개인정보 이용 동의(필수) 200 내용 0 0 0 0
2 TM008 TM001 고유식별정보 처리 동의(필수) 200 내용 0 0 0 0
2 TM009 TM001 통신사 이용약관 동의(필수) 200 내용 0 0 0 0
2 TM010 TM001 서비스 이용약관 동의(필수) 200 내용 0 0 0 0

 

설명

원본데이터와 같이 제귀호출 형태의 데이터가 있습니다.

데이터중 TERM_CONT_TYPE_CD가 100 이면서 계층구조의 최하위 레벨로 들어가게 되는

E-MAIL, SMS, TM, DM 값을 PIVOT으로 붙여 주고 싶습니다.

해당 데이터가 없는 경우는 0으로 찍어 줍니다.

 

 

감사합니다.

 

 

 

by DarkBee [2015.06.09 13:06:47] 
 SELECT MIN ( term_id    )                         term_id
      , MIN ( up_term_id )                         up_term_id
      , MIN ( DECODE ( LEVEL, 3, NULL, term_nm ) ) term_nm
      , MIN ( term_cont_type_cd )                  term_cont_type_cd
      , MIN ( term_cont         )                  term_cont
      , COUNT ( DECODE ( term_nm, 'E-MAIL', 1 ) )  email
      , COUNT ( DECODE ( term_nm, 'SMS'   , 1 ) )  sms
      , COUNT ( DECODE ( term_nm, 'TM'    , 1 ) )  tm
      , COUNT ( DECODE ( term_nm, 'DM'    , 1 ) )  dm
   FROM tt a
  START WITH term_cont_type_cd = '100'
         AND up_term_id        = 'TOP'
CONNECT BY PRIOR term_id       = up_term_id
  GROUP BY DECODE ( LEVEL, 3, PRIOR term_id, term_id ) 
  ORDER BY term_id

 

by 창조의날개 [2015.06.09 14:55:03]

DarkBee님의 좋은 의견으로 마무리 했습니다.

다만 계층구조의 최하위 레벨이 3레벨이 아닐 수 있기 때문에 아래와 같이 바꿔봤습니다.

다시 한번 감사합니다.

 

WITH TT(TERM_ID,UP_TERM_ID,TERM_NM,TERM_CONT_TYPE_CD,TERM_CONT) AS (
    SELECT 'TM001', 'TOP', '정보수집및이용동의', '100', NULL FROM DUAL UNION ALL
    SELECT 'TM002', 'TM001', '모든 채널 수신에 동의합니다.', '100', NULL FROM DUAL UNION ALL
    SELECT 'TM003', 'TM002', 'E-MAIL', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM004', 'TM002', 'SMS', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM005', 'TM002', 'TM', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM006', 'TM002', 'DM', '100', '1' FROM DUAL UNION ALL
    SELECT 'TM007', 'TM001', '개인정보 이용 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM008', 'TM001', '고유식별정보 처리 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM009', 'TM001', '통신사 이용약관 동의(필수)', '200', '내용' FROM DUAL UNION ALL
    SELECT 'TM010', 'TM001', '서비스 이용약관 동의(필수)', '200', '내용' FROM DUAL
)
SELECT  MIN ( term_id    )                         term_id
      , MIN ( up_term_id )                         up_term_id
      , MAX (  term_nm   )                         term_nm
      , MAX ( term_cont_type_cd )                  term_cont_type_cd
      , MAX ( term_cont         )                  term_cont
      , COUNT ( DECODE ( term_nm, 'E-MAIL', 1 ) )  email
      , COUNT ( DECODE ( term_nm, 'SMS'   , 1 ) )  sms
      , COUNT ( DECODE ( term_nm, 'TM'    , 1 ) )  tm
      , COUNT ( DECODE ( term_nm, 'DM'    , 1 ) )  dm
   FROM tt a
  START WITH up_term_id  = 'TOP'
CONNECT BY PRIOR term_id = up_term_id
  GROUP BY DECODE(CONNECT_BY_ISLEAF,1,DECODE(TERM_CONT_TYPE_CD,'100',UP_TERM_ID,TERM_ID),TERM_ID)
  ORDER BY term_id
;

 

by 마농 [2015.06.09 15:24:22]

Group By 에서 기준이 없이 사용된 MIN, MAX 로는 정확하지 않은 자료가 추출될 가능성이 있습니다.

by 창조의날개 [2015.06.09 15:52:55]

역시 마농님 정확한 부분을 지적하였네요..

그 부분때문에 데이터 입력시 E-MAIL, SMS, TM, DM등에 대한 다른 필드에는 데이터를 부모와 같은 데이터를 입력 하기로 했습니다.

꼼꼼하게 챙겨 주셔서 감사합니다.

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