SQL로 날짜 데이터 생성하기

카테시안 곱, 조인, 크로스 조인 방식으로 날짜 데이터를 생성하는 방법은 아래와 같습니다.

 

많은 증가하는 숫자, 날짜를 만들때는 아래 같은 SQL을 사용하면 됩니다.

SELECT DATE_ADD('2022-01-01', INTERVAL seq DAY) AS date
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY a) AS  seq 
    FROM (
        SELECT 0 AS seq 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
    ) a,
    (
        SELECT 0 AS b UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
        SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL
        SELECT 80 UNION ALL SELECT 90
    ) b,
    (
        SELECT 100 AS c UNION ALL SELECT 200 UNION ALL SELECT 300
    ) c
) d
WHERE DATE_ADD('2022-01-01', INTERVAL seq DAY) <= '2022-03-31'