Oracle分组函数

 

 

一、 分组函数的简单介绍

1、 定义：分组函数作用于一组数据，并对一组数据返回一个值。

2、 分组函数的类型：

AVG 求指定数据（列）平均值

COUNT 计算表的总记录数

MAX 求指定数据（列）的最大值

MIN 求指定数据（列）的最小值

STDDEV（标准方差）

SUM 求指定数据（列）的和

注：

可以对数值型数据使用AVG 和SUM 函数。

可以对任意数据类型的数据使用MIN 和MAX 函数。

COUNT(expr)返回 expr不为空的记录总数。

3、 函数语法

SELECT [column,]group_function(column), ...

FROM table

[WHERE condition]

[GROUP BY column]

[ORDER BY column];

二、 分组函数的使用方法，以及实例：

这里我以数据库中的scott用户下进行实例的编写。

1、 AVG（平均值）

SQL> select avg(sal) from emp; 计算工资的平均值

AVG(SAL)

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

2073.21429

2、COUNT（计数）

SQL> select count(*) fromemp; 计算emp表总记录数

COUNT(*)

----------

14

SQL> select count(ename)from emp;此语句和上一条语句运行结果相同

2、 MAX（最大值）

SQL>select max(sal) from emp; 得出最大的工资数

MAX(SAL)

----------

5000

3、 MIN（最小值）

SQL>select min(sal) from emp; 得出最小的工资数

MIN(SAL)

----------

800

4、 SUM（合计）

SQL>select sum(nvl(sal,0)) from emp; 计算工资的和

SUM(NVL(SAL,0))

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

29025

组函数的相关补充：

1、 DISTINCT（distinct） 关键字

在输出时去除重复的数据，例如：

SQL> select count(distinct comm) fromemp; 得出emp表格中奖金不为空的记录数

COUNT(DISTINCTCOMM)

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

4

2、 组函数与空值 组函数是忽略空值的

例如再计算emp表中奖金（comm）的平均值时就可以通过对比体现：

SQL>select avg(comm) from emp;

AVG(COMM)

----------

550

SQL>select sum(comm)/count(*) from emp;

SUM(COMM)/COUNT(*)

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

157.142857

第一种使用组函数求得的平均数，明显与通过计算得出的结果不同。

当然也可以使用nvl函数解决忽略空值：
SQL> selectavg(nvl(comm,0)) from emp;

AVG(NVL(COMM,0))

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

157.142857

3、分组数据：GROUP BY 子句语法

可以使用GROUP BY 子句将表中的数据分成若干组

举例说明：

SQL> selectdeptno,avg(sal) from emp group by deptno;

在emp表中根据部门分组，得出各部门的平均薪资。

DEPTNO AVG(SAL)

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

30 1566.66667

20 2175

10 2916.66667

注意：

在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

比如上面语句中deptno必须在GROUP BY子句中有，才能进行输出，但是也可以不输出列表。

在GROUP BY 子句中可以包含多个列

SQL> select job,deptno,sum(sal) from empgroup 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行。

4、非法使用组函数

a、上面已经介绍过在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

b、组函数不能在where子句中使用。

C、组函数可以在having子句中使用。

过滤分组： HAVING 子句

使用 HAVING 过滤分组:

1. 行已经被分组。

2. 使用了组函数。

3. 满足HAVING 子句中条件的分组将被显示。

例子说明：

首先按deptno分组：

SQL> selectdeptno,max(sal) from emp group by deptno;

DEPTNO MAX(SAL)

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

30 2850

20 3000

10 5000

然后在使用having子句对其进行过滤：

SQL> select deptno,max(sal) from emp group by deptnohaving max(sal)>3000;

DEPTNO MAX(SAL)

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

10 5000

5、 嵌套组函数（就是在组函数中再调用一个组函数）

在这里我分步对比一下：

首先我按部门分组得到各部门薪资的平均值。

SQL>select avg(sal) from emp group bydeptno;

AVG(SAL)

----------

1566.66667

2175

2916.66667

然后我在利用嵌套得出平均薪资最高的部门的平均薪资。

SQL>select max(avg(sal)) from emp group by deptno;

MAX(AVG(SAL))

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

2916.66667