[MySQL] 날짜 더미데이터 만들기

WITH문 재귀를 사용하여 날짜 더미데이터를 만들 수 있다.

WITH RECURSIVE A AS(
  SELECT 0 AS LEVEL
  UNION ALL
  SELECT 1+A.LEVEL
  FROM A
  WHERE A.LEVEL < (
    SELECT DATEDIFF('2023-12-31', '2023-01-01') #종료날짜, 시작 날짜
  )
) 

SELECT ADDDATE('2023-01-01', INTERVAL LEVEL DAY) #시작날짜
FROM A;

 

위 쿼리를 실행하면 다음과 같은 결과를 조회할 수 있다.

 

서버에서 WITH를 사용할 수 없을 때 다음과 같은 방법으로도 날짜 더미데이터를 조회할 수 있다.

(1년 후 날짜를 기준으로 9999일 이전 날짜까지만 조회가능)

SELECT DATE_FORMAT(D.Date, '%Y-%m-%d') AS DATE
    FROM (
		 SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR) - 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
    ) D
WHERE DATE_FORMAT(D.DATE, '%Y-%m-%d') BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(D.Date, '%Y-%m-%d')
ORDER BY DATE