MySQL-sql聚合（多行）函数-avg()

作者：马育民 • • 阅读：10107

介绍

AVG() 是 MySQL 中用于计算数值列平均值的聚合函数，它通过计算列值的总和除以非 NULL 值的数量来得到平均值。该函数主要用于统计分析场景，如计算平均销售额、平均年龄、平均分数等。

使用方法

1. 计算逻辑：

  • AVG(column) = SUM(column) / COUNT(column)
  • 自动忽略列中的 NULL 值（既不计入总和，也不计入数量）
  • 如果所有值都是 NULL 或没有匹配的行，AVG() 返回 NULL

2. 数据类型处理：

  • 仅适用于数值类型（INT、DECIMAL、FLOAT、DOUBLE 等）
  • 返回值类型通常为 DECIMAL 或 DOUBLE，保留适当的精度

3. 精度控制：

  • 可以结合 ROUND() 函数控制小数位数
  • 对于整数列，结果会自动转换为浮点数

查询平均工资

select avg(sal) from emp

案例

1. 基础环境准备

-- 创建学生成绩表
CREATE TABLE student_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    subject VARCHAR(50),
    score DECIMAL(5, 2),
    exam_date DATE,
    class VARCHAR(20),
    extra_points INT NULL  -- 附加分，可为NULL
);

-- 插入示例数据
INSERT INTO student_scores (student_id, subject, score, exam_date, class, extra_points) VALUES
(101, 'Math', 92.5, '2024-01-15', 'Class A', 5),
(101, 'English', 88.0, '2024-01-16', 'Class A', NULL),
(102, 'Math', 76.5, '2024-01-15', 'Class A', 3),
(102, 'English', 94.0, '2024-01-16', 'Class A', 2),
(103, 'Math', 85.0, '2024-01-15', 'Class B', NULL),
(103, 'English', 79.5, '2024-01-16', 'Class B', 1);

2. 基础平均值计算

-- 计算所有成绩的平均分
SELECT AVG(score) AS average_score FROM student_scores;  -- 返回 85.9167

-- 计算附加分的平均值（自动忽略NULL）
SELECT AVG(extra_points) AS avg_extra_points FROM student_scores;  -- 返回 2.75（4个非NULL值）

-- 结合ROUND函数控制精度
SELECT ROUND(AVG(score), 2) AS average_score FROM student_scores;  -- 返回 85.92

3. 结合 WHERE 条件筛选

-- 计算数学科目的平均分
SELECT AVG(score) AS math_average 
FROM student_scores 
WHERE subject = 'Math';  -- 返回 84.6667

-- 计算Class A的平均分
SELECT AVG(score) AS class_a_average 
FROM student_scores 
WHERE class = 'Class A';  -- 返回 87.75

4. 结合 GROUP BY 分组统计

-- 按科目分组计算平均分
SELECT 
    subject,
    AVG(score) AS subject_average,
    COUNT(*) AS student_count
FROM student_scores 
GROUP BY subject;

-- 按班级和科目分组统计
SELECT 
    class,
    subject,
    AVG(score) AS average_score,
    MAX(score) AS highest_score,
    MIN(score) AS lowest_score
FROM student_scores 
GROUP BY class, subject
ORDER BY class, subject;

5. 结合 HAVING 过滤分组结果

-- 筛选平均分高于85的科目
SELECT 
    subject,
    AVG(score) AS subject_average
FROM student_scores 
GROUP BY subject 
HAVING subject_average > 85;  -- 返回 Math (84.67不满足), English (87.17满足)

6. 使用表达式计算平均值

-- 计算包含附加分的总成绩平均值
SELECT AVG(score + COALESCE(extra_points, 0)) AS total_average FROM student_scores;

-- 计算及格率（score >= 60）
SELECT AVG(CASE WHEN score >= 60 THEN 1 ELSE 0 END) * 100 AS pass_rate FROM student_scores;

7. 处理 NULL 结果

-- 使用IFNULL确保返回0而非NULL
SELECT IFNULL(AVG(score), 0) AS average_score 
FROM student_scores 
WHERE class = 'Class C';  -- 返回 0（无匹配数据）

性能优化要点

1. 索引优化：

-- 为筛选列创建索引
CREATE INDEX idx_scores_subject ON student_scores(subject);
CREATE INDEX idx_scores_class ON student_scores(class);

-- 复合索引（适合GROUP BY场景）
CREATE INDEX idx_scores_class_subject ON student_scores(class, subject);
  1. 避免在 AVG() 中使用复杂表达式：
    ```sql
    — 不推荐（每次计算都要执行表达式）
    SELECT AVG(score * 1.2 + COALESCE(extra_points, 0)) FROM student_scores;

— 推荐（先计算基础值）
SELECT AVG(total) FROM (
SELECT score * 1.2 + COALESCE(extra_points, 0) AS total
FROM student_scores
) AS temp;


### 3. **使用近似计算**：
   - 对于超大数据集，可以使用采样方法估算平均值
   - 避免对全表进行精确的 AVG 计算

# 常见问题与解决方案

### 1. **AVG() 返回 NULL**：
```sql
-- 使用IFNULL或COALESCE处理
SELECT COALESCE(AVG(score), 0) FROM student_scores WHERE class = 'Class C';

2. 包含 NULL 值的计算：

-- 将NULL视为0参与计算
SELECT AVG(COALESCE(extra_points, 0)) AS avg_extra FROM student_scores;

3. 整数列的平均值精度：

-- 确保返回浮点数结果
SELECT AVG(CAST(score AS DECIMAL(5,2))) FROM student_scores;

总结

  1. AVG() 函数用于计算数值列的平均值，计算公式为总和除以非 NULL 值的数量，自动忽略 NULL 值。
  2. 可以与 WHERE、GROUP BY、HAVING 等子句结合，实现复杂的分组统计和筛选。
  3. 性能优化的关键是合理创建索引、简化计算表达式，同时注意使用 IFNULL() 或 COALESCE() 处理可能的 NULL 结果。
吉ICP备15003043号