PostgreSQL中generate_series关联random()值重复的修复方案

嘿，这个问题我之前也踩过坑！原因其实很简单：PostgreSQL在处理你写的那条SQL时，会把random()当成一个常量表达式，只在查询启动时计算一次，所以所有行都会复用同一个随机值。

要让每行都生成不同的随机数，核心就是要让PostgreSQL知道：random()需要和generate_series生成的每一行绑定，每行都重新计算一次。这里有几种实用的解决办法：

方法1：用子查询包裹时间序列生成逻辑 #

把generate_series放到子查询里，先生成所有时间戳行，再对每行计算random()，这样PostgreSQL就会逐行处理：

SELECT ts, random() as value
FROM (
    SELECT generate_series('2008-03-01 00:00'::timestamp, '2008-03-05 12:00', '1 day') as ts
) AS time_series;

方法2：用LATERAL连接（推荐，语义更清晰） #

LATERAL连接的特性就是允许右侧的表达式引用左侧表的列，哪怕我们这里不需要用到ts，也能强制PostgreSQL为每行重新计算random()：

SELECT ts, r.value
FROM generate_series('2008-03-01 00:00'::timestamp, '2008-03-05 12:00', '1 day') as ts
LATERAL (SELECT random() as value) r;

方法3：让random()“依赖”于时间戳（小技巧，有点hack） #

通过给random()加一个和ts相关但不改变结果的计算，让PostgreSQL误以为这个函数和每行的ts有关，从而逐行计算：

SELECT ts, random() + (ts - ts) as value
FROM generate_series('2008-03-01 00:00'::timestamp, '2008-03-05 12:00', '1 day') as ts;

不过这种方法不如前两种直观，更推荐用前两种。

随便跑其中一种，你就能得到每行都不一样的随机值了，比如：

"2008-03-01 00:00:00";0.816707271616906
"2008-03-02 00:00:00";0.123456789012345
"2008-03-03 00:00:00";0.987654321098765
"2008-03-04 00:00:00";0.456789012345678
"2008-03-05 00:00:00";0.789012345678901

本质上，这些方法都是在告诉PostgreSQL：别把random()当全局常量，每一行都得重新算一次！

内容的提问来源于stack exchange，提问作者DIBits