oracle聚合函数介绍 oracle聚合函数嵌套

聚合函数也叫组函数，有的地方也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数据，聚合函数对一组行中的某个列执行计算并返回单一的值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数。

1: AVG(DISTINCT|ALL)

ALL表示对所有的值求平均值,DISTINCT只对不同的值求平均值。

例如：select avg(sal) from scott.emp;

2: MAX(DISTINCT|ALL)

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次。

例如：select max(sal) from emp;

3: MIN(DISTINCT|ALL)

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次。

例如：select min(sal) from emp;

4: STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

例如： select stddev(sal) from emp;

5: VARIANCE(DISTINCT|ALL)

求协方差 ALL表示对所有的值求协方差,DISTINCT表示只对不同的值求协方差

例如： select variance(sal) from emp;

6: SUM(DISTINCT|ALL)

求和 ALL表示对所有值求和，DISTINCT表示只对不同值求和(相同值只取一次)

例如： select sum(sal) from emp;

7：COUNT(DISTINCT|ALL)

求记录、数据个数。 ALL对所有记录，数组做统计， DISTINCT只对不同值统计(相同值只取一次)

例如： select count(sal) from emp;

8： MEDIAN

求中位数

例如：select median(sal) from emp;

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

注意：group by子句一定要与分组函数结合使用,否则没有意义

1 求出每个部门的人数

SQL> select deptno,count(*) num from emp group by deptno order by deptno;

2 每个部门员工的平均工资

SQL> select deptno,avg(sal) from emp group by deptno;

3 每个部门员工的工资+奖金

SQL> select deptno,avg(sal+nvl(comm,0)) from emp group by deptno;

4 求出某个部门中相同职位的员工人数 group by 后可以跟多个分组的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

5 非法使用组函数

(1) 所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。

(2) 不能在 WHERE 子句中使用组函数（注意）。

SQL> select deptno from emp where count(job)>0 group by deptno;

(3) Having 子句

HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

注意事项：

1 group by后不可以接别名

例如： select deptno d,sum(sal) from emp group by deptno; 正确的。

select deptno d,sum(sal) from emp group by d;错误的。

2 group by后不能接数字

SQL> select job,sum(sal) from emp group by 1;错误的。

3 group by后可以接select后没有的列

SQL> select sum(sal) from emp group by deptno;错误的。

4 select 后出现的列，在group by后必须全部出现

SQL> select job,deptno,sum(sal) from emp group by job,deptno;正确的。

5 group by后不能使用where，因为where是在分组之前起作用的，分组后的数据在进行过滤需要使用having

SQL> select deptno,avg(sal) from emp group by deptno where deptno>10;错误的。

SQL> select deptno,avg(sal) from emp group by deptno having deptno>10;正确的。

6 group by之前可以使用where过滤数据

A:

SQL> select deptno,sum(sal) from emp where deptno!=30 group by deptno having sum(sal)>5000;

B：

SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>5000 and deptno!=30;

性能：能在where能过滤数据不要在having里过滤，A和B都能达到同样的目的，但是A性能相对好一些，因为A现将deptno=30的数据筛选出来，然后在将筛选的数据放入到临时表空间内进行分组；而B将全部的数据都读到临时表空间内，然后在临时表空间进行筛选数据，这样一来B就需要更大的临时表空间进行分组筛选，索引性能较差；