3.7 聚集函数 3.7.1 基本聚集

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 2.7k 阅读时长 ≈ 2 分钟

3.7 聚集函数

什么是聚集函数

聚集函数是以值的一个集合(集或多重集)为输入、返回单个值的函数。

五个固有聚集函数 avg min max sum count

SQL提供了五个固有聚集函数

  • 平均值:avg
  • 最小值:min
  • 最大值:max。
  • 总和:sum。
  • 计数:count。

其中sum和avg的输入必须是数字集,
其他运算符(min,max,count)还可作用在非数字数据类型的集合上,如字符串。

3.7.1 基本聚集

sum实例 找出Computer Science系所以教师的总工资

1
2
3
select sum(salary) as sum_salary
from instructor
where dept_name ='Comp. Sci. ';
1
2
3
4
5
6
7
8
9
mysql> select sum(salary) as sum_salary
from instructor
where dept_name ='Comp. Sci. ';
+------------+
| sum_salary |
+------------+
| 232000.00  |
+------------+
1 row in set

avg实例 找出Computer Science系教师的平均工资

1
2
3
select avg(salary)
from instructor
where dept_name ='Comp. Sci. ';

该查询的结果是一个具有单属性的关系,其中只包含一个元组,这个元组的数值对应Computer Science系教师的平均工资。

1
2
3
4
5
6
7
8
9
mysql> select avg(salary)
from instructor
where dept_name ='Comp. Sci. ';
+--------------+
| avg(salary)  |
+--------------+
| 77333.333333 |
+--------------+
1 row in set

使用as重命名聚集函数的结果

数据库系统可以给结果关系的属性一个任意的名字,该属性是由聚集产生的。然而,我们可以用as子句给属性赋个有意义的名称,如下所示:

1
2
3
select avg(salary) as avg_salary
from instructor
where dept_name ='Comp. Sci. ';
1
2
3
4
5
6
7
8
9
mysql> select avg(salary) as avg_salary
from instructor
where dept_name ='Comp. Sci. ';
+--------------+
| avg_salary   |
+--------------+
| 77333.333333 |
+--------------+
1 row in set

在instructor关系中, Computer Science系的工资值是75000美元、65000美元和92000美元,平均工资是232000/3=773333美元。

重复值

计算平均值是注意要保留重复元组

在计算平均值时保留重复元组是很重要的。假设Computer Science系增加了第四位教师,其工资正好是75000美元。如果去除重复的话,我们会得到错误的答案(232000/4=58000美元),而正确的答案是76750美元。

使用distinct在计算聚集函数之前 删除重复元组

有些情况下在计算聚集函数前需先删掉重复元组。如果我们确实想删除重复元组,可在聚集表达式中使用关键词distinct。比方有这样一个查询示例”找出在2010年春季学期讲授一门课程的教师总数“,在该例中不论一个教师讲授了几个课程段,他只应被计算一次。所需信息包含在teaches关系中,我们书写该查询如下

1
2
3
select count(distinct ID)
from teaches
where semester ='Spring' and year=2010;

计算一个关系中元组的个数count(*)

我们经常使用聚集函数count计算一个关系中元组的个数。SQL中该函数的写法是count(*)。因此,要找出course关系中的元组数,可写成:

1
2
select count(*)
from course;
1
2
3
4
5
6
7
8
mysql> select count(*)
from course;
+----------+
| count(*) |
+----------+
|       13 |
+----------+
1 row in set

没有count(distinct *)

SQL不允许在用count(*)时使用distinct。

max和min中可以使用distinct

在用max和min时使用distinct是合法的,不过没有必要,因为结果并无差别。

默认保留重复

我们可以使用关键词all替代distinct来说明保留重复元组,但是,既然all是默认的,所以没必要显示使用all。