课程 3.3 · 阅读时间：~8分钟

在本课程中，您将学习核心SQL数学函数，这些函数帮助您直接在查询中计算和转换数字数据。我们将涵盖四舍五入、取模、幂、根和数值比较，并提供Sakila的实际示例。到课程结束时，您将能够自信地在分析和实际任务中应用这些函数。

SQL中的核心数学函数

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()遵循与GREATEST()相同的特定DBMS的NULL行为。

为了使行为在不同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）查找偶数支付ID。

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

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

5. 控制数据类型： 如果精度很重要，请明确将值转换为所需类型（例如，CAST(value AS DECIMAL(10,2))），以避免因整数运算和四舍五入造成的意外情况。

常见问题

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

ROUND()四舍五入到最近的值（或到指定的小数位），CEIL()始终向上取整，而FLOOR()始终向下取整。

为什么类似的公式可能返回不同的结果？

主要原因是数据类型。整数和小数类型在处理除法、四舍五入和精度时有所不同。

何时应使用MOD()？

当您需要周期性检查时，MOD()非常有用，例如将记录分组或过滤偶数/奇数标识符。

为什么我应该关注GREATEST()和LEAST()的DBMS差异？

因为NULL处理在MySQL/MariaDB和PostgreSQL之间可能有所不同。为了获得可预测的行为，通常使用COALESCE()。

面试问题

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

首先定义规则：标准四舍五入（ROUND）、始终向上（CEIL）或始终向下（FLOOR）。然后验证它对最终报告指标的影响。

在没有明确类型转换的情况下进行计算的风险是什么？

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

SIGN()做什么，它在哪里有用？

SIGN()根据值的符号返回-1、0或1。它对于快速分类偏差为负、零或正非常有用。

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

根据DBMS行为和执行计划，随机值可能不会按预期在每行中重新计算。始终验证您引擎上的行为。

本课程的关键要点：

• SQL数学函数帮助您直接在查询中执行计算和转换。
• ROUND、CEIL和FLOOR解决不同的四舍五入任务，正确的选择取决于业务规则。
• MOD、POWER和SQRT在分析和数据检查中非常实用。
• 数据类型对精度和最终计算结果有很大影响。
• 对于跨DB查询，请考虑函数中的特定DBMS的NULL行为。

在下一课中，我们将转向日期和时间函数，并学习如何在SQL中处理时间值。