第二章 汇总和聚合数据（一）

为完整地了解oracle的聚合函数，我们还要知道当数据包含NULL时，情况会怎样。销售奖励只有销售人员才有，非销售人员没有，所以反映在HR.EMPLOYEES表中就是那些非销售人员的COMMISSioN_PCT字段的值为NULL。如果想计算或同级COMMISSION_PCT的平均值，或是此字段有值的行的数量，可以用下面的sql语句列出聚合计算的结果：

 select count(commission_pct),avg(commission_pct)
    from hr.employee;

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

COUNT(COMMISSION_PCT)    AVG(COMMISSION_PCT)

                                           38                                    .255

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

    虽然我们知道在统计薪水时有107位员工，但在统计销售奖励时，COUNT函数忽略了那些COMMISSION_CPT为NULL值的行，统计出仅有38位员工有销售奖励。同样，当计算员工的平均销售奖励时，Oracle也忽略了NULL值对应的那些行，仅仅计算那些有值的行。

    仅仅在两种特殊情况下，Oracle在聚合函数中考虑了NULL值。第一种是在GROUPING功能里，用来检验包含NULL值的分析函数的结果，是直接由所在的表得来，还是由分析计算得到的最终聚合“NULL集”得来。第二种情况是在COUNT(*)的功能里。因为通配符“*”表示表中所有的字段，所以Oracle忽略任何实际的数据，而单独统计行数，看起来像是把NULL值和普通值一样对待。

    为了演示，下面的SQL语句明确显示了COUNT(*)与COUNT(COMMISSION_PCT)的不同：

select count(*),count(commission_pct)
    from hr.employee;

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

COUNT(*)    COUNT(COMMISSION_PCT)

         107                                               38

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

    COUNT(*)统计了表中的所有行，而COUNT(COMMISSION_PCT)仅仅统计了表中字段COMMISSION_PCT不为NULL的那些行。  

    在分组进行数据汇总时，SELECT语句中选中的字段或值，要么参加聚合计算，要么被涵盖到GROUP BY子句中。若想写出语法正确的GROUP BY子句，就要永远牢记：值要么用来分组，要么用来参加聚合计算——没有其他用途。

    当按聚合或其他函数进行排序时，Oracle提供了排序字段的简化符号。不必在ORDER BY后面累赘地写上字段的全名，直接用他们在SELECT 语句中的位置序号即可。

Select department_id, job_id,min(salary), avg(salary), max(salary)
    from hr.employee
    group by department_id, job_id
    order by 1, 5 desc ;

    语句中ORDER BY 子句后面的1与5分别代表SELECT 语句中第一个位置和第五个位置的DEPARTMENT_ID与max(salary)。

    基于聚合函数或分组的结果，我们想排除某些数据组。也就是说，我们希望在GROUP BY子句的后面，再跟一个类似WHERE的子句，在分组或聚合之后进行筛选。

    SQL有一个HAVING子句，可以对数据进行选择。HAVING子句的条件可以无限地复杂，所以可以在分组排序中使用复合条件。

select department_id, job_id, min(salary), avg(salary), max(salary), count(*)
    from hr.employees
    group by department_id, job_id
    having count(*)>1
    and min(salary) between 2500 and 17000
    and avg(salary) !=5000
    and max(salary)/min(salary)<2;