彙總函式的應用

文章目錄

• 8.2  彙總函式的應用
• 8.2.3  最大/最小值函數—MAX()/MIN()
• 8.2.4  均值函數——AVG()
• 8.2.5  彙總分析的重值處理
• 8.2.6  彙總函式的組合使用

8.2  彙總函式的應用

彙總函式在資料庫資料的查詢分析中，應用十分廣泛。本節將分別對各彙總函式的應用進行說明。

8.2.1  求和函數——SUM()

求和函數SUM( )用於對資料求和，返回選取結果集中所有值的總和。文法如下。

SELECT          SUM(column_name)

FROM            table_name

說明：SUM()函數只能作用於數值型資料，即列column_name中的資料必須是數值型的。

執行個體1  SUM函數的使用

從TEACHER表中查詢所有男教師的工資總數。TEACHER表的結構和資料可參見5.2.1節的表5-1，下同。執行個體代碼：

SELECT SUM(SAL) AS BOYSAL

FROM    TEACHER

WHERE   TSEX='男'

運行結果8.1所示。

圖8.1  TEACHER表中所有男教師的工資總數

執行個體2  SUM函數對NULL值的處理

從TEACHER表中查詢年齡大於40歲的教師的工資總數。執行個體代碼：

SELECT SUM(SAL) AS OLDSAL

FROM    TEACHER

WHERE   AGE>=40

運行結果8.2所示。

圖8.2  TEACHER表中所有年齡大於40歲的教師的工資總數

當對某列資料進行求和時，如果該列存在NULL值，則SUM函數會忽略該值。

8.2.2  計數函數——COUNT()

COUNT()函數用來計算表中記錄的個數或者列中值的個數，計算內容由SELECT語句指定。使用COUNT函數時，必須指定一個列的名稱或者使用星號，星號表示計算一個表中的所有記錄。兩種使用形式如下。

     COUNT(*)，計算表中行的總數，即使表中行的資料為NULL，也被計入在內。

     COUNT(column)，計算column列包含的行的數目，如果該列中某行資料為NULL，則該行不計入統計總數。

1．使用COUNT(*)函數對錶中的行數計數

COUNT(*)函數將返回滿足SELECT語句的WHERE子句中的搜尋條件的函數。

執行個體3  COUNT(*)函數的使用

查詢TEACHER表中的所有記錄的行數。執行個體代碼：

SELECT COUNT(*) AS TOTALITEM

FROM    TEACHER

運行結果8.3所示。

圖8.3  使用COUNT(*)函數對錶中的行數計數

在該例中，SELECT語句中沒有WHERE子句，那麼認為表中的所有行都滿足SELECT語句，所以SELECT語句將返回表中所有行的計數，結果與5.2.1節的表5-1列出的TEACHER表的資料相吻合。

如果DBMS在其系統資料表中儲存了表的行數，COUNT(*)將很快地返回表的行數，因為這時，DBMS不必從頭到尾讀取表，並對物理表中的行計數，而直接從系統資料表中提取行的計數。而如果DBMS沒有在系統資料表儲存表的行數，將具有NOT NULL約束的列作為參數，使用COUNT( )函數，則可能更快地對錶行計數。

注意	COUNT(*)函數將準確地返回表中的總行數，而僅當COUNT()函數的參數列沒有NULL值時，才返回表中正確的行計數，所以僅當受NOT NULL限制的列作為參數時，才可使用COUNT( )函數代替COUNT(*)函數。

2．使用COUNT( )函數對一列中的資料計數

COUNT( )函數可用於對一列中的資料值計數。與忽略了所有列的COUNT(*)函數不同，COUNT( )函數逐一檢查一列（或多列）中的值，並對那些值不是NULL的行計數。

執行個體4  查詢多列中所有記錄的行數

查詢TEACHER表中的TNO列、TNAME列以及SAL列中包含的所有資料行數。執行個體代碼：

SELECT                              COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,

                                                        COUNT(SAL) AS TOTAL_SAL

FROM                                 TEACHER

運行結果8.4所示。

圖8.4  使用COUNT( )函數對一列中的資料計數

可見，TNO列與TNAME列由於其中不含有NULL值，所以其計數與使用COUNT(*)函數對TEACHER表中的記錄計數結果相一致，而SAL列由於其中有兩行資料為NULL，所以這兩列沒有被計入在內，計數結果也就是8。

3．使用COUNT( )函數對多列中的資料計數

COUNT( )函數不僅可用於對一列中的資料值計數，也可以對多列中的資料值計數。如果對多列計數，則需要將要計數的多列通過串連符串連後，作為COUNT( )函數的參數。下面將結合具體的多列計數的執行個體，說明其使用過程。

說明	關於如何使用串連符串連多列可參見本書的7.2節。

執行個體5  使用COUNT( )函數對多列中的資料計數

統計TEACHER表中的TNO列、TNAME列和SAL列中分別包含的資料行數，以及TNO列和TNAME列、TNAME列和SAL列一起包含的資料行數。執行個體代碼：

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

運行結果8.5所示。

圖8.5  使用COUNT( )函數對多列中的資料計數

在進行兩列的串連時，由於它們的資料類型不一致，因此要使用CAST運算式將它們轉換成相同的資料類型。

在7.2.1節已經講過，如果在被串連的列中的任何一列有NULL值時，那麼串連的結果為NULL，則該列不會被COUNT( )函數計數。

注意	COUNT( )函數只對那些傳遞到函數中的參數不是NULL的行計數。

4．使用COUNT函數對滿足某種條件的記錄計數

也可以在SELECT語句中添加一些子句約束來指定返回記錄的個數。

執行個體6  使用COUNT函數對滿足某種條件的記錄計數

查詢TEACHER表中女教師記錄的數目。執行個體代碼：

SELECT COUNT(*) AS TOTALWOMEN

FROM    TEACHER

WHERE           TSEX='女'

運行結果8.6所示。

圖8.6  使用COUNT函數對滿足某種條件的記錄計數

這時結果為6而不是前面的所有記錄10。之所以可以通過WHERE子句定義COUNT()函數的計數條件，這與SELECT語句各個子句的執行順序是分不開的。前面已經講過，DBMS首先執行FROM子句，而後是WHERE子句，最後是SELECT子句。所以COUNT()函數只能用於滿足WHERE子句定義的查詢條件的記錄。沒有包括在WHERE子句的查詢結果中的記錄，都不符合COUNT()函數。

8.2.3  最大/最小值函數—MAX()/MIN()

當需要瞭解一列中的最大值時，可以使用MAX()函數；同樣，當需要瞭解一列中的最小值時，可以使用MIN()函數。文法如下。

SELECT          MAX (column_name) / MIN (column_name)

FROM            table_name

說明：列column_name中的資料可以是數值、字串或是日期時間資料類型。MAX()/MIN()函數將返回與被傳遞的列同一資料類型的單一值。

執行個體7  MAX()函數的使用

查詢TEACHER表中教師的最大年齡。執行個體代碼：

SELECT MAX (AGE) AS MAXAGE

FROM    TEACHER

運行結果8.7所示。

圖8.7  TEACHER表中教師的最大年齡

然而，在實際應用中得到這個結果並不是特別有用，因為經常想要獲得的資訊是具有最大年齡的教師的教工號、姓名、性別等資訊。

然而SQL不支援如下的SELECT語句。

SELECT TNAME, DNAME, TSEX, MAX (AGE)

FROM    TEACHER

因為彙總函式處理的是資料群組，在本例中，MAX函數將整個TEACHER表看成一組，而TNAME、DNAME和TSEX的資料都沒有進行任何分組，因此SELECT語句沒有邏輯意義。同樣的道理，下面的代碼也是無效的。

SELECT TNAME, DNAME, TSEX,SAL ,AGE

FROM    TEACHER

WHERE   AGE＝MAX (AGE)

解決這個問題的方法，就是在WHERE子句中使用子查詢來返回最大值，然後再基於這個返回的最大值，查詢相關資訊。

執行個體8  在WHERE子句中使用子查詢返回最大值

查詢TEACHER表中年紀最大的教師的教工號、姓名、性別等資訊。

執行個體代碼：

SELECT TNAME, DNAME, TSEX, SAL, AGE

FROM    TEACHER

WHERE   AGE＝(SELECT MAX (AGE) FROM   TEACHER)

運行結果8.8所示。

圖8.8  在WHERE子句中使用子查詢返回最大值

MAX()和MIN()函數不僅可以作用於數值型資料，也可以作用於字串或是日期時間資料類型的資料。

執行個體9  MAX()函數用於字元型資料

如下面代碼：

SELECT MAX (TNAME) AS MAXNAME

FROM    TEACHER

運行結果8.9所示。

圖8.9  在字串資料型別中使用MAX的結果

可見，對於字串也可以求其最大值。

說明	對字元型資料的最大值，是按照首字母由A～Z的順序排列，越往後，其值越大。當然，對於漢字則是按照其全拼拼音排列的，若首字元相同，則比較下一個字元，以此類推。

當然，對與日期時間類型的資料也可以求其最大/最小值，其大小排列就是日期時間的早晚，越早認為其值越小，如下面的執行個體。

執行個體10  MAX()、MIN()函數用於時間型資料

從COURSE表中查詢最早和最晚考試課程的考試時間。其中COURSE表的結構和資料可參見本書6.1節的表6-1。執行個體代碼：

SELECT MIN (CTEST) AS EARLY_DATE,

                    MAX (CTEST) AS LATE_DATE

FROM    COURSE

運行結果8.10所示。

圖8.10  COURSE表中最早和最晚考試課程的考試時間

可見，返回結果的資料類型與該列定義的資料類型相同。

注意	確定列中的最大值（最小值）時，MAX( )（MIN( )）函數忽略NULL值。但是，如果在該列中，所有行的值都是NULL，則MAX( )/MIN( )函數將返回NULL值。

8.2.4  均值函數——AVG()

函數AVG()用於計算一列中資料值的平均值。文法如下。

SELECT  AVG (column_name)

FROM    table_name

說明：AVG()函數的執行過程實際上是將一列中的值加起來，再將其和除以非NULL值的數目。所以，與SUM( )函數一樣，AVG()函數只能作用於數值型資料，即列column_name中的資料必須是數值型的。

執行個體11  AVG()函數的應用

從TEACHER表中查詢所有教師的平均年齡。執行個體代碼：

SELECT AVG (AGE) AS AVG_AGE

FROM    TEACHER

運行結果8.11所示。

圖8.11  TEACHER表中所有教師的平均年齡

在計算平均值時，AVG()函數將忽略NULL值。因此，如果要計算平均值的列中有NULL值，計算均值時，要特別注意。

執行個體12  AVG()函數對NULL值的處理

從TEACHER表中查詢所有教師的平均工資。執行個體代碼：

SELECT                              AVG (SAL) AS AVG_AGE1，SUM(SAL)/COUNT(*) AS AVG_AGE2,

                                                         SUM(SAL)/COUNT(SAL) AS AVG_AGE3

FROM                                 TEACHER

運行結果8.12所示。

圖8.12  TEACHER表中所有教師的平均工資

可以發現得到了不同的結果。實際上，“AVG (SAL)”與“SUM(SAL)/COUNT(SAL)”語句是等價的。因為AVG(SAL)語句的執行過程實際上是將SAL列中的值加起來，再將其和（也就等價於SUM(SAL)）除以非NULL值的數目（也就等價於COUNT(SAL)）。而語句“SUM(SAL)/COUNT(*)”則不然，因為COUNT(*)返回的是表中所有記錄的個數，而不管SAL列中的數值是否為NULL。

注意	AVG()函數在計算一列的平均值時，忽略NULL值。但是，如果在該列中，所有行的值都是NULL，則AVG()函數將返回NULL值。

如果不想對列中的所有值求平均，則可在WHERE子句中使用搜尋條件來限制用於計算均值的行。

執行個體13  在WHERE子句中使用搜尋條件來限制用於計算均值的行

從TEACHER表中查詢所有電腦系教師的平均年齡。執行個體代碼：

SELECT AVG (AGE) AS AVGCOMPUTER_AGE

FROM    TEACHER

WHERE   DNAME = '電腦'

運行結果8.13所示。

圖8.13  TEACHER表中所有電腦系教師的平均年齡

當執行SELECT語句時，DBMS將表中的每行對WHERE子句中的搜尋條件“DNAME = '電腦'”求值。只有那些搜尋條件為True時，行中的AGE值才傳到均值函數AVG (AGE)中。

當然，除了顯示表中某列的平均值，還可用AVG()函數作為WHERE子句的一部分。與前面介紹的MAX()函數一樣，不能直接用於WHERE子句，必須以子查詢的形式。

執行個體14  AVG()函數作為WHERE子句中搜尋條件的一部分

從TEACHER表中查詢所有年齡高於平均年齡的教師的資訊。執行個體代碼：

SELECT                              *

FROM                                 TEACHER

WHERE                               AGE >= (SELECT AVG (AGE) FROM    TEACHER)

ORDER BY        AGE

運行結果8.14所示。

圖8.14  TEACHER表中所有年齡高於平均年齡的教師的資訊

8.2.5  彙總分析的重值處理

前面介紹的5種彙總函式，可以作用於所選列中的所有資料（不管列中的資料是否有重設），也可以只對列中的非重值進行處理，即把重複的值只取一次進行彙總分析。當然，對於MAX()/MIN()函數來講，重值處理意義不大。

可以使用ALL關鍵字指明對所選列中的所有資料進行處理，使用DISTINCT關鍵字指明對所選列中的非重值資料進行處理。以AVG()函數為例，文法如下。

SELECT  AVG ([ALL/DISTINCT] column_name)

FROM    table_name

說明：[ALL/DISTINCT]在預設狀態下，預設是ALL關鍵字，即不管是否有重值，處理所有資料。其他彙總函式的用法與此相同。

注意	Microsoft Access資料庫不支援在彙總函式中使用DISTINCT關鍵字。

執行個體15  彙總分析的重值處理

從TEACHER表中查詢工資SAL列中存在的所有記錄數。執行個體代碼：

SELECT COUNT(ALL SAL) AS ALLSAL_COUNT

FROM    TEACHER

運行結果8.15所示。

圖8.15  TEACHER表中工資SAL列中存在的所有記錄數

當然，在代碼中去除ALL關鍵字，也可以得到相同的結果。而如果從TEACHER表中，查詢工資SAL列中存在的不同記錄的數目，可採用如下代碼。

SELECT COUNT(DISTINCT SAL) AS DISTINCTSAL_COUNT

FROM    TEACHER

運行結果8.16所示。

圖8.16  TEACHER表中SAL列存在的不同記錄的數目

對比兩個結果，使用DISTINCT關鍵字後，工資SAL列中的重值並沒有列入統計的範圍之內。另外還要強調一點，在所有5種彙總函式中，除了COUNT(*)函數外，其他的函數在計算過程中都忽略NULL值，即把NULL值的行排除在外，不進行分析。

8.2.6  彙總函式的組合使用

前面介紹的執行個體中，彙總函式都是單獨使用的。彙總函式也可以組合使用，即在一條SELECT語句中，可以使用多個彙總函式。

執行個體16  使用多個彙總函式

如下面的代碼：

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

運行結果8.17所示。

圖8.17  彙總函式的組合應用

該例在一條SELECT語句中，幾乎用到了所有的彙總函式。其中num_items為TEACHER表所有記錄的條目，max_sal為TEACHER表中記錄的最高工資，min_age為TEACHER表中記錄的最小年齡，avg_sal為所有TEACHER表中的工資記錄的平均值，disavg_sal為TEACHER表中所有不同的工資記錄的平均值。