当前位置:数据库 > Oracle >>

Oracle分组与组函数

Oracle分组与组函数
 
一、求和与求平均
1、什么是组函数
组函数就是我们前面我说过的多行函数。它主要针对表进行操作。因为只有表中,才有多行数据。下面我们以SUM和AVG这两个组函数为例,先从它们开始,介绍组函数的特性。  www.zzzyk.com  
2、SUM 求和函数
gyj@OCM> select sum(salary) from t1;
SUM(SALARY)
-----------
     315888
 ORACLE在执行SUM()函数时,会从SALARY列取出每一行,把结果累加到一起。我们在讲单行函数和多行函数时就说过,对于单行函数,将列中每一行数据看作一个个体,单行函数分别对每个个体进行计算。而多行函数则把列中所有行数据看作一个整体,多行函数对这个整体中的所有元素,统一的做求和、求平均、统计最大或最小值等运算,无论此整体中包含多少行,最终返回的结果只有一个。一个总的和或平均值,或最大值,或最小值,等等。单行函数对每一行单独进行处理,每一行它都返回一个结果。
 gyj@OCM> select id,sum(salary) from t1;
select id,sum(salary) from t1
       *
ERROR at line 1:
ORA-00937: not a single-group group function
  www.zzzyk.com  
这是很容易理解的,id(编号)列有10行数据,而SUM(salary)只有一行结果。一个10行,一个只有一行,Oracle没办法将这两个列组合在一起。列可以和单行函数组合使用,因为单行函数每一行都返回一个结果,列中有10行,就返回10个结果。如:
gyj@OCM> select id,round(salary,1) from t1;
        ID ROUND(SALARY,1)
---------- ---------------
         2            8000
         3           10000
         4           15000
         5               0
         7           12000
         6           12000
         1           12000
3、WHERE 条件 与组合函数
在没有条件时,组合函数默认对列中所有行数据进行处理。我们可以使用条件,限制求和的行数。比如,我现在只想求一下姓名都为Joe的月薪的总和。
gyj@OCM> select sum(salary) from t1 where name='Joe';
SUM(SALARY)
-----------
      22000
4、VAG求平均值
 AVG和SUM的用法完全相同,它是在SUM求后和,再除以行数,就得到一个平均值。例如:
 gyj@OCM> select avg(salary) from t1;
AVG(SALARY)
-----------
 17549.3333
 5、DISTINCT
DISTINCT的俢作用是去除重复值,这我们在上一课有过描述。它也可以用在组函数中,下面我们看看效果:
gyj@OCM> select sum(distinct salary) from t1;
SUM(DISTINCTSALARY)
-------------------
             162888
6、NULL值的影响
首先,对于SUM()函数来说,多了个NULL值不会有任何的影响。SUM()函数仅仅是忽略NULL值。
gyj@OCM> insert into t1 values(26,'rose',null,null);
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select sum(salary) from t1;
SUM(SALARY)
-----------
     315888
结果仍是315888。刚才没有添加NULL值前,求和的结果也是315888。
对来求平均函数来说,它是先求和,再除以行数。那么,NULL值应不应该被计算进行数内呢。也就是说,现在salary列中有22行,其中有4行为NULL。求和的结果是315888,那么,在求平均时,是用总和数315888除以22呢,还是应该除以18。在默认情况下,是除以18。也就是说,ORACLE的组函数中,对于NULL值通常采取的都是不理不采,直接跳过,忽略它。我们来看一下求平均的结果:
gyj@OCM> select avg(salary) from t1;
AVG(SALARY)
-----------
 17549.3333
 再来看看这个结果是不是315888除以18:
gyj@OCM> select 315888/18 from dual;
 315888/18
----------
17549.3333
的确,是315888除以18。空值被简单的忽略了,就好像从来没有它
但有时我们不能简单的忽略空。空值也是一行数据。它可能刚刚加入,还没有产生业绩等数据,但它也是一行数据。有时不能把他从行中剔除。这个时候,NVL()函数就派上用场了。先用NVL()函数,把空值转换为0,再进行组函数的计算,空值就不会被忽略了,使用形式如下:
gyj@OCM> select avg(nvl(salary,0)) from t1;
AVG(NVL(SALARY,0))
------------------
        14358.5455
函数的嵌套,我们在前面也说过。也处理里层的函数,将处理结果传给外层,由外层处理。当遇到空值时,先由NVL将之转换为0,再由AVG()将0纳入到求平均的计算中。空会被AVG忽略,而0是不会被忽略的。
下面再验证一下:
gyj@OCM> select 315888/22 from dual;
 315888/22
----------
14358.5455
315888除以22,结果就是14358.5455。空值已经不被忽略了。
 
二、最大值 MAX 与最小值 MIN
在掌握前面两个函数的基础上,这两个函数的使用就非常简了。
gyj@OCM> select max(salary),min(salary) from t1;
MAX(SALARY) MIN(SALARY)
----------- -----------
      30000           0
SALARY列中的最大值是30000,最小值是0。NULL值不算。组函数都会忽略NULL值。当然,我们可以使用NVL将空值转换为非空:
gyj@OCM> select max(salary),min(nvl(salary,0)) from t1;
MAX(SALARY) MIN(NVL(SALARY,0))
----------- ------------------
      30000                  0
 
三、COUNT 统计函数
统计某列中满足条件行的数目。比如:
gyj@OCM> select count(salary) from t1 where salary>5000;
COUNT(SALARY)
-------------
           17
统计SALARY列大于5000的行的数目。COUNT(salary)括号中的salary列,可以换为* :
gyj@OCM> select count(*) from t1 where salary>5000;
  COUNT(*)
----------
        17
换为*后,是统计t1表中,满足salary>5000这个条件的行的数目。这和COUNT(salary)是差不多的。有一点不同,下面我们把条件去掉:
gyj@OCM>  select count(*) from t1;
  COUNT(*)
----------
        22
这将统计t1表中总共有多少行。*号表示,这个统计,是针对所有列的。而如果将COUNT(*)换为COUNT(salary),如下:
gyj@OCM> select count(salary) from t1;
COUNT(SALARY)
-------------
           18
这将只统计SALARY列有多少行数据。和其他函数一样,COUNT将忽略空值。
在COUNT中,也可以使用DISTINCT去掉重复值,只统计非重复行的数目:
gyj@OCM> select count(distinct salary) from t1;
COUNT(DISTINCTSALARY)
---------------------
                   11
在salary列,的确只有11种值。当然,空值仍然不被计算在内,但可以使用NVL将空值转换为0。
 
四、分组与组函数
1、分组 GROUP BY
分组的语法是:GROUP BY 列名1,列名2,……
分组的作用是根据某些列的值,将这些列值相同的行分为一组。这就是分组。分组和组函数和在一起,可以完成非常强大的功能。组函数从名字上就表现出来了,“组”(重读)函数吗,针对分组的函数。下面我们看一下t3测试表:
gyj@OCM> select * from t3;
        ID NAME           SALARY
---------- ---------- ----------
         1
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,