如何在纯SQL中请求随机行(或尽可能接近真正随机)？
请参阅以下文章: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
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).如果您的数据集强烈偏离此假设,您将得到偏差的结果(某些行将比其他行更频繁地出现).
我不知道这有多高效,但我以前用过它:
SELECT TOP 1 * FROM MyTable ORDER BY newid()
因为GUID非常随机,所以排序意味着你得到一个随机行.
ORDER BY NEWID()
需要 7.4 milliseconds
WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)
需要0.0065 milliseconds!
我一定会选择后一种方法.
你没有说你正在使用哪个服务器.在旧版本的SQL Server中,您可以使用:
select top 1 * from mytable order by newid()
在SQL Server 2005及更高版本中,您可以使用TABLESAMPLE获取可重复的随机样本:
SELECT FirstName, LastName FROM Contact TABLESAMPLE (1 ROWS) ;
对于SQL Server
newid()/ order by会工作,但对于大型结果集来说会非常昂贵,因为它必须为每一行生成一个id,然后对它们进行排序.
从性能的角度来看,TABLESAMPLE()很好,但是你会得到结果的结果(页面上的所有行都会被返回).
对于性能更好的真随机样本,最好的方法是随机过滤掉行.我在SQL Server联机丛书文章中使用TABLESAMPLE限制结果集中找到以下代码示例:
如果您确实需要单个行的随机样本,请修改查询以随机过滤行,而不是使用TABLESAMPLE.例如,以下查询使用NEWID函数返回Sales.SalesOrderDetail表的大约百分之一的行:
SELECT * FROM Sales.SalesOrderDetail WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)SalesOrderID列包含在CHECKSUM表达式中,以便NEWID()每行计算一次以实现每行的采样.表达式CAST(CHECKSUM(NEWID(),SalesOrderID)和0x7fffffff AS float/CAST(0x7fffffff AS int)计算为0到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应该是最后的手段.
京公网安备 11010802040832号 | 京ICP备19059560号-6 