[MYSQL] 중복 일수 제외 산출

Check overlap of date ranges in mysql

• 일반적으로 시작일과 종료일 사이의 일수를 구하는 경우(ex> 경력일수) start_date, end_date사이에 date_diff와 같은 함수를 사용하게 된다.
• 시작, 종료일자가 2개 이상이 있고 이에 대한 일수의 총합을 구하는 경우는 date_diff와 같은 함수로는 일수의 총합을 구할 수 없다.

1. 경력 테이블 생성

// 기존 경력 테이블
CREATE TABLE `tbl_date` (
`name` VARCHAR(10) NOT NULL,
`start_date` VARCHAR(10) NULL,
`end_date` VARCHAR(10) NULL,
PRIMARY KEY (`name`));
insert into tbl_date values('홍길동', '2016-10-01','2016-10-10');
insert into tbl_date values('홍길동', '2016-10-02','2016-10-11');

1. tbl_date 테이블

• 홍길동의 경력일수를 구한다고 하면 중복을 제거하면 총 11일이 되어야 된다. 하지만 date_diff를 통해 단순한 합으로 계산을 하면 20일이 되어버린다.

2. calendar 테이블 생성
- 이를 이용하여 중복일수를 group by를 통해 제거 하는 방법으로 계산해보았다.

//날짜 테이블 생성
CREATE TABLE `tbl_calendar` (
  `a_date` char(10) NOT NULL,
  PRIMARY KEY (`a_date`),
  UNIQUE KEY `idtbl_calendar_UNIQUE` (`a_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



//80년치 날짜 insert 
insert into tbl_calendar (
SELECT 
    TRIM('1950-01-01' + INTERVAL a + b + c + d + e DAY) AS a_date
FROM
    (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 a UNION SELECT 3 a UNION SELECT 4 a UNION SELECT 5 a UNION SELECT 6 a UNION SELECT 7 a UNION SELECT 8 a UNION SELECT 9 a) d,
    (SELECT 0 b UNION SELECT 10 b UNION SELECT 20 b UNION SELECT 30 b UNION SELECT 40 b UNION SELECT 50 b UNION SELECT 60 b UNION SELECT 70 b UNION SELECT 80 b UNION SELECT 90 b) m,
    (SELECT 0 c UNION SELECT 100 c UNION SELECT 200 c UNION SELECT 300 c UNION SELECT 400 c UNION SELECT 500 c UNION SELECT 600 c UNION SELECT 700 c UNION SELECT 800 c UNION SELECT 900 c) n,
    (SELECT 0 d UNION SELECT 1000 d UNION SELECT 2000 d UNION SELECT 3000 d UNION SELECT 4000 d UNION SELECT 5000 d UNION SELECT 6000 d UNION SELECT 7000 d UNION SELECT 8000 d UNION SELECT 9000 d) o,
    (SELECT 0 e UNION SELECT 10000 e UNION SELECT 20000 e ) 
p);

2. tbl_calendar 테이블

3. 해당 캘린더 테이블을 join -> group by를 제거하여 중복을 제거한 해당일(day) row를 구할 수 있다.

    SELECT 
        name, a_date
    FROM
        tbl_calendar
    JOIN tbl_date ON a_date >= start_date
        AND a_date <= end_date
    GROUP BY name , a_date

3. tbl_calendar테이블을 통한 해당일(day)차이 join 결과

4. 이를 group by하여 일수(day)만 계산할 수 있다.

SELECT 
    name, COUNT(*)
FROM
    (SELECT 
        name, a_date
    FROM
        tbl_calendar
    JOIN (SELECT 
        '2016-10-01' AS start_date,
            '2016-10-10' AS end_date,
            '홍길동' AS name
     UNION SELECT 
        '2016-10-02' AS start_date,
            '2016-10-11' AS end_date,
            '홍길동' AS name
    ) tbl_date ON a_date >= start_date
        AND a_date <= end_date
    GROUP BY name , a_date) result;

4. 해당 일수(day) count 결과

5. 중복되는 일수(day)만 구해 올 수도 있다.

    SELECT 
        name, a_date, count(*) dupl_date
    FROM
        tbl_calendar
    JOIN (SELECT 
        '2016-10-01' AS start_date,
            '2016-10-10' AS end_date,
            '홍길동' AS name
     UNION SELECT 
        '2016-10-02' AS start_date,
            '2016-10-11' AS end_date,
            '홍길동' AS name
    ) tbl_date ON a_date >= start_date
        AND a_date <= end_date
    GROUP BY name , a_date
    having dupl_date > 1

5. 중복되는 일수(day) 결과

* calendar 테이블에 unique, pk 등의 조건을 일부 넣어주면 속도는 충분히 개선되는 것을 확인 될 수 있을 것이다.