結構化查詢語言/SELECT：預定義函式

有兩組預定義函式

• 聚合函式。它們作用於一組行，這意味著它們接收一組行中的每一行的值，併為整個集合返回一個值。如果它們在 GROUP BY 子句的上下文中被呼叫，則它們對每個組呼叫一次，否則對所有行呼叫一次。
• 標量函式。它們作用於單個行，這意味著它們接收單個行的值，併為每個值返回一個值。

聚合函式

它們作用於一組行，並返回單個值，如行數、最高或最低值、標準差等。最重要的聚合函式是

簽名	語義
COUNT(*)	行數
COUNT(<列名>)	其中 <列名> 包含值（IS NOT NULL）的行數。在所有聚合函式中，都會消除所考慮列中具有 NULL 特殊標記的行。
MIN(<列名>)	最低值。對於字串，根據字元的順序。
MAX(<列名>)	最高值。對於字串，根據字元的順序。
SUM(<列名>)	所有值的總和
AVG(<列名>)	算術平均值

例如，我們檢索所有人的最大重量

SELECT MAX(weight)
FROM   person;

注意事項
聚合函式為一組行生成一個值。因此，不可能將它們與投影中的“正常”列一起使用（SELECT 關鍵字後面的部分）。例如，如果我們指定

SELECT lastname, SUM(weight)
FROM   person;

我們試圖指示 DBMS 顯示包含 lastname 的很多行以及一個值。這是一個矛盾，系統會丟擲異常。我們可以在一個投影中使用多個聚合函式，但不能將它們與“正常”列一起使用。

-- Multiple aggregate functions. No 'normal' columns.
SELECT SUM(weight)/COUNT(weight) as average_1, AVG(weight) as average_2
FROM   person;

分組
如果我們在包含 GROUP BY 的命令上下文中使用聚合函式，則每個組呼叫一次聚合函式。

-- Not only one resulting row, but one resulting row per lastname together with the average weight of all rows with this lastname.
SELECT AVG(weight)
FROM   person
GROUP BY lastname;

在這種情況下，GROUP BY 列可以顯示，因為它們不可能在組內發生變化。

-- The lastname may be shown as it is the GROUP BY criteria
SELECT lastname, AVG(weight)
FROM   person
GROUP BY lastname;

NULL 特殊標記

如果一行在命名列中沒有值（它包含 NULL 特殊標記），則該行不屬於計算的一部分。

-- If ssn is NULL, this row will not count.
SELECT COUNT(ssn)
FROM   person;

ALL vs. DISTINCT

函式的完整簽名更詳細一些。我們可以在列名前面加上兩個關鍵字 ALL 或 DISTINCT 之一。如果我們指定 ALL，這是預設值，則每個值都是計算的一部分，否則只有那些彼此不同的值。

function_name ([ALL|DISTINCT]<列名>)
COUNT (DISTINCT weight) -- 舉個例子

提示

標準定義了更多聚合函式來計算統計量度。此外，關鍵字 ANY、EVERY 和 SOME 也被正式定義為聚合函式。我們將在單獨的頁面上討論它們。

標量函式

標量函式按“每行”執行。它們對每行呼叫一次，並對每次呼叫返回一個值。它們通常根據它們所作用的資料型別進行分組

• 字串函式

SUBSTRING(<列名> FROM <pos> FOR <len>) 返回從 <pos> 位置（第一個字元計數為“1”）開始的長度為 <len> 的字串。

UPPER(<列名>) 返回列值的對應大寫形式。

LOWER(<列名>) 返回列值的對應小寫形式。

CHARACTER_LENGTH(<列名>) 返回列值的長度。

TRIM(<列名>) 返回不含前導和尾隨空格的列值。

TRIM(LEADING FROM <列名>) 返回不含前導空格的列值。

TRIM(TRAILING FROM <列名>) 返回不含尾隨空格的列值。

• 數值函式

SQRT(<列名>) 返回列值的平方根。

ABS(<列名>) 返回列值的絕對值。

MOD(<列名>, <除數>) 返回列值除以除數的餘數。

其他：FLOOR、CEIL、POWER、EXP、LN。

• 日期、時間和時間間隔函式

EXTRACT(month FROM date_of_birth) 返回 date_of_birth 列的月份。

• 內建函式。它們沒有輸入引數。

CURRENT_DATE() 返回當前日期。

CURRENT_TIME() 返回當前時間。

還有另一本華夏公益教科書詳細介紹了這些函式 詳細資訊。返回值的資料型別並不總是與輸入型別相同，例如，'character_length()' 接收字串並返回數字。

以下是一些使用標量函式的示例

SELECT LOWER(firstname), UPPER(lastname), CONCAT('today is: ', CURRENT_DATE)
FROM   person;

練習

到目前為止，愛好者表中使用了哪個最大的 ID？

點選檢視解決方案

SELECT max(id)
FROM   hobby;

在排序列表中，哪個姓氏會排在最前面？

點選檢視解決方案

SELECT min(lastname)
FROM   person;

是否存在使用 ALL 或 DISTINCT 關鍵字沒有區別的聚合函式？

點選檢視解決方案

Yes. min(ALL <column name>) leads to the same result as min(DISTINCT <column name>) as

it makes no difference whether the smallest value occurs one or more times. The same is true for max().

顯示姓氏較短（最多 4 個字元）的人。

點選檢視解決方案

-- We can use functions as part of the WHERE clause.
SELECT *
FROM   person
WHERE  character_length(firstname) <= 4; -- Hint: Some implementations use a different function name: length() or len().

顯示姓氏、名字以及連線字串的字元數。找到兩種不同的解決方案。您可以使用 character_length() 函式來計算字串的長度，並使用 concat() 函式來連線字串。

點選檢視解決方案

-- Addition of the computed length. Hint: Some implementations use a different function name: length() or len().
SELECT firstname, lastname, character_length(firstname) + character_length(lastname)
FROM   person;
-- length of the concatenated string
SELECT firstname, lastname, character_length(concat (firstname, lastname))
FROM   person;
-- show both solutions together
SELECT firstname, lastname,
       character_length(firstname) + character_length(lastname) as L1,
       character_length(concat (firstname, lastname)) as L2
FROM   person;