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)

----------

SQL> select count(all sal) from emp;

COUNT(ALLSAL)

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

SQL> select count(distinct sal) from emp;

COUNT(DISTINCTSAL)

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

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过滤数据

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聚合函数详解
分享链接：http://cdiso.cn/article/gjsigg.html

Oracle聚合函数详解

其他资讯