[oracle]쿼리 질문드립니다. 0 6 341

by 똑똑 [SQL Query] ORACLE GROUP BY SUM 날짜계산 [2021.01.07 13:54:22]


안녕하세요.

아이디와 시작날짜 끝 날짜를 가진 테이블과 001~부터 030까지의 코드 테이블이 따로 있으며,

해당 아이디가 코드목록을 가지고있고 해당하는 시작날짜와 끝나는 날짜를 더하여서 총 기간을 구하고 싶은데요. 

두 테이블을 그룹함수를 이용해서 SUM(DECODE를 사용해봤는데 날짜를 구해오는데 있어 어려움이 있습니다.
도움을 받고자 문의 드립니다.

아이디 코드 시작날짜 끝 날짜
1 001 2019-11-07 2020-06-29
1 001 2018-12-06 2019-04-19
1 002 2014-04-02 2018-11-30
1 002 1999-02-05 2007-09-03
2 001 2014-04-10 2020-11-23
2 001 2008-03-15 2011-02-27
2 002 1997-05-01 2007-12-31
3 003 2016-12-01 2019-07-02
3 004 2013-06-01 2016-08-31

표의 날짜는 임의로 값을 넣은 값입니다.

 

1 001 N년 N개월 N일
1 002 N년 N개월 N일
2 001 N년 N개월 N일
2 002 N년 N개월 N일
3 003 N년 N개월 N일
3 004 N년 N개월 N일

답변 꼭 부탁드리겠습니다. 감사합니다.

by 웅아 [2021.01.07 14:01:06]

해당 아이디의 MIN(시작날짜) MAX(끝날짜) 를 구하고

두개의 날짜로 db내 날짜 펑션을 이용하여 총 기간을 구하시면 됩니다.


by 똑똑 [2021.01.07 17:45:07]

답변 주셔서 감사합니다.

답변 주신 방법으로는 시도해보았지만 쿼리에 미숙한 탓에 function을 이용하지는 못했습니다.

새로운 방법을 알려주신 점에 대해 정말 감사드립니다.


by pajama [2021.01.07 14:55:03]

이런식일까요? 일수를 년월일로 바꾸면 될듯합니다~

with t as (
select 1 id, '001' code, '2019-11-07' sdt, '2020-06-29' edt from dual union all
select 1, '001', '2018-12-06', '2019-04-19' from dual union all
select 1, '002', '2014-04-02', '2018-11-30' from dual union all
select 1, '002', '1999-02-05', '2007-09-03' from dual union all
select 2, '001', '2014-04-10', '2020-11-23' from dual union all
select 2, '001', '2008-03-15', '2011-02-27' from dual union all
select 2, '002', '1997-05-01', '2007-12-31' from dual union all
select 3, '003', '2016-12-01', '2019-07-02' from dual union all
select 3, '004', '2013-06-01', '2016-08-31' from dual
)
select id, code, sum (to_date(edt) - to_date(sdt) + 1) days from t
group by id, code
order by id, code
;

 

 

 


by 똑똑 [2021.01.07 17:46:44]

답변 주신 점을 활용하여 문제를 해결 할 수 있었습니다.

너무 어렵게만 생각했던 탓에 많이 돌아왔던 것 같습니다.

다시 한 번 감사드립니다.


by 마농 [2021.01.07 16:27:51]

일수나 월수는 다음과 같이 구할 수 있습니다.
- 일수 : edt - sdt + 1
- 월수 : MONTHS_BETWEEN(edt + 1, sdt)
일수의 경우는 딱 맞아 떨어질 것이고
월수의 경우는 딱 맞아 떨어지지 않아 논란의 소지는 있습니다.
이렇게 구한 일수나 월수를 SUM 한 뒤 00년00개월00일 형태로 변환할 수 있는데.
어떤 기준을 가지고 변환할지 전략을 잘 세워서 로직을 구상하셔야 합니다.
어차피 변환하면서 다시 또 딱 맞아 떨어지지 않게 됩니다.
월마다 일수가 다르고 윤년, 윤달등이 존재하여 안맞는게 정상입니다.
근사치를 구한다고 생각하세요.

WITH t AS
(
SELECT 1 id, '001' cd, date '2019-11-07' sdt, date '2020-06-29' edt FROM dual
UNION ALL SELECT 1, '001', date '2018-12-06', date '2019-04-19' FROM dual
UNION ALL SELECT 1, '002', date '2014-04-02', date '2018-11-30' FROM dual
UNION ALL SELECT 1, '002', date '1999-02-05', date '2007-09-03' FROM dual
UNION ALL SELECT 2, '001', date '2014-04-10', date '2020-11-23' FROM dual
UNION ALL SELECT 2, '001', date '2008-03-15', date '2011-02-27' FROM dual
UNION ALL SELECT 2, '002', date '1997-05-01', date '2007-12-31' FROM dual
UNION ALL SELECT 3, '003', date '2016-12-01', date '2019-07-02' FROM dual
UNION ALL SELECT 3, '004', date '2013-06-01', date '2016-08-31' FROM dual
)
SELECT id, cd
     , d
     , ROUND(m, 5) m
       -- 개월수를 이용한 변환 예시 --
     , LPAD(FLOOR(m / 12)          , 2, '0') || '년'   ||
       LPAD(MOD(FLOOR(m), 12)      , 2, '0') || '개월' ||
       LPAD(ROUND(MOD(m, 1) * 30.4), 2, '0') || '일'   AS 기간
  FROM (SELECT id, cd
             , SUM(edt - sdt + 1) d                 -- 일수
             , SUM(MONTHS_BETWEEN(edt + 1, sdt)) m  -- 개월수
          FROM t
         GROUP BY id, cd
         ORDER BY id, cd
        )
;

 


by 똑똑 [2021.01.07 17:48:32]

그렇군요 저는 정확한 값만 구하려다 보니 자꾸 되돌아가기만 했는데

윤년과 윤달을 미쳐 생각하지 못하고 계속 끙끙 앓았네요..

답변해주셔서 정말 감사드립니다!

답변 주신 방법으로도 구현해 보도록 하겠습니다!

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