Oracle分组函数

Oracle分组函数

一、       分组函数的简单介绍

1、 定义：分组函数作用于一组数据，并对一组数据返回一个值。

2、 分组函数的类型：

           AVG   求指定数据（列）平均值

           COUNT    计算表的总记录数

           MAX         求指定数据（列）的最大值

           MIN                   求指定数据（列）的最小值

           STDDEV（标准方差）

           SUM      求指定数据（列）的和

注：

           可以对数值型数据使用AVG 和SUM 函数。

           可以对任意数据类型的数据使用MIN 和MAX 函数。

           COUNT(expr)返回 expr不为空的记录总数。

 

3、 函数语法

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

           FROM               table

           [WHERE  condition]

           [GROUP BY     column]

           [ORDER BY      column];

 

二、       分组函数的使用方法，以及实例：

这里我以数据库中的scott用户下进行实例的编写。

1、 AVG（平均值）  

           SQL>  select avg(sal) from emp;                计算工资的平均值

          AVG(SAL)

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

           2073.21429

         2、COUNT（计数）   

                   SQL> select count(*) fromemp;                   计算emp表总记录数

                  COUNT(*)

                     ----------

                    14

                   SQL> select count(ename)from emp;此语句和上一条语句运行结果相同

2、 MAX（最大值）                                                  

           SQL>select max(sal) from emp;                    得出最大的工资数

                    MAX(SAL)

                    ----------

              5000

3、 MIN（最小值）

           SQL>select min(sal) from emp;                     得出最小的工资数

          MIN(SAL)

            ----------

      800

4、 SUM（合计）

           SQL>select sum(nvl(sal,0)) from emp;                计算工资的和

           SUM(NVL(SAL,0))

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

         29025

组函数的相关补充：

1、 DISTINCT（distinct） 关键字

在输出时去除重复的数据，例如：

SQL> select count(distinct comm) fromemp;              得出emp表格中奖金不为空的记录数

 

COUNT(DISTINCTCOMM)

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

4                                                                   

2、 组函数与空值     组函数是忽略空值的

           例如再计算emp表中奖金（comm）的平均值时就可以通过对比体现：

                    SQL>select avg(comm) from emp;

                              AVG(COMM)

                                       ----------

                            550

                    SQL>select sum(comm)/count(*) from emp;

 

                    SUM(COMM)/COUNT(*)

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

                  157.142857

           第一种使用组函数求得的平均数，明显与通过计算得出的结果不同。

当然也可以使用nvl函数解决忽略空值：
                   SQL> selectavg(nvl(comm,0)) from emp;

 

           AVG(NVL(COMM,0))

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

           157.142857

3、分组数据：GROUP BY 子句语法

           可以使用GROUP BY 子句将表中的数据分成若干组

           举例说明：

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

                    在emp表中根据部门分组，得出各部门的平均薪资。

                     DEPTNO  AVG(SAL)

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

                         30 1566.66667

                         20      2175

                        10 2916.66667

注意：

           在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

比如上面语句中deptno必须在GROUP  BY子句中有，才能进行输出，但是也可以不输出列表。

在GROUP BY 子句中可以包含多个列

SQL> select job,deptno,sum(sal) from empgroup by job,deptno;

 

JOB        DEPTNO  SUM(SAL)

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

MANAGER        20      2975

PRESIDENT      10      5000

CLERK          10       1300

SALESMAN       30      5600

ANALYST        20      6000

MANAGER        30      2850

MANAGER        10      2450

CLERK          30        950

CLERK          20       1900

 

已选择9行。

 

4、非法使用组函数

a、上面已经介绍过在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

         b、组函数不能在where子句中使用。

         C、组函数可以在having子句中使用。

过滤分组： HAVING 子句

         使用 HAVING 过滤分组:

                   1.      行已经被分组。

                   2.      使用了组函数。

                   3.      满足HAVING 子句中条件的分组将被显示。

         例子说明：

                   首先按deptno分组：

                            SQL> selectdeptno,max(sal) from emp group by deptno;

 

                             DEPTNO  MAX(SAL)

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

                        30      2850

                        20      3000

                      10      5000

                   然后在使用having子句对其进行过滤：

                            SQL> select deptno,max(sal) from emp group by deptnohaving max(sal)>3000;

 

        DEPTNO   MAX(SAL)

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

              10      5000

 

5、 嵌套组函数（就是在组函数中再调用一个组函数）

           在这里我分步对比一下：

                    首先我按部门分组得到各部门薪资的平均值。

           SQL>select avg(sal)  from emp group bydeptno;

 

                 AVG(SAL)

                    ----------

                    1566.66667

                     2175

                    2916.66667

           然后我在利用嵌套得出平均薪资最高的部门的平均薪资。

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

 

                    MAX(AVG(SAL))

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

                  2916.66667