데이터베이스 질문과 답변
각각의 레코드의 구성이 ( ID / 시작일 / 종료일 / 기간(종료일 - 시작일)) 으로 된 테이블이 있습니다.
여기서 시작일과 종료일이 중복된 데이터는 제외된 순수 일자만 계산하여야 하는 문제가 발생했습니다.
즉, 아래와 같은 레코드가 있다고 가정하면,
1 / 2020-08-01 / 2020-08-02 / 2
2 / 2020-08-02 / 2020-08-05 / 4
3 / 2020-08-10 / 2020-08-15 / 6
순수 일수를 더하면 (12일)지만 1번 항목의 종료일과 2번항목의 시작일(2020-08-02)이 겹치므로 12 - 1인 (11일)의 값이 되어야 합니다.
이를 MySQL에서 어떻게 계산하여야 할 지 도무지 모르겠네요. 도움주세요~~
WITH TAB1 AS(
SELECT 1 EVENT_NO, '2020-08-01' EVENT_SDT, '2020-08-02' EVENT_EDT, 2 VAL FROM DUAL UNION ALL
SELECT 2, '2020-08-02' , '2020-08-05' , 4 FROM DUAL UNION ALL
SELECT 3, '2020-08-10' , '2020-08-15' , 6 FROM DUAL)
, TAB2 AS(
SELECT MIN(EVENT_SDT) MIN_ES, MAX(EVENT_EDT) MAX_EE FROM TAB1)
SELECT COUNT(*)
FROM (
SELECT TO_CHAR(TO_DATE(MIN_ES,'YYYY-MM-DD')+LEVEL-1,'YYYY-MM-DD') VAL
FROM TAB2
CONNECT BY TO_CHAR(TO_DATE(MIN_ES,'YYYY-MM-DD')+LEVEL-1,'YYYY-MM-DD')<=MAX_EE) A
WHERE EXISTS(SELECT 1
FROM TAB1 AA
WHERE A.VAL BETWEEN AA.EVENT_SDT AND AA.EVENT_EDT);
언급해주신 쿼리는 오라클 쿼리네요. 사용하는 DB가 MySQL이라서 위의 쿼리를 적용하면 에러가 발생합니다. 그리고 CONNECT BY LEVE 구문은 오라클 전용이네요.
그래도, 답변 주셔서 감사합니다.
recursive 쿼리를 참고해서 만들어봤습니다.
with recursive test (id, sdt, edt) as (
select 1, cast('2020-08-01' as date), cast('2020-08-02' as date)
union all
select 2, cast('2020-08-02' as date), cast('2020-08-05' as date)
union all
select 3, cast('2020-08-10' as date), cast('2020-08-15' as date)
union all
select id, (sdt + interval 1 day), edt from test where sdt < edt
)
select count(distinct sdt) from test
-- order by id, sdt
+---------------------+
| count(distinct sdt) |
+---------------------+
| 11 |
+---------------------+
1 row in set (0.001 sec)
위 쿼리를 적용하니 오류가 합니다. "MySQL server version for the right syntax to use near 'recursive test (id, sdt, edt) AS ( "
with 문이 문법 오류가 나는것 같은데, MySql 8+ 이상에서는 지원된다고 하는것 제건 14.4 버전인데 왜 그런지 모르겠네요. 좀 더 알아봐야겠습니다.
답변 감사합니다.
전 mariadb에서 테스트해서 그런가봅니다;; 컬럼명을 안에서 쓰면 어떠신가요?
with recursive test as (
select 1 id , cast('2020-08-01' as date) sdt, cast('2020-08-02' as date) edt
union all
select 2, cast('2020-08-02' as date), cast('2020-08-05' as date)
union all
select 3, cast('2020-08-10' as date), cast('2020-08-15' as date)
union all
select id, (sdt + interval 1 day), edt from test where sdt < edt
)
select count(distinct sdt) from test order by id, sdt;
홈스테드 설치한 후 거기의 DBMS를 MySQL로 설정해서 사용하고 있는데, 그 버전이 지원을 안하는것 같네요.
방금 MySQL 설치파일을 다운받아 제 PC에 직접 설치해서 주신 쿼리 적용해 보니 잘됩니다.
감사합니다.
with t as
( select 1 id, cast('2020-08-01' as date) sdt, cast('2020-08-02' as date) edt
union all
select 2 id, cast('2020-08-03' as date) sdt, cast('2020-08-07' as date) edt
union all
select 3 id, cast('2020-08-07' as date) sdt, cast('2020-08-15' as date) edt
)
select sum(c1 - c2)
from (select t1.id, t1.sdt, t1.edt
, t1.edt - t1.sdt + 1 as c1
, (select count(*)
from t t2
where 1=1
and t2.edt >= t1.sdt
and t2.edt < t1.edt
) as c2
from t t1
where 1=1 ) M
order by id
;
답변 감사합니다.
WITH문이 안되는 가상머신의 MySQL 버전이 5.x 였네요. 그래서 WITH 쿼리문이 오류가 났었습니다.
최신 버전이라면? 분석함수등을 이용해 기간을 합칠 수 있습니다. http://gurubee.net/lecture/2849
구 버전이라면? 달력테이블을 미리 생성해 두고 조인하여 사용하시면 편리합니다. http://gurubee.net/article/65315
SELECT a.id
, COUNT(DISTINCT b.dt) cnt
FROM data_t a
INNER JOIN calendar_t b
ON b.dt BETWEEN a.sdt AND a.edt
WHERE -- 검색조건 --
GROUP BY a.id
;
) 버튼을 클릭하여 작성 하시면 됩니다.