oracle分组函数_10级学员 郞志

Oracle分组函数
10级学员 郞志

一、        分组函数的简单介绍
1、 定义：分组函数作用于一组数据，并对一组数据返回一个值。
2、 分组函数的类型：
VG   求指定数据（列）平均值
           
           
ACOUNT    计算表的总记录数
    求指定数据（列）的最大值
           MIN    
           MAX    
                求指定数据（列）的最小值
           STDDEV（标准方差）
据使用AVG 和 SUM 函数。
           可以对
           SUM       求指定数据（列）的和
注：
           可以对数值型
数任意数据类型的数据使用 MIN 和 MAX 函数。
           COUNT(expr) 返回 expr不为空的记录总数。
 
3、 函数语法
         table
           [WHERE  condition]
          
           SELECT [column,] group_function(column), ...
           FROM     
  [GROUP BY     column]
           [ORDER BY      column];
 
二、        分组函数的使用方法，以及实例：
这里我以数据库中的scott用户下进行实例的编写。
1、 AVG（平均值）   
.21429
         2、COUNT（计数）   
                   SQL> select coun
           SQL>  select avg(sal) from emp;                计算工资的平均值
          AVG(SAL)
           ---------------
           207
3t(*) from emp;                   计算emp表总记录数
                  COUNT(*)
                     ----------
                    14
   
           SQL> select max(sal) from emp;                    得出最
                   SQL> select count(ename) from emp;此语句和上一条语句运行结果相同
2、 MAX（最大值）                                              
 大的工资数
                    MAX(SAL)
                    ----------
              5000
3、 MIN（最小值）
           SQL> select min(sal) from emp;                     得出最小的工资数
          MIN(SAL)
 29025
组函数的相关补充：
1、 DI
            ----------
       800
4、 SUM（合计）
           SQL> select sum(nvl(sal,0)) from emp;                计算工资的和
           SUM(NVL(SAL,0))
                    ---------------
        
 STINCT（distinct） 关键字
在输出时去除重复的数据，例如：
SQL> select count(distinct comm) from emp;              得出emp表格中奖不重复的记录数
 
COUNT(DISTINCTCOMM)
-------------------
     SQL> select avg(comm) from emp;
                              AV
4                                                                    
2、 组函数与空值     组函数是忽略空值的
           例如再计算emp表中奖金（comm）的平均值时就可以通过对比体现：
              
 G(COMM)
                                       ----------
                             550
                    SQL> select sum(comm)/count(*) from emp;
 
                    SUM(COMM)/COUNT(*)
        AVG(NVL(COMM,0))
             
                    ------------------
                   157.142857
           第一种使用组函数求得的平均数，明显与通过计算得出的结果不同。
当然也可以使用nvl函数解决忽略空值：
                   SQL> select avg(nvl(comm,0)) from emp;
 
  
        ----------------
            157.142857
3、分组数据：GROUP BY 子句语法
           可以使用GROUP BY 子句将表中的数据分成若干组
           举例说明：
                    SQL> select deptno,avg(sal) from emp group by deptno;
       2175
                        10 2916.
                    在emp表中根据部门分组，得出各部门的平均薪资。
                     DEPTNO   AVG(SAL)
                             ------- ----------
                         30 1566.66667
                         2
066667
注意：
           在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
比如上面语句中deptno必须在GROUP  BY子句中有，才能进行输出，但是也可以不输出列表。
在GROUP BY 子句中可以包含多个列
SQL> select job,deptno,sum(sal) from emp group by job,deptno;
 
ANAGER        10       2450
JOB        DEPTNO   SUM(SAL)
--------- ------- ----------
MANAGER        20       2975
PRESIDENT      10       5000
CLERK          10       1300
SALESMAN       30       5600
ANALYST        20       6000
MANAGER        30       2850

MCLERK          30        950
CLERK          20       1900
 
已选择9行。
 
4、非法使用组函数
a、上面已经介绍过在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
         b、组函数不能在where子句中使用。
         C、组函数可以在having子句中使用。
过滤分组： HAVING 子句
x(sal) from emp group by
         使用 HAVING 过滤分组:
                   1.      行已经被分组。
                   2.      使用了组函数。
                   3.      满足HAVING 子句中条件的分组将被显示。
         例子说明：
                   首先按deptno分组：
                            SQL> select deptno,m
a deptno;
 
                             DEPTNO   MAX(SAL)
                            ------- ----------
                        30       2850
                        20       3000
                      10       5000
                   然后在使用having子句对其进行过滤：
          首先我按部门分组得到各部门薪资的平均值。
           SQL> select avg(sal)  from emp group by deptno;
 
          
                            SQL> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
 
        DEPTNO   MAX(SAL)
                   ------- ----------
              10       5000
 
5、 嵌套组函数（就是在组函数中再调用一个组函数）
           在这里我分步对比一下：
         
        AVG(SAL)
                    ----------
                    1566.66667
                     2175
                    2916.66667
           然后我在利用嵌套得出平均薪资最高的部门的平均薪资。
                    SQL> select max(avg(sal)) from emp group by deptno;
 
                    MAX(AVG(SAL))
                    -------------
                  2916.66667