TA貢獻(xiàn)1775條經(jīng)驗(yàn) 獲得超8個(gè)贊
8.2 聚合函數(shù)的應(yīng)用
聚合函數(shù)在數(shù)據(jù)庫(kù)數(shù)據(jù)的查詢分析中，應(yīng)用十分廣泛。本節(jié)將分別對(duì)各聚合函數(shù)的應(yīng)用進(jìn)行說明。
8.2.1 求和函數(shù)——SUM()
求和函數(shù)SUM( )用于對(duì)數(shù)據(jù)求和，返回選取結(jié)果集中所有值的總和。語(yǔ)法如下。
SELECT SUM(column_name)
FROM table_name
說明：SUM()函數(shù)只能作用于數(shù)值型數(shù)據(jù)，即列column_name中的數(shù)據(jù)必須是數(shù)值型的。
實(shí)例1 SUM函數(shù)的使用
從TEACHER表中查詢所有男教師的工資總數(shù)。TEACHER表的結(jié)構(gòu)和數(shù)據(jù)可參見5.2.1節(jié)的表5-1，下同。實(shí)例代碼：
SELECT SUM(SAL) AS BOYSAL
FROM TEACHER
WHERE TSEX='男'
運(yùn)行結(jié)果如圖8.1所示。
圖8.1 TEACHER表中所有男教師的工資總數(shù)
實(shí)例2 SUM函數(shù)對(duì)NULL值的處理
從TEACHER表中查詢年齡大于40歲的教師的工資總數(shù)。實(shí)例代碼：
SELECT SUM(SAL) AS OLDSAL
FROM TEACHER
WHERE AGE>=40
運(yùn)行結(jié)果如圖8.2所示。
圖8.2 TEACHER表中所有年齡大于40歲的教師的工資總數(shù)
當(dāng)對(duì)某列數(shù)據(jù)進(jìn)行求和時(shí)，如果該列存在NULL值，則SUM函數(shù)會(huì)忽略該值。
8.2.2 計(jì)數(shù)函數(shù)——COUNT()
COUNT()函數(shù)用來(lái)計(jì)算表中記錄的個(gè)數(shù)或者列中值的個(gè)數(shù)，計(jì)算內(nèi)容由SELECT語(yǔ)句指定。使用COUNT函數(shù)時(shí)，必須指定一個(gè)列的名稱或者使用星號(hào)，星號(hào)表示計(jì)算一個(gè)表中的所有記錄。兩種使用形式如下。
* COUNT(*)，計(jì)算表中行的總數(shù)，即使表中行的數(shù)據(jù)為NULL，也被計(jì)入在內(nèi)。
* COUNT(column)，計(jì)算column列包含的行的數(shù)目，如果該列中某行數(shù)據(jù)為NULL，則該行不計(jì)入統(tǒng)計(jì)總數(shù)。
1．使用COUNT(*)函數(shù)對(duì)表中的行數(shù)計(jì)數(shù)
COUNT(*)函數(shù)將返回滿足SELECT語(yǔ)句的WHERE子句中的搜索條件的函數(shù)。
實(shí)例3 COUNT(*)函數(shù)的使用
查詢TEACHER表中的所有記錄的行數(shù)。實(shí)例代碼：
SELECT COUNT(*) AS TOTALITEM
FROM TEACHER
運(yùn)行結(jié)果如圖8.3所示。
圖8.3 使用COUNT(*)函數(shù)對(duì)表中的行數(shù)計(jì)數(shù)
在該例中，SELECT語(yǔ)句中沒有WHERE子句，那么認(rèn)為表中的所有行都滿足SELECT語(yǔ)句，所以SELECT語(yǔ)句將返回表中所有行的計(jì)數(shù)，結(jié)果與5.2.1節(jié)的表5-1列出的TEACHER表的數(shù)據(jù)相吻合。
如果DBMS在其系統(tǒng)表中存儲(chǔ)了表的行數(shù)，COUNT(*)將很快地返回表的行數(shù)，因?yàn)檫@時(shí)，DBMS不必從頭到尾讀取表，并對(duì)物理表中的行計(jì)數(shù)，而直接從系統(tǒng)表中提取行的計(jì)數(shù)。而如果DBMS沒有在系統(tǒng)表存儲(chǔ)表的行數(shù)，將具有NOT NULL約束的列作為參數(shù)，使用COUNT( )函數(shù)，則可能更快地對(duì)表行計(jì)數(shù)。
注意
COUNT(*)函數(shù)將準(zhǔn)確地返回表中的總行數(shù)，而僅當(dāng)COUNT()函數(shù)的參數(shù)列沒有NULL值時(shí)，才返回表中正確的行計(jì)數(shù)，所以僅當(dāng)受NOT NULL限制的列作為參數(shù)時(shí)，才可使用COUNT( )函數(shù)代替COUNT(*)函數(shù)。
2．使用COUNT( )函數(shù)對(duì)一列中的數(shù)據(jù)計(jì)數(shù)
COUNT( )函數(shù)可用于對(duì)一列中的數(shù)據(jù)值計(jì)數(shù)。與忽略了所有列的COUNT(*)函數(shù)不同，COUNT( )函數(shù)逐一檢查一列（或多列）中的值，并對(duì)那些值不是NULL的行計(jì)數(shù)。
實(shí)例4 查詢多列中所有記錄的行數(shù)
查詢TEACHER表中的TNO列、TNAME列以及SAL列中包含的所有數(shù)據(jù)行數(shù)。實(shí)例代碼：
SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,
COUNT(SAL) AS TOTAL_SAL
FROM TEACHER
運(yùn)行結(jié)果如圖8.4所示。
圖8.4 使用COUNT( )函數(shù)對(duì)一列中的數(shù)據(jù)計(jì)數(shù)
可見，TNO列與TNAME列由于其中不含有NULL值，所以其計(jì)數(shù)與使用COUNT(*)函數(shù)對(duì)TEACHER表中的記錄計(jì)數(shù)結(jié)果相一致，而SAL列由于其中有兩行數(shù)據(jù)為NULL，所以這兩列沒有被計(jì)入在內(nèi)，計(jì)數(shù)結(jié)果也就是8。
3．使用COUNT( )函數(shù)對(duì)多列中的數(shù)據(jù)計(jì)數(shù)
COUNT( )函數(shù)不僅可用于對(duì)一列中的數(shù)據(jù)值計(jì)數(shù)，也可以對(duì)多列中的數(shù)據(jù)值計(jì)數(shù)。如果對(duì)多列計(jì)數(shù)，則需要將要計(jì)數(shù)的多列通過連接符連接后，作為COUNT( )函數(shù)的參數(shù)。下面將結(jié)合具體的多列計(jì)數(shù)的實(shí)例，說明其使用過程。
*說明
關(guān)于如何使用連接符連接多列可參見本書的7.2節(jié)。
實(shí)例5 使用COUNT( )函數(shù)對(duì)多列中的數(shù)據(jù)計(jì)數(shù)
統(tǒng)計(jì)TEACHER表中的TNO列、TNAME列和SAL列中分別包含的數(shù)據(jù)行數(shù)，以及TNO列和TNAME列、TNAME列和SAL列一起包含的數(shù)據(jù)行數(shù)。實(shí)例代碼：
SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,
COUNT(SAL) AS TOTAL_SAL,
COUNT(CAST(TNO AS VARCHAR(5)) + TNAME) AS T_NONAME,
COUNT(TNAME + CAST(SAL AS VARCHAR(5))) AS T_NAMESAL
FROM TEACHER
運(yùn)行結(jié)果如圖8.5所示。
圖8.5 使用COUNT( )函數(shù)對(duì)多列中的數(shù)據(jù)計(jì)數(shù)
在進(jìn)行兩列的連接時(shí)，由于它們的數(shù)據(jù)類型不一致，因此要使用CAST表達(dá)式將它們轉(zhuǎn)換成相同的數(shù)據(jù)類型。
在7.2.1節(jié)已經(jīng)講過，如果在被連接的列中的任何一列有NULL值時(shí)，那么連接的結(jié)果為NULL，則該列不會(huì)被COUNT( )函數(shù)計(jì)數(shù)。
注意
COUNT( )函數(shù)只對(duì)那些傳遞到函數(shù)中的參數(shù)不是NULL的行計(jì)數(shù)。
4．使用COUNT函數(shù)對(duì)滿足某種條件的記錄計(jì)數(shù)
也可以在SELECT語(yǔ)句中添加一些子句約束來(lái)指定返回記錄的個(gè)數(shù)。
實(shí)例6 使用COUNT函數(shù)對(duì)滿足某種條件的記錄計(jì)數(shù)
查詢TEACHER表中女教師記錄的數(shù)目。實(shí)例代碼：
SELECT COUNT(*) AS TOTALWOMEN
FROM TEACHER
WHERE TSEX='女'
運(yùn)行結(jié)果如圖8.6所示。
圖8.6 使用COUNT函數(shù)對(duì)滿足某種條件的記錄計(jì)數(shù)
這時(shí)結(jié)果為6而不是前面的所有記錄10。之所以可以通過WHERE子句定義COUNT()函數(shù)的計(jì)數(shù)條件，這與SELECT語(yǔ)句各個(gè)子句的執(zhí)行順序是分不開的。前面已經(jīng)講過，DBMS首先執(zhí)行FROM子句，而后是WHERE子句，最后是SELECT子句。所以COUNT()函數(shù)只能用于滿足WHERE子句定義的查詢條件的記錄。沒有包括在WHERE子句的查詢結(jié)果中的記錄，都不符合COUNT()函數(shù)。
8.2.3 最大/最小值函數(shù)—MAX()/MIN()
當(dāng)需要了解一列中的最大值時(shí)，可以使用MAX()函數(shù)；同樣，當(dāng)需要了解一列中的最小值時(shí)，可以使用MIN()函數(shù)。語(yǔ)法如下。
SELECT MAX (column_name) / MIN (column_name)
FROM table_name
說明：列column_name中的數(shù)據(jù)可以是數(shù)值、字符串或是日期時(shí)間數(shù)據(jù)類型。MAX()/MIN()函數(shù)將返回與被傳遞的列同一數(shù)據(jù)類型的單一值。
實(shí)例7 MAX()函數(shù)的使用
查詢TEACHER表中教師的最大年齡。實(shí)例代碼：
SELECT MAX (AGE) AS MAXAGE
FROM TEACHER
運(yùn)行結(jié)果如圖8.7所示。
圖8.7 TEACHER表中教師的最大年齡
然而，在實(shí)際應(yīng)用中得到這個(gè)結(jié)果并不是特別有用，因?yàn)榻?jīng)常想要獲得的信息是具有最大年齡的教師的教工號(hào)、姓名、性別等信息。
然而SQL不支持如下的SELECT語(yǔ)句。
SELECT TNAME, DNAME, TSEX, MAX (AGE)
FROM TEACHER
因?yàn)榫酆虾瘮?shù)處理的是數(shù)據(jù)組，在本例中，MAX函數(shù)將整個(gè)TEACHER表看成一組，而TNAME、DNAME和TSEX的數(shù)據(jù)都沒有進(jìn)行任何分組，因此SELECT語(yǔ)句沒有邏輯意義。同樣的道理，下面的代碼也是無(wú)效的。
SELECT TNAME, DNAME, TSEX,SAL ,AGE
FROM TEACHER
WHERE AGE＝MAX (AGE)
解決這個(gè)問題的方法，就是在WHERE子句中使用子查詢來(lái)返回最大值，然后再基于這個(gè)返回的最大值，查詢相關(guān)信息。
實(shí)例8 在WHERE子句中使用子查詢返回最大值
查詢TEACHER表中年紀(jì)最大的教師的教工號(hào)、姓名、性別等信息。
實(shí)例代碼：
SELECT TNAME, DNAME, TSEX, SAL, AGE
FROM TEACHER
WHERE AGE＝(SELECT MAX (AGE) FROM TEACHER)
運(yùn)行結(jié)果如圖8.8所示。
圖8.8 在WHERE子句中使用子查詢返回最大值
MAX()和MIN()函數(shù)不僅可以作用于數(shù)值型數(shù)據(jù)，也可以作用于字符串或是日期時(shí)間數(shù)據(jù)類型的數(shù)據(jù)。
實(shí)例9 MAX()函數(shù)用于字符型數(shù)據(jù)
如下面代碼：
SELECT MAX (TNAME) AS MAXNAME
FROM TEACHER
運(yùn)行結(jié)果如圖8.9所示。
圖8.9 在字符串?dāng)?shù)據(jù)類型中使用MAX的結(jié)果
可見，對(duì)于字符串也可以求其最大值。
*說明
對(duì)字符型數(shù)據(jù)的最大值，是按照首字母由A～Z的順序排列，越往后，其值越大。當(dāng)然，對(duì)于漢字則是按照其全拼拼音排列的，若首字符相同，則比較下一個(gè)字符，以此類推。
當(dāng)然，對(duì)與日期時(shí)間類型的數(shù)據(jù)也可以求其最大/最小值，其大小排列就是日期時(shí)間的早晚，越早認(rèn)為其值越小，如下面的實(shí)例。
實(shí)例10 MAX()、MIN()函數(shù)用于時(shí)間型數(shù)據(jù)
從COURSE表中查詢最早和最晚考試課程的考試時(shí)間。其中COURSE表的結(jié)構(gòu)和數(shù)據(jù)可參見本書6.1節(jié)的表6-1。實(shí)例代碼：
SELECT MIN (CTEST) AS EARLY_DATE,
MAX (CTEST) AS LATE_DATE
FROM COURSE
運(yùn)行結(jié)果如圖8.10所示。
圖8.10 COURSE表中最早和最晚考試課程的考試時(shí)間
可見，返回結(jié)果的數(shù)據(jù)類型與該列定義的數(shù)據(jù)類型相同。
注意
確定列中的最大值（最小值）時(shí)，MAX( )（MIN( )）函數(shù)忽略NULL值。但是，如果在該列中，所有行的值都是NULL，則MAX( )/MIN( )函數(shù)將返回NULL值。
8.2.4 均值函數(shù)——AVG()
函數(shù)AVG()用于計(jì)算一列中數(shù)據(jù)值的平均值。語(yǔ)法如下。
SELECT AVG (column_name)
FROM table_name
說明：AVG()函數(shù)的執(zhí)行過程實(shí)際上是將一列中的值加起來(lái)，再將其和除以非NULL值的數(shù)目。所以，與SUM( )函數(shù)一樣，AVG()函數(shù)只能作用于數(shù)值型數(shù)據(jù)，即列column_name中的數(shù)據(jù)必須是數(shù)值型的。
實(shí)例11 AVG()函數(shù)的應(yīng)用
從TEACHER表中查詢所有教師的平均年齡。實(shí)例代碼：
SELECT AVG (AGE) AS AVG_AGE
FROM TEACHER
運(yùn)行結(jié)果如圖8.11所示。
圖8.11 TEACHER表中所有教師的平均年齡
在計(jì)算平均值時(shí)，AVG()函數(shù)將忽略NULL值。因此，如果要計(jì)算平均值的列中有NULL值，計(jì)算均值時(shí)，要特別注意。
實(shí)例12 AVG()函數(shù)對(duì)NULL值的處理
從TEACHER表中查詢所有教師的平均工資。實(shí)例代碼：
SELECT AVG (SAL) AS AVG_AGE1，SUM(SAL)/COUNT(*) AS AVG_AGE2,
SUM(SAL)/COUNT(SAL) AS AVG_AGE3
FROM TEACHER
運(yùn)行結(jié)果如圖8.12所示。
圖8.12 TEACHER表中所有教師的平均工資
可以發(fā)現(xiàn)得到了不同的結(jié)果。實(shí)際上，“AVG (SAL)”與“SUM(SAL)/COUNT(SAL)”語(yǔ)句是等價(jià)的。因?yàn)锳VG(SAL)語(yǔ)句的執(zhí)行過程實(shí)際上是將SAL列中的值加起來(lái)，再將其和（也就等價(jià)于SUM(SAL)）除以非NULL值的數(shù)目（也就等價(jià)于COUNT(SAL)）。而語(yǔ)句“SUM(SAL)/COUNT(*)”則不然，因?yàn)镃OUNT(*)返回的是表中所有記錄的個(gè)數(shù)，而不管SAL列中的數(shù)值是否為NULL。
注意
AVG()函數(shù)在計(jì)算一列的平均值時(shí)，忽略NULL值。但是，如果在該列中，所有行的值都是NULL，則AVG()函數(shù)將返回NULL值。
如果不想對(duì)列中的所有值求平均，則可在WHERE子句中使用搜索條件來(lái)限制用于計(jì)算均值的行。
實(shí)例13 在WHERE子句中使用搜索條件來(lái)限制用于計(jì)算均值的行
從TEACHER表中查詢所有計(jì)算機(jī)系教師的平均年齡。實(shí)例代碼：
SELECT AVG (AGE) AS AVGCOMPUTER_AGE
FROM TEACHER
WHERE DNAME = '計(jì)算機(jī)'
運(yùn)行結(jié)果如圖8.13所示。
圖8.13 TEACHER表中所有計(jì)算機(jī)系教師的平均年齡
當(dāng)執(zhí)行SELECT語(yǔ)句時(shí)，DBMS將表中的每行對(duì)WHERE子句中的搜索條件“DNAME = '計(jì)算機(jī)'”求值。只有那些搜索條件為True時(shí)，行中的AGE值才傳到均值函數(shù)AVG (AGE)中。
當(dāng)然，除了顯示表中某列的平均值，還可用AVG()函數(shù)作為WHERE子句的一部分。與前面介紹的MAX()函數(shù)一樣，不能直接用于WHERE子句，必須以子查詢的形式。
實(shí)例14 AVG()函數(shù)作為WHERE子句中搜索條件的一部分
從TEACHER表中查詢所有年齡高于平均年齡的教師的信息。實(shí)例代碼：
SELECT *
FROM TEACHER
WHERE AGE >= (SELECT AVG (AGE) FROM TEACHER)
ORDER BY AGE
運(yùn)行結(jié)果如圖8.14所示。
圖8.14 TEACHER表中所有年齡高于平均年齡的教師的信息
8.2.5 聚合分析的重值處理
前面介紹的5種聚合函數(shù)，可以作用于所選列中的所有數(shù)據(jù)（不管列中的數(shù)據(jù)是否有重置），也可以只對(duì)列中的非重值進(jìn)行處理，即把重復(fù)的值只取一次進(jìn)行聚合分析。當(dāng)然，對(duì)于MAX()/MIN()函數(shù)來(lái)講，重值處理意義不大。
可以使用ALL關(guān)鍵字指明對(duì)所選列中的所有數(shù)據(jù)進(jìn)行處理，使用DISTINCT關(guān)鍵字指明對(duì)所選列中的非重值數(shù)據(jù)進(jìn)行處理。以AVG()函數(shù)為例，語(yǔ)法如下。
SELECT AVG ([ALL/DISTINCT] column_name)
FROM table_name
說明：[ALL/DISTINCT]在缺省狀態(tài)下，默認(rèn)是ALL關(guān)鍵字，即不管是否有重值，處理所有數(shù)據(jù)。其他聚合函數(shù)的用法與此相同。
注意
Microsoft Access數(shù)據(jù)庫(kù)不支持在聚合函數(shù)中使用DISTINCT關(guān)鍵字。
實(shí)例15 聚合分析的重值處理
從TEACHER表中查詢工資SAL列中存在的所有記錄數(shù)。實(shí)例代碼：
SELECT COUNT(ALL SAL) AS ALLSAL_COUNT
FROM TEACHER
運(yùn)行結(jié)果如圖8.15所示。
圖8.15 TEACHER表中工資SAL列中存在的所有記錄數(shù)
當(dāng)然，在代碼中去除ALL關(guān)鍵字，也可以得到相同的結(jié)果。而如果從TEACHER表中，查詢工資SAL列中存在的不同記錄的數(shù)目，可采用如下代碼。
SELECT COUNT(DISTINCT SAL) AS DISTINCTSAL_COUNT
FROM TEACHER
運(yùn)行結(jié)果如圖8.16所示。
圖8.16 TEACHER表中SAL列存在的不同記錄的數(shù)目
對(duì)比兩個(gè)結(jié)果，使用DISTINCT關(guān)鍵字后，工資SAL列中的重值并沒有列入統(tǒng)計(jì)的范圍之內(nèi)。另外還要強(qiáng)調(diào)一點(diǎn)，在所有5種聚合函數(shù)中，除了COUNT(*)函數(shù)外，其他的函數(shù)在計(jì)算過程中都忽略NULL值，即把NULL值的行排除在外，不進(jìn)行分析。
8.2.6 聚合函數(shù)的組合使用
前面介紹的實(shí)例中，聚合函數(shù)都是單獨(dú)使用的。聚合函數(shù)也可以組合使用，即在一條SELECT語(yǔ)句中，可以使用多個(gè)聚合函數(shù)。
實(shí)例16 使用多個(gè)聚合函數(shù)
如下面的代碼：
SELECT COUNT(*) AS num_items,
MAX(SAL) AS max_sal,
Min(AGE) AS min_age,
SUM(SAL)/COUNT(SAL) AS avg_sal,
AVG(DISTINCT SAL) AS disavg_sal
FROM TEACHER
運(yùn)行結(jié)果如圖8.17所示。
圖8.17 聚合函數(shù)的組合應(yīng)用
該例在一條SELECT語(yǔ)句中，幾乎用到了所有的聚合函數(shù)。其中num_items為TEACHER表所有記錄的條目，max_sal為TEACHER表中記錄的最高工資，min_age為TEACHER表中記錄的最小年齡，avg_sal為所有TEACHER表中的工資記錄的平均值，disavg_sal為TEACHER表中所有不同的工資記錄的平均值。
舉報(bào)