使用组功能

使用 Group 函数报告汇总数据

SQL 有许多预定义的聚合函数，可用于编写查询以准确生成此类信息。 GROUP BY 子句指定聚合信息时如何对数据表中的行进行分组，而 HAVING 子句过滤掉不属于指定的组。

聚合函数执行多种操作，例如对表中的所有行进行计数、对列数据求平均以及对数值数据求和。聚合还可以搜索表以查找列中的最高“MAX”或最低“MIN”值。与其他类型的查询一样，您可以使用 WHERE 子句限制或过滤掉这些函数所作用的行。例如，如果经理需要知道在一个组织中有多少员工工作，可以使用名为 COUNT(*) 的聚合函数来生成此信息。下面 SELECT 语句中显示的 COUNT(*) 函数计算一个组织中的所有行桌子。

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

COUNT(*) 函数的结果表是来自单行的单列，称为标量结果或值。请注意，结果表的列标题与 SELECT 子句中指定的聚合函数的名称相对应。

一些常用的聚合函数如下 –

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

ALL 和 DISTINCT 关键字是可选的，它们的作用与您学习编写的 SELECT 子句一样。ALL 关键字是允许选项的默认关键字。语法中列出的表达式可以是常量、函数、或由算术运算符连接的列名、常量和函数的任意组合。但是，聚合函数最常与列名一起使用。除 COUNT 函数外，所有聚合函数均不考虑 NULL 值。

使用聚合时，您必须了解并遵循两条规则：

集合函数可以在 SELECT 和 HAVING 子句中使用（HAVING 子句将在本章后面介绍）。
不能在 WHERE 子句中使用聚合函数。它的违反会产生 Oracle ORA-00934 group function is not allowed here 错误信息。

插图

下面的 SELECT 查询计算组织中的员工人数。

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

下面的 SELECT 查询返回组织中员工工资的平均值。

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

下面的 SELECT 查询返回组织中员工的工资总和。

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

下面的 SELECT 查询返回组织中员工的最早和最晚雇用日期。

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

通过…分组

聚合函数通常与 GROUP BY 子句结合使用。GROUP BY 子句使您能够使用聚合函数来回答更复杂的管理问题，例如：

每个部门员工的平均工资是多少？

每个部门有多少员工？

有多少员工在从事特定项目？

Group by 函数基于列建立数据组，并仅聚合组内的信息。分组标准由 GROUP BY 子句中指定的列定义。按照这个层次结构，数据首先按组组织，然后 WHERE 子句限制每个组中的行。

使用 GROUP BY 子句的准则

(1) GROUP BY 函数中使用的所有依赖列或列必须构成分组的基础，因此也必须包含在 GROUP BY 子句中。

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) GROUP BY 子句不支持使用列别名，而是使用实际名称。

(3) GROUP BY 子句只能与 SUM、AVG、COUNT、MAX 和 MIN 等聚合函数一起使用。如果与单行函数一起使用，Oracle 会抛出异常“ORA-00979: not a GROUP BY expression” .

(4) 不能在 GROUP BY 子句中使用聚合函数。Oracle 将在此处返回“ORA-00934: group function not allowed”错误消息。

下面的查询列出了在每个部门工作的员工人数。

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

类似地，在下面的查询中查找每个部门中各个职位 ID 的工资总和。请注意，该组是根据部门和工作 ID 建立的。所以它们出现在 GROUP BY 子句中。

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

下面的查询也产生相同的结果。请注意，分组基于部门 ID 和作业 ID 列，但不用于显示目的。

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

将 DISTINCT、ALL 关键字与聚合函数一起使用

通过使用输入参数指定 DISTINCT 关键字，group by 函数只考虑列的唯一值进行聚合。通过使用输入参数指定 ALL 关键字，group by 函数会考虑列的所有值进行聚合，包括空值和重复值。ALL 是默认规范。

HAVING 子句

HAVING 子句用于聚合函数的方式与 WHERE 子句用于列名和表达式的方式相同。本质上，HAVING 和 WHERE 子句做同样的事情，即根据条件过滤包含在结果表中的行. 虽然 HAVING 子句可能会过滤掉组，但实际上并没有。相反，HAVING 子句会过滤行。

当一个组的所有行都被消除时，该组也会被消除。总而言之，WHERE 和 HAVING 子句之间的重要区别是：

WHERE 子句用于在 GROUPING 操作之前（即，在计算聚合函数之前）过滤行。

HAVING 子句在 GROUPING 操作之后（即，在计算聚合函数之后）过滤行。

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

HAVING 子句是与 GROUP BY 子句选项直接相关的条件选项，因为 HAVING 子句根据 GROUP BY 子句的结果从结果表中删除行。

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function