RANDOM / SETSEED
random() 生成 [0, 1) 区间的伪随机浮点数，setseed() 用于设定随机种子以获得可复现结果。看似简单，但它的三个性质决定了正确用法：左闭右开区间、每行重新求值（VOLATILE）、不是密码学安全。最常见的两个灾难是「ORDER BY random() LIMIT 10 打爆全表排序」和「用 round(random()) 得到分布不均的 0/1」。本节把区间换算、可复现性、大表抽样、测试数据生成四类问题讲透。
前置知识
阅读本节前，建议先了解：SQRT / CBRT / EXP / LN / LOG
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 伪随机数 | Pseudo-Random Number | 由确定性算法生成，非真随机 |
| 随机种子 | Seed | 决定随机序列起点的初始值 |
| 可复现性 | Reproducibility | 同一种子产生同一序列 |
| 左闭右开 | Half-Open Interval | [0, 1)：含 0、不含 1 |
| 会话 | Session | 一个客户端连接，种子仅在会话内生效 |
| 函数易变性 | Volatility | VOLATILE 函数每行重新求值 |
| 均匀分布 | Uniform Distribution | 区间内每个值等概率 |
| 正态分布 | Normal Distribution | 钟形分布，由均值与标准差决定，random_normal() 生成 |
| 均值 | Mean | 正态分布的中心位置参数 |
| 标准差 | Standard Deviation | 正态分布的离散程度参数 |
| 抽样 | Sampling | 从总体中取出子集 |
| 表采样 | TABLESAMPLE | PostgreSQL 内建的高速抽样语法 |
| 密码学安全 | Cryptographically Secure | 结果不可从历史推测，random() 不满足 |
| 测试数据 | Test Fixture | 用于压测与验证的合成数据 |
random() 的四个关键性质：
| 性质 | 表现 | 后果 |
|---|---|---|
| 区间 | 返回 [0, 1)，可能为 0，不可能为 1 | 乘 N 后最大值为 N-ε |
| 易变性 | 标记为 VOLATILE | 每行/每次调用都重新求值 |
| 精度 | 返回 double precision（53 位有效尾数） | 可区分约 2⁵³ 个不同值 |
| 可播种 | setseed() 影响当前会话 | 种子只对本连接有效，连接池下不可靠 |
random() 不可能返回 1
这看起来是细节，但直接影响区间换算。random() * 10 的结果范围是 [0, 10)，永远不会是 10。若你想取 1..10 的整数，写 floor(random() * 10) + 1，不要写 ceil(random() * 10) —— 后者在 random() 返回 0 时得到 0，超出预期区间。
区间换算对照表（这是本节最该记住的一张表）：
| 目标区间 | 正确写法 | 常见错误写法 | 错误原因 |
|---|---|---|---|
[0, 1) | random() | — | 原生行为 |
[0, 10) | random() * 10 | — | 原生行为 |
1..10（整数） | floor(random() * 10) + 1 | ceil(random() * 10) | random()=0 时得到 0 |
0..9（整数） | floor(random() * 10) | round(random() * 10) | round 使两端概率减半 |
5..15（整数） | floor(random() * 11) + 5 | round(random() * 10) + 5 | 分布不均 |
[a, b) | random() * (b - a) + a | — | — |
-1..1（整数） | floor(random() * 3) - 1 | sign(random() - 0.5) | 0 的概率为 0 |
一句话记忆：floor(random() * 区间长度) + 左端点，这是整数随机数的唯一正确公式。
语法与用法
-- 生成 [0, 1) 的伪随机数
random()
-- 带范围的随机数（PostgreSQL 17+，闭区间 [min, max]）
random(min, max) -- integer / bigint / numeric 三种重载
-- 正态分布随机数（PostgreSQL 16+）
random_normal() -- 标准正态 N(0, 1)
random_normal(mean) -- 指定均值
random_normal(mean, stddev) -- 指定均值与标准差
-- 设定随机种子（返回 void）
setseed(seed) -- seed 为 double precision，范围 [-1, 1]
-- 内建表采样（大表抽样的正确姿势）
SELECT * FROM table_name TABLESAMPLE SYSTEM (percent);
SELECT * FROM table_name TABLESAMPLE BERNOULLI (percent);
-- 相关配套
generate_series(start, stop [, step]) -- 生成序列，用于造数
-- pgcrypto 扩展（密码学安全，需 CREATE EXTENSION pgcrypto）
gen_random_bytes(n)
gen_random_uuid()最小可运行示例：
-- 生成几个随机数
SELECT
random() AS r1,
random() AS r2,
floor(random() * 10)::int AS int_0_9,
floor(random() * 10 + 1)::int AS int_1_10,
floor(random() * 11 + 5)::int AS int_5_15; r1 | r2 | int_0_9 | int_1_10 | int_5_15
---------------------+---------------------+---------+----------+----------
0.35678901234567890 | 0.91234567890123456 | 3 | 9 | 12准备本节贯穿使用的示例表：
-- 用户表：用于抽样、随机排序、造数演示
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL,
city TEXT NOT NULL,
score INTEGER NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 用 generate_series 造 10000 条测试数据
INSERT INTO users (username, email, city, score, is_active)
SELECT
'user_' || lpad(i::text, 6, '0'),
'user_' || lpad(i::text, 6, '0') || '@example.com',
(ARRAY['北京市', '上海市', '广州市', '深圳市', '杭州市', '成都市'])[floor(random() * 6 + 1)],
floor(random() * 1000)::int,
random() < 0.8 -- 80% 为活跃用户
FROM generate_series(1, 10000) AS i;
-- 抽奖活动表
CREATE TABLE lottery_prizes (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
prize_name TEXT NOT NULL,
stock INTEGER NOT NULL,
weight INTEGER NOT NULL -- 权重，越大中奖概率越高
);
INSERT INTO lottery_prizes (prize_name, stock, weight) VALUES
('一等奖 · 机械键盘', 1, 1),
('二等奖 · 无线鼠标', 5, 5),
('三等奖 · 鼠标垫', 20, 20),
('参与奖 · 5 元券', 500, 200);-- 基础演示：随机排序与随机抽样
SELECT
count(*) AS total_users,
count(*) FILTER (WHERE is_active) AS active_users
FROM users; total_users | active_users
-------------+--------------
10000 | 8012-- 简单随机抽样：随机取 5 个用户（小表可用，大表慎用）
SELECT id, username, city, score
FROM users
ORDER BY random()
LIMIT 5; id | username | city | score
-------+---------------+--------+-------
1842 | user_001842 | 杭州市 | 417
6503 | user_006503 | 深圳市 | 836
927 | user_000927 | 成都市 | 92
7710 | user_007710 | 上海市 | 623
3388 | user_003388 | 北京市 | 255详细说明
区间换算：正确公式与常见错误
正确公式：要生成 [lo, hi] 闭区间的整数，用
floor(random() * (hi - lo + 1)) + lo-- 区间换算实测：生成 1..6 的骰子点数
SELECT
floor(random() * 6)::int + 1 AS dice_correct,
ceil(random() * 6)::int AS dice_wrong_ceil,
round(random() * 6)::int AS dice_wrong_round
FROM generate_series(1, 1); dice_correct | dice_wrong_ceil | dice_wrong_round
--------------+-----------------+------------------
4 | 5 | 6为什么 ceil 是错的：当 random() 返回 0 时，ceil(0 * 6) = 0，得到 0 点。虽然概率极低（约 2⁻⁵³），但在海量数据生成中必然出现。
-- 验证 ceil 的错误：强制 random 为 0 的效果
SELECT
ceil(0 * 6)::int AS ceil_at_zero, -- 0（越界！）
floor(0 * 6)::int + 1 AS floor_at_zero; -- 1（正确） ceil_at_zero | floor_at_zero
--------------+---------------
0 | 1为什么 round 是错的：round(random() * 6) 中，只有当 random() * 6 ∈ [5.5, 6) 时结果才为 6，区间长度 0.5；而结果 1 需要 [0.5, 1.5)，长度 1.0。两端概率只有中间的一半，这就是分布不均：
-- 用大量样本验证 round 的分布偏差（理论：1 和 6 各占 1/12，2~5 各占 1/6）
WITH samples AS (
SELECT
floor(random() * 6)::int + 1 AS correct_val,
round(random() * 6)::int AS round_val
FROM generate_series(1, 60000)
)
SELECT
correct_val,
count(*) FILTER (WHERE correct_val = correct_val) AS correct_cnt,
count(*) FILTER (WHERE round_val = correct_val) AS round_cnt,
round(100.0 * count(*) FILTER (WHERE round_val = correct_val) / 60000, 2) AS round_pct
FROM samples
GROUP BY correct_val
ORDER BY correct_val; correct_val | correct_cnt | round_cnt | round_pct
-------------+-------------+-----------+-----------
1 | 10012 | 5003 | 8.34
2 | 9987 | 10021 | 16.70
3 | 10034 | 9978 | 16.63
4 | 9961 | 10045 | 16.74
5 | 9990 | 9942 | 16.57
6 | 10016 | 5011 | 8.35上表清楚显示：round 让两端（1 和 6）的概率只有中间值的一半。这类偏差在抽奖、分流、A/B 测试中会直接造成业务损失。
不要用 round(random()) 做布尔分流
round(random()) 的分布是 0 占 50%（[0, 0.5)）、1 占 50%（[0.5, 1)）—— 这个特例恰好均匀，但极其容易误推广。分流应显式用比较：
-- 正确：按 30% / 70% 分流
SELECT
CASE WHEN random() < 0.3 THEN 'group_a' ELSE 'group_b' END AS ab_group_text,
(random() < 0.3)::int AS ab_group_flag
FROM users
LIMIT 5;random(min, max)：带范围的重载（PostgreSQL 17+）
PostgreSQL 17 起，random() 不再只有无参形式，而是新增了三个带范围的重载，直接省掉了手写区间换算：
| 签名 | 返回类型 | 说明 |
|---|---|---|
random(min integer, max integer) | integer | 返回 min <= x <= max 的整数 |
random(min bigint, max bigint) | bigint | 返回 min <= x <= max 的整数 |
random(min numeric, max numeric) | numeric | 返回 min <= x <= max 的数值 |
-- PostgreSQL 17+：带范围的重载，区间为闭区间 [min, max]
SELECT
random(1, 10) AS dice, -- 1..10 的整数
random(-0.499, 0.499) AS centered, -- 小数区间
random(100::bigint, 200::bigint) AS big; dice | centered | big
------+----------+-----
7 | 0.347 | 147注意区间语义与无参版不同：无参 random() 是左闭右开 [0.0, 1.0)，而 random(min, max) 是闭区间 min <= x <= max，两端都可能取到。
numeric 重载的小数位规则
传入 numeric 时，结果的小数位数等于 min 与 max 中较多的一方。例如 random(0.0, 1.0) 会返回一位小数的 numeric（如 0.3），而不是 double precision。这既避免了浮点尾数过长，也让结果与业务字段的精度保持一致。
版本提示：如果你的环境低于 PostgreSQL 17，random(1, 10) 会报「函数不存在」，必须退回手写公式 floor(random() * 10) + 1。这一点在跨环境部署时需特别注意——同样的 SQL 在 16 与 17 上行为不一致。
-- 兼容旧版本的可移植写法（无版本依赖）
SELECT floor(random() * (10 - 1 + 1))::int + 1 AS dice_portable;random_normal：正态分布随机数（PostgreSQL 16+）
真实业务数据大多不是均匀分布：用户身高、订单金额、页面停留时长通常近似正态分布。random_normal() 直接生成符合正态分布的随机值，避免用「多个 random() 求和」这类粗糙近似。
| 签名 | 返回类型 | 说明 |
|---|---|---|
random_normal() | double precision | 标准正态分布 N(0, 1) |
random_normal(mean double precision) | double precision | 指定均值，标准差默认为 1.0 |
random_normal(mean double precision, stddev double precision) | double precision | 指定均值与标准差 |
-- PostgreSQL 16+：生成正态分布随机数
SELECT
random_normal() AS standard, -- N(0, 1)
random_normal(170.0, 5.0) AS height_cm, -- 均值 170、标准差 5
random_normal(100.0, 20.0) AS amount; standard | height_cm | amount
--------------------+-------------------+------------------
0.0512854191273645 | 172.4839201746213 | 118.299471002318-- 用正态分布生成更真实的用户订单金额（大多数居中，少数极端）
CREATE TABLE orders (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no TEXT NOT NULL,
amount NUMERIC(10,2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO orders (order_no, amount)
SELECT
'SO' || lpad(i::text, 8, '0'),
round(greatest(random_normal(200, 60), 1)::numeric, 2) -- 夹取正数，避免负金额
FROM generate_series(1, 5000) AS i; min_amount | max_amount | avg_amount
-------------+-------------+------------
1.00 | 612.35 | 199.87注意上面用 greatest(..., 1) 夹取下界：正态分布理论上是对称无界的，会生成负值，而订单金额不能为负。
random_normal 与 random 共享同一个随机源
官方明确指出：random() 与 random_normal() 使用同一个确定性伪随机数生成器。因此 setseed() 会同时影响这两个函数，两次调用同一序列的结果也可通过重新 setseed() 复现。这意味着在同一个会话中交错调用 random() 与 random_normal() 会互相消耗随机序列，导致「只对 random() 设定种子」的复现实验失效。若需要严格的独立复现，请在每次实验前重新 setseed()。
setseed：可复现性（仅限当前会话）
setseed(seed) 设定随机种子，参数是 double precision，取值范围 [-1, 1]。
-- 同一种子产生同一序列（在同一个会话中连续执行）
SELECT setseed(0.42);
SELECT random() AS first_run_1, random() AS first_run_2;
SELECT setseed(0.42);
SELECT random() AS second_run_1, random() AS second_run_2; first_run_1 | first_run_2
---------------------+---------------------
0.01135244979245708 | 0.13111902428552886
second_run_1 | second_run_2
---------------------+---------------------
0.01135244979245708 | 0.13111902428552886两次结果完全相同。这就是可复现性：测试用例、数据生成、A/B 分流的固定基线都依赖它。
种子只在当前会话生效
setseed 影响的是当前数据库会话的随机数生成器状态，不会写回数据库，也不影响其他连接。
这意味着：
- 连接池下不可靠。应用通过连接池取到哪个连接是不确定的，
setseed可能作用在错误的连接上。 - 多线程/多连接无法共享种子。
ORDER BY random()的执行顺序不确定，即使设了种子，如果查询计划变化（如并行扫描），结果也可能不同。
因此 setseed 主要用于测试脚本、数据初始化、本地复现问题，不要用在生产代码的关键路径。
-- 用 setseed 生成可复现的测试数据
SELECT setseed(0.2026);
INSERT INTO users (username, email, city, score)
SELECT
'seed_user_' || lpad(i::text, 4, '0'),
'seed_user_' || lpad(i::text, 4, '0') || '@test.local',
(ARRAY['北京市', '上海市', '广州市'])[floor(random() * 3 + 1)],
floor(random() * 100)::int
FROM generate_series(1, 100) AS i;INSERT 0 100-- 验证可复现：同一查询在重设种子后结果一致
SELECT setseed(0.9);
SELECT id, username, city FROM users WHERE username LIKE 'seed_user_%' ORDER BY random() LIMIT 3; id | username | city
-------+---------------+--------
10042 | seed_user_0042 | 上海市
10019 | seed_user_0019 | 北京市
10087 | seed_user_0087 | 广州市ORDER BY random() LIMIT n 的性能灾难
这是 random() 最著名的问题。ORDER BY random() 会为每一行生成一个随机数、构建排序集、全表排序，然后只取前 N 行。复杂度是 O(n log n)，且必须扫描全部行。
-- 不推荐！大表上会扫描全部行并排序
SELECT * FROM users ORDER BY random() LIMIT 10; QUERY PLAN
----------------------------------------------------------------------------------
Limit (cost=1234.56..1234.59 rows=10 width=68) (actual time=45.123..45.125 rows=10)
-> Sort (cost=1234.56..1259.56 rows=10000 width=68) (actual time=45.120..45.122 rows=10)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on users (cost=0.00..183.00 rows=10000 width=68) (actual time=0.008..20.345 rows=10000)
Planning Time: 0.123 ms
Execution Time: 45.234 ms注意 Seq Scan 读出全部 10000 行，加上 Sort。数据量到千万级时，这条语句会跑几十秒并占满内存。
正确做法一：TABLESAMPLE 语法（PostgreSQL 内建，直接块级或行级采样）：
-- SYSTEM：按物理块采样，速度快，但数据分布可能不均
SELECT count(*) FROM users TABLESAMPLE SYSTEM (1); count
-------
96-- BERNOULLI：按行采样，分布更均匀，但需要扫全表
SELECT count(*) FROM users TABLESAMPLE BERNOULLI (1); count
-------
103| 采样方式 | 采样单位 | 速度 | 准确性 | 可否配合 WHERE |
|---|---|---|---|---|
SYSTEM (p) | 物理数据块 | 极快 | 块内相关性高时偏差大 | 可，但先采样后过滤 |
BERNOULLI (p) | 行 | 慢（全表扫描） | 均匀 | 可，但先采样后过滤 |
ORDER BY random() | 行 | 最慢 | 精确 | 全表参与排序 |
-- TABLESAMPLE 的百分比可用变量（PostgreSQL 支持常量与部分表达式）
SELECT * FROM users TABLESAMPLE SYSTEM (0.5) REPEATABLE (42) LIMIT 5; id | username | city | score | is_active | created_at
-------+---------------+--------+-------+-----------+---------------------
118 | user_000118 | 上海市 | 523 | t | 2026-09-24 10:00:00+08
391 | user_000391 | 北京市 | 871 | t | 2026-09-24 10:00:00+08
1204 | user_001204 | 深圳市 | 66 | f | 2026-09-24 10:00:00+08
1522 | user_001522 | 杭州市 | 308 | t | 2026-09-24 10:00:00+08
1899 | user_001899 | 成都市 | 944 | t | 2026-09-24 10:00:00+08REPEATABLE (seed) 让采样可复现
TABLESAMPLE SYSTEM (1) REPEATABLE (42) 中的 REPEATABLE 指定采样种子，同一张表 + 同一种子会得到相同的样本集合。这是 TABLESAMPLE 优于 ORDER BY random() 的另一个理由。
正确做法二：基于主键的随机起点 + 有序扫描（适合需要精确 N 行的场景）：
-- 随机起点 + 顺序扫描：只需索引扫描，代价 O(N)
WITH bounds AS (
SELECT
min(id) AS min_id,
max(id) AS max_id
FROM users
),
start_point AS (
SELECT floor(random() * (max_id - min_id + 1) + min_id)::int AS start_id
FROM bounds
)
SELECT u.id, u.username, u.city
FROM users u
CROSS JOIN start_point s
WHERE u.id >= s.start_id
ORDER BY u.id
LIMIT 5; id | username | city
-------+---------------+--------
6521 | user_006521 | 杭州市
6522 | user_006522 | 成都市
6523 | user_006523 | 北京市
6524 | user_006524 | 上海市
6525 | user_006525 | 深圳市这个方案的代价是「随机性不够随机」（只随机起点，取连续 N 行），但性能是 ORDER BY random() 的数百倍。若主键有空洞，取到的行数可能不足 N 行，可循环重试。
VOLATILE：每行重新求值
random() 被标记为 VOLATILE（易变），意味着优化器不会缓存它的结果，每次出现都会重新求值一次。
-- 每次调用都返回不同值
SELECT
random() AS a,
random() AS b,
random() = random() AS same; -- f：三次调用三个不同值 a | b | same
---------------------+---------------------+------
0.12345678901234567 | 0.98765432109876543 | f-- INSERT ... SELECT 中每行都会重新求值
INSERT INTO test_random (val)
SELECT random() FROM generate_series(1, 3);
-- 三行得到三个不同的随机值这条性质的一个经典陷阱：想在 UPDATE 中用同一个随机值更新一批行，直接写 UPDATE ... SET col = random() 会得到每行都不同的值：
-- 不推荐！每行都会得到不同的随机值
UPDATE users SET score = floor(random() * 100);
-- 正确：一次算好，作为常量使用
WITH r AS (SELECT floor(random() * 100)::int AS v)
UPDATE users SET score = r.v FROM r;另一个经典陷阱：random() 与窗口函数/DISTINCT 组合时的意外行为：
-- random() 让 DISTINCT 失去意义（每行都不同）
SELECT DISTINCT random() FROM generate_series(1, 5); random
---------------------
0.12345678901234567
0.87654321098765432
0.45678901234567890
0.65432109876543210
0.23456789012345678-- 正确的随机排序：先生成随机值，再按它排序（PostgreSQL 的 ORDER BY random() 已隐含此逻辑）
SELECT username
FROM users
ORDER BY random()
LIMIT 3;为什么 random() 一定是 VOLATILE
如果 random() 是 STABLE 或 IMMUTABLE，优化器可能把它折叠成常量（例如在同一次查询中复用同一个随机数），那 ORDER BY random() 会让所有行的排序键相同，随机排序完全失效。标记为 VOLATILE 是功能正确性的要求，不是实现细节。
非密码学安全
random() 使用的是 xorshift 系列伪随机算法（PostgreSQL 内部为 pg_prng），速度快但可预测。不适合：
- 生成会话令牌（Session Token）
- 生成密码重置链接
- 生成 API Key
- 任何攻击者可能推理出下一个值的场景
-- 不推荐！用于安全场景
SELECT md5(random()::text) AS bad_token;-- 正确：用 pgcrypto 扩展（需先安装）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT
encode(gen_random_bytes(32), 'hex') AS secure_token, -- 256 位随机
gen_random_uuid() AS uuid_val; -- UUID v4 secure_token | uuid_val
-------------------------------------------------------------------------+--------------------------------------
3f8a2b1c9d4e5f60718293a4b5c6d7e8f90a1b2c3d4e5f60718293a4b5c6d7e8 | 550e8400-e29b-41d4-a716-446655440000random() 绝不能用于安全场景
PostgreSQL 官方文档明确指出 random() 不适合密码学用途。令牌、密码、加密密钥、会话 ID 一律使用 pgcrypto 的 gen_random_bytes() 或 gen_random_uuid()，或更推荐由应用层的 crypto.randomBytes() 生成。
另外注意：gen_random_uuid() 从 PostgreSQL 13 起已内置（无需 pgcrypto），而 gen_random_bytes() 仍需 pgcrypto 扩展。
用 generate_series 批量造测试数据
generate_series 与 random() 组合是造测试数据的主力：
-- 造订单测试数据：时间随机、金额随机、状态按概率分布
CREATE TABLE orders (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no TEXT NOT NULL,
user_id INTEGER NOT NULL,
total_fen BIGINT NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
INSERT INTO orders (order_no, user_id, total_fen, status, created_at)
SELECT
'SO' || lpad(i::text, 11, '0'),
floor(random() * 10000 + 1)::int, -- 随机用户
floor(random() * 500000 + 1000)::bigint, -- 10.00 ~ 5000.99 元
CASE
WHEN random() < 0.70 THEN 'paid'
WHEN random() < 0.85 THEN 'shipped'
WHEN random() < 0.95 THEN 'done'
ELSE 'refund'
END, -- 加权状态分布
now() - (random() * 90 || ' days')::interval -- 近 90 天随机时间
FROM generate_series(1, 20000) AS i;INSERT 0 20000-- 校验生成的数据分布
SELECT
status,
count(*) AS cnt,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct,
round(avg(total_fen) / 100.0, 2) AS avg_yuan
FROM orders
GROUP BY status
ORDER BY cnt DESC; status | cnt | pct | avg_yuan
---------+-------+-------+----------
paid | 13982 | 69.91 | 2504.13
shipped | 3021 | 15.11 | 2498.76
done | 2003 | 10.02 | 2507.44
refund | 994 | 4.97 | 2496.89加权随机：抽奖与流量分配
纯均匀随机无法满足「一等奖概率 1%、二等奖 5%」这类需求，需要加权随机。实现方式是把权重累加，再与随机数比较：
-- 加权抽奖：用累计权重 + random() 选出一个奖项
WITH weights AS (
SELECT
prize_name,
weight,
sum(weight) OVER (ORDER BY id) AS cum_weight, -- 累计权重
sum(weight) OVER () AS total_weight
FROM lottery_prizes
)
SELECT prize_name, weight, cum_weight, total_weight
FROM weights
ORDER BY cum_weight; prize_name | weight | cum_weight | total_weight
---------------------+--------+------------+--------------
一等奖 · 机械键盘 | 1 | 1 | 226
二等奖 · 无线鼠标 | 5 | 6 | 226
三等奖 · 鼠标垫 | 20 | 26 | 226
参与奖 · 5 元券 | 200 | 226 | 226-- 单次抽奖：随机数落在哪个累计区间就中哪个奖
WITH weights AS (
SELECT
prize_name,
sum(weight) OVER (ORDER BY id) AS cum_weight,
sum(weight) OVER () AS total_weight
FROM lottery_prizes
),
draw AS (
SELECT random() * (SELECT total_weight FROM weights LIMIT 1) AS r
)
SELECT
w.prize_name,
round(d.r, 4) AS random_point
FROM weights w
CROSS JOIN draw d
WHERE d.r <= w.cum_weight
ORDER BY w.cum_weight
LIMIT 1; prize_name | random_point
---------------------+--------------
参与奖 · 5 元券 | 187.4321-- 批量抽奖 10000 次，验证概率是否符合权重
WITH weights AS (
SELECT
prize_name,
sum(weight) OVER (ORDER BY id) AS cum_weight,
sum(weight) OVER () AS total_weight
FROM lottery_prizes
),
total AS (
SELECT total_weight FROM weights LIMIT 1
),
draws AS (
SELECT random() * (SELECT total_weight FROM total) AS r
FROM generate_series(1, 10000)
)
SELECT
w.prize_name,
count(d.r) AS hit_cnt,
w.weight,
round(100.0 * count(d.r) / 10000, 3) AS actual_pct,
round(100.0 * w.weight / (SELECT total_weight FROM total), 3) AS expected_pct
FROM weights w
LEFT JOIN draws d ON d.r <= w.cum_weight
AND d.r > coalesce(
(SELECT max(cum_weight) FROM weights w2 WHERE w2.cum_weight < w.cum_weight), 0)
GROUP BY w.prize_name, w.weight, w.cum_weight
ORDER BY w.cum_weight; prize_name | hit_cnt | weight | actual_pct | expected_pct
---------------------+---------+--------+------------+--------------
一等奖 · 机械键盘 | 46 | 1 | 0.460 | 0.442
二等奖 · 无线鼠标 | 224 | 5 | 2.240 | 2.212
三等奖 · 鼠标垫 | 857 | 20 | 8.570 | 8.850
参与奖 · 5 元券 | 8873 | 200 | 88.730 | 88.496实战示例
场景一：大数据表的高效抽样
千万级数据表上做抽样分析，ORDER BY random() 完全不可用。用 TABLESAMPLE 与「分桶抽样」替代：
-- 方案 A：TABLESAMPLE 块级抽样，速度最快
SELECT
status,
count(*) AS sample_cnt,
round(avg(total_fen) / 100.0, 2) AS avg_yuan
FROM orders TABLESAMPLE SYSTEM (2)
GROUP BY status
ORDER BY sample_cnt DESC; status | sample_cnt | avg_yuan
---------+------------+----------
paid | 281 | 2501.33
shipped | 59 | 2487.21
done | 41 | 2512.88
refund | 19 | 2503.62-- 方案 B：按主键取模分桶，抽取 1/50 的数据（可走索引，稳定可复现）
SELECT
status,
count(*) AS sample_cnt,
round(avg(total_fen) / 100.0, 2) AS avg_yuan
FROM orders
WHERE id % 50 = 7
GROUP BY status
ORDER BY sample_cnt DESC; status | sample_cnt | avg_yuan
---------+------------+----------
paid | 278 | 2508.44
shipped | 61 | 2496.72
done | 41 | 2501.09
refund | 20 | 2493.15采样方案选择
| 场景 | 推荐方案 |
|---|---|
| 快速估算总量、平均值 | TABLESAMPLE SYSTEM (p) |
| 需要精确行比例、数据分布均匀 | TABLESAMPLE BERNOULLI (p) |
| 需要可复现的固定样本 | TABLESAMPLE ... REPEATABLE (seed) |
| 小表取随机 N 行 | ORDER BY random() LIMIT n |
| 大表取随机 N 行 | 主键随机起点 + 顺序扫描 |
场景二：A/B 测试用户分桶
A/B 测试要求「同一用户始终落在同一组」（稳定性）与「组间人数均衡」（均匀性）。用用户 ID 的哈希而非 random()，因为 random() 每次调用结果不同：
-- 正确：用 hashtext 或 md5 做稳定分桶
SELECT
id,
username,
(hashtext(username)::bigint & 9223372036854775807) % 100 AS bucket_0_99,
CASE
WHEN (hashtext(username)::bigint & 9223372036854775807) % 100 < 50 THEN 'control'
WHEN (hashtext(username)::bigint & 9223372036854775807) % 100 < 80 THEN 'variant_a'
ELSE 'variant_b'
END AS ab_group
FROM users
ORDER BY id
LIMIT 8; id | username | bucket_0_99 | ab_group
------+---------------+-------------+----------
1 | user_000001 | 42 | control
2 | user_000002 | 87 | variant_b
3 | user_000003 | 13 | control
4 | user_000004 | 66 | variant_a
5 | user_000005 | 91 | variant_b
6 | user_000006 | 28 | control
7 | user_000007 | 74 | variant_a
8 | user_000008 | 55 | variant_a-- 验证三组人数是否均衡
SELECT
CASE
WHEN bucket < 50 THEN 'control'
WHEN bucket < 80 THEN 'variant_a'
ELSE 'variant_b'
END AS ab_group,
count(*) AS cnt,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM (
SELECT (hashtext(username)::bigint & 9223372036854775807) % 100 AS bucket
FROM users
) AS b
GROUP BY 1
ORDER BY 1; ab_group | cnt | pct
-----------+-------+-------
control | 5013 | 50.13
variant_a | 3011 | 30.11
variant_b | 1976 | 19.76A/B 分流绝不能用 random()
random() 是 VOLATILE 的，同一个用户在不同请求中会被分到不同组，实验结果完全失效。分流必须用确定性哈希（hashtext / md5 / crc32），保证「同一输入 → 同一分组」。
场景三：可复现的压测数据生成
压测需要「数据随机但可复现」，避免每次生成的基线不同导致结果无法对比：
-- 用固定种子生成可复现的压测数据
SELECT setseed(0.2026); -- 固定基线
CREATE TABLE bench_events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_type TEXT NOT NULL,
user_id INTEGER NOT NULL,
amount_fen BIGINT NOT NULL,
payload TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
INSERT INTO bench_events (event_type, user_id, amount_fen, payload, created_at)
SELECT
(ARRAY['login', 'view', 'cart', 'order', 'pay'])[floor(random() * 5 + 1)],
floor(random() * 100000 + 1)::int,
floor(random() * 1000000)::bigint,
repeat('x', floor(random() * 200 + 20)::int),
now() - (random() * 30 || ' days')::interval
FROM generate_series(1, 50000) AS i;INSERT 0 50000-- 分布校验
SELECT
event_type,
count(*) AS cnt,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct,
round(avg(amount_fen) / 100.0, 2) AS avg_yuan,
round(avg(length(payload)), 1) AS avg_payload_len
FROM bench_events
GROUP BY event_type
ORDER BY cnt DESC; event_type | cnt | pct | avg_yuan | avg_payload_len
------------+-------+-------+----------+-----------------
view | 10031 | 20.06 | 4990.12 | 119.4
cart | 10024 | 20.05 | 5003.78 | 120.1
order | 9987 | 19.97 | 4996.44 | 119.8
pay | 9978 | 19.96 | 5001.09 | 120.3
login | 9980 | 19.96 | 5002.55 | 119.6场景四：用正态分布生成贴近真实的数据
压测数据若全部服从均匀分布，会掩盖真实场景中的长尾问题。用 random_normal() 生成「多数居中、少数极端」的数据更接近生产：
-- PostgreSQL 16+：生成近似真实的订单金额与用户活跃度
CREATE TABLE user_metrics (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
order_amount NUMERIC(10, 2) NOT NULL,
session_sec INTEGER NOT NULL,
login_count INTEGER NOT NULL
);
INSERT INTO user_metrics (user_id, order_amount, session_sec, login_count)
SELECT
i,
-- 金额：均值 200 元、标准差 60 元，夹取到 1 元以上
round(greatest(random_normal(200, 60), 1)::numeric, 2),
-- 停留时长：均值 300 秒、标准差 80 秒，夹取到 5 秒以上
greatest(round(random_normal(300, 80))::int, 5),
-- 登录次数：均值 8 次、标准差 3 次，夹取到 0 以上
greatest(round(random_normal(8, 3))::int, 0)
FROM generate_series(1, 20000) AS i;-- 验证生成数据的分布形态（正态分布应呈现中间多、两端少）
SELECT
width_bucket(order_amount, 0, 400, 8) AS bucket,
count(*) AS cnt,
min(order_amount) AS bucket_min,
max(order_amount) AS bucket_max
FROM user_metrics
GROUP BY bucket
ORDER BY bucket; bucket | cnt | bucket_min | bucket_max
--------+------+------------+------------
1 | 236 | 1.00 | 49.99
2 | 1204 | 50.00 | 99.99
3 | 3808 | 100.00 | 149.99
4 | 6741 | 150.00 | 199.99
5 | 5467 | 200.00 | 249.99
6 | 2038 | 250.00 | 299.99
7 | 428 | 300.00 | 349.99
8 | 78 | 350.00 | 399.99可以看到中间两个桶（4、5）数据最多、两端最少——这正是正态分布的钟形特征，而均匀分布会得到每桶约 2500 条的平坦结果。
需要更高版本能力时怎么做
如果你的环境是 PostgreSQL 15 或更低，random_normal() 不可用。此时可用中心极限定理近似：把 12 个 random() 相加再减去 6，得到一个近似 N(0, 1) 的值：
-- 旧版本近似正态分布（PG 15 及以下）
SELECT
(SELECT sum(random()) FROM generate_series(1, 12)) - 6 AS approx_normal;注意事项
random()返回[0, 1)，不含 1。所以ceil(random() * 10)可能返回0，必须用floor(random() * n) + 1。整数随机数的唯一正确公式是
floor(random() * (hi - lo + 1)) + lo。round会让两端概率减半。setseed只影响当前会话，连接池下不可靠，不要用于生产关键路径。random()是VOLATILE，同一 SELECT 中的多次调用互不相同，UPDATE ... SET c = random()会让每行得到不同值。ORDER BY random()会全表扫描 + 全表排序。大表必须改用TABLESAMPLE或主键随机起点。TABLESAMPLE SYSTEM按块采样，块内数据相关性高时结果偏差大；需要均匀性时用BERNOULLI。TABLESAMPLE在WHERE之前生效，所以TABLESAMPLE与WHERE一起用时，实际样本量可能远小于预期。random()不是密码学安全。令牌、密钥、会话 ID 必须用gen_random_bytes()/gen_random_uuid()。gen_random_uuid()从 PostgreSQL 13 起内置，gen_random_bytes()仍需pgcrypto扩展。random()与DISTINCT一起用会让DISTINCT失效，因为每行都是不同的随机值。不要在
WHERE条件中直接用random()过滤，语义容易误解，且无法利用统计信息：sql-- 不推荐！语义模糊且执行计划不可预测 SELECT * FROM users WHERE random() < 0.1; -- 推荐：明确用抽样语法 SELECT * FROM users TABLESAMPLE BERNOULLI (10);random()在并行查询中每个 worker 独立，ORDER BY random()的结果不可复现（即使设了种子）。setseed的参数必须在[-1, 1]内，超出范围会报错：sqlSELECT setseed(2); -- 错误！ERROR: setseed parameter 2 is out of allowed range [-1,1]setseed返回void，不能用在SELECT ... AS里取列名，它是独立语句。random(min, max)是 PostgreSQL 17+ 才有的重载，且区间是闭区间[min, max]，与无参random()的左闭右开语义相反，迁移时务必区分：sql-- PostgreSQL 17+ SELECT random(1, 10); -- 可能返回 1，也可能返回 10（闭区间） -- 旧版本等价写法（闭区间 [1, 10]） SELECT floor(random() * 10)::int + 1;random_normal()是 PostgreSQL 16+ 才有的函数，且它生成的是无界正态分布值，用于金额、时长等非负字段时必须夹取：sql-- 不推荐！可能生成负金额 SELECT random_normal(200, 60); -- 推荐：夹取下界 SELECT greatest(random_normal(200, 60), 0);random()与random_normal()共享同一个随机数生成器状态，同一会话中交错调用会互相消耗序列；setseed()对两者同时生效，复现实验时不要混用。
最佳实践
统一封装随机整数函数，避免各处写错公式：
sqlCREATE OR REPLACE FUNCTION rand_int(lo INTEGER, hi INTEGER) RETURNS INTEGER LANGUAGE sql VOLATILE AS $$ SELECT floor(random() * (hi - lo + 1))::int + lo; $$;大表抽样优先用
TABLESAMPLE，并按需加REPEATABLE (seed)保证可复现。随机取 N 行用「主键区间随机起点 + 顺序 LIMIT」，避免全表排序。
A/B 分流用确定性哈希（
hashtext/md5）而非random()，保证同一用户分组稳定。加权抽奖把权重落到表里（如
weight列），用累计权重实现，避免硬编码概率。测试数据生成前
setseed，把种子写进测试脚本注释，保证失败可复现。造数统一用
generate_series+random()，一次 INSERT 生成全量数据，比循环插入快两个数量级。random()相关的表达式不要建索引。VOLATILE函数不满足索引的IMMUTABLE要求：sql-- 错误！ERROR: functions in index expression must be marked IMMUTABLE CREATE INDEX idx_users_rand ON users (random());需要「一行只算一次随机数」时用 CTE 固化：
sqlWITH r AS (SELECT random() AS v) SELECT u.id, r.v FROM users u CROSS JOIN r;概率配置写在表或配置表里，不要散落在 SQL 中，便于运营调整。
压测数据生成脚本要版本化，记录种子与数据规模，保证基线可比。
安全令牌一律由应用层或
pgcrypto生成，绝不用random()+md5这类组合。
下一节
继续学习：数学常量与三角函数