聚合函数同时对一组行进行操作,对每组返回一行输出结果。
注意:
由于聚合函数都是对成组的行进行操作,因此聚合函数也被称为分组函数(group function)
下表列出了部分常用的聚合函数,这些函数全部返回一个number类型的值。
函数 | 说明 |
---|---|
avg(x) | 返回x的平均值 |
count(x) | 返回一个包含x的查询返回的行数 |
max(x) | 返回x的最大值 |
median(x) | 返回x的中间值 |
min(x) | 返回x的最小值 |
stddev(x) | 返回x的标准差 |
sum(x) | 返回x的和 |
variance(x) | 返回x的方差 |
在使用聚合函数时要注意以下几个问题:
- 聚合函数可用于任何有效的表达式。例如:可以对数字、字符串和日期使用count()、max()和min()函数。
- 聚合函数会忽略空值。这是因为空值说明该值未知,因此不适合于聚合函数的计算。
- distinct关键字可以与聚合函数一起使用,这样可以在聚合函数的计算中排除重复项。
1. avg()
avg(x)函数用于计算x的平均值。下面这个查询计算职员的平均工资;注意emp表中的sal列被作为参数传递给了avg()函数;
SQL> select avg(sal) from emp;
AVG(SAL)
---------------
2073.21429
聚合函数中可以使用任意有效的表达式。例如,下面这个查询向avg()函数传递的参数是表达式sal+200;意思是为每行的sal列值加200,然后再返回这些值的平均值;
SQL> select avg(sal+200) from emp;
AVG(SAL+200)
---------------
2273.21429
DISTINCT关键字可以用于从分组计算中排除重复的值。例如,下面这个查询在使用avg()函数计算平均值时,使用distinct关键字来排除sal列中重复的值:
SQL> select avg(distinct sal) from emp;
AVG(DISTINCT SAL)
----------------
2064.58333
2.count()
count(x)函数用于计算查询返回的行数。下面这个查询使用count()函数得到emp表中的行数(准确地说是不为null的行数):
SQL> select count(deptno) from emp;
COUNT(DEPTNO)
-------------
14
提示:
在使用count()函数时要避免使用星号(*),因为这样count()在返回结果时需要的时间可能会更长。相反,应该使用表中的某一列或者使用rowid伪列,rowid伪列包含了该行在数据库内部的位置。
下面这个例子向count()函数传递rowid参数,并返回emp表中的行数:
SQL> select count(rowid) from emp;
COUNT(ROWID)
------------
14
3.max()和min()
max()和min()用于计算x的最大值和最小值。下面这个查询显示了使用max()和min()函数计算emp表中sal列的最大值和最小值:
SQL> select max(sal),min(sal) from emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
max()和min()函数可以用于任何数据类型,包括字符串和日期。在对字符串使用max()函数时,字符串按照字母表顺序排序,字符串的“最大值”位于列表的底部,“最小值”位于列表的顶部。例如,在这种列表中,字符串ADAMS就会出现在WARD之前。
SQL> select max(ename),min(ename) from emp;
MAX(ENAME) MIN(ENAME)
---------- ----------
WARD ADAMS
在使用日期的情况下,日期的“最大值”是最迟的日期,“最小值”是最早的日期。下面这个查询使用max()和min()函数计算emp表中hiredate的最大值和最小值:
SQL> select max(hiredate),min(hiredate) from emp;
MAX(HIREDATE) MIN(HIREDATE)
-------------- --------------
23-5月 -87 17-12月-80
4.stddev()
stddev(x)用于计算x的标准差。标准差是一个统计函数,其定义是方差的平方根。
下面这个查询使用stddev()函数计算emp表中sal列值的标准差:
SQL> select stddev(sal) from emp;
STDDEV(SAL)
-----------
1182.50322
5.sum()
sum(x)用于计算并返回x中所有值之和。下面这个查询使用sum()函数得到emp表中sal列之和:
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
6.variance()
variance(x)用于计算x的方差。方差是一个统计函数,其定义为一组样本数据的偏离程度,等于标准差的平方。
下面这个例子使用variance()函数计算emp表中sal列的方差:
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
注解:本文中所使用的数据库为oracle数据库中user为scott下的表emp中的数据;如下所示:
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。