主页 / dba / 问题 / 231100
Accepted
TMorgan
Asked: 2019-03-02 14:44:26 +0800 CST

postgresql 随机函数在查询中使用随机排序时返回重复结果

  • 772

我在 Windows 10 上使用 postgresql 11.1-1。在我的系统上，如果我运行如下查询，我将为每个随机列获得不同的数字：

postgres=# SELECT random() as sortOrder, random() as col2;
     sortorder     |       col2
-------------------+-------------------
 0.607938482426107 | 0.121234225574881
(1 row)

但是如果我添加一个 ORDER BY 子句，如下所示，那么 random 每次为每一行返回相同的随机数。

postgres=# SELECT random() as sortOrder, random() as col2 
               FROM generate_series(0,1) 
               ORDER BY sortOrder;
     sortorder     |       col2
-------------------+-------------------
 0.100375576410443 | 0.100375576410443
 0.170669795479625 | 0.170669795479625
(2 rows)

我假设这是有技术原因的，但是在最后一个查询中是否有可能为每列获取不同的随机值？

编辑： 我最初的问题可能过于简单化了。我尝试的实际查询更像是以下内容：

SELECT column1, random(), column2, random()
               FROM table1
               ORDER BY random();

因此，拥有可以与表查询结合的答案也很重要。

postgresql sorting

2 个回答

  1. Best Answer

    random()是易变的，文档说关于易变性：

    使用 volatile 函数的查询将在需要其值的每一行重新评估该函数 。

    基于这个定义，引擎在每行只评估一次函数是正确的，而不是对同一行的每列评估一次。

    要关闭此优化，您可能会调整优化器的查询以忽略两列带有相同表达式的事实。例如：

    postgres=> SELECT random()+0 as sortOrder, random() as col2 
               FROM generate_series(0,1) 
               ORDER BY sortOrder;
     sortorder      |        col2         
--------------------+---------------------
 0.0412265718914568 | 0.00992022315040231
  0.841067179106176 |   0.818967831321061
(2 rows)

    现在，这个简单的解决方案可能看起来有点脆弱。在未来的某个版本中，优化器理论上可能会变得足够聪明，可以识别这两个表达式有一个可以重用的公共子表达式。

    一个真正强大的解决方案是在不同的行中生成列，以便强制优化器使用不同的函数调用，无论它多么努力避免多次评估。例如：

    SELECT v1,v2 FROM
(select random() as v1, row_number() over() as r1
 FROM generate_series(0,1)) as s1
JOIN
(select random() as v2, row_number() over() as r2
 FROM generate_series(0,1)) as s2
ON s1.r1=s2.r2
ORDER BY 1;
        v1         |        v2         
-------------------+-------------------
 0.145532250870019 | 0.963712519966066
 0.277611976023763 | 0.252654927782714
(2 rows)
    • 3

  2. 我最终使用了另一种方法，灵感来自 Daniel Vérité 的答案，因为我无法将他的答案与我写的一个相当大的查询结合起来。所以，我最终创建了一个名为 true_random 的 postgresql 函数，代码如下：

    select random() as v1
    FROM generate_series(0,1)

    然后我用 true_random() 替换了 random() 的每个实例。这似乎解决了这个问题。（尽管我仍然给予丹尼尔信用，因为他回答了我最初提出的问题）。

    • 1

相关问题