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

데이터 조회 질문입니다.

by 똑똑 [SQL Query] ORACLE SELECT [2021.03.09 10:28:06]

안녕하세요. 해당 쿼리에서 데이터 조회하는데 원하는 데이터가 조회가 되지 않아서 여쭤봅니다.
현재 데이터에서 조건을 0부터 ~ 365 사이의 데이터만(1년미만) 출력하고
조건이 365 ~ 729 의 데이터만(1년이상....) 출력을 하려고 합니다.
어떻게 조건을 주면은 좋을지 답변을 주시면 감사하겠습니다.
 
 
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', '001', '20021223', '20070930' FROM dual
UNION ALL SELECT 'AAA', '006', '20060615', '20060830' 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
UNION ALL SELECT 'CCC', '005', '20140401', '20140531' FROM dual
UNION ALL SELECT 'CCC', '005', '20140801', '20141130' FROM dual
UNION ALL SELECT 'CCC', '005', '20150101', '20151231' FROM dual
UNION ALL SELECT 'CCC', '005', '20160101', '20160331' FROM dual
UNION ALL SELECT 'CCC', '005', '20200302', '20200622' FROM dual
UNION ALL SELECT 'DDD', '002', '19990104', '19991130' FROM dual
UNION ALL SELECT 'DDD', '005', '20070702', '20080608' FROM dual
UNION ALL SELECT 'DDD', '005', '20081208', '20090630' FROM dual
UNION ALL SELECT 'DDD', '005', '20130701', '20140630' FROM dual
)
SELECT * from(
SELECT
cd,
                       SUM(DECODE(itsqf,'001', to_date(e) - to_date(s))) AA
                     , SUM(DECODE(itsqf,'002', to_date(e) - to_date(s))) AB
                     , SUM(DECODE(itsqf,'003', to_date(e) - to_date(s))) AC
                     , SUM(DECODE(itsqf,'004', to_date(e) - to_date(s))) AD
                     , SUM(DECODE(itsqf,'005', to_date(e) - to_date(s))) AE
                     , SUM(DECODE(itsqf,'006', to_date(e) - to_date(s))) AF
  FROM (SELECT cd, itsqf
                     , MIN(s_day) s
                     , MAX(e_day) 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
              )

by 마농 [2021.03.09 13:46:57]

1. 질문이 모호합니다.
- 원하는 데이터를 말로만 설명하지 마시고
- 결과표가 어떻게 나와야 하는지를 눈으로 보여주세요.
2. to_date(e) 는 잘못된 사용법입니다.
- to_date(e, 'yyyymmdd') 포멧을 지정해 주셔야 합니다.

by 똑똑 [2021.03.09 14:11:49]

CD	AA	AB	AC	AD	AE	AF
AAA	3,192		170	30	943	76
BBB	577	418
CCC					747
DDD		330			910

0~에서 365사이의 데이터만 출력하고자하는 결과표는 아래표와 같습니다.

사이값이 해당되는 컬럼만 출력이 되게 하려고합니다.

CD	AB	AC	AD	AF
AAA		170	30	76
DDD	330

365 ~ 729의 사이값을 가진 데이터가 조건이라면 아래표처럼 출력을 하고자 합니다.

CD	AA	AB
BBB	577	418

by 마농 [2021.03.09 15:00:32]

1 열은 가변으로 조정할 수 없습니다.
2. 일수계산시 +1 을 해줘야 합니다.
3. s_day, e_day 가 Date 타입이라고 가정하고 답변 드립니다.(이전질문 확인사항)

WITH tech AS
(
SELECT 'AAA' cd, '001' itsqf, TO_DATE('20130101', 'yyyymmdd') s_day, TO_DATE('20131231', 'yyyymmdd') e_day FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20130601', 'yyyymmdd'), TO_DATE('20160831', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20140101', 'yyyymmdd'), TO_DATE('20141231', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20140401', 'yyyymmdd'), TO_DATE('20140531', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20140801', 'yyyymmdd'), TO_DATE('20141130', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20150101', 'yyyymmdd'), TO_DATE('20151231', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20160101', 'yyyymmdd'), TO_DATE('20160331', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20200302', 'yyyymmdd'), TO_DATE('20200622', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '001', TO_DATE('20021223', 'yyyymmdd'), TO_DATE('20070930', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '006', TO_DATE('20060615', 'yyyymmdd'), TO_DATE('20060830', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '003', TO_DATE('20140613', 'yyyymmdd'), TO_DATE('20141130', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '004', TO_DATE('20161101', 'yyyymmdd'), TO_DATE('20161201', 'yyyymmdd') FROM dual
UNION ALL SELECT 'AAA', '005', TO_DATE('20161201', 'yyyymmdd'), TO_DATE('20190702', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '001', TO_DATE('20100201', 'yyyymmdd'), TO_DATE('20110131', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '001', TO_DATE('20100201', 'yyyymmdd'), TO_DATE('20110708', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '001', TO_DATE('20101220', 'yyyymmdd'), TO_DATE('20110901', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '001', TO_DATE('20100301', 'yyyymmdd'), TO_DATE('20110228', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '001', TO_DATE('20110101', 'yyyymmdd'), TO_DATE('20110901', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '002', TO_DATE('20080406', 'yyyymmdd'), TO_DATE('20080721', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '002', TO_DATE('20080912', 'yyyymmdd'), TO_DATE('20081111', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '002', TO_DATE('20081101', 'yyyymmdd'), TO_DATE('20090721', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '002', TO_DATE('20081117', 'yyyymmdd'), TO_DATE('20081230', 'yyyymmdd') FROM dual
UNION ALL SELECT 'BBB', '002', TO_DATE('20081117', 'yyyymmdd'), TO_DATE('20090721', 'yyyymmdd') FROM dual
UNION ALL SELECT 'CCC', '005', TO_DATE('20140401', 'yyyymmdd'), TO_DATE('20140531', 'yyyymmdd') FROM dual
UNION ALL SELECT 'CCC', '005', TO_DATE('20140801', 'yyyymmdd'), TO_DATE('20141130', 'yyyymmdd') FROM dual
UNION ALL SELECT 'CCC', '005', TO_DATE('20150101', 'yyyymmdd'), TO_DATE('20151231', 'yyyymmdd') FROM dual
UNION ALL SELECT 'CCC', '005', TO_DATE('20160101', 'yyyymmdd'), TO_DATE('20160331', 'yyyymmdd') FROM dual
UNION ALL SELECT 'CCC', '005', TO_DATE('20200302', 'yyyymmdd'), TO_DATE('20200622', 'yyyymmdd') FROM dual
UNION ALL SELECT 'DDD', '002', TO_DATE('19990104', 'yyyymmdd'), TO_DATE('19991130', 'yyyymmdd') FROM dual
UNION ALL SELECT 'DDD', '005', TO_DATE('20070702', 'yyyymmdd'), TO_DATE('20080608', 'yyyymmdd') FROM dual
UNION ALL SELECT 'DDD', '005', TO_DATE('20081208', 'yyyymmdd'), TO_DATE('20090630', 'yyyymmdd') FROM dual
UNION ALL SELECT 'DDD', '005', TO_DATE('20130701', 'yyyymmdd'), TO_DATE('20140630', 'yyyymmdd') FROM dual
)
SELECT cd
     , SUM(DECODE(itsqf, '001', days)) aa
     , SUM(DECODE(itsqf, '002', days)) ab
     , SUM(DECODE(itsqf, '003', days)) ac
     , SUM(DECODE(itsqf, '004', days)) ad
     , SUM(DECODE(itsqf, '005', days)) ae
     , SUM(DECODE(itsqf, '006', days)) af
  FROM (SELECT cd, itsqf
             , SUM(e - s + 1) days
          FROM (SELECT cd, itsqf
                     , MIN(s_day) s
                     , MAX(e_day) 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
--     HAVING SUM(e - s + 1) BETWEEN 365*0 AND 365*1-1
       HAVING SUM(e - s + 1) BETWEEN 365*1 AND 365*2-1
       )
 GROUP BY cd
 ORDER BY cd
;

by 똑똑 [2021.03.09 15:37:16]

감사합니다. 쿼리 적용해보았더니 결과가 잘 나왔습니다.

정말 감사합니다.!

댓글등록

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

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

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

데이터베이스 질문과 답변