Oracle聚合函数详解
Oracle 聚合函数详解
专注于为中小企业提供成都做网站、网站制作、成都外贸网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业德令哈免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上千多家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。
一 聚合函数的定义
聚合函数也叫组函数，有的地方也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数据，聚合函数对一组行中的某个列执行计算并返回单一的值。聚合函数经常与 SELECT语句的 GROUP BY子句一同使用,所以有的时候也把其称之为分组函数。有一点需要注意的是，除了count和grouping之后，其它的统计运算均会忽略值为null的列。
二 聚合函数的分类
1: AVG(DISTINCT|ALL)
ALL表示对所有的值求平均值,DISTINCT只对不同的值求平均值
SQL> select avg(sal) from scott.emp;
AVG(SAL)
----------
2073.21429
SQL> select avg(distinct sal) from scott.emp;
AVG(DISTINCTSAL)
----------------
2064.58333
SQL> select avg(all sal) from scott.emp;
AVG(ALLSAL)
-----------
2073.21429
注意事项：
SQL> select avg(comm) from emp;
AVG(COMM)
----------
550 -------------550=sum(comm)/4其中4是comm不为null的员工数
SQL> select sum(comm)/14 from emp;
SUM(COMM)/14
------------
157.142857
SQL> select avg(nvl(comm,0)) from emp;
AVG(NVL(COMM,0))
----------------
157.142857
2: MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> select max(all sal) from emp;
MAX(ALLSAL)
-----------
5000
SQL> select max(distinct sal) from emp;
MAX(DISTINCTSAL)
----------------
5000
SQL> select max(hiredate) from emp;
MAX(HIRED
---------
23-MAY-87
3: MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(sal) from emp;
MIN(SAL)
----------
800
SQL> select min(all sal) from emp;
MIN(ALLSAL)
-----------
800
SQL> select min(distinct sal) from emp;
MIN(DISTINCTSAL)
----------------
800
SQL> select min(hiredate),min(to_char(sysdate,'yyyy-mm-dd hh34:mi:ss')) from emp;
MIN(HIRED MIN(TO_CHAR(SYSDATE
--------- -------------------
17-DEC-80 2014-08-23 22:10:49
4: STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from emp;
STDDEV(SAL)
-----------
1182.50322
SQL> select stddev(all sal) from emp;
STDDEV(ALLSAL)
--------------
1182.50322
SQL> select stddev(distinct sal) from emp;
STDDEV(DISTINCTSAL)
-------------------
1229.95096
5: VARIANCE(DISTINCT|ALL)
求协方差 ALL表示对所有的值求协方差,DISTINCT表示只对不同的值求协方差
SQL> select variance(sal) from emp;
VARIANCE(SAL)
-------------
1398313.87
SQL> select variance(all sal) from emp;
VARIANCE(ALLSAL)
----------------
1398313.87
SQL> select variance(distinct sal) from emp;
VARIANCE(DISTINCTSAL)
---------------------
1512779.36
6: SUM(DISTINCT|ALL)
求和 ALL表示对所有值求和，DISTINCT表示只对不同值求和(相同值只取一次)
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
SQL> select sum(all sal) from emp;
SUM(ALLSAL)
-----------
29025
SQL> select sum(distinct sal) from emp;
SUM(DISTINCTSAL)
----------------
24775
7：COUNT(DISTINCT|ALL)
求记录、数据个数。 ALL对所有记录，数组做统计， DISTINCT只对不同值统计(相同值只取一次)
SQL> select count(sal) from emp;
COUNT(SAL)
----------
14
SQL> select count(all sal) from emp;
COUNT(ALLSAL)
-------------
14
SQL> select count(distinct sal) from emp;
COUNT(DISTINCTSAL)
------------------
12
8： MEDIAN
求中位数
SQL> select median(sal) from emp;
MEDIAN(SAL)
-----------
1550
SQL> select median(all sal) from emp;
MEDIAN(ALLSAL)
--------------
1550
SQL> select median(distinct sal) from emp;
select median(distinct sal) from emp
*
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function --错误：DISTINCT选项在此函数中禁用。
三 Group by子句
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;
DEPTNO NUM
---------- ----------
10 3
20 5
30 6
2 每个部门员工的平均工资
SQL> select deptno,avg(sal) from emp group by deptno;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;
DEPTNO AVG(NVL(SAL,0))
---------- ---------------
30 1566.66667
20 2175
10 2916.66667
3 每个部门员工的工资+奖金
SQL> select deptno,avg(sal+nvl(comm,0)) from emp group by deptno;
DEPTNO AVG(SAL+NVL(COMM,0))
---------- --------------------
30 1933.33333
20 2175
10 2916.66667
SQL> select deptno,avg(nvl(sal,0)+nvl(comm,0)) from emp group by deptno;
DEPTNO AVG(NVL(SAL,0)+NVL(COMM,0))
---------- ---------------------------
30 1933.33333
20 2175
10 2916.66667
注意：group by 子句中的列不必包含在SELECT 列表中
4 求出某个部门中相同职位的员工人数 group by后可以跟多个分组的字段
SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;
DEPTNO JOB COUNT(*)
---------- --------- ----------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
9 rows selected.
5 非法使用组函数
(1)所用包含于SELECT列表中，而未包含于组函数中的列都必须包含于 GROUP BY子句中。
举例：
SQL> select deptno,count(job) from emp;
select deptno,count(job) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
正确写法如下：
SQL> select deptno,count(job) from emp group by deptno;
DEPTNO COUNT(JOB)
---------- ----------
30 6
20 5
10 3
(2) 不能在 WHERE子句中使用组函数（注意）。
SQL> select deptno from emp where count(job)>0 group by deptno;
备注ERROR at line 1: ORA-00933: SQL command not properly ended
此处不允许使用分组函数
(3) Having子句
HAVING子句对 GROUP BY子句设置条件的方式与 WHERE子句和 SELECT语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING语法与 WHERE语法类似，但 HAVING可以包含聚合函数。HAVING子句可以引用选择列表中出现的任意项。
备注：having子句通常与group by子句结合使用
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
查询部门员工人数大于3的部门编号
SQL> select deptno,count(*) from emp group by deptno having count(*)>3 order by deptno;
DEPTNO COUNT(*)
---------- ----------
20 5
30 6
注意：
1 group by后不可以接别名
SQL> select deptno d,sum(sal) from emp group by deptno;
D SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
SQL> select deptno d,sum(sal) from emp group by d;
select deptno d,sum(sal) from emp group by d
*
ERROR at line 1:
ORA-00904: "D": invalid identifier
2 group by后不能接数字
SQL> select job,sum(sal) from emp group by 1;
select job,sum(sal) from emp group by 1
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select job,sum(sal) from emp group by job;
JOB SUM(SAL)
--------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
3 group by后可以接select后没有的列
SQL> select sum(sal) from emp group by deptno;
SUM(SAL)
----------
9400
10875
8750
4 select后出现的列，在group by后必须全部出现
SQL> select job,deptno,sum(sal) from emp group 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 rows selected.
SQL> select job,deptno,sum(sal) from emp group by job;
select job,deptno,sum(sal) from emp group by job
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select job,deptno,sum(sal) from emp group by deptno;
select job,deptno,sum(sal) from emp group by deptno
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
4 group by后不能使用where，因为where是在分组之前起作用的，分组后的数据在进行过滤需要使用having
SQL> select deptno,avg(sal) from emp group by deptno where deptno>10;
select deptno,avg(sal) from emp group by deptno where deptno>10
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select deptno,avg(sal) from emp group by deptno having deptno>10;
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
5 group by之前可以使用where过滤数据
A:
SQL> select deptno,sum(sal) from emp where deptno!=30 group by deptno having sum(sal)>5000;
DEPTNO SUM(SAL)
---------- ----------
20 10875
10 8750
B：
SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>5000 and deptno!=30;
DEPTNO SUM(SAL)
---------- ----------
20 10875
10 8750
性能：能在where能过滤数据不要在having里过滤，A和B都能达到同样的目的，但是A性能相对好一些，因为A现将deptno=30的数据筛选出来，然后在将筛选的数据放入到临时表空间内进行分组；而B将全部的数据都读到临时表空间内，然后在临时表空间进行筛选数据，这样一来B就需要更大的临时表空间进行分组筛选，索引性能较差；
参考与--- http://www.2cto.com/database/201305/214634.html
文章名称：Oracle聚合函数详解
文章URL：http://ybzwz.com/article/gjsigg.html
