教学文章
Technology Exchange
Technology Exchange
400电话
免费咨询热线
400-090-9964
概念及注意事项
1、组函数:对一组数据进行操作，最后返回一个值，用作统计分析，求所有的非空值。
2、关键字DISTINCT可以排除重复值。
3、参数的类型可以是CHAR,VARCHAR2,NUMBER,DATE
4、除了COUNT(*)外，其他所有组函数都忽略空值，可以使用NVL函数处理
5、select之后,如果其中有一个组函数,那么其他的也要是组函数。
avg() 平均
count() 计数
max() 最大值
min() 最小值
sum() 求和
组函数的使用
1、数字类型可以使用很多组函数
SQL> select sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min,count(*) count
2 from emp1;
SUM AVG MAX MIN COUNT
---------- ---------- ---------- ---------- ----------
35025 2335 6000 800 15
2、对日期类型使用MIN,MAX
SQL> select min(hiredate),max(hiredate)
2 from emp;
MIN(HIREDATE) MAX(HIREDATE)
------------------- -------------------
1980-12-17 00:00:00 1987-05-23 00:00:00
3、COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行。
SQL> select count(*) from emp1;
COUNT(*)
----------
15
COUNT(EXPR)返回expr标识的列所含非空行的数量。
SQL> select count(comm) from emp1;
COUNT(COMM)
-----------
4
4、在组函数中使用NVL函数
SQL> select avg(nvl(comm,0)) from emp1;//将分母变成有效的值14
AVG(NVL(COMM,0))
----------------
146.666667
SQL> select avg(comm) from emp1;//这里的分母是4，只有四个非空值
AVG(COMM)
----------
550
GROUP BY 创建数据组
1、基本用法
SQL> select deptno,avg(sal) from emp1
2 group by deptno;
2、使用WHERE先过滤到一些信息
SQL> select deptno,avg(sal) avg from emp1
2 where sal > 1200
3 group by deptno;
3、对分组结果排序
1 select deptno,avg(sal) from emp1
2 where sal > 2000
3 group by deptno
4* order by avg(sal)
4、对分组结果进行过滤
1 select deptno,avg(sal) from emp1
2 where avg(sal) > 2000
3* group by deptno//这是错误的
where avg(sal) > 2000
*
ERROR at line 2:
ORA-00934: group function is not allowed her
应该使用HAVING子句【HAVING子句应该在GROUP BY子句后面】
1 select deptno,avg(sal) from emp1
2 group by deptno
3* having avg(sal)>2000
SQL> /
DEPTNO AVG(SAL)
---------- ----------
20 2175
40 6000
10 2916.66667
5、注意事项
（1）确保SELECT列表中除了组函数的项，所有列都包含在GROUP BY子句中
SQL> select deptno,avg(sal) avg,comm //comm不在group子句中
2 from emp1
3 where sal>1200
4 group by deptno
5 ;
select deptno,avg(sal) avg,comm
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
（2）GROUP BY产生的结果，是基于分组列升序排列的。
（3）如果使用ORDER BY子句对分组结果排序，确保ORDER BY子句在最后。
其它函数
stddev()返回标准差
variance()返回统计方差
分组函数（2）
前面介绍了分组函数的基本用法，下面介绍几种关于GROUP BY更高级的用法。
1、ORACLE数据库中的ROLLUP配合GROUP BY命令使用，可以提供信息汇总功能（与"小计"相似）
SQL> SELECT deptno, job,sum(sal) from emp1 group by deptno,job
2 order by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------------------------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
40 SALESMAN 6000
//会对每一个不同的dept,job生成一行独立的结果
1 SELECT deptno, job,sum(sal) from emp1
2 group by rollup(deptno,job)
3* order by deptno,job
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------------------------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
40 SALESMAN 6000
40 6000
35025
//结果中除了上述的结果结果之外，还会对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一行。
总结：
（1）可以看出,用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。
（2）这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。
2、CUBE，也是GROUP BY子句的一种扩展
可以返回每一个列组合的小计记录（从左向右+从右向左），同时在末尾加上总计记录。
1 SELECT deptno, job,sum(sal) from emp1
2 group by cube(deptno,job)
3* order by deptno,job
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------------------------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
40 SALESMAN 6000
40 6000
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 11600
35025
这里需要注意的是是使用了group by和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933: SQL 命令未正确结束的错误。
3、其他情况
但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数，然后再用decode函数判断一下是否为空就可以了。
select decode(grouping_id(job,deptno),1,'sum',job||deptno) as group_col,sum(sal) total_sal
from emp
group by rollup(job,deptno);
GROUP_COL TOTAL_SAL
------------------ ----------
CLERK10 1300
CLERK20 1900
CLERK30 950
sum 4150
ANALYST20 6000
sum 6000
MANAGER10 2450
MANAGER20 2975
MANAGER30 2850
sum 8275
SALESMAN30 5600
sum 5600
PRESIDENT10 5000
sum 5000
29025
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。