SQL - 使用分组函数

上一页
下一页

使用分组函数报告聚合数据

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 函数根据列建立数据组，并且仅聚合组内信息。分组标准由 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 here”。

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

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 函数考虑列的所有值进行聚合，包括 NULL 值和重复值。ALL 是默认规范。

HAVING 子句

HAVING 子句用于聚合函数，其方式与 WHERE 子句用于列名和表达式的方式相同。本质上，HAVING 和 WHERE 子句执行相同的操作，即根据条件过滤掉结果表中包含的行。虽然看起来 HAVING 子句过滤掉组，但实际上并非如此。相反，HAVING 子句过滤行。

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

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

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

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
打印页面
上一页
下一页
广告