postgresql - Amazon Redshift - SQL - 在多个 ROW_NUMBER() ORDER BY 子句中调用时 RANDOM() 的行为

Question

想知道是否有人对我在 Amazon Redshift 查询中遇到的这种奇怪行为有所了解，涉及单个表中的多个 ROW_NUMBER() OVER (ORDER BY RANDOM()) 列(a从表格中抽取几个随机样本的简单方法)。

最小工作示例:假设我有一个表 data，其中包含一列 id 和 10 行。这个查询:

SELECT id,
       ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rk1,
       ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rk2
FROM data ORDER BY rk1

总是给我这样的东西:

id  rk1 rk2
5   1   1
3   2   2
7   3   3
10  4   4
6   5   5
8   6   6
1   7   7
9   8   8
2   9   9
4   10  10

当我期待这样的事情时:

id  rk1 rk2
5   1   6
3   2   3
7   3   9
10  4   1
6   5   8
8   6   5
1   7   7
9   8   10
2   9   4
4   10  2

也就是说，Redshift 似乎只在每一行上计算一次 RANDOM()，尽管有两次调用，所以顺序总是相同的。但这很奇怪，因为这个简单的查询:

SELECT RANDOM(), RANDOM()

返回类似的东西:

random              random
0.87943100551116793 0.23245479410162884

如预期。那么这是怎么回事？它与在窗口函数中调用 RANDOM() 是否有关？

对于我的需求而言，这并不是一个真正的关键问题，因为我可以在单独的查询中进行这些随机排名并加入，但这本来是一种非常简单的方法。

在网站上找不到任何与此类似的内容，如果已经有人询问并解决了，我们深表歉意。谢谢!!

Answer 1

您可以使用子查询来计算两次行号:

SELECT id, rk1,  row_number() over (ORDER BY  random()) AS rk2
FROM (SELECT id, row_number() over (ORDER BY  random()) AS rk1
      FROM generate_series(1,10) AS "data"(id)) as sub
ORDER BY rk1;

SqlFiddleDemo

输出:

╔═════╦══════╦═════╗
║ id  ║ rk1  ║ rk2 ║
╠═════╬══════╬═════╣
║  1  ║   1  ║   7 ║
║  2  ║   2  ║   8 ║
║  6  ║   3  ║   4 ║
║  9  ║   4  ║  10 ║
║  8  ║   5  ║   3 ║
║ 10  ║   6  ║   9 ║
║  4  ║   7  ║   6 ║
║  5  ║   8  ║   1 ║
║  7  ║   9  ║   2 ║
║  3  ║  10  ║   5 ║
╚═════╩══════╩═════╝