MySQL-sql聚合（多行）函数-avg()-马育民老师

# 介绍

`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. 基础环境准备
```sql
-- 创建学生成绩表
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. 基础平均值计算
```sql
-- 计算所有成绩的平均分
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 条件筛选
```sql
-- 计算数学科目的平均分
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 分组统计
```sql
-- 按科目分组计算平均分
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 过滤分组结果
```sql
-- 筛选平均分高于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. 使用表达式计算平均值
```sql
-- 计算包含附加分的总成绩平均值
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 结果
```sql
-- 使用IFNULL确保返回0而非NULL
SELECT IFNULL(AVG(score), 0) AS average_score 
FROM student_scores 
WHERE class = 'Class C';  -- 返回 0（无匹配数据）
```

# 性能优化要点

### 1. **索引优化**：
```sql
-- 为筛选列创建索引
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);
```

2. **避免在 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 值的计算**：
```sql
-- 将NULL视为0参与计算
SELECT AVG(COALESCE(extra_points, 0)) AS avg_extra FROM student_scores;
```

### 3. **整数列的平均值精度**：
```sql
-- 确保返回浮点数结果
SELECT AVG(CAST(score AS DECIMAL(5,2))) FROM student_scores;
```

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

原文出处：http://www.malaoshi.top/show_1GW2Jjyry1AG.html