postgresql - 具有随机排序行的可重复分页

Question

我有从数据库返回圆顶分页行的 API。它有效，但是当我按 RANDOM() 排序行时，我在连续的页面上得到重复项。是否有任何选项可以为每个查询设置随机种子？

如果不是，是否可以全局设置随机 SEED 以强制 RANDOM() 为每个查询生成相同的值？然后我可以每 3 分钟或类似的时间更改一次全局随机...

---

你使用这段代码:

SELECT * FROM "table" ORDER BY RANDOM() OFFSET 5 LIMIT 5

现在我想将种子传递给这个查询，这样我就可以对随机结果进行分页。我应该这样做吗？:

SELECT "table".*, SETSEED(0.1) FROM "table" ORDER BY RANDOM() OFFSET 5 LIMIT 5
SELECT "table".*, SETSEED(0.1) FROM "table" ORDER BY RANDOM() OFFSET 10 LIMIT 5

结果会正确分页吗？

Answer 1

如果顺序需要“打乱”但不是真正随机的...

(更新:请参阅 my other answer 以获得更灵活和可随机化的解决方案。)

你说的是“随机”顺序，这是调用 ORDER BY random() 时得到的 - 对于每一行，PostgreSQL 调用 random()，获取一个值，并使用它来决定如何在结果集中对该行进行排序。

要使其可重复，您必须弄乱种子。这感觉恶心。根据the docs :

the effects will persist until the end of the session, unless overridden by another SET

我认为这意味着在使用连接池时，setseed 会为使用该连接的下一个进程改变连接。

模数呢？

我有一种情况不需要真正的随机性。我的标准是:

• 每次的顺序都不一样
• 同一结果集页面内的可预测顺序，这样我们就不会在后续页面上出现重复

例如，这样就好了:

• list 1
  • 第 1 页:第 1、4 项
  • 第 2 页:第 3、2 项
• list 2(不同的用户，或稍后回来的同一用户)
  • 第 1 页:项目 3、1
  • 第 2 页:第 2、4 项

要得到这样的东西，取模似乎很有效。例如，ORDER BY id % 7, id 用于请求 1 的所有页面，ORDER BY id % 11, id 用于请求 2 的所有页面。也就是说，对于每个行，将其 id 除以模数并按余数排序。在具有相同余数的行内，按 id 排序(以确保排序稳定)。

可以为第一页随机选取模数，然后将其重新用作每个后续页面请求的参数。

您可以看到这对您的数据库有何作用，如下所示:

echo "select id, id % 7 FROM my_table ORDER BY id % 77, id" | psql my_db > sort.txt

质数模数可能会给您带来最大的变化。如果您的 ID 从 1 开始(这样 % 77 将使前 77 行按正常顺序返回)，您可以尝试在时间戳字段上取模。例如:

ORDER BY (extract(epoch from inserted_at)* 100000::bigint % 77

但是您需要一个函数索引来提高性能。