mysql 日期维度表

CREATE TABLE date_dimension (
    date DATE PRIMARY KEY,
    day_of_week VARCHAR(10),
    month VARCHAR(10),
    quarter INT,
    year INT,
    is_holiday BOOLEAN
);

DELIMITER //
CREATE PROCEDURE fill_date_dimension()
BEGIN
    DECLARE start_date DATE := '2020-01-01';
    DECLARE end_date DATE := CURDATE();
    DECLARE current_date DATE;

    WHILE start_date <= end_date DO
        SET current_date = start_date;
        INSERT INTO date_dimension (date, day_of_week, month, quarter, year, is_holiday)
        VALUES (
            current_date,
            DAYNAME(current_date),
            MONTHNAME(current_date),
            QUARTER(current_date),
            YEAR(current_date),
            CASE WHEN DAYOFWEEK(current_date) IN (1, 7) THEN TRUE ELSE FALSE END -- 假设周末为节假日
        );
        SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

CALL fill_date_dimension();

SELECT 
    date,
    day_of_week,
    month,
    quarter,
    year,
    is_holiday
FROM 
    date_dimension
WHERE 
    date BETWEEN '2023-01-01' AND '2023-01-31';