mysql生成时间维度表

CREATE TABLE calendar (
    date DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    week INT,
    quarter INT
);

DELIMITER //
CREATE PROCEDURE fill_calendar (IN start_date DATE, IN end_date DATE)
BEGIN
    DECLARE current_date DATE;
    SET current_date = start_date;
    WHILE current_date <= end_date DO
        INSERT INTO calendar (date, year, month, day, week, quarter)
        VALUES (current_date, YEAR(current_date), MONTH(current_date), DAY(current_date), WEEK(current_date), QUARTER(current_date));
        SET current_date = DATE_ADD(current_date, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

CALL fill_calendar('2020-01-01', '2023-12-31');

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

SELECT c.year, c.month, SUM(s.amount) AS total_sales
FROM calendar c
JOIN sales s ON c.date = s.sale_date
WHERE c.year = 2023 AND c.month = 1
GROUP BY c.year, c.month;