第二章   匯總和聚合數(shù)據(jù)（一）

為完整地了解Oracle的聚合函數(shù)，我們還要知道當(dāng)數(shù)據(jù)包含NULL時(shí)，情況會(huì)怎樣。銷售獎(jiǎng)勵(lì)只有銷售人員才有，非銷售人員沒有，所以反映在HR.EMPLOYEES表中就是那些非銷售人員的COMMISSION_PCT字段的值為NULL。如果想計(jì)算或同級(jí)COMMISSION_PCT的平均值，或是此字段有值的行的數(shù)量，可以用下面的SQL語(yǔ)句列出聚合計(jì)算的結(jié)果：

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

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

COUNT(COMMISSION_PCT)    AVG(COMMISSION_PCT)

                                           38                                    .255

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

    雖然我們知道在統(tǒng)計(jì)薪水時(shí)有107位員工，但在統(tǒng)計(jì)銷售獎(jiǎng)勵(lì)時(shí)，COUNT函數(shù)忽略了那些COMMISSION_CPT為NULL值的行，統(tǒng)計(jì)出僅有38位員工有銷售獎(jiǎng)勵(lì)。同樣，當(dāng)計(jì)算員工的平均銷售獎(jiǎng)勵(lì)時(shí)，Oracle也忽略了NULL值對(duì)應(yīng)的那些行，僅僅計(jì)算那些有值的行。

    僅僅在兩種特殊情況下，Oracle在聚合函數(shù)中考慮了NULL值。第一種是在GROUPING功能里，用來(lái)檢驗(yàn)包含NULL值的分析函數(shù)的結(jié)果，是直接由所在的表得來(lái)，還是由分析計(jì)算得到的最終聚合“NULL集”得來(lái)。第二種情況是在COUNT(*)的功能里。因?yàn)橥ㄅ浞?”表示表中所有的字段，所以O(shè)racle忽略任何實(shí)際的數(shù)據(jù)，而單獨(dú)統(tǒng)計(jì)行數(shù)，看起來(lái)像是把NULL值和普通值一樣對(duì)待。

    為了演示，下面的SQL語(yǔ)句明確顯示了COUNT(*)與COUNT(COMMISSION_PCT)的不同：

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

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

COUNT(*)    COUNT(COMMISSION_PCT)

         107                                               38

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

    COUNT(*)統(tǒng)計(jì)了表中的所有行，而COUNT(COMMISSION_PCT)僅僅統(tǒng)計(jì)了表中字段COMMISSION_PCT不為NULL的那些行。  

    在分組進(jìn)行數(shù)據(jù)匯總時(shí)，SELECT語(yǔ)句中選中的字段或值，要么參加聚合計(jì)算，要么被涵蓋到GROUP BY子句中。若想寫出語(yǔ)法正確的GROUP BY子句，就要永遠(yuǎn)牢記：值要么用來(lái)分組，要么用來(lái)參加聚合計(jì)算——沒有其他用途。

    當(dāng)按聚合或其他函數(shù)進(jìn)行排序時(shí)，Oracle提供了排序字段的簡(jiǎn)化符號(hào)。不必在ORDER BY后面累贅地寫上字段的全名，直接用他們?cè)赟ELECT 語(yǔ)句中的位置序號(hào)即可。

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 ;

    語(yǔ)句中ORDER BY 子句后面的1與5分別代表SELECT 語(yǔ)句中第一個(gè)位置和第五個(gè)位置的DEPARTMENT_ID與max(salary)。

    基于聚合函數(shù)或分組的結(jié)果，我們想排除某些數(shù)據(jù)組。也就是說(shuō)，我們希望在GROUP BY子句的后面，再跟一個(gè)類似WHERE的子句，在分組或聚合之后進(jìn)行篩選。

    SQL有一個(gè)HAVING子句，可以對(duì)數(shù)據(jù)進(jìn)行選擇。HAVING子句的條件可以無(wú)限地復(fù)雜，所以可以在分組排序中使用復(fù)合條件。

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;