基于月份分组的SQL高效查询需求：聚合MAX/MIN/AVG值

原始数据 #

需求说明 #

按月份分组，分别计算：

• 每个月中data_type为'max'的data_value的最大值
• 每个月中data_type为'min'的data_value的最小值
• 每个月中data_type为'avg'的data_value的平均值

期望结果：

性能最优的SQL语句 #

使用条件聚合实现，只需扫描一次表，是性能最优的方案：

SELECT
    EXTRACT(MONTH FROM TO_DATE(date, 'DD-MM-YYYY')) AS month,
    MAX(CASE WHEN data_type = 'max' THEN data_value END) AS max,
    MIN(CASE WHEN data_type = 'min' THEN data_value END) AS min,
    AVG(CASE WHEN data_type = 'avg' THEN data_value END) AS avg
FROM your_table_name
GROUP BY EXTRACT(MONTH FROM TO_DATE(date, 'DD-MM-YYYY'))
ORDER BY month;

性能优势说明 #

1. 单次扫描：该查询仅对表执行一次全表扫描（若date字段有索引，则可利用索引快速过滤分组），避免了子查询、多表JOIN等方案带来的多次扫描开销。
2. 高效聚合：条件聚合逻辑直接在GROUP BY阶段完成计算，无需额外的中间结果集处理。
3. 兼容性广：该语法适用于绝大多数关系型数据库（如MySQL、PostgreSQL、Oracle等），仅需根据数据库类型微调日期转换函数（例如MySQL可用MONTH(STR_TO_DATE(date, '%d-%m-%Y'))）。

索引优化建议 #

若表数据量较大，可创建联合索引进一步提升性能：

-- MySQL示例
CREATE INDEX idx_date_datatype_value ON your_table_name(date, data_type, data_value);

该索引可让数据库直接通过索引完成分组和聚合计算，无需回表查询原始数据。

内容的提问来源于stack exchange，提问作者Danilo Giovannico