分组聚合函数详细讲解

聚合函数总结

在实际SQL应用中，经常需要进行分组聚合，即将查询对象按一定条件分组，然后对每一 个组进行聚合分析。比如我们常见的一些分组计算需求：求某个部门的薪资总和，薪资平均 值，薪资最大值等等。

分组聚合函数也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以 是一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值。

1.分组聚合函数语法以及 SQL 语句执行过程

SQL中使用分组聚合函数的语法

SELECT [column,] group_function(column), ... FROM table

[WHERE condition]

[GROUP BY column][having] [ORDER BY column];

在 select 语句中使用 group by 子句将行划分成较小的组，然后，使用聚组函数返回每 一个组的汇总信息，另外，可以使用 having 子句限制返回的结果集。

查询语句的 select 和 group by,having，Order by 子句是分组聚合函数唯一出现的地方， 在 where 子句中不能使用分组聚合函数。

select department_id, sum(salary) from employees where salary>10000 group by department_id

having sum(salary) > 11000 order by sum(salary)

sql语句执行过程

（1） 按select找到where满足条件的元组形成结果表。 相当于：

select department_id, salary from employees

where salary>10000

（2） 将结果表按group中指定的列进行分组，该属性列值相等的元组为一组，通常会 在每组中作用集函数。（如果group指定多个列，先按第一列分组，然后在每个组中再按第二 列分组）

相当于：

select department_id, sum(salary) from employees where salary>10000 group by department_id

（3） 如果group子句带having短语，则分组聚合后只有满足having指定条件的组才输 出。

相当于：

select department_id, sum(salary)

from employees where

salary>10000 group by department_id

having sum(salary) > 11000

（4） 如果有order子句，则将结果表按order子句指定的列升序或降序排序。 相当于：

select department_id, sum(salary) from employees where salary>10000 group by department_id

having sum(salary) > 11000 order by sum(salary)

2.group by 可选项

什么时候可以无：非分组查询中 非分组查询中，聚合函数实际上等于将表中所有记录作

为一个组来运算。此时在 select 列表中指定的列只能是包含聚组函数，不能包含数据表本身的列。比如求所有员工的总工资。

select sum(salary) from employees

什么时候必须有：在分组查询中 分组查询中，聚合函数是将数据按分组关键字分组，然后对每一组的函数自变量中的内 容进行聚合运算。Select 子句字段可以是分组关键字（group by 后面字段）和聚合函数。 比 如求各个部门员工总工资。

注意：

a.如果没有group by 子句，select 列表中不允许出现字段与分组函数混用的情况。 b.在带有group by子句的查询语句中，在select列表中指定的列要么是group by 子 句

中指定的列，要么包含聚组函数。出现在select列表中的字段，如果不是包含在分组函数

中，那么该字段必须同时出在Group by子句中。

c.group by后面字段的顺序不同分组结果不同。

3. 条件查询（where 和 having）

在一个 sql 语句中可以有 where 子句和 having 子句。having 与 where 子句类似，均 用于设置限定条件。

where 子句的作用是在对查询结果进行分组前，将不符合 where 条件的行去掉，即在分 组之前过滤数据，条件中不能包含聚组函数，使用 where 条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组 函数，使用 having 条件显示特定的组，也可以使用多个分组标准进行分组。

group by 可以与 where 来搭配使用，where 只能在 group by 的前面，group by 后面 不能使用 where；where 只针对单记录，不针对记录组的搜索条件，子句能包括分组函数，当 用户要指定一个记录组的搜索条件只能使用 having 表示。

4.常用的聚合函数

4.1 计数（COUNT） COUNT()函数用来计算表中记录的个数(count(*))或者列中值的个数

(count(column))，

计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号 表示计算一个表中的所有记录。两种使用形式如下。

*COUNT(*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。

*COUNT(column)，计算column列包含的行的数目，如果该列中某行数据为NULL，则该行 不计入统计总数。

注意： COUNT()函数只对那些传递到函数（括号）中的参数不是NULL的行计数。 Select count(1) 和Select count(*) 返回的结果一样。

(1)使用COUNT(*)函数对表中的行数计数。 COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。 示例 查询employees表中的所有记录的行数。实例代码： select count(*) from employees

在该例中，SELECT语句中没有WHERE子句，那么认为表中的所有行都满足SELECT语句，所 以SELECT语句将返回表中所有行的计数。

如果DBMS在其系统表中存储了表的行数，COUNT(*)将很快地返回表的行数，因为这时， DBMS不必从头到尾读取表，并对物理表中的行计数，而直接从系统表中提取行的计数。而如 果DBMS没有在系统表存储表的行数，将具有NOT NULL约束的列作为参数，使用COUNT函数，则 可能更快地对表行计数。

（2）使用COUNT(column)函数对一列中的数据计数。 COUNT(column)函数可用于对一列中的数据值计数。与忽略了所有列的COUNT(*)函数不同，

COUNT(column)函数逐一检查一列（或多列）中的值，并对那些值不是NULL的行计数。

实例 查询多列中所有记录的行数

SELECT COUNT(employee_id) FROM employees;

3）使用COUNT(column)函数同时对多列中的数据计数 （

COUNT(column )函数不仅可用于对一列中的数据值计数，也可以对多列中的数据值计数。 如果对多列计数，则需要将要计数的多列连接后，作为COUNT(column )函数的参数。

实例 使用COUNT(column )函数对多列中的数据计数

SELECT COUNT(employee_id) AS c_eno, --employee_id不为空的记录个数

COUNT(salary) AS c_salary,--salary不为空的记录个数

COUNT(employee_id+salary) AS t_eno_salary--两者都不为空的个数

FROM employees;

在进行两列的连接时，如果它们的数据类型不一致，要使用CAST表达式将它们转换成相 同的数据类型。

SELECT COUNT(employee_id) AS c_eno,--employee_id不为空的记录个数

--department_id不为空的记录个数 COUNT(department_id) AS c_dno,

--两者都不为空的个数

COUNT(employee_id+cast(department_id as int)) as c_eno_dno FROM employees

如果在被连接的列中的任何一列有NULL值时，那么连接的结果为NULL，则该列不会被 COUNT( )函数计数。

4.2 求和(SUM)

求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下 SELECT SUM(column_name) FROM table_name

说明： 适用范围：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型 的。

对NULL值的处理：当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该 值。

示例：查找员工总工资大于10000的部门 select department_id, sum(salary) from employees

group by department_id having sum(salary) > 10000 order by department_id;

4.3 求平均值(AVG) 函数AVG()用于计算一列中数据值的平

均值。语法如下 SELECT AVG (column_name) FROM table_name

说明：

适用范围：与SUM( )函数一样，AVG()函数只能作用于数值型数据，即列column_name中 的数据必须是数值型的。

对NULL值的处理：在计算平均值时，AVG()函数将忽略NULL值。AVG()函数的执行过程实

际上是将一列中的值加起来，再将其和除以非NULL值的数目，等价于sum(column)/ count(column)。如果在某列中，所有行的值都是NULL，则AVG()函数将返回NULL值。

示例：

select avg(salary) as AVG1,

sum(salary) / count(*) as AVG2,

sum(salary) / count(salary) as AVG3, FROM employees

运行结果如图所示

可以发现得到了不同的结果。 实际上，

count(salary)a ”语句是等价的。因为 “avg(salary)”与“sum(salary)/s

avg(salary)语句的执行过程实际上是将salary列中的值加起来，再将其和除以非NULL值的数

a n (*)”则不然，目（也就等价于count(salary)）。而语句“sum(salary) / count因为 COUNT(*)

返回的是表中所有记录的个数，而不管salary列中的数值是否为NULL。

4.4 求最大值(MAX)，最小值(MIN) 当需要了解一列中的最大值时，可以使用MAX()函数；同

样，当需要了解一列中的最小值 时，可以使用MIN()函数。语法如下。

SELECT MAX (column_name) / MIN (column_name) FROM table_name 说明：

适用范围：列column_name中的数据可以是数值、字符串或是日期时间数据类型。 MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。

对 NULL 值的处理：确定列中的最大值（最小值）时，MAX、MIN 函数忽略 NULL 值。但是， 如果在该列中，所有行的值都是 NULL，则 MAX、MIN 函数将返回 NULL 值。

示例：获得各种工作的最高工资和最低工资

i max(salary), , min(salary) FROM employees group by SELECT job_id,

job_id;

注意：

在字符串数据类型中使用 MAX 和 MIN 时，对字符型数据的最大值，是按照首字母由 A～Z 的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相 同，则比较下一个字符，以此类推，返回结果的数据类型为字符型数据。

在日期数据类型中使用 MAX 和 MIN 时，对日期时间类型的数据也可以求其最大/最小值， 其大小排列就是日期时间的早晚，越早认为其值越小，返回结果的数据类型为日期型。

4.5 聚合函数的重值处理 前面介绍的几种聚合函数，可以作用于所选列中的所有数据（不

管列中的数据是否有重

置），也可以只对列中的非重值进行处理，即把重复的值只取一次进行聚合分析。当然，对 于 MAX()/MIN()函数来讲，重值处理意义不大。

可以使用 ALL 关键字指明对所选列中的所有数据进行处理，使用 DISTINCT 关键字指明对

所选列中的非重值数据进行处理。以 COUNT()函数为例，语法如下。

SELECT COUNT([ALL/DISTINCT] column_name) FROM table_name 说明：[ALL/DISTINCT]在缺省状态下，默认是 ALL 关键字，即不管是否有重 值，处理所有数据。其他聚合函数的用法与此相同。

例如查询工资部门编号列中存在的不同记录的数目：

d t M

employees;e select count(distinct department_id) FROM

5 ROLLUP运算符和cube运算符

5.1 ROLLUP运算符

在Group By 中使用Rollup 可以产生常规分组汇总行以及分组小计。 如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，然后对全表进行GROUP BY操作,最后进行union操作。

Rollup 后面跟了n个字段，就将进行n+1次分组，从左到右每次减少一个字段进行分组； 然后进行union。

Rollup一个字段：

select department_id,sum(salary) from employees group by rollup(department_id);

首先会对department_id进行GROUP BY,结果等价于：

select department_id,sum(salary) from employees group by department_id order by department_id;

然后对全表进行GROUP BY操作，结果等价于： select sum(salary) from employees 最后进行union操作。

意义：首先根据部门编号分组计算工资，然后对分组进行汇总合计，并且一次呈现结果， 这是常规分组函数无法做到的。

注意：由于第二步中没有department_id字段，和第一步产生的表union时，department_id 字段为空，以此类推。

Rollup多个字段

select department_id,job_id,sum(salary) from employees group by rollup(department_id,job_id);

首先会对(department_id,job_id)进行GROUP BY，相当于

select department_id, job_id, sum(salary) from employees

group by department_id, job_id order by department_id, job_id; 然后对(department_id)进行GROUP BY，相当于

select department_id, sum(salary) from employees

group by department_id order by department_id;

然后对全表进行GROUP BY操作，相当于

select sum(salary) from employees 最后union。

ab

3

c

意义：a是对各个部门按照job分组进行薪资汇总；b是对各个部门总体进行薪资汇总；c是对所有部门

进行薪资汇总。

5.2 cube运算符

在Group By 中使用Cube可以产生Rollup结果集+和多维度的交叉表数据源。

GROUP BY CUBE(A, B, C)，首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、 C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。

Cube 后面跟了n个字段，就将进行2的N次方的分组运算，然后进行union；

SELECT department_id, job_id, SUM(salary) FROM employees

GROUP BY cube(department_id, job_id); 首先会对(department_id,job_id)进行GROUP BY，相当于 select department_id, job_id, sum(salary) from employees

group by department_id, job_id

order by department_id, job_id; --24 rows

然后对(department_id)进行GROUP BY，相当于

select department_id, sum(salary) from employees

group by department_id

order by department_id; --12 rows

然后对(job_id)进行GROUP BY，相当于

select job_id, sum(salary) from employees group by job_id

order by job_id; --20 rows 然后对全表进行GROUP BY操作，相当于

select sum(salary) from employees; --1 rows 最后union。--57 rows

5.3 GROUPING函数

Rollup 和Cube有点抽象，他分别相当于n+1 和2的n次方常规Group by 运算；那么在 Rollup 和Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行分组运 算的结果的？ 答案是可以使用Grouping 函数； 没有被Grouping到返回1，否则返回0。

举例如下：

SELECT department_id,

job_id,

SUM(salary), GROUPING(department_id) GRP_DEPT,

GROUPING(job_id) GRP_JOB FROM employees

GROUP BY ROLLUP(department_id, job_id);

5.4 Grouping Set

Grouping Set单一字段

SELECT department_id, job_id, sum(salary) FROM employees

GROUP BY GROUPING SETS(department_id, job_id);

相当于先按department_id分组，等价于

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

然后按job_id分组，等价于

SELECT job_id, sum(salary) FROM employees GROUP BY job_id 最后结果union all到一起。

可以使用Grouping Set 来代替多次UNION:

Grouping Set组合字段

SELECT department_id, job_id, manager_id, sum(salary) FROM employees GROUP BY GROUPING SETS((department_id, job_id),(job_id, manager_id));

相当于先按department_id,job_id分组

SELECT department_id, job_id,sum(salary)

FROM employees

GROUP BY department_id,job_id 然后按job_id, manager_id分组

SELECT job_id, manager_id, sum(salary) FROM employees

GROUP BY job_id, manager_id;

union all。 最后

1

2

3

6. 自定义聚集函数

6.1 原理介绍

Oracle 提供了很多预定义好的聚集函数，比如前面讲的 Max(), Sum(), AVG()， 但是这 些预定义的聚集函数基本上都是适应于标量数据， 对于复杂的数据类型，比如说用户自定义 的 Object type, Clob 等，是不支持的。

但是，幸运的是， Oracle 的定制功能十分强大，允许用户定制自己的聚集函数。用户可 以通过实现 Oracle 的 Extensibility Framework 中的 ODCIAggregate interface 来创建自定 义聚集函数，而且自定义的聚集函数跟内建的聚集函数用法上没有差别。

通过实现 ODCIAggregate rountines 来创建自定义的聚集函数。可以通过定义一个对象 类型（Object Type），然后在这个类型内部实现 ODCIAggregate 接口函数(routines)， 可 以用任何一种 Oracle 支持的语言来实现这些接口函数，比如 C/C++, JAVA, PL/SQL 等。在这 个 Object Type 定义之后，相应的接口函数也都在该 Object Type Body 内部实现之后， 就 可以通过 CREATE FUNCTION 语句来创建自定义的聚集函数了。

每个自定义的聚集函数需要实现 4 个 ODCIAggregate 接口函数， 这些函数定义了任何一 个聚集函数内部需要实现的操作，这些函数分别是 initialization, iteration, merging 和 termination。

a.STATIC FUNCTION ODCIAGGREGATEINITIALIZE(s_obj IN OUT str_agg_typ e) RETURN NUMBER

自定义聚集函数初始化操作,从这儿开始一个聚集函数。初始化的聚集环境会以对象实例 传回给 oracle.

b.MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT str_agg_type, p_var IN VARCHAR2) RETURN NUMBER

自定义聚集函数最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例 子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的 计算结果进行关联

这个函数用来遍历需要处理的数据，被 oracle 重复调用。每次调用的时候，当前的聚集 环境和新的（一组）值会作为传入参数。 这个函数会处理这些传入值，然后返回更新后的聚 集环境.这个函数对每一个 NON-NULL 的值都会被执行一次。NULL 值不会被传递个聚集函数。

c.MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT str_agg_type, p_ obj IN str_agg_type) RETURN NUMBER

用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别 是处理并行查询聚集函数的时候.

这个函数用来把两个聚集环境整合在一起，一般用来并行计算中（当一个函数被设置成 enable parallel 处理的时候）。 d.FUNCTION ODCIAGGREGATETERMINATE(self IN str_agg_type, x_var OUT V ARCHAR2, p_flags IN NUMBER) RETURN NUMBER

终止聚集函数的处理,返回聚集函数处理的结果.

这个函数是 Oracle 调用的最后一个函数。它接收聚集环境作为参数，返回最后的聚集函 数处理的结果.

6.2 应用之字符串相 SUM（字符串相连接） 第一步：建立对象类型规范

create or replace type str_agg_type as object (

str VARCHAR2(300),

separator_flag VARCHAR2(64), --分隔符，默认用自由定义|，可以修改此处

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(s_obj IN OUT str_agg_type) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT str_agg_type, p_var IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT str_agg_type, p_obj IN str_agg_type) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN str_agg_type, x_var OUT VARCHAR2, p_flags IN NUMBER) RETURN NUMBER );

第二步：建立对象类型体

create or replace type body str_agg_type is

--自定义聚集函数初始化操作

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(s_obj IN OUT str_agg_type) RETURN NUMBER is

begin

s_obj:= str_agg_type('',',');--初始化为空，用逗号分隔

RETURN ODCICONST.SUCCESS; end;

--定义函数的功能，实现字符串sum（拼接）

MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT str_agg_type, p_var IN VARCHAR2) RETURN NUMBER is

begin

self.str:= self.str || p_var||self.separator_flag;

RETURN ODCICONST.SUCCESS; end;

--用来合并两个聚集函数的两个不同的指针对应的结果，此处默认即可

MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT str_agg_type, p_obj IN str_agg_type) RETURN NUMBER is

begin

RETURN ODCICONST.SUCCESS; end;

--定义终止聚集函数的处理,返回聚集函数处理的结果

MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN str_agg_type, x_var OUT VARCHAR2, p_flags IN NUMBER) RETURN NUMBER is

begin

IF x_var IS NOT NULL THEN

x_var:= SUBSTR(self.str,1,LENGTH(self.str)-1);

ELSE

x_var:= self.str;

END IF;

RETURN ODCICONST.SUCCESS;

end;

end;

第三步：创建自定义聚集函数

create or replace function sum_str(p_str in varchar2) return varchar2 AGGREGATE USING str_agg_type; 第四步：使用自定义聚集函数

找出部门号为 60,70,80 的部门员工名称集合

SQL> SELECT department_id,sum_str(last_name) as ename FROM employees where department_id in('60','70','80') GROUP BY department_id; 输出：

DEPARTMENT_ID ENAME

------------------------------------------------------------------

60 Hunold,Ernst,Austin,Lorentz,Pataballa, 70 Baer,

80 Russell,Doran,Smith,McEwen,Sully,King,Tuvault,

Cambrault,Olsen,Hall,Livingston,Ta

注意：如果希望自定义的聚集函数和系统预定义的聚集函数同名，比如

create or replace function sum(p_str in varchar2) return varchar2 AGGREGATE USING str_agg_type;

那么在使用该自定义聚集函数是必须指定函数所属用户，否则报错。 SQL> SELECT department_id,sum(last_name) as ename FROM employees where department_id in('60','70','80') GROUP BY department_id;

ORA-01722: invalid number

SQL> SELECT department_id,xqq.sum(last_name) as ename FROM employees

where department_id in('60','70','80') GROUP BY department_id; DEPARTMENT_ID ENAME

------------------------------------------------------------------

60 Hunold,Ernst,Austin,Lorentz,Pataballa, 70 Baer,

80 Russell,Doran,Smith,McEwen,Sully,King,Tuvault,

Cambrault,Olsen,Hall,Livingston,Ta

6.2 应用之模拟预定义COUNT

创建自定义对象类型规范 创建自定义对象类型体 创建自定义聚集函数 使用自定义聚集函数

第一步：创建自定义对象类型规范

create or replace type count_num_type as object (

num number,

static function ODCIAggregateInitialize(p_type In Out count_num_type) return number,

member function ODCIAggregateIterate(self In Out count_num_type,

p_num In number) return number,

member function ODCIAggregateMerge(self In Out count_num_type,

p_type In count_num_type) return number,

member function ODCIAggregateTerminate(self In Out count_num_type,

x_num Out number,

flags In number) return number

);

第二步：创建自定义对象类型体

create or replace type body count_num_type is

static function ODCIAggregateInitialize(p_type In Out count_num_type) return number is begin

p_type:=count_num_type(0); return ODCIConst.Success; end;

member function ODCIAggregateIterate(self In Out count_num_type,

p_num In number) return number is

begin self.num:=self.num+1; return ODCIConst.Success; end; member function ODCIAggregateMerge(self In Out count_num_type,

p_type In count_num_type) return number is

begin

self.num := self.num;

return ODCIconst.Success; end;

member function ODCIAggregateTerminate(self In Out count_num_type,

x_num Out number,