如何在纯SQL中请求随机行(或者尽可能接近真正的随机行)？

解决方案

回答

我不知道这有多有效，但是我以前用过：

SELECT TOP 1 * FROM MyTable ORDER BY newid()

由于GUID非常随机，因此排序意味着我们将获得随机行。

回答

SELECT * FROM table ORDER BY RAND() LIMIT 1

回答

我们没有说要使用哪个服务器。在旧版本的SQL Server中，可以使用以下命令：

select top 1 * from mytable order by newid()

在SQL Server 2005及更高版本中，我们可以使用" TABLESAMPLE"来获得可重复的随机样本：

SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;

回答

参见这篇文章：SQL从数据库表中选择一个随机行。它介绍了在MySQL，PostgreSQL，Microsoft SQL Server，IBM DB2和Oracle中执行此操作的方法(以下内容从该链接复制)：

使用MySQL选择一个随机行：

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

使用PostgreSQL选择一个随机行：

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

使用Microsoft SQL Server选择一个随机行：

SELECT TOP 1 column FROM table
ORDER BY NEWID()

使用IBM DB2选择随机行

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

使用Oracle选择一个随机记录：

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

回答

最好的方法是为此目的在新列中放入一个随机值，并使用如下代码(伪代码+ SQL)：

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

这是MediaWiki代码采用的解决方案。当然，对于较小的值存在一些偏差，但是他们发现，在没有获取任何行的情况下，将随机值包装为零就足够了。

newid()解决方案可能需要全表扫描，以便可以为每行分配一个新的guid，这将大大降低性能。

rand()解决方案可能根本无法工作(例如，使用MSSQL)，因为该函数只会被评估一次，并且每一行都将被分配相同的"随机"数字。

回答

杰里米(Jeremies)等解决方案：

SELECT * FROM table ORDER BY RAND() LIMIT 1

可以工作，但是它们需要对所有表进行顺序扫描(因为需要计算与每一行关联的随机值，以便可以确定最小的行)，即使对于中等大小的表也可能相当慢。我的建议是使用某种索引数字列(许多表将它们作为主键)，然后编写类似以下内容的内容：

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1

如果对" num_value"进行了索引，则无论表大小如何，它都能以对数时间工作。一个警告：假设num_value均匀分布在0..MAX(num_value)范围内。如果数据集严重偏离此假设，我们将获得歪斜的结果(某些行会比其他行更频繁地出现)。

回答

我必须同意CD-MaN：使用" ORDER BY RAND()"可以很好地用于小型表或者仅执行几次SELECT时。

我还使用了" num_value> = RAND()* ..."技术，如果我真的想获得随机结果，则在表中有一个特殊的"随机"列，该列每天大约更新一次。单个UPDATE运行将花费一些时间(特别是因为我们必须在该列上有一个索引)，但是它比每次运行选择时为每一行创建随机数要快得多。

回答

对于SQL Server 2005和2008，如果我们要随机获取单个行的样本(来自联机丛书)：

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)

回答

注意，因为TableSample实际上不会返回随机的行样本。它指示查询查看构成行的8KB页面的随机样本。然后，针对这些页面中包含的数据执行查询。由于可能在这些页面上对数据进行分组(插入顺序等)，因此这可能导致数据实际上不是随机样本。

请参阅：http://www.mssqltips.com/tip.asp?tip=1308

这个用于TableSample的MSDN页面包括一个如何生成实际随机数据样本的示例。

http://msdn.microsoft.com/en-us/library/ms189108.aspx

回答

对于SQL Server

newid()/ order by可以工作，但是对于大型结果集将非常昂贵，因为它必须为每一行生成一个id，然后对其进行排序。

从性能的角度来看，TABLESAMPLE()很好，但是我们会得到成堆的结果(将返回页面上的所有行)。

为了获得性能更好的真实随机样本，最好的方法是随机过滤出行。我在SQL Server联机丛书文章"使用TABLESAMPLE限制结果集"中找到以下代码示例：

If you really want a random sample of
  individual rows, modify your query to
  filter out rows randomly, instead of
  using TABLESAMPLE. For example, the
  following query uses the NEWID
  function to return approximately one
  percent of the rows of the
  Sales.SalesOrderDetail table:

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)

  
  The SalesOrderID column is included in
  the CHECKSUM expression so that
  NEWID() evaluates once per row to
  achieve sampling on a per-row basis.
  The expression CAST(CHECKSUM(NEWID(),
  SalesOrderID) & 0x7fffffff AS float /
  CAST (0x7fffffff AS int) evaluates to
  a random float value between 0 and 1.

当对具有1,000,000行的表运行时，这是我的结果：

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

如果我们可以避免使用TABLESAMPLE，它将为我们带来最佳性能。否则，请使用newid()/ filter方法。如果结果集较大，则newid()/ order by应该是最后的选择。

回答

这里的大多数解决方案旨在避免排序，但是它们仍然需要对表进行顺序扫描。

还有一种方法可以通过切换到索引扫描来避免顺序扫描。如果我们知道随机行的索引值，则几乎可以立即获得结果。问题是如何猜测索引值。

以下解决方案适用于PostgreSQL 8.4：

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

我在上面的解决方案中，我们猜测范围为0 .. [id的最后值]的10个各种随机索引值。

数字10是任意的，我们可以使用100或者1000，因为它(对我们而言)对响应时间没有太大的影响。

如果ID稀疏，可能还会遗漏一个问题。解决方案是有一个备份计划：)在这种情况下，可以通过random()查询获得纯旧订单。当组合的ID如下所示：

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

不是union ALL子句。在这种情况下，如果第一部分返回任何数据，则永远不会执行第二部分！

回答

最近，但是是通过Google到达的，因此为了后代，我将添加一个替代解决方案。

另一种方法是两次交替使用TOP。我不知道它是否是"纯SQL"，因为它在TOP中使用了变量，但它在SQL Server 2008中有效。如果想要一个随机单词，这是我对字典单词表使用的示例。

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

当然，@ idx是目标表上包含1到COUNT(*)在内的一些随机生成的整数。如果列已建立索引，那么我们也会从中受益。另一个优点是我们可以在函数中使用它，因为不允许使用NEWID()。

最后，以上查询在同一张表上执行的时间是NEWID()类型查询的执行时间的大约1/10. YYMV。

回答

ORDER BY NEWID()

需要7.4毫秒

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

需要0.0065毫秒！

我绝对会选择后一种方法。