sql—将order by random与postgresql一起使用时的确定顺序

l0oc07j2 于 2021-08-01 发布在 Java

关注(0)|答案(3)|浏览(403)

我使用的查询如下：

SELECT * FROM items ORDER BY RANDOM()

如果行数少，一切都好。然而，在我的测试中，我想有一些可复制的东西来验证。这就是为什么我要给随机数生成器植入种子：

SELECT setseed(0.123);
SELECT * FROM items ORDER BY RANDOM();

很好，工作很好。看起来每次执行的顺序都是一样的。只是它不是完全可复制的。在某些情况下，测试成功，我得到了预期的顺序和结果。在执行相同的测试时，我不知道。为什么？

3条答案

这个问题与这样一个事实有关：行首先是按未指定的顺序（如果没有指定ORDERBY子句）获取的，然后才为每一行调用random（）函数。这意味着在应用orderby random（）之后，未指定的顺序将影响行顺序。
例如，在两种情况下使用相同的种子：
案例1

SELECT * FROM items
returns
item_1
item_2
item_3
item_4

SELECT * FROM items ORDER BY RANDOM();
may return

item_3
item_4
item_1
item_2

案例2

SELECT * FROM items
returns
item_4
item_3
item_2
item_1

SELECT * FROM items ORDER BY RANDOM();
may return

item_2
item_1
item_4
item_3

然后，解决方法是在按random（）对行进行排序之前对行进行排序。最终结果是100%确定的。

使用子查询可能会更好：

SELECT setseed(0.123);
SELECT *
FROM (SELECT i.*, RANDOM() as rand
      items i
     ) i
ORDER BY rand;

原因是 RANDOM() 在排序过程中多次调用。一些排序算法是不确定的——这会影响下游行。
这不是100%保证，因为子查询仍然不能按顺序处理（尽管它应该在单处理器系统上）。但是您可以通过使用散列而不是随机值来进一步纠正这个问题。所以：

order by md5(item_id || '0.123')

这个 item_id 假定每行上的值不同。这个 '0.123' 是添加的，以便您可以轻松地更改顺序。

你似乎想要一个可重复的随机排序。 setseed() 是正确的方法，但是您需要在查询中设置它，因此它适用于 random() .
下面是一个使用 union all :

select item_id
from (
    select setseed(0.5), null item_id
    union all
    select null, item_id from items
    offset 1
) s
order by random()

这演示了如何处理只有一列的表。您可以通过添加更多的列来扩展它 null 列到第一个子查询（并相应地列出另一个子查询中的相应列） union all 外部查询中的成员和）。