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

 

 

안녕하세요.

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

 

 

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

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

 

 

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

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

 

 

 

---

 

 

 

 

 

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

 

 

 

 

 

 

 

더미 데이터 생성

 

 

create table t (n int);

 

 

날짜 테이블을 생성하기 위한 더미 테이블을 생성하고

 

 

 

 

 

insert into t values (1);

 

 

 

값을 1을 넣어줍니다.

 

 

 

 

 

insert into t select * from t

 

 

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

13번만 하시면 됩니다...ㅎㅎ

 

 

 

 

 

 

 

 

 

날짜테이블 생성

 

 

 

 

create table date_t (temp_date char(20));

 

 

이렇게 날짜를 넣을 테이블과 컬럼을 설정하구요.

 

 

 

 

 

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

 

 

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

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

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

이 데이터를 가지고

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

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

 

 

 

 

 

반응형

 

 

 

 

 

사용 방법

 

 

 

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

 

 

 

 

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

count를 본인 컬럼에 idx를 넣어줍니다. 

 

 

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

 

 

group by를 date로 하구요

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

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

주의사항

 

 

 

 

 

SELECT
    date_format(aa.temp_date, '%Y-%m') date,
    count(board_idx) as cnt
FROM
    date_t  aa
left join 
    tbl_board c on c.board_edate = aa.temp_date 
    
    where c.board_idx is null -- 사용하면 임의의 날짜를 부를 수 없음
    
group by 
    date

 

 

 

만약에 날짜 테이블인 date_t가 아닌

Left Join한 테이블 tbl_board 의 컬럼을 where 조건을 걸 경우

date_t의 임의의 날짜 값을 불러올 수 없습니다.

(조인 후 Where 조건에서는 date_t의 속성만 사용해야합니다.)

 

 

 

 

 

 

SELECT
    date_format(aa.temp_date, '%Y-%m') date,
    count(board_idx) as cnt
FROM
    date_t  aa
left join 
    (
    select * from tbl_board where board_idx is not null   -- 서브쿼리 안에서 사용
    )
     c on c.board_edate = aa.temp_date 
    where (aa.temp_date >= '2023-01-01' AND  aa.temp_date <= '2023-05-07') 
    
group by 
    date

 

 

이를 해결하기 위해서는 Left Join하기 전에

tbl_board를 서브쿼리하여 조건대로 뽑아준 다음 Left Join하여 사용하여야 합니다.