PostgreSQL：为Calendar表每行匹配随机Transactions.id的简化方案

问题背景 #

现有Calendar和Transactions两张表，两表均包含id列。需求是编写SQL查询，让Calendar表的每一行都对应一个随机的Transactions.id。

失败的尝试 #

以下两种查询均无法实现需求，所有行返回的transaction_id完全相同：

方法1：子查询取随机行 #

SELECT "Calendar".id,
      (SELECT id FROM Transactions ORDER BY Random() LIMIT 1) as tr_id
FROM "Calendar"

原因：数据库优化器判定该子查询不依赖外部表，只会执行一次，结果复用给所有行。

方法2：数组随机索引 #

WITH cte_tr_array AS (SELECT ARRAY_AGG("Transactions".id) AS id_array
                      FROM "Transactions"
                      LIMIT 1
                     )
SELECT * ,
  (SELECT * FROM (SELECT cte_tr_array.id_array[FLOOR(RANDOM() * array_length(cte_tr_array.id_array,1))] FROM cte_tr_array) as tr_array) as transaction_id
FROM "Calendar";

原因：CTE仅计算一次，RANDOM()在CTE上下文里只执行一次，导致所有行使用同一个数组索引。

可行但较繁琐的方案 #

通过在Calendar子查询中为每行生成独立随机值的CTE方法可以实现需求（已修正原SQL的语法错误）：

WITH cte_tr_array           AS (SELECT ARRAY_AGG("Transactions".id) AS id_array
                                FROM "Transactions"
                                LIMIT 1
                               ),
     cte_count_transactions AS (SELECT COUNT(*) AS count
                                FROM "Transactions"
                               )
SELECT *, (SELECT id_array[aug_c.random_id + 1] FROM cte_tr_array) AS transaction_id
FROM (SELECT *,
             FLOOR(RANDOM() * (SELECT * FROM cte_count_transactions LIMIT 1)) AS random_id
      FROM "Calendar"
     ) AS aug_c;

注：PostgreSQL数组索引从1开始，因此给random_id加1，避免取到0索引的空值。

更简便的实现方式 #

方案1：使用横向连接（LATERAL JOIN） #

这是最直观且高效的方式，LATERAL关键字会让子查询为Calendar的每一行独立执行一次：

SELECT c.id, t.id AS transaction_id
FROM "Calendar" c
CROSS JOIN LATERAL (
    SELECT id FROM Transactions ORDER BY random() LIMIT 1
) t;

这种写法直接明了，数据库会为Calendar的每一行单独从Transactions中随机选取一个id，完美满足需求。

方案2：随机数关联匹配 #

如果不想用LATERAL，也可以通过为两张表生成随机数后关联的方式实现：

SELECT c.id, t.id AS transaction_id
FROM (
    SELECT id, random() AS rnd FROM "Calendar"
) c
JOIN (
    SELECT id, random() AS rnd FROM Transactions
) t ON TRUE
ORDER BY c.rnd, t.rnd
LIMIT (SELECT COUNT(*) FROM "Calendar");

该方法通过随机数排序后取对应行数的结果，也能实现每行匹配随机id，但如果Transactions表数据量远小于Calendar，可能会出现重复匹配的情况，不如LATERAL方案可靠。

扩展到10个不同表的随机值选取 #

如果需要为Calendar的每一行同时从10个不同表中各选取一个随机列值，只需叠加多个LATERAL连接即可，每个LATERAL子查询对应一个目标表：

SELECT 
    c.id,
    t.id AS transaction_id,
    u.username AS random_username,
    p.product_name AS random_product,
    o.order_no AS random_order,
    -- 继续添加剩余6个表的查询
FROM "Calendar" c
CROSS JOIN LATERAL (SELECT id FROM Transactions ORDER BY random() LIMIT 1) t
CROSS JOIN LATERAL (SELECT username FROM Users ORDER BY random() LIMIT 1) u
CROSS JOIN LATERAL (SELECT product_name FROM Products ORDER BY random() LIMIT 1) p
CROSS JOIN LATERAL (SELECT order_no FROM Orders ORDER BY random() LIMIT 1) o
-- 依次添加其他表的LATERAL子查询，格式与上述一致

每个LATERAL子查询都会为Calendar的当前行独立生成随机结果，确保每行的10个随机值都是独立的。

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