안겹치는부분X겹치는 부분 하는방법? 0 2 527

by 멸치국수 [2018.03.13 11:34:38]


ID 구분숫자 시작일 종료일 구분코드
a 1 20170102 20180303 5 A
a 1 20170102 20170701 100 B
a 2 20170701 99991231 105 B
a 1 20170102 20170615 13 C
a 2 20170615 20171225 23 C
a 3 20171225 99991231 14 C

b

1 20180302 20181012 4 A

안녕하세요 db 예시는 저렇습니다.구분코드에서 날짜가 변하면 다른 계약으로 취급할려고 합니다.

 

원하는 형태는

ID 시작일 종료일 구분코드A값 구분코드B 값 구분코드C값  
a 20170102 20170615 5 100 13  
a 20170615 20170701 5 100 23  
a 20170701 20171225 5 105 23  
a 20171225 20180803 5 105 14  
b 20180302   4      
             
             

이런식으로 결과를 출력하려 합니다.. 설명이 미흡해보이지만 이해가 되신다면 답변 부탁드리겠습니다.

by 마농 [2018.03.13 13:11:12]
WITH t AS
(
SELECT 'a' id, 1 no, '20170102' sdt, '20180303' edt, 5 v, 'A' cd FROM dual
UNION ALL SELECT 'a', 1, '20170102', '20170701', 100, 'B' FROM dual
UNION ALL SELECT 'a', 2, '20170701', '99991231', 105, 'B' FROM dual
UNION ALL SELECT 'a', 1, '20170102', '20170615',  13, 'C' FROM dual
UNION ALL SELECT 'a', 2, '20170615', '20171225',  23, 'C' FROM dual
UNION ALL SELECT 'a', 3, '20171225', '99991231',  14, 'C' FROM dual
UNION ALL SELECT 'b', 1, '20180302', '20181012',   4, 'A' FROM dual
)
SELECT a.id, a.sdt, a.edt
     , MIN(DECODE(b.cd, 'A', b.v)) a
     , MIN(DECODE(b.cd, 'B', b.v)) b
     , MIN(DECODE(b.cd, 'C', b.v)) c
  FROM (SELECT id, sdt
             , LEAD(sdt) OVER(PARTITION BY id ORDER BY sdt) edt
          FROM (SELECT id, sdt, edt FROM t)
         UNPIVOT (sdt FOR gb IN (sdt, edt))
         GROUP BY id, sdt
        ) a
     , t b
 WHERE a.id  = b.id
   AND a.sdt < b.edt
   AND a.edt > b.sdt
 GROUP BY a.id, a.sdt, a.edt
 ORDER BY a.id, a.sdt
;
-- Result --
ID  SDT       EDT         A    B    C
a   20170102  20170615    5  100   13
a   20170615  20170701    5  100   23
a   20170701  20171225    5  105   23
a   20171225  20180303    5  105   14
a   20180303  99991231       105   14
b   20180302  20181012    4

 


by 우리집아찌 [2018.03.13 13:49:03]
WITH T (ID,구분숫자,시작일,종료일,값,구분코드 ) AS (
SELECT 'a','1','20170102','20180303','5','A'   FROM DUAL UNION ALL
SELECT 'a','1','20170102','20170701','100','B' FROM DUAL UNION ALL
SELECT 'a','2','20170701','99991231','105','B' FROM DUAL UNION ALL
SELECT 'a','1','20170102','20170615','13','C'  FROM DUAL UNION ALL
SELECT 'a','2','20170615','20171225','23','C'  FROM DUAL UNION ALL
SELECT 'a','3','20171225','99991231','14','C'  FROM DUAL UNION ALL
SELECT 'b','1','20180302','20181012','4','A'   FROM DUAL 
)
       
SELECT A.ID
     , 시작일
     , LEAST(종료일, NVL(LEAD(시작일,1) OVER(PARTITION BY ID  ORDER BY 시작일),종료일)) 종료일
     , LAST_VALUE(A) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) A
     , LAST_VALUE(B) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) B
     , LAST_VALUE(C) IGNORE NULLS OVER(PARTITION BY ID ORDER BY 시작일 ) C
 FROM( SELECT ID 
            , 시작일 
            , CASE WHEN MAX(종료일) = '99991231' THEN '1' ELSE MAX(종료일) END 종료일
            , MIN(CASE WHEN 구분코드 = 'A' THEN 값 END) A
            , MIN(CASE WHEN 구분코드 = 'B' THEN 값 END) B
            , MIN(CASE WHEN 구분코드 = 'C' THEN 값 END) C 
        FROM ( SELECT ID,구분숫자,시작일,MAX(CASE WHEN 종료일 != '99991231' THEN 종료일 END ) OVER(PARTITION BY ID) 종료일,값,구분코드 FROM T ) A
       GROUP BY ID , 시작일 
     ) A
     
-- RESULT
ID 시작일      종료일      A   B   C  
- -------- -------- --- --- ---
a 20170102 20170615 5   100 13 
a 20170615 20170701 5   100 23 
a 20170701 20171225 5   105 23 
a 20171225 20180303 5   105 14 
b 20180302 20181012 4          

     

 

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