SQL 中的核心数学函数

第 3.3 课 · 阅读时间：约 8 分钟 · 练习题 ↓

在本课中，你将学习核心的 SQL 数学函数，它们可以直接在查询中计算和转换数值数据。我们会介绍舍入、取模、乘方、开方和数值比较，并配有 Sakila 的实用示例。学完本课后，你将能够在分析和实际任务中熟练运用这些函数。

SQL 中的数学函数用于对数值数据进行各种计算。它们可以舍入数值、找出最小值和最大值、计算余数等等。本课介绍最常用的数学函数，并以 Sakila 数据库为例。

重要提示：SQL 中的数值数据可以使用不同的类型（INTEGER、REAL/FLOAT、DECIMAL/NUMERIC）。同一个公式会因数据类型不同而得到不同的结果（例如整数除法、舍入和精度的影响）。如果忽略数据类型，最终结果可能与预期不符。

常用数学函数

ABS()：返回数字的绝对值。

语法：

ABS(number)

示例：

SELECT ABS(amount - 5) AS abs_difference
FROM payment
LIMIT 3;

结果： 返回 amount 与 5 之差的绝对值。

CEIL() / CEILING()：向上取整（到最接近的整数）。

语法：

CEIL(number)
CEILING(number)

示例：

SELECT CEIL(amount) AS rounded_up
FROM payment
LIMIT 3;

结果： 把 amount 向上取整到最接近的整数。

FLOOR()：向下取整（到最接近的整数）。

语法：

FLOOR(number)

示例：

SELECT FLOOR(amount) AS rounded_down
FROM payment
LIMIT 3;

结果： 把 amount 向下取整到最接近的整数。

ROUND()：把数字舍入到指定的小数位数。

语法：

ROUND(number, decimals)

示例：

SELECT ROUND(amount, 1) AS rounded_amount
FROM payment
LIMIT 3;

结果： 把 amount 舍入到一位小数。

POWER() / POW()：求数字的乘方。

语法：

POWER(number, exponent)
POW(number, exponent)

示例：

SELECT POWER(amount, 2) AS squared_amount
FROM payment
LIMIT 3;

结果： 计算 amount 的平方。

SQRT()：返回数字的平方根。

语法：

SQRT(number)

示例：

SELECT SQRT(amount) AS sqrt_amount
FROM payment
LIMIT 3;

结果： 返回 amount 的平方根。

PI()：返回数学常数 π。

语法：

PI()

示例：

SELECT PI() AS pi_value;

结果： 返回 π 的值（约为 3.141592653589793）。

MOD()：返回除法的余数。

语法：

MOD(dividend, divisor)

示例：

SELECT MOD(payment_id, 5) AS mod_result
FROM payment
LIMIT 3;

结果： 返回 payment_id 除以 5 的余数。

在 WHERE 中使用函数的示例（查找偶数值）：

SELECT payment_id, amount
FROM payment
WHERE MOD(payment_id, 2) = 0
LIMIT 10;

结果： 只返回 payment_id 为偶数的行。

SIGN()：返回数字的符号（-1、0 或 1）。

语法：

SIGN(number)

示例：

SELECT SIGN(amount - 5) AS sign_value
FROM payment
LIMIT 3;

结果： 负数返回 -1，零返回 0，正数返回 1。

GREATEST()：返回所给值中的最大值（MySQL、PostgreSQL）。

语法：

GREATEST(value1, value2, ...)

示例：

SELECT GREATEST(amount, 5) AS max_value
FROM payment
LIMIT 3;

结果： 返回 amount 和 5 中较大的值。

重要（NULL）： GREATEST() 的行为取决于 DBMS。

• 在 MySQL/MariaDB 中，只要有一个参数为 NULL，结果通常就是 NULL。
• 在 PostgreSQL 中，NULL 参数会被忽略，只有当所有参数都是 NULL 时才返回 NULL。

LEAST()：返回所给值中的最小值（MySQL、PostgreSQL）。

语法：

LEAST(value1, value2, ...)

示例：

SELECT LEAST(amount, 5) AS min_value
FROM payment
LIMIT 3;

结果： 返回 amount 和 5 中较小的值。

重要（NULL）： LEAST() 在处理 NULL 时与 GREATEST() 存在相同的 DBMS 差异。

为了让不同 DBMS 中的行为可预期，通常会使用 COALESCE()，例如：

SELECT GREATEST(COALESCE(value1, 0), COALESCE(value2, 0));

RAND()：返回 0 到 1 之间的随机数。

语法：

RAND()

示例：

SELECT RAND() AS random_value
FROM payment
LIMIT 3;

结果： 返回 0 到 1 之间的随机数。

重要： 不要假定 RAND() 在任何情况下都会为每一行重新计算。根据 DBMS、执行计划、是否使用 CTE/子查询等因素，同一个随机值可能会被多行复用。

如果每行必须得到不同的随机值，请在你所用的 DBMS 上、针对具体的查询结构验证其行为。

实际应用场景

1. 对付款金额取整： 使用 ROUND(amount, 0) 把数值舍入为整数。

2. 按余数查找记录： 在 WHERE 中使用 MOD(payment_id, 2)（例如 MOD(payment_id, 2) = 0）查找编号为偶数的付款。

3. 计算平方根： 使用 SQRT(amount) 分析付款的分布。

4. 比较数值： 使用 GREATEST() 和 LEAST() 从多个值中选出最大值或最小值。

5. 控制数据类型： 如果精度很重要，请把值显式转换为所需的类型（例如 CAST(value AS DECIMAL(10,2))），以免整数运算和舍入带来意外。

常见问题

ROUND()、CEIL() 和 FLOOR() 有什么区别？

ROUND() 舍入到最接近的值（或指定的小数位），CEIL() 总是向上取整，FLOOR() 总是向下取整。

为什么相似的公式会返回不同的结果？

主要原因是数据类型。整数类型和小数类型在除法、舍入和精度方面的处理方式不同。

什么时候应该使用 MOD()？

当需要做周期性检查时，MOD() 很有用，例如把记录分组，或者筛选奇数/偶数编号。

为什么要关注 GREATEST() 和 LEAST() 在不同 DBMS 中的差异？

因为 MySQL/MariaDB 和 PostgreSQL 对 NULL 的处理可能不同。为了让行为可预期，通常会使用 COALESCE()。

面试问题

如何为业务规则选择合适的舍入函数？

先明确规则：标准舍入（ROUND）、总是向上（CEIL）还是总是向下（FLOOR）。然后检查它对最终报表指标的影响。

不做显式类型转换的计算有哪些风险？

可能会因为整数除法或精度损失而得到意外的结果。在关键计算中，使用 CAST(... AS DECIMAL(...)) 这样的显式转换更安全。

SIGN() 有什么作用，适用于哪些场景？

SIGN() 根据值的符号返回 -1、0 或 1。它适合快速把偏差分为负、零和正三类。

为什么在分析查询中要谨慎使用 RAND()？

根据 DBMS 的行为和执行计划，随机值可能不会像预期那样逐行重新计算。务必在你使用的数据库引擎上验证其行为。

本课要点：

• SQL 数学函数可以直接在查询中完成计算和转换。
• ROUND、CEIL 和 FLOOR 解决不同的舍入问题，正确的选择取决于业务规则。
• MOD、POWER 和 SQRT 在分析和数据检查中很实用。
• 数据类型对精度和最终计算结果影响很大。
• 编写跨数据库的查询时，要考虑不同 DBMS 在函数中处理 NULL 的差异。