Oracle的分组函数

1.最重要的五个分组函数

1. sum()
2. avg()
3. count()
4. max()
5. min()

数值类型可以使用所有分组函数

SQL> select sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min,count(*) count from emp;
       SUM        AVG        MAX        MIN      COUNT
---------- ---------- ---------- ---------- ----------
     29025 2073.21428       5000        800         14

• min(),max(),count()可以作用于日期类型和字符类型

SQL> select min(hiredate),max(hiredate),min(ename),max(ename),count(hiredate) from emp;
MIN(HIREDATE) MAX(HIREDATE) MIN(ENAME) MAX(ENAME) COUNT(HIREDATE)
------------- ------------- ---------- ---------- ---------------
1980/12/17    1987/5/23     ADAMS      WARD                    14

count(*)函数返回表中行的总数，包括重复行与数据列中含有空值得行，而其他分组函数的统计都不包括空值的行

• count(comm)返回该列所含非空行的数量

SQL> select count(*),count(comm) from emp;
  COUNT(*) COUNT(COMM)
---------- -----------
        14           4

2.在组函数中使用NVL函数

SQL> select deptno,avg(nvl(comm,0)) from emp group by deptno;
DEPTNO AVG(NVL(COMM,0))
------ ----------------
    30 366.666666666667
    20                0
    10                0

SQL> select deptno,avg(comm) from emp group by deptno;
DEPTNO  AVG(COMM)
------ ----------
    30        550
    20 
    10 

• 如上代码nvl()函数会将没有值的字段赋值为0

3.GROUP BY创建组

• group by 后面的列也叫分组特性，一旦使用了group by，select 后面只能有两种列，一个是组函数列，而另一个是分组特性列。
• 对分组结果进行过滤

SQL> select deptno,avg(sal) avgcomm from emp group by deptno having avg(sal) > 2000;
DEPTNO    AVGCOMM
------ ----------
    20       2175
    10 2916.66666

• 对分组结果进行排序

SQL> select deptno,avg(nvl(sal,0)) avgcomm from emp group by deptno order by avg(nvl(sal,0));
DEPTNO    AVGCOMM
------ ----------
    30 1566.66666
    20       2175
    10 2916.66666

• 排序的列不在select投影选项中也是可以的，因为order by实在select投影前完成的。

确保select列表中除了组函数的项以外，所有列都包含在GROUP BY子句中。

4.分组函数的嵌套

• 单行函数可以嵌套任意分层，但分组函数最多可以嵌套两层。
• 比如：count(sum(avg)) 会返回错误“ORA-00935：group function is nested too deeply”.
• 在分组函数内可以嵌套单行函数，如要计算各个部门ename值的平均长度之和

SQL> select sum(avg(length(ename))) from emp group by deptno;
SUM(AVG(LENGTH(ENAME)))
-----------------------
       14.9666666666667