mysql 일별 통계

특정 기간의 통계를 화면에 출력해야 할 경우가 있다. 일련의 날짜들이 DB 테이블에 들어 있다면 별 문제가 없겠지만, 굳이 별도의 통계 테이블이 필요하지 않다면 쿼리로 일련의 날짜들에 관련된 임시 테이블을 생성해야 한다. 우선 필요한 날짜 기간을 파악하고, 날짜 테이블을 만들어 본다.

 

curdate() - interval 1 day
curdate() - interval 2 day
curdate() - interval 3 day

 

이런 식이라면 현재부터 원하는 이전날짜까지의 결과셋을 만들 수 있다.

 

아래는 0000 부터 9999까지 각 자리마다 컬럼을 구성하는 쿼리이다.

select *
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

0 | 0 | 0 | 0 부터
1 | 0 | 0 | 0
...
9 | 9 | 9 | 8
9 | 9 | 9 | 9 까지가 출력된다.

 

그리고 아래와 같이 select 문을 변경하면 원하는 일자 테이블을 출력할 수 있다.

 

select curdate() - interval (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) day as date

 

2021-10-07
2021-10-06
2021-10-05
...

 

그리고 여기에 기간도 설정하고 다른 테이블과 조인하여 통계에도 사용할 수 있다.

 

select a.date as daily, sum(b.cost) as dailycost
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 left outer join `sales` b on a.date = Date(b.createDatetime)
where 1=1
and a.gDate between '2021-09-01' and '2021-09-31' 
group by daily
order by daily asc;

 

위 쿼리로 2021-09-01 부터 2021-09-31 까지의 일별 매출 통계를 출력할 수 있다.

 

약간 무식해 보이긴 하지만 필요하다면 ^^