스킴구조는 아래와 같습니다.
place_id | start_date | end_date |
10001 | 2021-01-01 | 2021-05-31 |
10002 | 2021-03-01 | 2021-07-31 |
위 테이블을 기준으로
place_id 는 업체의 고유 아이디 입니다.
start_date 와 end_date 는 제휴시작일과 제휴 종료일입니다.
업체의 제휴기간 월별 통계를 구하려고 합니다.
도무지 감자체가 안와서 이렇게 문의를 드립니다.
월별 row 단위로 되어 있지 않아서 어렵네요.. 로직을 바꿔야 하는건지..
어떤식으로 접근을 해야 하나요?
==================
원하는 결과물은
10001 업체는 1,2,3,4,5 월까지 가입이 되었다고 월별 카운트가 더해져야 하고
10002 업체는 4,5,6,7 월까지 가입이 되었다고 월별 카운트에 더해져야 해서
결국
1월 ,2월 ,3월 1개
4월 2개
5월 2개
,6월,7월 1개
의 통계가 나와야 합니다.
WITH RECURSIVE calendar AS ( SELECT DATE '2021-01-01' dt UNION ALL SELECT dt + INTERVAL 1 MONTH FROM calendar WHERE dt + INTERVAL 1 MONTH <= '2021-08-31' ) , t AS ( SELECT 10001 place_id, DATE '2021-01-01' start_date, DATE '2021-05-31' end_date UNION ALL SELECT 10002, DATE '2021-03-01', DATE '2021-07-31' ) SELECT DATE_FORMAT(a.dt, '%Y-%m') ym , COUNT(b.end_date) cnt FROM calendar a LEFT OUTER JOIN t b ON b.start_date < a.dt + INTERVAL 1 MONTH AND b.end_date >= a.dt GROUP BY DATE_FORMAT(a.dt, '%Y-%m') ;