数据库原理与选型 / 系统课时
数值函数与表达式计算
数值函数与表达式计算
本课解决 SQLite 中数值运算结果不符合直觉的问题。完成后,你可以正确处理运算优先级、整数除法、类型转换、舍入和随机数范围,并能通过查询结果检查计算是否符合预期。
学习目标
- 理解算术运算优先级和括号的作用。
- 使用
abs、round与random处理常见数值需求。 - 通过实数参与运算,避免整数除法截断小数,并识别除零等边界情况。
运算优先级
SQLite 支持加法 +、减法 -、乘法 *、除法 / 和取余 %。乘除和取余的优先级高于加减,同一优先级通常从左向右计算,括号可以明确改变计算顺序。
SELECT
2 + 3 * 4 AS default_order,
(2 + 3) * 4 AS with_parentheses;预期输出
default_order | with_parentheses
14 | 20第一列表达式先计算 3 * 4,再加 2,因此得到 14;第二列先计算括号中的 2 + 3,因此得到 20。
复杂表达式即使不加括号也能运行,但显式括号更容易审核,也能减少修改表达式时产生的错误。例如计算含折扣和税率的金额时,应使用括号明确税费是在折扣前还是折扣后计算,避免仅凭运算符优先级推断业务规则。
整数除法与类型转换
当除号两侧都是整数时,SQLite 执行整数除法,结果向零截断,而不是四舍五入。让任意一侧成为实数即可保留小数部分。
SELECT
7 / 2 AS integer_result,
7 / 2.0 AS real_result,
CAST(7 AS REAL) / 2 AS cast_result;预期输出
integer_result | real_result | cast_result
3 | 3.5 | 3.57 / 2 的两个操作数都是整数,因此得到 3;7 / 2.0 中存在实数,因此得到 3.5;CAST(7 AS REAL) 则显式将整数转换为实数。可以使用 typeof(7 / 2) 和 typeof(7 / 2.0) 检查结果类型,分别为 integer 和 real。
比例、平均值和百分比计算通常应显式乘以 1.0,或使用 CAST(... AS REAL)。例如 20 次请求成功 7 次,成功率应为 35%。表达式 7 * 100.0 / 20 得到 35.0,而 7 / 20 * 100 因为先发生整数除法,最终得到 0。
还需要处理除数为零的情况。SQLite 中除以零返回 NULL,而不是抛出除零异常。对于来自表字段的分母,可使用 NULLIF(total, 0) 将零转换为 NULL,避免把无法计算的比例误当作有效数值。使用 COALESCE 将其替换成默认值之前,应先确认业务上是否允许将未知结果解释为零。
绝对值与四舍五入
abs(X) 返回数值的绝对值,常用于误差计算和差值统计。round(X, N) 将数值四舍五入到小数点后 N 位;省略 N 时按 0 位处理。
SELECT
abs(-12.5) AS absolute_value,
round(3.14159, 2) AS rounded_two_digits,
round(2.5) AS rounded_integer;预期输出
absolute_value | rounded_two_digits | rounded_integer
12.5 | 3.14 | 3.0abs(-12.5) 得到 12.5;round(3.14159, 2) 得到 3.14;round(2.5) 得到 3.0。SQLite 的 round 返回实数结果,即使舍入后没有小数部分,也可能显示为 3.0。
需要注意,round 的舍入规则不是银行家舍入。对于恰好位于两个整数中间的数,SQLite 的 round 会按远离零的方向舍入,因此 round(2.5) 为 3.0,而 round(-2.5) 为 -3.0。
round(X, N) 适合展示和普通统计,不应代替金融系统中的精确金额规则。SQLite 的 REAL 使用浮点数表示,部分十进制小数不能被精确存储。对于必须精确计算的金额,通常应根据业务规则使用整数最小货币单位,例如用整数分数表示金额,并在展示时转换。
生成指定范围的随机整数
random() 返回一个带符号的 64 位伪随机整数。如果只需要生成指定范围内的测试数据,可以先取余,将结果限制在较小范围,再取绝对值并调整起点。
下面生成 1 到 100 的随机整数,并通过 BETWEEN 检查范围。
WITH r(n) AS (
SELECT abs(random() % 100) + 1
)
SELECT
n,
n BETWEEN 1 AND 100 AS valid
FROM r;预期输出
n | valid
<1到100之间的整数> | 1random() % 100 的结果位于 -99 到 99 之间,abs 将其转换为非负数,再加 1,结果便位于 1 到 100 之间。BETWEEN 包含上下界,条件成立时返回整数 1。
n 每次执行都可能不同,但 valid 应始终为 1。可以重复执行查询 10 次,观察随机数变化,并确认每次结果都位于指定范围。
实际编写数值查询时,应先明确操作数类型和运算顺序,再根据业务要求决定是否需要舍入。验证时重点检查整数除法、零分母、舍入边界和随机数范围,而不只检查普通输入下的计算结果。