如何在 PostgreSQL 中获得随机笛卡尔积？

我有两张桌子，custassets and tags。为了生成一些测试数据，我想做一个INSERT INTO一个多对多表SELECT从每个表中获取随机行（以便一个表中的随机主键与第二个表中的随机主键配对）。令我惊讶的是，这并不像我最初想象的那么容易，所以我坚持这样做来自学。

这是我的第一次尝试。我选择10custassets and 3 tags，但两者在每种情况下都是相同的。我可以修复第一个表，但我想随机分配分配的标签。

SELECT
    custassets_rand.id custassets_id,
    tags_rand.id tags_rand_id
FROM
    (
        SELECT id FROM custassets WHERE defunct = false ORDER BY RANDOM() LIMIT 10
    ) AS custassets_rand
,
    (
        SELECT id FROM tags WHERE defunct = false ORDER BY RANDOM() LIMIT 3
    ) AS tags_rand

这会产生：

custassets_id | tags_rand_id 
---------------+--------------
          9849 |         3322  }
          9849 |         4871  } this pattern of tag PKs is repeated
          9849 |         5188  }
         12145 |         3322
         12145 |         4871
         12145 |         5188
         17837 |         3322
         17837 |         4871
         17837 |         5188
....

然后我尝试了以下方法：执行第二个RANDOM()调用SELECT列列表。然而这个更糟糕，因为它选择单个标签 PK 并坚持下去。

SELECT
    custassets_rand.id custassets_id,
    (SELECT id FROM tags WHERE defunct = false ORDER BY RANDOM() LIMIT 1) tags_rand_id
FROM
    (
        SELECT id FROM custassets WHERE defunct = false ORDER BY RANDOM() LIMIT 30
    ) AS custassets_rand

Result:

 custassets_id | tags_rand_id 
---------------+--------------
         16694 |         1537
         14204 |         1537
         23823 |         1537
         34799 |         1537
         36388 |         1537
....

这对于脚本语言来说很容易，而且我确信使用存储过程或临时表可以很容易地完成。但我可以只用一个INSERT INTO SELECT?

我确实考虑过使用随机函数选择整数主键，但不幸的是两个表的主键在增量序列中都有间隙（因此每个表中可能会选择一个空行）。不然就好了！

请注意，您正在寻找的是not a 笛卡尔积 https://en.wikipedia.org/wiki/Cartesian_product，这会产生n*m行；而是随机的 1:1 关联，这会产生GREATEST(n,m) rows.

生产真正随机的组合，随机化就足够了rn对于更大的集合：

SELECT c_id, t_id
FROM  (
   SELECT id AS c_id, row_number() OVER (ORDER BY random()) AS rn
   FROM   custassets
   ) x
JOIN   (SELECT id AS t_id, row_number() OVER () AS rn FROM tags) y USING (rn);

If 随意的组合足够好，这更快（特别是对于大表）：

SELECT c_id, t_id
FROM   (SELECT id AS c_id, row_number() OVER () AS rn FROM custassets) x
JOIN   (SELECT id AS t_id, row_number() OVER () AS rn FROM tags) y USING (rn);

如果两个表中的行数不匹配，并且您不想丢失较大表中的行，请使用模运算符% https://www.postgresql.org/docs/current/functions-math.html多次连接较小表中的行：

SELECT c_id, t_id
FROM  (
   SELECT id AS c_id, row_number() OVER () AS rn
   FROM   custassets -- table with fewer rows
   ) x
JOIN  (
   SELECT id AS t_id, (row_number() OVER () % small.ct) + 1 AS rn
   FROM   tags
       , (SELECT count(*) AS ct FROM custassets) AS small
   ) y USING (rn);

窗口函数 https://www.postgresql.org/docs/current/functions-window.html是随 PostgreSQL 8.4 添加的。