날짜관련 쿼리 좀 도와주세요~ 0 4 713

by 산달 [SQL Query] [2017.02.21 10:03:49]


일반적인 날짜 중복제외 쿼리로는 도저히 결과값이 나오지 않아 도움을 요청합니다.

연번이 같은 경우는 날짜 중복 인정 계산,

다를 경우는 연번이 작은 것 부터 날짜를 중복 인정하면서 이전의 모든 날짜와의 중복은 제외

연번 시작일자   종료일자

1    20160101    20160229
1    20160101    20160229
3    20160801    20161231
4    20160101    20161231

정렬은 구분, 시작일자

결과는

1 = 4개월

3 = 5개월

4 = 5개월

그럼 감사드리며 수고하세요~

by jkson [2017.02.21 11:21:20]

시작 종료일자는 월단위라 보고 round처리 했습니다.

--윈도우함수 사용
with t(cd, sdt, edt) as
(
select '1', '20160101', '20160331' from dual union all
select '1', '20160201', '20160430' from dual union all
select '1', '20160601', '20160930' from dual union all
select '3', '20160801', '20161231' from dual union all
select '4', '20160101', '20161231' from dual
)
select cd, sum(months) months
  from
    (
    select cd, gb, round(months_between(to_date(max(edt),'yyyymmdd') , to_date(min(sdt),'yyyymmdd'))) months
      from
        (  
        select cd, sdt, edt, sum(changed) over(partition by cd order by sdt, edt) gb 
          from
            (
            select cd, sdt, edt
                 , case when max(edt) over(partition by cd 
                                           order by sdt, edt 
                                           rows between unbounded preceding and 1 preceding) >= sdt 
                             then 0 else 1 
                        end  changed 
              from t
            )
        )
      group by cd, gb
    )
 group by cd
 
--재귀쿼리 사용
with t(cd, sdt, edt) as
(
select '1', '20160101', '20160331' from dual union all
select '1', '20160201', '20160430' from dual union all
select '1', '20160601', '20160930' from dual union all
select '3', '20160801', '20161231' from dual union all
select '4', '20160101', '20161231' from dual
)
, t2(cd, sdt, edt) as
(
select cd, to_date(sdt,'yyyymmdd'), to_date(edt,'yyyymmdd')
  from t
union all
select cd, sdt + 1, edt
  from t2
 where sdt + 1 <= edt
)
select cd, count(distinct to_char(sdt,'yyyymm')) months  from t2
group by cd

--계층쿼리 사용 
select cd, sum(months) months
  from
    (  
    select max(cd) cd, round(months_between(max(edt), min(sdt))) months
      from
        (
        select connect_by_root(cd||sdt||edt) root, cd, sdt, edt
          from t a
         start with not exists (select 1 
                                  from t b
                                 where b.cd = a.cd
                                   and b.sdt != a.sdt
                                   and b.edt != a.edt
                                   and b.sdt <= a.sdt
                                   and b.edt >= a.sdt)
         connect by nocycle prior cd = cd
                        and prior sdt != sdt
                        and prior edt != edt
                        and prior sdt <= edt
                        and prior edt >= sdt
        )
     group by root
    )
 group by cd 

 


by 마농 [2017.02.21 11:33:23]
WITH t AS
(
SELECT 1 no, '20160101' sdt, '20160229' edt FROM dual
UNION ALL SELECT 1, '20160101', '20160229' FROM dual
UNION ALL SELECT 1, '20160601', '20160930' FROM dual -- 추가 테스트
--UNION ALL SELECT 3, '20160801', '20161231' FROM dual
UNION ALL SELECT 3, '20160801', '20161130' FROM dual -- 변경 테스트
UNION ALL SELECT 4, '20160101', '20161231' FROM dual
)
SELECT no
     , SUM(mm) mm
  FROM (SELECT no
             , MONTHS_BETWEEN(MAX(dt) + 1, MIN(dt)) * cnt mm
          FROM (SELECT MIN(no) no
                     , sdt + lv - 1 dt
                     , COUNT(*) KEEP(DENSE_RANK FIRST ORDER BY no) cnt
                     , ROW_NUMBER() OVER(PARTITION BY MIN(no) ORDER BY sdt + lv - 1) rn
                  FROM (SELECT no
                             , TO_DATE(sdt, 'yyyymmdd') sdt
                             , TO_DATE(edt, 'yyyymmdd') edt
                          FROM t
                        )
                     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999)
                 WHERE lv <= edt - sdt + 1
                 GROUP BY sdt + lv - 1
                )
         GROUP BY no, cnt, dt - rn
        )
 GROUP BY no
 ORDER BY no
;

 


by 랑에1 [2017.02.21 11:38:19]

WITH T(연번,시작일자,종료일자) AS (
SELECT 1, TO_DATE('20160101','yyyymmdd'), TO_DATE('20160229','yyyymmdd') FROM dual UNION ALL
SELECT 1, TO_DATE('20160101','yyyymmdd'), TO_DATE('20160229','yyyymmdd') FROM dual UNION ALL
SELECT 3, TO_DATE('20160801','yyyymmdd'), TO_DATE('20161231','yyyymmdd') FROM dual UNION ALL
SELECT 4, TO_DATE('20160101','yyyymmdd'), TO_DATE('20161231','yyyymmdd') FROM dual
)


SELECT 연번, SUM(cnt) 날짜합계
FROM 
(
  SELECT dt, 연번, cnt
  FROM 
  (
    SELECT dt, 연번, cnt, ROW_NUMBER() OVER(PARTITION BY dt ORDER BY 연번) rn
    FROM 
    (
      SELECT dt, 연번, COUNT(*) cnt
      FROM 
      (
        SELECT 연번, 시작일자 + lv - 1 dt
        FROM T, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 1000)
        WHERE lv <= 종료일자 - 시작일자 + 1
      )
      GROUP BY dt, 연번
    )
  )
  WHERE rn = 1  
)  
GROUP BY 연번
ORDER BY 연번

모법답안은 위에 있지만..
날짜 갯수 세는걸로 해봤습니다.

 


by 산달 [2017.02.21 13:15:40]

고수님들 대단히 감사드립니다.~

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