一、八个函数，常用的只有三个

真正天天用的是 ROUND、ABS、CEILING/FLOOR 这三个。POWER 和 SQRT 偶尔用， 三角函数基本不用——那类计算应该放在程序里做。 SQUARE(x) 就是 x*x。 实际项目里直接写 x*x 更省事， 也不依赖函数存在——SQLite 根本没有 SQUARE 和 POWER，本机是靠垫片补的。 CEILING 和 FLOOR 的名字在别的库里可能是 CEIL。差一个字母、 功能一样——这种差异只能靠查文档，没法靠猜。

二、ROUND 的两套圆整规则

这一节最该看的是这张表。同一个值，SQL 和 Python 给出不同结果：

2.5 这一行是关键：SQL 给 3，Python 给 2。SQL 的规则是「四舍五入、远离零」， 遇到 .5 一律往上（或者往下，看正负）走；Python 的是「银行家舍入」，遇到 .5 就取最近的偶数。 这不是谁对谁错。 银行家舍入在统计上更公平（大量数据累计时不会系统性偏大）， 但业务上「2.5 变 2」多数人不能接受——因为大家从小算的是四舍五入。 所以算钱的时候，别用 Python 的 round() 去模仿 SQL 的 ROUND。两边结果会对不上， 而且只在 .5 那一个点上对不上——测试的时候很容易漏过去。 还有一行值得看：2.565 两边都给出 2.56。不是算错了——2.565 在二进制里其实是 2.56499…， 本来就比 2.565 小一点。这是浮点表示的问题，换成 decimal 类型就不会有。

三、RAND：带种子就可复现

RAND() 不带参数，每次调用结果不同；RAND(42) 带种子，同一个种子永远给同一串数。

RAND()      -- 0.1446、0.9163……每次都不一样
RAND(42)    -- 永远是 0.6394、0.0250、0.2754……

可复现这件事在测试里很有用。 造一批随机数据压测，出了 bug——带上种子就能原样重跑一遍， 定位到具体哪一行。不带种子，这个 bug 就复现不了。 但反过来，抽奖、发券、生成密码都不能用它。它的「随机」是伪随机：算法固定，种子一猜出来， 整串序列都能算出来。这类场景要用真正随机的源，或者用 NEWID() 那种基于系统熵的。 RAND() 还有一个坑：在 SELECT 里它只算一次。SELECT RAND() FROM 一张一万行的表， 一万行拿到的是同一个值——因为它被当成常量了。 要让每行不同，得用 RAND(CHECKSUM(NEWID())) 这种写法。

四、AVG 为什么「不准」

这是本节最实用的一段。先看数据：四行，90、80、NULL、70。

AVG 会自动跳过 NULL，所以分母是 3——(90+80+70)/3 = 80。 这在「没考试」的场景下是对的，在「考了 0 分」的场景下是错的。 两个数差 20 分，而语句只差一个 COALESCE。这就是所谓的「统计口径」—— 口径错了，后面所有分析全错，而且看不出来。 判断标准是业务语义： NULL 表示「没有这个值」还是「值是 0」？ 没参加考试是「没有」，考了 0 分是「0」——这两种情况在数据上必须分开存，否则再怎么算都算不对。 顺带说一句：SUM 也跳过 NULL。 所以「总销售额」这种指标，如果有一行是 NULL， 它算出来是「已知部分的和」——不是「总额」，是「部分和」。

五、动手 Demo：ROUND 两套规则与 AVG 的分母

这一段跑八个数学函数，重点看 ROUND 在 SQL 和 Python 下的差别；再演一次带种子和不带种子的随机数； 最后用四行数据说明 AVG 的分母为什么是 3。

import sqlite3

c = sqlite3.connect(u":memory:")

# ROUND：SQL 与 Python 的差别
for v in (2.5, 3.5, -2.5):
    s = c.execute(u"SELECT round(?, 0)", (v,)).fetchone()[0]
    print(v, u"SQL", s, u"Python", round(v))

# AVG 跳过 NULL
c.execute(u"CREATE TABLE tb (score INT)")
c.executemany(u"INSERT INTO tb VALUES (?)",
              [(90,), (80,), (None,), (70,)])
print(u"COUNT(*)   =", c.execute(u"SELECT COUNT(*) FROM tb").fetchone()[0])
print(u"COUNT(score)=", c.execute(u"SELECT COUNT(score) FROM tb").fetchone()[0])
print(u"AVG(score) =", c.execute(u"SELECT AVG(score) FROM tb").fetchone()[0])
print(u"AVG(COALESCE) =",
      c.execute(u"SELECT AVG(COALESCE(score,0)) FROM tb").fetchone()[0])

实跑输出：

--------------------------------------------------------------------
数学函数与 ROUND 的两套圆整
--------------------------------------------------------------------


  ① 八个常用数学函数
  --------------------------------------------------------------------
| 函数 | 作用 | 算出来 |
|---|---|---|
| ABS(-12.5) | 绝对值 | 12.5 |
| PI() | 圆周率 | 3.14159265358979 |
| POWER(2,10) | 2 的 10 次方 | 1024.0 |
| SQUARE(7) | 7 的平方 | 49 |
| SQRT(144) | 平方根 | 12.0 |
| ROUND(2.567,2) | 四舍五入到 2 位 | 2.57 |
| CEILING(2.1) | 向上取整 | 3.0 |
| FLOOR(2.9) | 向下取整 | 2.0 |

  POWER 和 SQUARE 是 SQL Server 的函数；SQLite 没有 power（本机是靠垫片补的）。
  CEILING 和 FLOOR 在其它库里可能叫 CEIL——名字差一个字母，功能一样。
  SQUARE(x) 就是 x*x，实际项目里直接写 x*x 更省事，也不依赖函数存在。

  ② ROUND 的两套圆整规则
  --------------------------------------------------------------------
| 值 | 小数位 | SQLite round() | Python round() |
|---|---|---|---:|
| 2.5 | 0 | 3.0 | 2.0 |
| 3.5 | 0 | 4.0 | 4.0 |
| -2.5 | 0 | -3.0 | -2.0 |
| 2.567 | 2 | 2.57 | 2.57 |
| 2.565 | 2 | 2.56 | 2.56 |

  2.5 这一行是关键：SQLite 给 3，Python 给 2。
  SQLite（以及 SQL Server）的 ROUND 是「四舍五入、远离零」，
  Python 的 round() 是「银行家舍入」——遇到 .5 就取最近的偶数。
  这不是谁对谁错：银行家舍入在统计上更公平，但业务上「2.5 变 2」多数人不能接受。
  所以算钱的时候别用 Python 的 round() 去模仿 SQL 的 ROUND，两边结果会对不上。
  还有一行值得看：2.565 两边都给出 2.56。不是算错了——2.565 在二进制里是 2.56499…，
  本来就比 2.565 小一点。这是浮点表示的问题，换成 decimal 类型就不会有。

  ③ RAND：带种子就是可复现的
  --------------------------------------------------------------------
  同一个种子 42，两次取三个随机数：
    第 1 次 [0.6394, 0.025, 0.275]
    第 2 次 [0.6394, 0.025, 0.275]
    完全一样：是
  不设种子，两次不同：0.7197 和 0.9427
  SQL Server 的 RAND() 不带参数每次不同，RAND(42) 带种子就可复现。
  可复现这件事在测试里很有用——造一批随机数据，出了 bug 能原样重跑一遍。
  但别用它生成密码或者抽奖结果：种子被猜到，序列就全被算出来了。

  ④ AVG 为什么有时候「不准」
  --------------------------------------------------------------------
  四行数据：90、80、NULL、70
    COUNT(*)     = 4（数所有行）
    COUNT(score) = 3（不数 NULL）
    AVG(score)   = 80.00（分母是 3，不是 4）
  AVG 会自动跳过 NULL，所以分母是 3——(90+80+70)/3 = 80。
  如果业务上「没考试算 0 分」，那就得写 AVG(COALESCE(score,0))，结果变成 60。
  这两个数差的不是一点半点，而语句只差一个函数——这是统计口径的典型陷阱。

第一段三行里，第一行和第三行 SQL 与 Python 结果不同（2.5→3 对 2，-2.5→-3 对 -2）。 中间那个 3.5 两边都是 4——因为 4 是偶数，正好符合银行家舍入。 所以「差一个」这件事只在某些值上出现。 这比「全都差」更麻烦——测试用例碰巧没覆盖到 .5， 你就以为两边一致。 第二段四个数是 4、3、80.0、60.0。 前两个说明 COUNT 的两种写法差别， 后两个说明口径差别——同一个表，两种算法差 20 分。 AVG(COALESCE(score,0)) 那个 60.0 才是「把缺考当 0 分」的正确算法。 而 80.0 是「只算参加了的」——两个都对，取决于业务要怎么算。

30 秒实验

1. 在你的库上跑 SELECT ROUND(2.5, 0), ROUND(3.5, 0), ROUND(-2.5, 0)。 SQL Server 给 3、4、-3——全部远离零。 而 Python 给 2、4、-2。
2. 造一张有 NULL 的表，分别算 AVG(列) 和 SUM(列)/COUNT(*)。 两个结果不一样——因为前者分母不含 NULL，后者含。
3. 想一想：为什么 RAND() 不能用来抽奖？ 因为它是伪随机，算法固定。 种子一旦被猜到，整串序列都能算出来——这不是随机，是可预测。

常见误区

1. 「ROUND 就是四舍五入，跟编程语言一样」。不一样。 Python、C# 默认用银行家舍入，SQL 用远离零。2.5 一个得 3、 一个得 2——算钱的时候这个差别会变成一分钱的账不平。
2. 「AVG 的分母就是行数」。不是。 它跳过 NULL，分母是「非空值的个数」。要包含空行， 得写 SUM/COUNT(*) 或者 AVG(COALESCE(...))。
3. 「RAND() 每行都会重新算」。在 SELECT 里它只算一次， 整列拿到同一个值——因为它被当成常量了。要每行不同，得用 RAND(CHECKSUM(NEWID()))。

下一篇 7.2 讲字符串函数。 十二个函数里只有六个名字一样，而且 CHARINDEX 和 instr 的参数顺序是反的——反了不报错， 只是永远返回 0。

• ☐ （本篇完，下一篇 7.2 字符串函数）

相关笔记

• 字符串函数
• 运算符
• 48 课 MySQL从入门到精通 · 索引
• 🪟 SQL Server从入门到精通 · 索引