mysql 按月按周统计

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    month
ORDER BY 
    month;

SELECT 
    YEARWEEK(order_date) AS week,
    SUM(amount) AS total_amount
FROM 
    orders
GROUP BY 
    week
ORDER BY 
    week;

ALTER TABLE orders MODIFY COLUMN order_date DATE;
UPDATE orders SET order_date = STR_TO_DATE(order_date, '%Y-%m-%d') WHERE ISDATE(order_date) = 0;

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount) AS total_amount
FROM 
    orders
WHERE 
    order_date IS NOT NULL AND amount IS NOT NULL
GROUP BY 
    month
ORDER BY 
    month;

CREATE INDEX idx_order_date ON orders(order_date);

ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2020-01-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2021-01-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2022-01-01')),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);