mysql 按照季度分组

SELECT 
    YEAR(date) AS year,
    QUARTER(date) AS quarter,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    YEAR(date), QUARTER(date)
ORDER BY 
    YEAR(date), QUARTER(date);

-- 确保日期字段为 DATETIME 类型
ALTER TABLE sales MODIFY COLUMN date DATETIME;

-- 清理无效日期
DELETE FROM sales WHERE date IS NULL OR date < '2000-01-01';

-- 创建分区表
ALTER TABLE sales
PARTITION BY RANGE (TO_DAYS(date)) (
    PARTITION p2020_q1 VALUES LESS THAN (TO_DAYS('2020-04-01')),
    PARTITION p2020_q2 VALUES LESS THAN (TO_DAYS('2020-07-01')),
    PARTITION p2020_q3 VALUES LESS THAN (TO_DAYS('2020-10-01')),
    PARTITION p2020_q4 VALUES LESS THAN (TO_DAYS('2021-01-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 在日期字段上建立索引
CREATE INDEX idx_date ON sales(date);