[mysql] 월별통계방법!! (데이터 없는 날짜 0 처리 방법)

안녕하세요. 오늘은 통계를 위한 월별통계 쿼리를 알아보겠습니다.

예를들어 올해인 2021년의 월별통계를 구하기 위해서는 group by를 해야하는데 

데이터가 없는 경우 group by에서 빠지게 됩니다.

이것을 처리하려하는데.....저는 어찌어찌 처리했지만 먼가 억지스러운 느낌이 납니다...ㅜㅜ

더 좋은 방법 있으면 댓글로 공유해주세요!!

---

저의 구현방법입니다.

 

1. 10년치날짜더미데이터 테이블 생성

2. 구하고자하는 테이블과 left join

 

 

 

 

1. 10년치날짜더미데이터 테이블 생성

 

 

더미 데이터 생성

 

날짜 테이블을 생성하기 위한 더미 테이블을 생성하고 값을 1을 넣어줍니다.

create table t (n int);

insert into t values (1);

 

그리고 더미 데이터를 생성해줍니다... 10년치 데이터만큼 데이터를 만들어주는거죠. 13번만 하심 됩니다...ㅎ

insert into t select * from t

 

 

날짜테이블 생성

 

날짜 테이블을 생성하고 포맷을 본인포맷과 맞게 변경합니다. 저는 '%Y-%m-%d' 으로 하였습니다.

그리고 시작날짜를 '2021-01-01' 으로하고 2031년까지 설정하였습니다.

 

create table date_t (d char(20), ds char(20)); 

insert into date_t
select d, date_format(d, '%Y-%m-%d') from (
  select @rnum:=@rnum+1 as rownum, date(adddate('2021-01-01', interval @rnum day)) as d
  from (select @rnum:=-1) r, t
  ) t
where year(d) < 2031; 

 

전 테이블명와 컬럼명을 temp_date로 통일 시켰습니다.

그럼 이렇게 데이터가 생깁니다.

 

 

이젠 이 데이터를 가지고 left join으로해서 해당년에 1월부터 12월까지 데이터가 나오면서

빈 데이터가 있더라도 0이 될 수 있도록 쿼리를 작성하겠습니다.

---

 

데이터포맷을 년과 월로 나타낸 후,

count를 본인 컬럼에 idx를 넣어줍니다. (idx는 pk값이기에 무조건 값이 들어가 있으니까요)

그리고 left join으로 본인 날짜컬럼과 temp_date의 컬럼을 조인해줍니다.

group by를 date로 하구요

where절에 원하는 년도를 넣어주면 끝입니다!!

select 
	*
from (
	select
		date_format(aa.temp_date, '%Y-%m') date,
		count(board_idx) as cnt
	from
		temp_date aa
			left join 
            	tbl_board c on (c.board_edate = aa.temp_date)
    		group by 
            	date
) a
where 
	date like concat ('%' , '2025', '%')

그리고 추가로!!

위에 소스에 조건을 넣고 싶으면 밖에 있는 where이 아닌 안에 있는 on절에 넣어주시면 됩니다.

 

아래 화면을 보시면 2025년 5월에 데이터가 1개가 들어가 있고 나머지 데이터 없는 것들은 0으로 표시되는 것을 알 수 있습니다!!

 

출처 : [mysql] 월별통계방법!! (데이터 없는 날짜 0 처리 방법) (tistory.com)