聚集函數

比如，我們可以用下面的語句找出所有記錄中低温中的最高温度

SELECT max(temp_lo) FROM weather;

max----- 46(1 row)

如果我們想知道該讀數發生在哪個城市，我們可以用

SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG不過這個方法不能運轉，因為聚集 max 不能用於 WHERE 子句中。 （存在這個限制是因為 WHERE 子句決定哪些行可以進入聚集階段；因此它必需在聚集函數之前計算。） 不過，我們通常都可以用其它方法實現我們的目的；這裏我們就可以使用子查詢：

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

city--------------- San Francisco(1 row)這樣做是 OK 的，因為子查詢是一次獨立的計算，它獨立於外層的查詢計算出自己的聚集。

聚集同樣也常用於 GROUP BY 子句。比如， 我們可以獲取每個城市低温的最高值

SELECT city, max(temp_lo) FROM weather GROUP BY city;

city | max---------------+----- Hayward | 37 San Francisco | 46(2 rows)這樣給我們每個城市一個輸出。 每個聚集結果都是在匹配該城市的行上面計算的。 我們可以用 HAVING 過濾這些分組：

SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

city | max---------+----- Hayward | 37(1 row)這樣就只給出那些 temp_lo 數值曾經有低於 40 度温度的城市。 最後，如果我們只關心那些名字以 "S" 開頭的城市，我們可以用

SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40; LIKE 做模式匹配，在 Section 裏有解釋。

理解聚集和SQL的 WHERE 以及 HAVING 子句之間的關係對我們非常重要。 WHERE 和 HAVING 的基本區別如下： WHERE 在分組和聚集計算之前選取輸入行（因此，它控制哪些行進入聚集計算）， 而 HAVING 在分組和聚集之後選取分組的行。 因此，WHERE 子句不能包含聚集函數； 因為試圖用聚集函數判斷那些行輸入給聚集運算是沒有意義的。 相反，HAVING 子句總是包含聚集函數。 （嚴格説來，你可以寫不使用聚集的 HAVING 子句， 但這樣做只是白費勁。同樣的條件可以更有效地用於 WHERE 階段。）

在前面的例子裏，我們可以在 WHERE 裏應用城市名稱限制，因為它不需要聚集。 這樣比在 HAVING 裏增加限制更加高效，因為我們避免了為那些未通過 WHERE 檢查的行進行分組和聚集計算。

聚集函數對一組值進行操作以返回單個標量值。可以在查詢和子查詢方法中使用這些函數。

以下示例描述聚集：SELECT SUM (e.salary) FROM EmpBean e WHERE e.dept.deptno =20

此聚集計算第 20 個部門的總計月薪。

聚集函數是 AVG、COUNT、MAX、MIN 和 SUM。以下示例中描述了聚集函數的語法：aggregation-function ( [ ALL | DISTINCT ] expression )或：COUNT( [ ALL | DISTINCT ] identification-variable )或：COUNT( * )

在應用函數之前，DISTINCT 選項消除重複值。ALL 是缺省選項，並且不消除重複。計算聚集函數時，除了 COUNT(*) 和 COUNT(identification-variable)（它們返回此集合中的所有元素的計數值），空值都被忽略。

如果您的數據存儲是 Informix，那麼當使用 COUNT 函數或 SUM、AVG、MIN 和 MAX 函數的 DISTINCT 格式時，必須將表達式參數限定為單個值的路徑表達式。

定義返回類型 對於使用聚集函數的選擇方法，您可以將返回類型定義為基本類型或包裝器類型。返回類型必須與來自數據存儲的返回類型兼容。MAX 和 MIN 函數可以應用於任何數字、字符串或日期時間數據類型並返回相應的數據類型。SUM 和 AVG 函數採用數字類型作為輸入，並返回數據存儲中使用的同一數字類型。COUNT 函數可以採用任何數據類型，並返回整數。

當應用於空集時，SUM、AVG、MAX 和 MIN 函數可以返回 null 值。當 COUNT 函數應用於空集時，它返回零（0）。如果返回值可能是 NULL，那麼使用包裝器類型；否則，容器會顯示 ObjectNotFound 異常。

使用 GROUP BY 和 HAVING 用於聚集函數的值的集合是由查詢的 FROM 和 WHERE 子句產生的集合來確定的。您可以將該集合分成幾個組，並將聚集函數應用於每個組。要執行此操作，請在查詢中使用 GROUP BY 子句。GROUP BY 子句定義組成路徑表達式列表的分組成員。每個路徑表達式指定作為基本類型（byte、short、int、long、float、double、boolean、char）或包裝器類型（Byte、Short、Integer、Long、Float、Double、BigDecimal、String、Boolean、Character、java.util.Calendar、java.util.Date、 java.sql.Date、java.sql.Time 或 java.sql.Timestamp）的字段。

以下示例描述計算每個部門的平均月薪的查詢中 GROUP BY 子句的使用：

SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e GROUP BY e.dept.deptno將集合分為幾個組時，認為一個 NULL 值等於另一個 NULL 值。

正如 WHERE 子句從 FROM 子句過濾元組（即，返回多值的記錄），可以使用測試組屬性（涉及聚集函數或分組成員）的 HAVING 子句來過濾組：SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e GROUP BY e.dept.deptnoHAVING COUNT(*) > 3 AND e.dept.deptno > 5

此查詢返回多於三個職員且部門號大於五的部門的平均月薪。

可以使用不帶 GROUP BY 子句的 HAVING 子句，在此情況下將整個集合視為應用 HAVING 子句的單個組。