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