[MySQL]DATE_FORMAT을 이용한 날짜(일, 월, 년)별 통계(없는 데이터는 0처리 포함)

 MySQL에서는 일, 월, 년도 별로 집계를 해야 하는 상황이 발생합니다. 하지만 GROUP BY를 사용하여 집계를 하는 경우에 데이터가 존재하지 않는다면 그 기간에 데이터를 집계할 수 없습니다. 이번 포스팅에서는 없는 데이터의 포함하는 쿼리와 포함하지 않는 쿼리 두 가지 방법을 알아보도록 하겠습니다. 

 

📌일별 통계 

SELECT DATE(Date) AS DATE
    ,  COUNT(id) 
   FROM Table
  GROUP BY DATE;

 

📌월별 통계

#DATE_FORMAT을 사용하여 월별 조회
SELECT DATE_FORMAT(Date, '%Y-%m') AS Month 
    ,  COUNT(id) 
   FROM Table
  GROUP BY Month;

 

📌연별 통계

#DATE_FORMAT을 사용하여 년별 조회
SELECT DATE_FORMAT(Date, '%Y') AS Year
    ,  COUNT(id) 
   FROM Table
  GROUP BY Year;

 

🚨위의 방법은 MySQL에 DATE_FORMAT(날짜 형식 설정)을 이용하여 원하는 날짜 형식으로 바꾸어 집계를 한 쿼리입니다. 

 

 

 

📌DATE_FORMAT의 치환 형식 

기호	역할	기호	역할	기호	역할
%Y	4자리 년도			%d	2자리 일자
%y	2자리 년도	%m	2자리 월	%H	시간(24)

 

🚨위의 기호 외에도 날짜를 표현할 수 기호의 종류는 많습니다. (가장 자주 쓰이는 기호)  위의 기호 외에는 사실 자주 쓰이진 않는 것 같습니다. 

 

---

📌통계 - 없는 데이터 O 처리

위의 방법은 없는 데이터를 조회할 수 없습니다. 하지만 없는 데이터를 포함하여 집계해야 하는 경우가 있습니다. 보통 날짜 테이블을 만들어 JOIN 하여 집계를 하지만 RECURSIVE 구문을 사용하여 작성해 보도록 하겠습니다. 

 

2022.11.26 - [RDBS/MYSQL] - [MYSQL] 계층 쿼리 - WITH RECURSIVE 사용

[MYSQL] 계층 쿼리 - WITH RECURSIVE 사용

 

📌매월 말일 구하기 - 재귀

WITH RECURSIVE T AS (
  SELECT 1 AS NUM
  UNION ALL
  SELECT A+1 
    FROM T
   WHERE NUM < DAY(LAST_DAY('2023-02-01'))
)
SELECT *
 FROM T;

 

 

1	8	15	22
2	9	16	23
3	10	17	24
4	11	18	25
5	12	19	26
6	13	20	27
7	14	21	28

 

🚨MySQL의 LAST_DAY 함수를 이용하여 해당 월의 말일을 구하여 말일까지 재귀한다. 2023-02-01 기준 말일은 28일 이므로 28개의 레코드를 만든다. 

 

 

 

 

📌예시 테이블 : 2023.2월 데이터 

SELECT COUNT(*) AS CNT
     , DATE_FORMAT(DATE, '%Y-%m-%d') AS DATE
   FROM TABLE
  WHERE DATE_FORMAT(DATE, '%Y-%m') = '2023-02'
  GROUP BY DATE
 ORDER BY DATE ASC

 

🚨2023-02 데이터 집계 쿼리 

CNT	DATE
3	2023-02-10
2	2023-02-15
1	2023-02-20
4	2023-02-22
3	2023-02-23
1	2023-02-24
1	2023-02-27

 

 

📌예시 테이블 : 2023.2월 데이터 - 없는 데이터 포함

WITH RECURSIVE T AS (
  SELECT 1 AS NUM
  UNION ALL
  SELECT NUM+1 
    FROM T
   WHERE NUM < DAY(LAST_DAY('2023-02-01'))
)
SELECT NUM
     , IFNULL(B.CNT, 0) AS CNT
   FROM T
   LEFT OUTER JOIN (
                    SELECT COUNT(*) AS CNT
                         , DATE_FORMAT(DATE, '%Y-%m-%d') AS DATE
                       FROM TABLE
                      WHERE DATE_FORMAT(DATE, '%Y-%m') = '2023-02'
                     GROUP BY DATE
                     ORDER BY DATE ASC ) B
       ON NUM = DAY(DATE)

 

🚨이와 같이 작성된 쿼리는 2023-02 집계된 결과뿐만 아니라 다른 일자에 해당하는 결과도 조회 가능합니다. (하지만 데이터가 없기 때문에 0으로 표현될 것입니다. 

 

 

이 포스팅은 쿠팡파트너스 활동의 일환으로 일정액의 수수료를 제공받습니다.