4.3 组函数

  第3章介绍过单行函数，是针对每行数据计算出结果。而本章介绍的组函数，是对多行的数据集合进行操作，对每组给出一个结果。如果没有对数据集合进行分组的话，则是把整个数据集合当作一组，给出一个结果。

4.3.1 组函数引入

  上述查询公司所有雇员中，薪水小于IT部门里最高薪水的所有雇员，其SQL语句如下：

SELECT employee_id,last_name, salary, department_id FROM employees
WHERE salary < ANY(SELECT salary FROM employees WHERE department_id = 60)

  在该SQL语句中，使用了“< ANY子查询”的方式完成了案例要求。虽然完成了案例要求，但可以有更便捷的方法。也就是可以通过MAX组函数，直接获得IT部门里薪水最高的员工的薪水值，让这个查询作为单行子查询，放入主查询中直接进行比较，其SQL语句如下：

SELECT employee_id,last_name, salary, department_id FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees WHERE department_id = 60)

  执行该SQL语句，也返回80行记录。从SQL语句的子查询中可以看出，MAX(salary)函数直接获得了IT部门中最高的薪水值。

4.3.2 组函数概述

  组函数是对多行数据进行操作，在操作时有如下注意事项：

• 组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。

• 组函数默认考虑重复值。为了让组函数不考虑重复值，可以使用DISTINCT关键字。

• 用于函数参数的数据类型可以是CHAR、VARCHAR2、NUMBER 或 DATE。

  下面列举了一些常用的组函数，如表4.1所示，其中输入参数用input表示，代表字段名或表达式。

表4.1 常用组函数

  因为雇员表中的记录比较多，为了方便演示，所以仅选了部门编号为60的雇员信息参与计算，先看一下图4.8显示的该部门雇员信息。

图4.8 IT部门雇员信息

  对该数据集使用MAX、MIN、AVG和SUM这四个组函数，SQL语句如下，运行结果如图4.9所示。

SELECT MAX(salary) AS "最高薪", MIN(salary) AS "最低薪", AVG(salary) AS "平均薪水", SUM(salary) AS "薪水总和" 
FROM employees WHERE department_id = 60

图4.9 组函数使用

  修改该SQL语句，将AVG(salary)和SUM(salary)这两个组函数修改为AVG(DISTINCT salary)和SUM(DISTINCT salary)，运行结果如图4.10所示。

图4.10 组函数使用

  使用DISTINCT关键字的目的是去除重复，SUM(DISTINCT salary)函数仅对不重复的行进行求和。图4.8中，编号为105和106的员工薪水重复，所以仅取一行数据参与求和，故AVG(DISTINCT salary)实际是对9000、6000、4800和4200求和，得到的结果是24000。同理，AVG(DISTINCT salary)函数也仅对这四个数求平均值，结果为6000。

  需要注意的是，MAX和MIN函数不仅可以使用在数字类型上，还可以用于任意数据类型。而AVG、SUM以及STDDEV和VARIANCE仅能用在数字类型上。

  接下来介绍COUNT函数，COUNT函数的参数除了可以是字段值和表达式外，还可以是“”。如果是字段值或表达式，则和其他组函数一样，忽略空值且考虑重复值；如果是“”，则计算所有的行，也包括空值。

  如果想获取公司中所有雇员的人数、有佣金的雇员人数以及共有多少种佣金百分比，其SQL语句如下：

SELECT COUNT(*) AS "总人数",COUNT(commission_pct) AS "拿佣金人数" ,COUNT(DISTINCT commission_pct) AS "佣金种数" 
FROM employees

  执行该SQL语句，结果如图4.11所示。

图4.11 COUNT组函数使用

  上面的例子中，统计雇员总人数时，是对“*”进行COUNT。如果COUNT函数里的参数是字段manager_id，执行SQL语句后，统计出的人数是106名，并不是正确的数值。其原因在于，雇员表中老板这条记录的manager_id字段为空，COUNT(manager_id)是忽略空值的，所以没有统计老板。

  为了解决这个问题，可以使用NVL函数将空值置为某值，使组函数在进行统计、计算时，不忽略该行，具体SQL语句如下：

SELECT COUNT(NVL(manager_id,0)) AS "总人数" FROM employees

  执行该SQL语句，统计出总人数为107人。

4.3.3 创建分组

  直到现在，所有的组函数都是针对整个数据集进行的操作。接下来，会将整个数据集分成若干个分组，针对每个分组使用组函数进行操作。

  用GROUP BY子句可以把表中的数据集合划分为若干个组。GROUP BY 子句后面的表达式用于指定分组的依据。GROUP BY 子句置于WHERE子句后、ORDER BY子句前，后面的表达式必须包含字段，且不能使用列的别名。

  在使用GROUP BY进行分组时，必须注意，如果SELECT子句中包含了组函数，就不能选择单独的字段，除非这个字段出现在GROUP BY子句后面。

  假设想获得每个部门最高的薪水值，并显示出部门编号和薪水，其SQL语句如下：

SELECT department_id,MAX(salary) 
FROM employees GROUP BY department_id

  执行该SQL语句，显示结果如图4.12所示。

图4.12 GROUP BY使用

  注意，在SELECT子句中，只可以有组函数和分组字段（包括对分组字段的操作），如果包括其他字段会报错。另外，如果GROUP BY子句后要包括ORDER BY子句，则ORDER BY子句用于排序的字段必须是分组字段或组函数。例如要将刚才获取的结果按照各部门最高薪水值进行从高到底的排序，其SQL语句如下：

SELECT department_id, MAX(salary) FROM employees 
GROUP BY department_id ORDER BY MAX(salary) DESC

  执行SQL语句，运行结果如图4.13所示。

图4.13 对分组结果进行排序

  读者可以尝试一下，修改上面的SQL语句，分别在SELECT子句和ORDER BY子句中增加salary或其他字段，执行SQL语句，看看会出现什么样的结果。

4.3.4 分组中再分组

  前面对所有雇员按照部门进行了分组，在每个部门中还有不同的职位，有时需要在部门分组的基础上再对职位进行分组，这就是分组中再分组。

  假设现在想算出每个部门每个职位中哪个雇员的薪水最高，并显示出部门编号、职位编号和薪水，显示时按部门编号和职位编号升序排序，其SQL语句如下：

SELECT department_id, job_id, MAX(salary) 
FROM employees GROUP BY department_id, job_id
ORDER BY department_id, job_id

  执行该SQL语句，部分显示结果如图4.14所示。

图4.14 分组中再分组

图4.15 分组前使用WHERE子句约束行

  这里需要强调的是，WHERE子句必须在GROUP BY子句前，而且从含义上来说，也是先进行约束之后再进行分组。所以，WHERE子句中选择的字段，并非必须为分组字段，但也肯定不能是组函数。

  那如果要对分组后的结果进行约束，比如说只显示最高薪水大于12000元的结果，该如何操作呢？这时候就需要使用HAVING子句对分组结果进行约束。HAVING子句必须在GROUP BY子句之后，在ORDER BY子句之前。完成上面需求的SQL语句如下：

SELECT department_id, MAX(salary) FROM employees 
WHERE department_id IN(50,60,80,90) 
GROUP BY department_id HAVING MAX(salary) > 12000

  执行该SQL语句，运行结果如图4.16所示。

图4.16 HAVING子句使用

  请读者思考一下，可以在HAVING子句中使用非分组字段、组函数吗？