Sql Certificate 简明教程

SQL - Using Group functions

Reporting Aggregate data using the Group functions

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

SQL has numerous predefined aggregate functions that can be used to write queries to produce exactly this kind of information.The GROUP BY clause specifies how to group rows from a data table when aggregating information, while the HAVING clause filters out rows that do not belong in specified groups.

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

Aggregate functions perform a variety of actions such as counting all the rows in a table, averaging a column’s data, and summing numeric data. Aggregates can also search a table to find the highest "MAX" or lowest "MIN" values in a column. As with other types of queries, you can restrict, or filter out the rows these functions act on with the WHERE clause. For example, if a manager needs to know how many employees work in an organization, the aggregate function named COUNT() can be used to produce this information.The COUNT() function shown in the below SELECT statement counts all rows in a table.

SELECT COUNT(*)
FROM employees;

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

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

The result table for the COUNT(*) function is a single column from a single row known as a scalar result or value. Notice that the result table has a column heading that corresponds to the name of the aggregate function specified in the SELECT clause.

如下为一些常用的集合函数 -

Some of the commonly used aggregate functions are as below -

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

ALL 和 DISTINCT 关键字是可选的，并执行与您已了解的书写 SELECT 子句时它们所执行的动作相同.ALL 关键字是选项允许时的默认值.语法中列出的表达式可以是常量、函数，或列名、变量和函数通过算术运算符连接的任何组合.但是，集合函数最常与列名一起使用.除了 COUNT 函数，所有集合函数不考虑 NULL 值.

The ALL and DISTINCT keywords are optional, and perform as they do with the SELECT clauses that you have learned to write.The ALL keyword is the default where the option is allowed.The expression listed in the syntax can be a constant,a function, or any combination of column names, constants, and functions connected by arithmetic operators.However, aggregate functions are most often used with a column name. Except COUNT function,all the aggregate functions do not consider NULL values.

使用集合时，您必须理解并遵循以下两条规则：

There are two rules that you must understand and follow when using aggregates:

Aggregate functions can be used in both the SELECT and HAVING clauses (the HAVING clause is covered later in this chapter).
Aggregate functions cannot be used in a WHERE clause. Its violation will produce the Oracle ORA-00934 group function is not allowed here error message.

Illustrations

下面的 SELECT 查询统计组织中的雇员数量.

The below SELECT query counts the number of employees in the organization.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

下面的 SELECT 查询返回组织中雇员薪金的平均值.

The below SELECT query returns the average of the salaries of employees in the organization.

SELECT AVG(Salary) average_sal
FROM employees;

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

下面的 SELECT 查询返回组织中雇员薪金的总和.

The below SELECT query returns the sum of the salaries of employees in the organization.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

下面的 SELECT 查询返回组织中雇员最早和最晚受雇日期.

The below SELECT query returns the oldest and latest hired dates of employees in the organization.

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 子句使您能够使用集合函数来回答更复杂管理问题，如：

Aggregate functions are normally used in conjunction with a GROUP BY clause. The GROUP BY clause enables you to use aggregate functions to answer more complex managerial questions such as:

每个部门中雇员的平均薪金是多少？

What is the average salary of employees in each department?

每个部门中有多少雇员？

How many employees work in each department?

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

How many employees are working on a particular project?

分组按功能根据列建立数据组，并且仅集合组中的信息.分组标准由在 GROUP BY 子句中指定的列定义.在此层次结构后，数据首先在组中组织，然后 WHERE 子句限制每组中的行.

Group by function establishes data groups based on columns and aggregates the information within a group only. The grouping criterion is defined by the columns specified in GROUP BY clause. Following this hierarchy, data is first organized in the groups and then WHERE clause restricts the rows in each group.

Guidelines of using GROUP BY clause

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

(1) All the dependent columns or columns used in GROUP BY function must form the basis of grouping, hence must be included in GROUP BY clause also.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

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

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

(2) GROUP BY clause does not support the use of column alias, but the actual names.

(3) GROUP BY 子句只能与诸如 SUM、AVG、COUNT、MAX 和 MIN 的集合函数一起使用.如果将其与单行函数一起使用，Oracle 会触发异常，如“ORA-00979：非 GROUP BY 表达式”.

(3) GROUP BY clause can only be used with aggregate functions like SUM, AVG, COUNT, MAX, and MIN.If it is used with single row functions,Oracle throws an exception as "ORA-00979: not a GROUP BY expression".

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

(4) Aggregate functions cannot be used in a GROUP BY clause. Oracle will return the "ORA-00934: group function not allowed" here error message.

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

Below query lists the count of employees working in each department.

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

同样，下面的查询用于查找每个部门中各自工作 ID 的工资总额。请注意，该组是基于部门和工作 ID 建立的。因此，它们出现在 GROUP BY 子句中。

Similarly, below query to find sum of salaries for respective job ids in each department. Note the group is established based on Department and Job id. So they appear in GROUP BY clause.

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

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

The below query also produces the same result. Please note that grouping is based on the department id and job id columns but not used for display purpose.

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

Use of DISTINCT, ALL keywords with Aggregate functions

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

By specifying DISTINCT keyword with the input parameter, group by function considers only the unique value of the column for aggregation. By specifying ALL keyword with the input parameter, group by function considers all the values of the column for aggregation, including nulls and duplicates. ALL is the default specification.

The HAVING clause

HAVING 子句用于聚合函数，就像 WHERE 子句用于列名和表达式一样。本质上，HAVING 和 WHERE 子句的作用相同，即根据条件从结果表中过滤行。虽然 HAVING 子句似乎可以过滤出组，但实际上它不能。相反，HAVING 子句过滤行。

The HAVING clause is used for aggregate functions in the same way that a WHERE clause is used for column names and expressions.Essentially,the HAVING and WHERE clauses do the same thing, that is filter rows from inclusion in a result table based on a condition. While it may appear that a HAVING clause filters out groups, it does not.Rather,a HAVING clause filters rows.

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

When all rows for a group are eliminated so is the group.To summarize, the important differences between the WHERE and HAVING clauses are:

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

A WHERE clause is used to filter rows BEFORE the GROUPING action (i.e., before the calculation of the aggregate functions).

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

A HAVING clause filters rows AFTER the GROUPING action (i.e., after the calculation of the aggregate functions).

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

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

The HAVING clause is a conditional option that is directly related to the GROUP BY clause option because a HAVING clause eliminates rows from a result table based on the result of a GROUP BY clause.

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