PostgreSQL：随机选择？

Question

我正在使用PHP操作PostgreSQL。

是否可能从条件为 WHERE condition 的 table 列中选择特定数量的随机值，而不是使用以下语句： select column FROM table WHERE condition 然后将它们转换为数组并使用 array_rand()？

（我不想这样做，因为我将有数百万行数据，先选择所有值再使用 array_rand() 可能需要很长时间。）

假设我有一个像这样的表：

   name    |   items
-----------+------------
    Ben    | {dd,ab,aa}  
-----------+------------
   David   |  {dd,aa}  
-----------+------------
   Bryan   | {aa,ab,cd}
-----------+------------
    Glen   |    {cd}
-----------+------------
   Edward  |   {aa,cd}
-----------+------------
   Aaron   |  {dd,aa}
-----------+------------
  ..... (many many more)

更新：

我需要在一列中选择符合条件的10个随机值（或者说是10个随机行），条件为@> ARRAY[aa]，但是不能使用顺序表扫描或其他耗费时间的方法。

order by random() 会花费很长时间，因为它必须处理每一行，所以我将采用更好的解决方案。

- user1282226

---

"order by random()" 是一个完全有效的解决方案，但是它的性能很差，并且随着“数百万行”的增加而逐渐降低，因为 random() 函数必须对 每一行 进行评估，然后 所有 行必须在返回前进行排序。这是一个性能噩梦！如果您披露有关您的情况的更多信息，我可能能够提供一个更快的解决方案。我最近在这里发布了一个解决方案。 - Erwin Brandstetter

http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/ - Brendan Long

@ErwinBrandstetter 哦，抱歉。高达数亿。哈哈... - user1282226

主表是只读的吗？还是会频繁更新/插入数据？ - Erwin Brandstetter

不断更新和插入用户的内容。大约每天几次。 - user1282226

Answer 1

在PostgreSQL中，你可以通过使用order by random()来实现只选择前几行或随机选择行的功能，因此这应该是你想要的功能。

select name
from table
where items @>ARRAY['aa']
order by random()
limit 10;

- user554546

---

谢谢你的回答，但就像 Erwin 所说的那样，它的性能很差，因为它必须评估每一行，然后对所有行进行排序。很抱歉，我无法接受你的答案。 - user1282226

Answer 2

如果基础表格更新频繁，则此解决方案可能不太适用。否则，维护成本可能会超过收益。

如果您的条件始终为@> ARRAY[aa]，则可以创建一个辅助查找表（基本上是一个物化视图）。

CREATE TABLE tbl_pick (rn serial, id int, PRIMARY KEY (rn, id);

INSERT INTO tbl_pick (id)
SELECT id FROM tbl
WHERE  items @> ARRAY[aa];

然后你可以应用类似的方法，如 这里所描述：

SELECT *
FROM  (
    SELECT 1 + floor(random() * <insert_count_plus_a_bit_here>)::integer AS rn
    FROM   generate_series(1, 20) g
    GROUP  BY 1                     -- trim duplicates
    ) r
JOIN   tbl_pick USING (rn)
JOIN   tbl USING (id)
LIMIT  10;                          -- trim surplus

这应该是非常快的，因为它只需要索引扫描，并且只从表中读取了 ~10 行。

当然，在（相关的）INSERT / DELETE / UPDATE 到 tbl 后，您必须更新 tbl_pick。 少量的更新可以直接添加 / 删除到 tbl_pick 中（没有更新），因为该方法有一些余地。在一定数量的更新后，您将会 TRUNCATE 并重新运行完整的 INSERT。基本上重写你的材料化视图。

可以使用外键约束和 ON UPDATE CASCADE ON DELETE CASCADE 将 UPDATE 和 DELETE 级联到 tbl_pick。对于新插入的行，可以使用触发器 AFTER INSERT。所有这些都取决于基本表中可行的操作。

并安排在规律间隔内（最好在非工作时间）完全重写 tbl_pick。

如果你的随机查询以突发方式出现，使用一个“变量”来指示 tbl_pick 是否有改动（而不是使用fk约束和触发器），只有在这种情况下再重新填充表格，然后运行查询（多次）可能会更便宜。这非常取决于你的使用模式。这个“变量”可以是一个一行的表格，只允许 UPDATE 操作。在对tbl 进行相关更新之后将其设置为 TRUE，在刷新材料视图之后将其设置为 FALSE。

- Erwin Brandstetter