[mySql] 특정 기간 조회 / 데이터가 없는 날엔 0 처리

특정 기간 조회시 , 데이터가 있는 날짜는 출력되지만 

데이터가 없는 날을 그룹바이 하면 중간중간 날짜가 빵꾸가 납니다.

 

날짜를  Right Join  걸어 주어  null일시, 0을 출력해주는 예제입니다. 

실제는..작업하는 프로젝트 코드임으로 T200의 경우 날짜가 포함된 다른 코드로 변경했습니다.

T200에는 걸어줄 쿼리를 널어주면 됩니다.

SELECT T200.yyyymmdd AS date
		IFNULL(T300.cnt, 0) 
FROM 
	(
        select date_format(publish_time, '%Y-%m-%d')as yyyymmdd, count(*) cnt 
        from sample  
        where publish_time between '2019-06-03 00:00:00' and '2019-08-04 23:59:59' 
        group by date_format(publish_time, '%Y-%m-%d')  
	) T300
RIGHT JOIN  
	(
		SELECT * 
		FROM (   -- T10
		     SELECT n, yyyymmdd
		       FROM ( -- T1
		             SELECT @N := @N +1 AS n ,  
		                    DATE_FORMAT( DATE_ADD( '2022-03-30' , interval @N -1 day),'%Y-%m-%d') as yyyymmdd  
		               FROM (data ), (SELECT @N:=0 FROM dual ) a  
		              LIMIT 500  
		       ) T1
		      WHERE yyyymmdd <= '2022-04-07' 
		     ) T10
		WHERE yyyymmdd BETWEEN '2022-03-30' AND '2022-04-07'
	) T200 on (T300.yyyymmdd = T200.yyyymmdd)
ORDER BY T200.yyyymmdd ASC
;

 

 

설명 추가한 쿼리 는

SELECT T200.yyyymmdd AS date
		IFNULL(T300.cnt, 0) 
FROM 
	(
        select date_format(publish_time, '%Y-%m-%d')as yyyymmdd, count(*) cnt 
        from sample  
        where publish_time between 시작날 and 종료날
        group by date_format(publish_time, '%Y-%m-%d')  
	) T300
RIGHT JOIN  
	(
		SELECT * 
		FROM (   -- T10
		     SELECT n, yyyymmdd
		       FROM ( -- T1
		             SELECT @N := @N +1 AS n ,  
		                    DATE_FORMAT( DATE_ADD( 시작날 , interval @N -1 day),'%Y-%m-%d') as yyyymmdd  
		               FROM (DB에 500개 정도 row가 쌓인 테이블 아무거나), (SELECT @N:=0 FROM dual ) a  
		              LIMIT 500  
		       ) T1
		      WHERE yyyymmdd <= 종료날 
		     ) T10
		WHERE yyyymmdd BETWEEN 시작날 AND 종료날
	) T200 on (T300.yyyymmdd = T200.yyyymmdd)
ORDER BY T200.yyyymmdd ASC
;

여기서 포인트는

날짜를 순서대로 출력하는 쿼리입니다.

select * from
(select adddate('2022-03-31',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2022-03-31' and '2022-04-05';

select a.Date 
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2020-01-01' and '2020-01-24';

두개 다 사용 가능