java - 以伪随机顺序选择表记录

Question

就我而言，我使用的是嵌入式 H2 数据库，但我的问题实际上是一般 SQL 数据库。

考虑一下这张表，其中一条记录可能会也可能不会引用另一条记录，并且永远不会从多个位置引用同一条记录。

CREATE TABLE test (id NUMBER, data VARCHAR, reference NUMBER) ;
INSERT INTO test (id, data) 
SELECT x, 'P'||x FROM system_range(0, 9);
UPDATE test SET reference = 2 where id = 4;
UPDATE test SET reference = 4 where id = 6;
UPDATE test SET reference = 1 where id = 7;
UPDATE test SET reference = 8 where id = 9;

SELECT * FROM test ORDER BY id;

ID  DATA    REFERENCE
----------------------------------
0   P0      null 
1   P1      null 
2   P2      null 
3   P3      null 
4   P4      2
5   P5      null 
6   P6      4 
7   P7      1 
8   P8      null 
9   P9      8 

现在我想要一个 SQL，它将以随机顺序选择测试记录，唯一的限制是引用的记录永远不会在引用它的记录之前被选择。

有一个可行的方法是 SELECT * FROM test ORDER BY reference, RAND()但对我来说，这似乎不够随机，因为它总是首先选择所有未引用的记录，这降低了随机性水平。

说一个好的、有效的结果集就是下面的结果集。

ID  DATA    REFERENCE
----------------------------------
8   P8      null 
2   P2      null 
1   P1      null 
4   P4      2
3   P3      null 
9   P9      8 
5   P5      null 
6   P6      4 
0   P0      null
7   P7      1 

我更喜欢纯 SQL 解决方案，但提供 H2 很容易扩展，我不会通过公开我自己的 Java 方法来创建自定义函数。

更新这不是 How to request a random row in SQL 的重复项因为:

1. 除了随机性要求之外，我还有引用限制。事实上，我的问题的复杂程度来自于这个引用限制，而不是随机的。
2. 我需要选择所有表记录，而不仅仅是一个

Answer 1

好吧，在你真正深入挖掘之前，你永远不应该说“永远”。当我为 Jim 添加评论时，我实际上问自己 H2 是否提出了与 Oracle 分层查询等效的方案。当然，高级部分 H2 recursive queries 下的 H2 文档中也有一些解释。

所以这是我的工作查询，几乎满足我的要求:

WITH link(id, data, reference, sort_val, level, tree_id) AS (
    -- Each tree root starts with a random sorting value up to half the number of records.
    -- This half the number of records is not really needed it can be a hard coded value
    -- I just said half to achieve a relative uniform distribution of three ids
    -- take the id of the starting row as a three id
    SELECT id, data, reference, round(rand()*(select count(*) FROM test)/2) AS sort_val, 0, id FROM test WHERE reference IS NULL

    UNION ALL

    -- Increase the sort value by level for each referencing row
    SELECT test.id, test.data, test.reference, link.sort_val + (level + 1) AS sort_val, level + 1, link.tree_id
       FROM link
       JOIN test ON link.id = test.reference
)
-- sort value, level and tree id are printed here just to make it easier to understand how it works
SELECT id, data, reference, sort_val, level, tree_id
  FROM link
 ORDER BY sort_val;