Dawn's Blogs

分享技术 记录成长

0%

MySQL基础 (6) 聚合函数

发表于 更新于 分类于

聚合函数介绍

聚合函数作用于一组数据，并对一组数据返回一个值

常用的聚合函数

  • 适用于数值类型
    • AVG：求平均值
    • SUM：求和
  • 适用于数值类型、字符串、日期类型（这些都是可比较的）
    • MAX：最大值
    • MIN：最小值
  • COUNT：计算指定字段在查询结果中出现的个数（COUNT不计入NULL值，AVG = SUM / COUNT恒成立）
    • 统计表中记录数，使用COUNT(*) COUNT(1)效率较高
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 计算评价奖金率（有些人奖金率为NULL）
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)) "平均奖金率"
FROM employees;
+------------+
| 平均奖金率 |
+------------+
|   0.072897 |
+------------+
SELECT AVG(IFNULL(commission_pct,0)) "平均奖金率"
FROM employees;
+------------+
| 平均奖金率 |
+------------+
|   0.072897 |
+------------+

GROUP BY的使用

基本使用

可以使用GROUP BY将表中数据分成若干组

1
2
3
4
5
6
7
8
9
10
11
12
13
# 查询各个部门的平均工资，最高工资
SELECT department_id, AVG(salary), MAX(salary)
FROM employees
GROUP BY department_id;
+---------------+--------------+-------------+
| department_id | AVG(salary)  | MAX(salary) |
+---------------+--------------+-------------+
|          NULL |  7000.000000 |     7000.00 |
|            10 |  4400.000000 |     4400.00 |
|            20 |  9500.000000 |    13000.00 |
|            30 |  4150.000000 |    11000.00 |
|            40 |  6500.000000 |     6500.00 |

使用多个列进行分组

1
2
3
4
5
6
7
8
9
10
11
12
# 查询各个department_id, job_id的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;
+---------------+------------+--------------+
| department_id | job_id     | AVG(salary)  |
+---------------+------------+--------------+
|            90 | AD_PRES    | 24000.000000 |
|            90 | AD_VP      | 17000.000000 |
|            60 | IT_PROG    |  5760.000000 |
|           100 | FI_MGR     | 12000.000000 |
|           100 | FI_ACCOUNT |  7920.000000 |

WITH ROLLUP在GROUP BY分组的基础上再进行统计数据

使用WITH ROLLUP不能使用ORDER BY；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
# WITH ROLLUP对每个部门计算AVG
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|          NULL |  7000.000000 |
|            10 |  4400.000000 |
...
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL |  6461.682243 |
+---------------+--------------+

HAVING的使用

HAVING用于过滤数据

  • 如果过滤条件中使用了聚合函数，必须用HAVING，需要和GROUP BY一起使用

  • 当过滤条件中没有聚合函数，声明在WHERE中，WHERE效率更高

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 查询各个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+