Oracle学习（4）：分组函数

分组函数

什么是分组函数？

分组函数作用于一组数据，并对一组数据返回一个值

组函数的类型

lAVG lCOUNT lMAX lMIN lSUM

数值求和

SQL> --求员工工资总额

SQL> select sum(sal) from emp;


SUM(SAL)
----------
29025

求数据条数

SQL> --求员工的人数
SQL> select count(*)from emp;


COUNT(*)
----------
14

求平均

SQL> --平均工资
SQL> select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;


方式一 方式二
---------- ----------
2073.21429 2073.21429


SQL> --奖金的平均值
SQL> select sum(comm)/count(*) 方式一,sum(comm)/count(comm) 方式二, avg(comm) 方式三
2 from emp;


方式一 方式二 方式三
---------- ---------- ----------
157.142857 550 550


SQL> select count(*), count(comm) from emp;


COUNT(*) COUNT(COMM)
---------- ----------- （组函数自动滤空）
14 4


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 13-7月 -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 13-7月 -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行。

组函数自动滤空

SQL> --组函数自动滤空
SQL> select count(*), count(nvl(comm,0)) from emp;


COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14

最大最小值

SQL> --最高工资 最低工资
SQL> select max(sal),min(sal) from emp;


MAX(SAL) MIN(SAL)
---------- ----------
5000 800


SQL> host cls

分组数据

group by分组

SQL> --group by分组：求各个部门的平均工资
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno;


DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667


SQL> ed
已写入 file afiedt.buf


1 select deptno,job,avg(sal)
2 from emp
3 group by deptno,job
4* order by 1
SQL> /


DEPTNO JOB AVG(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 950
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400

已选择9行。

**********************************************************************************************

注意：

在SELECT列表中所有未包含在组函数中的列都应该包含

在GROUPBY子句中。

包含在GROUPBY 子句中的列不必包含在SELECT 列表中

**********************************************************************************************

group by作用于多列

SQL> --group by作用于多列：先按照第一列分组；如果相同，再按照第二列分组
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)>2000;


DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667

having:在分组的基础上，再过滤

SQL> --having:在分组的基础上，再过滤
SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 where depno=10;
where depno=10
*
第 4 行出现错误:
ORA-00933: SQL 命令未正确结束
**********************************************************************

（where后面不能跟组函数！！！）

不能在 WHERE 子句中使用组函数（注意）。 可以在 HAVING 子句中使用组函数。 **********************************************************

SQL> ed
已写入 file afiedt.buf


1 select deptno,avg(sal)
2 from emp
3 where deptno=10
4* group by deptno
5 /


DEPTNO AVG(SAL)
---------- ----------
10 2916.66667


SQL> select deptno,avg(sal)
2 from emp
3 group by deptno
4 having deptno=10;


DEPTNO AVG(SAL)
---------- ----------
10 2916.66667

数据库优化问题

SQL> --where和having均可使用时，尽量采用where！ 因为where可以先对数据进行过滤然后分组，提高分组效率！
SQL> host cls

group by的增强

SQL> /*
SQL> group by的增强
SQL> group by deptno,job
SQL> +
SQL> group by deptno
SQL> +
SQL> group by null
SQL> =
SQL> group by rollup(deptno,job)

SQL>

SQL>
SQL> group by rollup(a,b)
SQL> =
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null

SQL> */

SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);


DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025


已选择13行。

设定格式

SQL> break on deptno skip 2
SQL> /


DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750


20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875


30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400



DEPTNO JOB SUM(SAL)
---------- --------- ----------

29025




已选择13行。

取消格式

SQL> break on null
SQL> /


DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025


已选择13行。


SQL> spool off