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

쿼리 질문입니다.

by 똑똑 [SQL Query] [2021.03.02 15:09:34]

안녕하세요. 
itsqf의 001이란 코드에서 시작 날짜와 끝 날짜 중 날짜가 겹치는 기간은 제외하고 일수를 구하는 쿼리를 작성하고자 합니다.
itsqf의 001과 001의 날짜가 겹쳤을 때는 그대로 년차를 구하지만
itsqf의 코드가 같고 겹치는 날짜 제외하고 겹치지 않는 날짜들로만 총 날짜를 구하고자 합니다. 

WITH tech AS
(
SELECT 'AAA' cd, '001' itsqf, '20130101' s_day, '20131231' e_day FROM dual 
UNION ALL SELECT 'AAA', '001', '20130601', '20160831' FROM dual
UNION ALL SELECT 'AAA', '001', '20140101', '20141231' FROM dual 
UNION ALL SELECT 'AAA', '001', '20140401', '20140531' FROM dual 
UNION ALL SELECT 'AAA', '001', '20140801', '20141130' FROM dual 
UNION ALL SELECT 'AAA', '001', '20150101', '20151231' FROM dual 
UNION ALL SELECT 'AAA', '001', '20160101', '20160331' FROM dual 
UNION ALL SELECT 'AAA', '001', '20200302', '20200622' FROM dual 
UNION ALL SELECT 'AAA', '003', '20140613', '20141130' FROM dual 
UNION ALL SELECT 'AAA', '004', '20161101', '20161201' FROM dual 
UNION ALL SELECT 'AAA', '005', '20161201', '20190702' FROM dual 
UNION ALL SELECT 'BBB', '001', '20100201', '20110131' FROM dual
UNION ALL SELECT 'BBB', '001', '20100201', '20110708' FROM dual
UNION ALL SELECT 'BBB', '001', '20101220', '20110901' FROM dual
UNION ALL SELECT 'BBB', '001', '20100301', '20110228' FROM dual
UNION ALL SELECT 'BBB', '001', '20110101', '20110901' FROM dual
UNION ALL SELECT 'BBB', '002', '20080406', '20080721' FROM dual 
UNION ALL SELECT 'BBB', '002', '20080912', '20081111' FROM dual
UNION ALL SELECT 'BBB', '002', '20081101', '20090721' FROM dual
UNION ALL SELECT 'BBB', '002', '20081117', '20081230' FROM dual
UNION ALL SELECT 'BBB', '002', '20081117', '20090721' FROM dual
)
SELECT NVL(a.y_nm, '합계') y_nm
     , COUNT(DECODE(b.itsqf, '001', 1)) "001"
     , COUNT(DECODE(b.itsqf, '002', 1)) "002"
     , COUNT(DECODE(b.itsqf, '003', 1)) "003"
     , COUNT(DECODE(b.itsqf, '004', 1)) "004"
     , COUNT(DECODE(b.itsqf, '005', 1)) "005"
  FROM (SELECT LEVEL - 1 y
             , CASE WHEN LEVEL = 1 THEN '1년 미만'
                    WHEN LEVEL = 6 THEN '5년 이상'
                    ELSE (LEVEL-1) || '년' END y_nm
          FROM dual
         CONNECT BY LEVEL <= 6
        ) a
     , (SELECT cd
             , itsqf
             , LEAST(5, FLOOR(SUM(TO_DATE(e_day, 'yyyymmdd') - TO_DATE(s_day, 'yyyymmdd') + 1) / 365)) y
          FROM tech
         GROUP BY cd, itsqf    
        ) b
 WHERE a.y = b.y(+)
 GROUP BY ROLLUP((a.y, a.y_nm))
 ORDER BY a.y
;

현재 쿼리에서 다른 글들을 참고해서 작성을 해보았으나, 작성이 쉽지가 않아서 도움을 요청하게 되었습니다.

답변주시면 감사하겠습니다.

by 마농 [2021.03.02 16:42:13]

WITH tech AS
(
SELECT 'AAA' cd, '001' itsqf, '20130101' s_day, '20131231' e_day FROM dual
UNION ALL SELECT 'AAA', '001', '20130601', '20160831' FROM dual
UNION ALL SELECT 'AAA', '001', '20140101', '20141231' FROM dual
UNION ALL SELECT 'AAA', '001', '20140401', '20140531' FROM dual
UNION ALL SELECT 'AAA', '001', '20140801', '20141130' FROM dual
UNION ALL SELECT 'AAA', '001', '20150101', '20151231' FROM dual
UNION ALL SELECT 'AAA', '001', '20160101', '20160331' FROM dual
UNION ALL SELECT 'AAA', '001', '20200302', '20200622' FROM dual
UNION ALL SELECT 'AAA', '003', '20140613', '20141130' FROM dual
UNION ALL SELECT 'AAA', '004', '20161101', '20161201' FROM dual
UNION ALL SELECT 'AAA', '005', '20161201', '20190702' FROM dual
UNION ALL SELECT 'BBB', '001', '20100201', '20110131' FROM dual
UNION ALL SELECT 'BBB', '001', '20100201', '20110708' FROM dual
UNION ALL SELECT 'BBB', '001', '20101220', '20110901' FROM dual
UNION ALL SELECT 'BBB', '001', '20100301', '20110228' FROM dual
UNION ALL SELECT 'BBB', '001', '20110101', '20110901' FROM dual
UNION ALL SELECT 'BBB', '002', '20080406', '20080721' FROM dual
UNION ALL SELECT 'BBB', '002', '20080912', '20081111' FROM dual
UNION ALL SELECT 'BBB', '002', '20081101', '20090721' FROM dual
UNION ALL SELECT 'BBB', '002', '20081117', '20081230' FROM dual
UNION ALL SELECT 'BBB', '002', '20081117', '20090721' FROM dual
)
SELECT NVL(a.y_nm, '합계') y_nm
     , COUNT(DECODE(b.itsqf, '001', 1)) "001"
     , COUNT(DECODE(b.itsqf, '002', 1)) "002"
     , COUNT(DECODE(b.itsqf, '003', 1)) "003"
     , COUNT(DECODE(b.itsqf, '004', 1)) "004"
     , COUNT(DECODE(b.itsqf, '005', 1)) "005"
  FROM (SELECT LEVEL - 1 y
             , CASE WHEN LEVEL = 1 THEN '1년 미만'
                    WHEN LEVEL = 6 THEN '5년 이상'
                    ELSE (LEVEL-1) || '년' END y_nm
          FROM dual
         CONNECT BY LEVEL <= 6
        ) a
     , (SELECT cd, itsqf
             , LEAST(5, FLOOR(SUM(e - s + 1) / 365)) y
          FROM (SELECT cd, itsqf
                     , TO_DATE(MIN(s_day), 'yyyymmdd') s
                     , TO_DATE(MAX(e_day), 'yyyymmdd') e
                  FROM (SELECT cd, itsqf, s_day, e_day
                             , SUM(flag) OVER(PARTITION BY cd, itsqf ORDER BY s_day, e_day) grp
                          FROM (SELECT cd, itsqf, s_day, e_day
                                     , CASE
                                       WHEN s_day
                                         <= MAX(e_day) OVER(PARTITION BY cd, itsqf ORDER BY s_day, e_day
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                                       THEN 0 ELSE 1 END flag
                                  FROM tech
                                )
                        )
                 GROUP BY cd, itsqf, grp
                )
         GROUP BY cd, itsqf
        ) b
 WHERE a.y = b.y(+)
 GROUP BY ROLLUP((a.y, a.y_nm))
 ORDER BY a.y
;

쿼리 설명
1. flag : 연속 여부 구하기(연속 0, 불연속 1), 이전 종료일이 시작일보다 크면 불연속
2. grp : flag 를 순차적으로 누적합산하여 구함, 연속인 경우 같은 그룹 번호 생성
3. s ~ e : 연속된 기간을 하나로 그룹핑, GROUP BY cd, itsqf, grp
4. 이후 집계 과정은 질문 쿼리와 동일.

by 똑똑 [2021.03.02 19:07:53]

답변 정말 감사드립니다.

제가 작업 하던 쿼리에 적용을 했을 때 TO_DATE 지점에서 문제가 발생을 합니다.

숫자가 아닌 문자가 발견됐다는 오류문이 납니다.

TO_CHAR로 변경해서 실행을 해보니 결과 값은 다르게 나왔습니다.. 어디가 문제인지 찾는 중 입니다...

답변 정말 감사드립니다.

by 마농 [2021.03.03 08:33:37]

s_day, e_day 의 자료형이 뭔가요?
- Date 라면? to_date 가 필요 없습니다.
- varchar2 라면? 날짜포멧 확인 및 오류 자료 확인이 필요합니다.

by 똑똑 [2021.03.03 10:11:27]

to_date를 지우고 실행해보니 잘 되었습니다.

정말 감사합니다!

댓글등록

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

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

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

데이터베이스 질문과 답변