SQL——聚集函数

1、定义

聚集函数是以值是一个集合（集或者多重集）为输入、返回单个值得函数。SQL提供了五个固有聚集函数。
平均值：avg
最小值：min
最大值：max
总和：sum
计数：count

2、基本聚集

以上五个固有聚集函数都是属于基本聚集
示例：

-- 找出教师的平均工资
SELECT AVG (salary)
FROM instructor

其他基本聚集使用形式差不多。

--找出名字的个数，这里的distinct作用是是的重复的元素不被计数
SELECT COUNT(DISTINCT name)
FROM instructor;

3、分组聚集

如果希望将聚集函数作用在单个元组集上，也希望作用到一组元组集上，此时可以利用group by子句来实现。

group by 子句作用：对给出的一个或多个属性来构造分组，将属性上取值相同的元组分到同一组中。

示例：

# 找出每个系的教师平均工资
SELECT dept_name, avg(salary) AS avg_salary
FROM instructor
GROUP BY dept_name;

# 找出教师的平均工资,则是省略了group by，将整个关系当成一个组别
SELECT AVG (salary)
FROM instructor

值得注意的是，当SQL查询使用分组的时候，需要保证出现在select语句中但没有被聚集的属性只能是出现在group by 子句中的那些属性。换句话说，任何没有出现在group by子句中的属性如果出现在select子句中的话，它只能出现在聚集函数的内部，否则这样的查询就是错误的，例如：

SELECT dept_name,id,avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name;

不过令我哭笑不得是，呃……，竟然运行没有报错[捂脸.jpg]，然后仔细观察了以下，原因是id是没有被聚集，所以是属于查询错误，
看下面的结果，类别biology的id只有10211，其实还有另外一个。在分组计算中只输出一个元组，这样是无法确定选择哪一个id作为输出，下一次运行结果id值可能为其他值。[如果你有什么新发现，望告知]

结果表 原表

4、having子句

有时候限定分组条件比对元组限定条件更有用。比如我们只对工资超过15000某一个系感兴趣。该条件并不针对某个元组，而是针对group by子句构成的分组。即是说，having子句是在分组之后才生效的，可以使用聚集函数。例如：

SELECT dept_name,avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name
HAVING avg(salary)>15000;

注意：与select子句的情况类似，任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中，否则查询就被当成是错误的。

包含聚集、group by 或者having子句的查询的含义可通过下述操作序列定义：

1、根据from子句计算出一个关系

2、如果出现where子句，where子句的谓词将应用到from子句的结果上

3、如果出现group by子句，满足where子句的元组通过group by子句形成分组。如果没有
group by子句，满足where谓词的整个元组集被当做一个分组

4、如果出现having子句，他将应用到每个分组上；不满足having子句谓词的分组将被抛弃。

5、select子句利用剩下的分组产生出查询结果中的元组，即每个分组上应用聚集函数来得到单个关系元组

5、对空值和布尔值的聚集

空值的出现对聚集运算带来了麻烦，例如下列句子：

select sum(salary)
from instructor;

当instructor关系有些元组在salary属性的值为空，则在查询待求和的值中就包含了空值。SQL标准并不认为总和本身为null，而是认为sum运算符应忽略输入中的null值（因为算术表达式如果有null，那么结果为null）。

所以，聚集函数根据以下原则处理空值：
除了count（*）外，所有的聚集函数都忽略输入集合中的空值。由于空值被忽略，可能会造成参加聚集函数的输入值集合为空集。规定空集的count运算值为0，其他所有聚集运算在输入为空集的情况下返回一个空值。

处理布尔值的聚集函数：some 和every。
从字面意义上就可以知道，some是只要满足其中任意一个条件即可，而every则是所有条件都要满足，比如说1=some（集合A），若A={1,2,3}，则为真，若A={0,2,3}则为假，又如1>some(集合A），结果分别为假、真。
例如：

#该示例是将表instructor根据dept_name分组，
#用having子句判断dept_name是否在关系表department中，
#然后select dept_name，和avg(salary)；
SELECT dept_name,avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name
HAVING dept_name = SOME (SELECT dept_name FROM department)

没有加having子句 加了having子句 department表 instructor表