oracle之分组函数

分组函数

5.1 最重要的五个分组函数

sum(); avg(); count(); max(); 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.21429       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 00:00:00 1987-05-23 00:00:00 ADAMS         WARD                    14


COUNT(*)函数返回表中行的总数，包括重复行与数据列中含有空值的行，而其他分组函数的统计都不包括空值的行。（考点）
COUNT(comm)返回该列所含非空行的数量。

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

  COUNT(*) COUNT(COMM)
---------- -----------
        14           4

5.2 GROUP BY建立分组
SQL>select deptno, avg(nvl(sal,0)) from emp group by deptno;

group by后面的列也叫分组特性，一旦使用了group by, select后面只能有两种列，一个是组函数列，而另一个是分组特性列（可选）。

对分组结果进行过滤
SQL>select deptno, avg(sal) avgcomm from emp group by deptno having avg(sal)>2000;
SQL>select deptno, avg(sal) avgcomm from emp where avg(sal)>2000 group by deptno;   //错误的,应该使用HAVING子句

对分组结果排序
SQL>select deptno, avg(nvl(sal,0)) avgcomm from emp group by deptno order by avg(nvl(sal,0));  

    DEPTNO    AVGCOMM
---------- ----------
        30       1566.66667
        20       2175
        10        2916.66667

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

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

5.3 分组函数的嵌套

单行函数可以嵌套任意层，但分组函数最多可以嵌套两层。（考点）

比如：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.9666667