0%

3.7.2 分组聚集

发表于 更新于 分类于 ， ， ， Valine：


3.7.2 分组聚集

有时候我们不仅希望将聚集函数作用在单个元组集上,而且也希望将其作用到一组元组集上;在SQL中可用group by子句实现这个愿望。 group by子句中给出的一个或多个属性是用来构造分组的。

group by子句分组原则

在group by子句中的所有属性上取值相同的元组将被分在一个组中。

实例 使用dept_name属性给instructor关系分组

instructor关系中的元组按照dept_name属性进行分组的情况,如下所示:

1
2
3
select *
from instructor
group by dept_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select *
from instructor
group by dept_name;
+-------+------------+------------+--------+
| ID    | name       | dept_name  | salary |
+-------+------------+------------+--------+
| 76766 | Crick      | Biology    | 72000  |
| 10101 | Srinivasan | Comp. Sci. | 65000  |
| 98345 | Kim        | Elec. Eng. | 80000  |
| 12121 | Wu         | Finance    | 90000  |
| 32343 | El Said    | History    | 60000  |
| 15151 | Mozart     | Music      | 40000  |
| 22222 | Einstein   | Physics    | 95000  |
+-------+------------+------------+--------+
7 rows in set

SQL查询 找出每个系的平均工资

作为示例,考虑查询”找出每个系的平均工资“,该查询书写如下:

1
2
3
select dept_name, avg( salary) as avg_salary
from instructor
group by dept_name;

先分组 再查询

分组是计算查询结果的第一步。在每个分组上都要进行指定的聚集计算。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select dept_name, avg( salary) as avg_salary
from instructor
group by dept_name;
+------------+--------------+
| dept_name  | avg_salary   |
+------------+--------------+
| Biology    | 72000        |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000        |
| Finance    | 85000        |
| History    | 61000        |
| Music      | 40000        |
| Physics    | 91000        |
+------------+--------------+
7 rows in set

省略group by子句时整个关系作为一个分组

SQL查询 找出所有教师的平均工资

相反,考虑査询”找出所有教师的平均工资”。我们把此查询写做如下形式:

1
2
select avg(salary)
from instructor;

在这里省略了group by子句,因此整个关系被当作是一个分组。
1
2
3
4
5
6
7
8
mysql> select avg(salary)
from instructor;
+--------------+
| avg(salary)  |
+--------------+
| 74833.333333 |
+--------------+
1 row in set

SQL查询 找出每个系在2010年春季学期讲授一门课程的教师人数

作为在元组分组上进行聚集操作的另一个例子,考虑查询”找出每个系在2010年春季学期讲授一门课程的教师人数“。有关每位教师在每个学期讲授每个课程段的信息在teaches关系中。但是,这些信息需要与来自instructor关系的信息进行连接,才能够得到每位教师所在的系名。这样,我们把此查询写做如下形式:

1
2
3
4
select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester='Spring' and year =2010
group by dept_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester='Spring' and year =2010
group by dept_name;
+------------+-------------+
| dept_name  | instr_count |
+------------+-------------+
| Comp. Sci. |           3 |
| Finance    |           1 |
| History    |           1 |
| Music      |           1 |
+------------+-------------+
4 rows in set

没有出现在group by子句中的属性只能出现在select子句的聚集函数内部

任何没有出现在group by子句中的属性如果出现在select子句中的话,它只能出现在聚集函数内部,否则这样的查询就是错误的。
例如,下述查询是错误的,因为ID没有出现在group by子句中,但它出现在了select子句中,但没有出现在聚集函数内部。

1
2
3
select dept_name,ID, avg(salary)
from instructor
group by dept_name;

在一个特定分组(通过dept_name定义)中的每位教师都有一个不同的I,既然每个分组只输出个元组,那就无法确定选哪个D值作为输出。其结果是,SQL不允许这样的情况出现。

MySQL中没有出现再group by子句中的属性也可以出现在select子句聚集函数外面

经过我的测试MySQL是可以的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select dept_name,ID, avg(salary)
from instructor
group by dept_name;
+------------+-------+--------------+
| dept_name  | ID    | avg(salary)  |
+------------+-------+--------------+
| Biology    | 76766 | 72000        |
| Comp. Sci. | 10101 | 77333.333333 |
| Elec. Eng. | 98345 | 80000        |
| Finance    | 12121 | 85000        |
| History    | 32343 | 61000        |
| Music      | 15151 | 40000        |
| Physics    | 22222 | 91000        |
+------------+-------+--------------+
7 rows in set