BigQuery中使用DISTINCT时ORDER BY RAND()排序失效问题求助

想要使用ORDER BY RAND()随机打乱查询结果，但在使用DISTINCT时，排序语句似乎被忽略。执行以下Google BigQuery代码后，结果按字母顺序排列，而非预期的随机排序：

WITH tmp AS (
SELECT 'aa' cardno
UNION ALL
SELECT 'bb' cardno
UNION ALL
SELECT 'cc' cardno
UNION ALL
SELECT 'dd' cardno
UNION ALL
SELECT 'ee' cardno
UNION ALL
SELECT 'ff' cardno
UNION ALL
SELECT 'gg' cardno
UNION ALL
SELECT 'hh' cardno
)

SELECT DISTINCT cardno FROM
(
SELECT *, RAND() rnd FROM tmp
ORDER BY 2
)

SQL标准中，子查询的ORDER BY不会被外层查询保留——当外层查询没有显式指定ORDER BY时，数据库不保证结果的输出顺序。BigQuery在处理DISTINCT时，会对数据进行去重优化，这会覆盖子查询的排序逻辑，最终默认按某种规则（如本例的字母序）返回结果。

直接在外层查询中添加ORDER BY RAND()，无需在子查询中排序，这样既能保证去重，又能实现随机排序：

WITH tmp AS (
SELECT 'aa' cardno
UNION ALL
SELECT 'bb' cardno
UNION ALL
SELECT 'cc' cardno
UNION ALL
SELECT 'dd' cardno
UNION ALL
SELECT 'ee' cardno
UNION ALL
SELECT 'ff' cardno
UNION ALL
SELECT 'gg' cardno
UNION ALL
SELECT 'hh' cardno
)
SELECT DISTINCT cardno 
FROM tmp
ORDER BY RAND()

如果是复杂场景下需要基于子查询生成的随机值排序，也可以先为每条数据生成随机标识，再完成去重和排序，但上述方法是最简洁高效的实现方式。

内容的提问来源于stack exchange，提问作者Rob