SQLite 数学函数：ROUND、ABS、CEIL、FLOOR 等

SQLite 的数学能力比你想象的多

SQLite 以精简著称，但它自带了一整套数值函数：四舍五入、绝对值、向上和向下取整、乘方、开方、对数、三角函数、随机数。大部分数学函数是在 SQLite 3.35（2021 年）加入的，所以任何还算新的环境，比如 Python、Node 自带的版本、浏览器里 WebSQL 的前身，或者官方命令行工具，都可以直接使用。

深入之前，先快速看一组示例：

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

六个函数，一行结果。这一页剩下的部分会逐一介绍每类函数的用途，以及值得知道的坑。

ROUND：你最常用的那个

ROUND(value, digits) 把数值四舍五入到指定的小数位数。第二个参数可以省略，省略时会四舍五入到最接近的整数（但结果仍然是浮点数）：

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

有几点需要注意：

• ROUND(3.14159) 返回的是 3.0，不是 3。如果你想要整数，用 CAST(ROUND(x) AS INTEGER)；如果要直接截断，用 CAST(x AS INTEGER)。
• SQLite 采用“远离零方向舍入”：2.5 舍入为 3，-2.5 舍入为 -3。有些数据库使用银行家舍入法（四舍六入五成双），SQLite 不是。
• digits 参数可以是负数：ROUND(1234.5, -2) 会舍入到最接近的 100，得到 1200。

实际工作中，你写得最多的大概是用来显示金额的 ROUND(price, 2)。

ROUND 和 CAST 不一样

很多人想四舍五入时却用了 CAST(x AS INTEGER)，结果就掉进了坑里：

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

CAST 是向零截断，直接把小数部分扔掉。ROUND 则是四舍五入到最接近的整数。对 2.9 来说，两者相差整整 1。选哪个，取决于你真正想要的行为。

ABS、SIGN 与数的正负

ABS(x) 返回绝对值。SIGN(x) 根据正负返回 -1、0 或 1：

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS 是最常用的，在“这两个值相差多少”这类查询里很方便。SIGN 用得少一些，但当你想按方向给行分组（支出还是收入、盈利还是亏损），又不想写一个显式的 CASE 时，它很有用。

CEIL、FLOOR 和 TRUNC

这几个函数得到的是整数形式的值，但不是四舍五入。CEIL 总是向上取，FLOOR 总是向下取，TRUNC 总是向零取：

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

留意负数的情况。FLOOR(-2.9) 是 -3（离零更远），而 TRUNC(-2.9) 是 -2（向零靠近）。处理负数时 FLOOR 和 TRUNC 的结果不同，选错了就是经典的差一错误。

CEILING 是 CEIL 的别名，哪个读起来顺手就用哪个。

真正的坑是整数除法

这不是函数，而是 / 运算符，但它绊倒新手的次数比任何数学函数都多：

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

当两边都是整数时，SQLite 会做整数除法并截断结果。只要有一边是 REAL，整个表达式就变成实数运算。解决办法是确保至少有一个操作数是浮点数：要么把 2 写成 2.0，要么做类型转换。

引用列的时候最容易中招：total_cents / 100 返回的是整数，total_cents / 100.0 才会返回你真正想要的美元金额。

MOD 和 % 运算符

MOD(x, y) 返回 x / y 的余数。% 运算符做的是同一件事：

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) 和 17 % 5 都返回 2。在 SQLite 中对零取模会返回 NULL，而不是报错，这和大多数编程语言不一样。如果你在意这一点，就先检查除数，或者把调用包在 CASE WHEN y = 0 THEN ... END 里。

函数写法和运算符写法可以互换。大多数人会用 %，因为更短。

POWER、SQRT、EXP、LOG

用于乘方和开方：

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

几个容易让人出错的地方：

• POW 是 POWER 的别名。
• SQLite 中的 LOG(x) 以 10 为底。LN(x) 是自然对数。带两个参数的 LOG(b, x) 是以 b 为底的对数。（这和很多编程语言不同，那些语言里 log 是自然对数，这里遵循的是 SQL 的惯例。）
• 对负数求 SQRT 会返回 NULL，而不是报错。
• 按照惯例，POWER(0, 0) 返回 1。

计算复利、换算成分贝、计算距离，凡是涉及几何或指数运算的地方，这些函数都用得上。

RANDOM 和 RANDOMBLOB

RANDOM() 返回一个有符号 64 位整数，可能落在整个取值范围内的任何位置：

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

要得到某个范围内的数，先用 ABS 包一层（因为 RANDOM() 是有符号的），再用 %。要得到 0 到 1 之间的实数，就除以 64 位整数的最大值。SQLite 没有返回 0 到 1 之间随机数的内置 RAND()，需要你自己拼出来。

RANDOMBLOB(n) 返回 n 个字节的随机数据，适合用来生成会话令牌或测试数据。配合 HEX() 可以得到可打印的字符串：

SELECT HEX(RANDOMBLOB(8)) AS token;

每次调用都会产生一个新值。不要指望 RANDOM() 在同一行里两次返回相同的数，即使在同一个表达式里，每次调用也是相互独立的。

综合运用

一个小例子：对一张商品表做几项计算，并把价格舍入到分。

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Mug',     1299, 350.5),
    (2, 'Notebook', 850, 180.2),
    (3, 'Pen',      199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

关键在于 price_cents / 100.0：那个 .0 让除法变成实数运算，然后 ROUND 把结果保留到两位小数。没有它，1299 / 100 得到的是 12，而不是 12.99。

下一篇：日期与时间

数值函数负责数学运算，日期和时间则需要一套自己的工具。SQLite 把它们存成文本、实数或整数，并提供了一组小而强大的函数，用来解析、格式化日期时间并做日期运算。这就是接下来的内容。