ORDER BY RAND()似乎小于随机数 [英] ORDER BY RAND() seems to be less than random

问题描述

我有一个相当简单的SQL(MySQL):

I have a fairly simple SQL (MySQL):

SELECT foo FROM bar ORDER BY rank, RAND()

我注意到当我刷新结果时，随机性很可疑.

I notice that when I refresh the results, the randomness is suspiciously weak.

目前，在样本数据中有六个具有相同等级(整数为零)的结果.有很多随机性测试，但这是一个手工完成的简单测试:运行两次时，两次运行中的第一个结果应该是相同的，大约是时间的六分之一.这肯定不会发生，领先的结果至少三分之一是相同的.

In the sample data at the moment there are six results with equal rank (integer zero). There are lots of tests for randomness but here is a simple one to do by hand: when run twice, the first result should be the same in both runs about one sixth of the time. This is certainly not happening, the leading result is the same at least a third of the time.

我想要排列的均匀分布.我不是专业的统计学家，但我很确定ORDER BY RAND()应该可以实现这一目标.我想念什么?

I want a uniform distribution over the permutations. I'm not an expert statistician but I'm pretty sure ORDER BY RAND() should achieve this. What am I missing?

在MySQL中，SELECT rand(), rand()显示两个不同的数字，所以我不购买每个查询一次"的解释

With MySQL, SELECT rand(), rand() shows two different numbers, so I don't buy the "once per query" explanation

推荐答案

RAND()每个查询仅执行一次.您可以通过查看结果集来验证这一点.

RAND() is only executed once per query. You can verify this by looking at the result set.

如果要获取随机订单，则应使用NEWID()或CHECKSUM(NEWID()).

If you're trying to get a randomized order, you should be using either NEWID() or CHECKSUM(NEWID()).

WITH T AS ( -- example using RAND()
  SELECT 'Me' Name UNION SELECT 'You' UNION SELECT 'Another'
)
SELECT Name, RAND()
FROM T;

WITH T AS ( -- example using just NEWID()
  SELECT 'Me' Name UNION SELECT 'You' UNION SELECT 'Another'
)
SELECT Name, NEWID()
FROM T;

WITH T AS ( -- example getting the CHECKSUM() of NEWID()
  SELECT 'Me' Name UNION SELECT 'You' UNION SELECT 'Another'
)
SELECT Name, CHECKSUM(NEWID())
FROM T;

这篇关于ORDER BY RAND()似乎小于随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！