如何在纯SQL中请求随机行(或尽可能接近真正的随机)?
当前回答
如果可能的话,使用存储语句来避免RND()上的索引和创建记录编号字段的效率低下。
PREPARE RandomRecord FROM "SELECT * FROM table LIMIT ?,1"; SET @n=FLOOR(RAND()*(SELECT COUNT(*) FROM table)); EXECUTE RandomRecord USING @n;
其他回答
而不是使用RAND(),因为它是不鼓励的,你可以简单地得到max ID (= max):
SELECT MAX(ID) FROM TABLE;
在1..Max (= My_Generated_Random)
My_Generated_Random = rand_in_your_programming_lang_function(1..Max);
然后运行SQL:
SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1
注意,它将检查id等于或高于所选值的任何行。 也可以在表中寻找行,并获得一个等于或低于My_Generated_Random的ID,然后修改查询如下:
SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1
使用SQL Server 2012+,您可以使用OFFSET FETCH查询对单个随机行执行此操作
select * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY
其中id是一个标识列,n是您想要的行—计算为表的0到count()-1之间的随机数(偏移量0毕竟是第一行)
这适用于表数据中的漏洞,只要您有一个用于ORDER BY子句的索引。它对随机性也很有好处——因为你可以自己计算出来传递进来,但其他方法中的琐碎问题是不存在的。此外,性能非常好,在一个较小的数据集上,它表现得很好,尽管我没有尝试过针对数百万行进行认真的性能测试。
Oracle有更好的解决方案,而不是使用dbms_random。值,而它需要完全扫描dbms_random来排序行。值,对于大表来说非常慢。
用这个代替:
SELECT *
FROM employee sample(1)
WHERE rownum=1
SQL Server
Newid ()/order by可以工作,但对于大型结果集来说代价非常高,因为它必须为每一行生成一个id,然后对它们进行排序。
从性能的角度来看,TABLESAMPLE()很好,但是您将得到结果的聚集(将返回页面上的所有行)。
为了获得更好的真实随机样本,最好的方法是随机过滤掉行。我在SQL Server Books Online文章使用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,它将为您提供最佳性能。否则使用newwid ()/filter方法。如果结果集很大,Newid ()/order by应该是最后的选择。
请参阅这篇文章:从数据库表中随机选择一行的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
推荐文章
- 使用该集合的原因。种子功能
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?
- NOLOCK提示在SELECT语句中的作用
- SQL OVER()子句-它什么时候有用,为什么有用?
- 如果字段在MySQL中为空,则返回0
- 从枚举中选择一个随机值?
- 我如何使用ROW_NUMBER()?