结构化查询语言/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;