Sql Certificate 简明教程

SQL - Using Group functions

Reporting Aggregate data using the Group functions

SQL 具有许多预定义的聚合函数，可用于编写查询以生成这类信息。GROUP BY 子句指定了在聚合信息时如何对数据表中的行进行分组，而 HAVING 子句则筛选出不属于指定组的行。

聚合函数执行各种操作，例如对表中的所有行进行计数，计算列的平均值和对数字数据求和。聚合还可以搜索表以查找列中的最高“MAX”值或最低“MIN”值。与其他类型的查询一样，WHERE 子句还可以限制或筛选这些函数所作用的行。例如，如果经理需要知道组织中有多少员工，则下面 SELECT 语句中显示的名为 COUNT( ) can be used to produce this information.The 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 分组函数不可用在此错误消息.

Illustrations

下面的 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 子句限制每组中的行.

Guidelines of using GROUP BY clause

(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：非 GROUP BY 表达式”.

(4) 无法在 GROUP BY 子句中使用集合函数.Oracle 会返回“ORA-00934：此处不允许分组函数”错误消息.

如下查询列出在每个部门工作的雇员数.

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;

Use of DISTINCT, ALL keywords with Aggregate functions

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

The HAVING clause

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