分组聚合函数详细讲解.doc

聚合函数总结在实际SQL应用中，经常需要进行分组聚合，即将查询对象按一定条件分组，然后对每一 个组进行聚合分析。比如我们常见的一些分组计算需求：求某个部门的薪资总和，薪资平均 值，薪资最大值等等。分组聚合函数也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以 是一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值。1.分组聚合函数语法以及 SQL 语句执行过程SQL中使用分组聚合函数的语法SELECT column, group_function(column), . FROM tableWHERE conditionGROUP BY columnhaving ORDER BY column;在 select 语句中使用 group by 子句将行划分成较小的组，然后，使用聚组函数返回每 一个组的汇总信息，另外，可以使用 having 子句限制返回的结果集。查询语句的 select 和 group by,having，Order by 子句是分组聚合函数唯一出现的地方， 在 where 子句中不能使用分组聚合函数。select department_id, sum(salary)from employees where salary10000 group by department_idhaving sum(salary) 11000 order by sum(salary)sql语句执行过程（1） 按select找到where满足条件的元组形成结果表。 相当于：select department_id, salary from employeeswhere salary10000（2） 将结果表按group中指定的列进行分组，该属性列值相等的元组为一组，通常会 在每组中作用集函数。（如果group指定多个列，先按第一列分组，然后在每个组中再按第二 列分组）相当于：select department_id, sum(salary)from employees where salary10000 group by department_id（3） 如果group子句带having短语，则分组聚合后只有满足having指定条件的组才输 出。相当于：select department_id, sum(salary)from employees where salary10000 group by department_idhaving sum(salary) 11000（4） 如果有order子句，则将结果表按order子句指定的列升序或降序排序。 相当于：select department_id, sum(salary)from employees where salary10000 group by department_idhaving sum(salary) 11000order 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_dnoFROM employees如果在被连接的列中的任何一列有NULL值时，那么连接的结果为NULL，则该列不会被 COUNT( )函数计数。4.2 求和(SUM)求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下 SELECTSUM(column_name) FROMtable_name说明： 适用范围：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。对NULL值的处理：当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该值。示例：查找员工总工资大于10000的部门select department_id, sum(salary)from employeesgroup by department_id having sum(salary) 10000 order by department_id;4.3 求平均值(AVG) 函数AVG()用于计算一列中数据值的平均值。语法如下 SELECTAVG (column_name) FROMtable_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运行结果如图所示s可以发现得到了不同的结果。 实际上，“avg(salary)”与“sum(salary)/acount(salary)”语句是等价的。因为anavg(salary)语句的执行过程实际上是将salary列中的值加起来，再将其和除以非NULL值的数 目（也就等价于count(salary)）。而语句“sum(salary) / count(*)”则不然，因为 COUNT(*) 返回的是表中所有记录的个数，而不管salary列中的数值是否为NULL。4.4 求最大值(MAX)，最小值(MIN) 当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。语法如下。SELECTMAX (column_name) / MIN 说明：(column_name) FROMtable_name适用范围：列column_name中的数据可以是数值、字符串或是日期时间数据类型。 MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。对 NULL 值的处理：确定列中的最大值（最小值）时，MAX、MIN 函数忽略 NULL 值。但是， 如果在该列中，所有行的值都是 NULL，则 MAX、MIN 函数将返回 NULL 值。示例：获得各种工作的最高工资和最低工资i,SELECT job_id, max(salary), min(salary) FROM employees group byjob_id;注意：在字符串数据类型中使用 MAX 和 MIN 时，对字符型数据的最大值，是按照首字母由 AZ 的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相 同，则比较下一个字符，以此类推，返回结果的数据类型为字符型数据。在日期数据类型中使用 MAX 和 MIN 时，对日期时间类型的数据也可以求其最大/最小值， 其大小排列就是日期时间的早晚，越早认为其值越小，返回结果的数据类型为日期型。4.5 聚合函数的重值处理 前面介绍的几种聚合函数，可以作用于所选列中的所有数据（不管列中的数据是否有重置），也可以只对列中的非重值进行处理，即把重复的值只取一次进行聚合分析。当然，对 于 MAX()/MIN()函数来讲，重值处理意义不大。可以使用 ALL 关键字指明对所选列中的所有数据进行处理，使用 DISTINCT 关键字指明对 所选列中的非重值数据进行处理。以 COUNT()函数为例，语法如下。SELECT COUNT(ALL/DISTINCT column_name)FROM table_name说明：ALL/DISTINCT在缺省状态下，默认是 ALL 关键字，即不管是否有重 值，处理所有数据。其他聚合函数的用法与此相同。例如查询工资部门编号列中存在的不同记录的数目：dtMeselect count(distinctdepartment_id) FROM employees;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 employeesgroup by department_id, job_id order by department_id, job_id;然后对(department_id)进行GROUP BY，相当于select department_id, sum(salary)from employeesgroup by department_id order by department_id;然后对全表进行GROUP BY操作，相当于select sum(salary) from employees 最后union。1ab42c3意义：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 employeesGROUP BY cube(department_id, job_id); 首先会对(department_id,job_id)进行GROUP BY，相当于 select department_id, job_id, sum(salary)from employeesgroup by department_id, job_idorder by department_id, job_id; -24 rows然后对(department_id)进行GROUP BY，相当于select department_id, sum(salary)from employeesgroup by department_idorder by department_id; -12 rows然后对(job_id)进行GROUP BY，相当于select job_id, sum(salary)from employees group by job_idorder by job_id; -20 rows然后对全表进行GROUP BY操作，相当于select sum(salary) from employees; -1 rows 最后union。-57 rows5.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_JOBFROM employeesGROUP BY ROLLUP(department_id, job_id);5.4 Grouping Set可以使用Grouping Set 来代替多次UNION:Grouping Set单一字段SELECT department_id, job_id, sum(salary) FROM employeesGROUP BY GROUPING SETS(department_id, job_id);相当于先按department_id分组，等价于SELECT department_id, sum(salary) FROM employees GROUP BYdepartment_id然后按job_id分组，等价于SELECT job_id, sum(salary) FROM employees GROUP BY job_id最后结果union all到一起。Grouping Set组合字段SELECT department_id, job_id, manager_id, sum(salary) FROM employeesGROUP BY GROUPING SETS(department_id, job_id),(job_id, manager_id);相当于先按department_id,job_id分组SELECT department_id, job_id,sum(salary) FROM employeesGROUP BY department_id,job_id然后按job_id, manager_id分组SELECT job_id, manager_id, sum(salary) FROM employeesGROUP BY job_id, manager_id;最后union all。1236. 自定义聚集函数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_objIN str_agg_type) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN str_agg_type, x_varOUT 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 isbegins_obj:= str_agg_type(,);-初始化为空，用逗号分隔RETURN ODCICONST.SUCCESS;end;-定义函数的功能，实现字符串sum（拼接）MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT str_agg_type, p_var IN VARCHAR2) RETURN NUMBER isbeginself.str:= self.str | p_var|self.separator_flag; RETURN ODCICONST.SUCCESS;end;-用来合并两个聚集函数的两个不同的指针对应的结果，此处默认即可MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT str_agg_type, p_objIN str_agg_type) RETURN NUMBER is beginRETURN ODCICONST.SUCCESS;end;-定义终止聚集函数的处理,返回聚集函数处理的结果MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN str_agg_type, x_varOUT VARCHAR2, p_flags IN NUMBER) RETURN NUMBER is beginIF x_var IS NOT NULL THENx_var:= SUBSTR(self.str,1,LENGTH(self.str)-1);ELSEx_var:= self.str;end;END IF;RETURN ODCICONST.SUCCESS;end;第三步：创建自定义聚集函数create or replace function sum_str(p_str in varchar2) return varchar2AGGREGATE 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 varchar2AGGREGATE 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 numberSQL 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,Ta6.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(selfIn Out count_num_type, p_num In number) return number,member function ODCIAggregateMerge(selfIn Out count_num_type, p_type In count_num_type) return number,member function ODCIAggregateTerminate(selfIn Out count_num_type, x_num Out number,flags In number) return number);第二步：创建自定义对象类型体create or replace type body count_num_type isstatic function ODCIAggregateInitialize(p_type In Out count_num_type)return number is beginp_type:=count_num_type(0); return ODCIConst.Success; end;member function ODCIAggregateIterate(selfIn Out count_num_type,p_num In number) return number isbegin self.num:=self.num+1; return ODCIConst.Success; end;member function ODCIAggregateMerge(selfIn Out count_num_type,p_type In count_num_type) return number isbeginself.num := self.num; return ODCIconst.Success; end;member function ODCIAggregateTerminate(selfIn Out count_num_type, x_num Out number,beginx_num:= self.num;return ODCIConst.Success;end;flags In number) return number isend;第三步：创建自定义聚集函数CREATE OR REPLACE FUNCTION count_num(input number) RETURN numberPARALLEL_ENABLE AGGREGATE USING count_num_type;第四步：使用自定义聚集函数select department_id, count_num(employee_id),count(employee_id) from employees group by department_id6.3 应用之模拟预定义SUM创建自定义对象类型规范创建自定义对象类型体创建自定义聚集函数使用自定义聚集函数第一步：创建自定义对象类型规范create or replace type sum_num_type as object(num number,static function ODCIAggregateInitialize(p_type In Out sum_num_type)return number,member function ODCIAggregateIterate(selfIn Out sum_num_type, p_num In number) return number,member function ODCIAggregateMerge(selfIn Out sum_num_type, p_type In sum_num_type) return number,member function ODCIAggregateTerminate(selfIn Out sum_num_type, x_num Out number,flags In number) return number);第二步：创建自定义对象类型体create or replace type body sum_num_type isstatic function ODCIAggregateInitialize(p_type In Out sum_num_type)return number is beginp_type:=sum_num_type(0); return ODCIConst.Success; end;member function ODCIAggregateIterate(selfIn Out sum_num_type,p_num In number) return number isbegin self.num:=self.num+p_num; return ODCIConst.Success; end;member function ODCIAggregateMerge(selfIn Out sum_num_type,p_type In sum_num_type) return number isbeginself.num := self.num; return ODCIconst.Success; end;member function ODCIAggregateTerminate(selfIn Out sum_num_type, x_num Out number,flags In number) return number isbeginx_num:= self.num;return ODCIConst.Success;end;end;第三步：创建自定义聚集函数CREATE OR REPLACE FUNCTION sum_num(input number) RETURN numberPARALLEL_ENABLE AGGREGATE USING sum_num_type;第四步：使用自定义聚集函数select department_id, sum_num(salary),sum(salary) from employees group by department_id;6.4 应用之模拟预定义AVG创建自定义对象类型规范创建自定义对象类型体创建自定义聚集函数使用自定义聚集函数第一步：创建自定义对象类型规范create or replace type avg_num_type as object(num number,-总数sum number,-总和static function ODCIAggregateInitialize(p_type In Out avg_num_type)return number,member function ODCIAggregateIterate(selfIn Out avg_num_type, p_num In number) return number,member function ODCIAggregateMerge(selfIn Out avg_num_type, p_type In avg_num_type) return number,member function ODCIAggregateTerminate(selfIn Out avg_num_type, x_num Out number,flags In number) return number);第二步：创建自定义对