如何在纯SQL中请求随机行(或尽可能接近真正的随机)?
当前回答
用于MySQL获取随机记录
SELECT name
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
详情http://jan.kneschke.de/projects/mysql/order-by-rand/
其他回答
您也可以尝试使用new id()函数。
只需编写一个查询并使用order by new id()函数。它是随机的。
对于SQL Server和需要“单个随机行”..
如果不需要真采样,生成一个随机值[0,max_rows)并使用ORDER BY..OFFSET..从SQL Server 2012+获取。
如果COUNT和ORDER BY在适当的索引上,这是非常快的——这样数据就已经沿着查询行“排序”了。如果涵盖了这些操作,那么它就是一个快速请求,并且不会受到使用ORDER BY NEWID()或类似方法的可怕可伸缩性的影响。显然,这种方法在非索引的HEAP表上不能很好地伸缩。
declare @rows int
select @rows = count(1) from t
-- Other issues if row counts in the bigint range..
-- This is also not 'true random', although such is likely not required.
declare @skip int = convert(int, @rows * rand())
select t.*
from t
order by t.id -- Make sure this is clustered PK or IX/UCL axis!
offset (@skip) rows
fetch first 1 row only
确保使用了适当的事务隔离级别和/或考虑0结果。
对于SQL Server,需要一个“一般行样本”的方法..
注意:这是一个在SQL Server上找到的关于获取行样本的特定问题的答案的改编。它是根据上下文量身定制的。
虽然这里应该谨慎使用一般抽样方法,但对于其他答案(以及关于非伸缩和/或有问题的实现的重复建议),它仍然是潜在的有用信息。如果目标是找到“单个随机行”,那么这种抽样方法的效率低于所示的第一个代码,并且容易出错。
这是一个更新和改进的对行百分比进行抽样的形式。它基于与其他一些使用CHECKSUM / BINARY_CHECKSUM和modulus的答案相同的概念。
It is relatively fast over huge data sets and can be efficiently used in/with derived queries. Millions of pre-filtered rows can be sampled in seconds with no tempdb usage and, if aligned with the rest of the query, the overhead is often minimal. Does not suffer from CHECKSUM(*) / BINARY_CHECKSUM(*) issues with runs of data. When using the CHECKSUM(*) approach, the rows can be selected in "chunks" and not "random" at all! This is because CHECKSUM prefers speed over distribution. Results in a stable/repeatable row selection and can be trivially changed to produce different rows on subsequent query executions. Approaches that use NEWID() can never be stable/repeatable. Does not use ORDER BY NEWID() of the entire input set, as ordering can become a significant bottleneck with large input sets. Avoiding unnecessary sorting also reduces memory and tempdb usage. Does not use TABLESAMPLE and thus works with a WHERE pre-filter.
这是要点。有关更多细节和注意事项,请参阅这个答案。
Naï亿一下:
declare @sample_percent decimal(7, 4)
-- Looking at this value should be an indicator of why a
-- general sampling approach can be error-prone to select 1 row.
select @sample_percent = 100.0 / count(1) from t
-- BAD!
-- When choosing appropriate sample percent of "approximately 1 row"
-- it is very reasonable to expect 0 rows, which definitely fails the ask!
-- If choosing a larger sample size the distribution is heavily skewed forward,
-- and is very much NOT 'true random'.
select top 1
t.*
from t
where 1=1
and ( -- sample
@sample_percent = 100
or abs(
convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
) % (1000 * 100) < (1000 * @sample_percent)
)
这可以在很大程度上通过混合抽样和ORDER by从小得多的样本集中选择的混合查询来补救。这将排序操作限制为样本大小,而不是原始表的大小。
-- Sample "approximately 1000 rows" from the table,
-- dealing with some edge-cases.
declare @rows int
select @rows = count(1) from t
declare @sample_size int = 1000
declare @sample_percent decimal(7, 4) = case
when @rows <= 1000 then 100 -- not enough rows
when (100.0 * @sample_size / @rows) < 0.0001 then 0.0001 -- min sample percent
else 100.0 * @sample_size / @rows -- everything else
end
-- There is a statistical "guarantee" of having sampled a limited-yet-non-zero number of rows.
-- The limited rows are then sorted randomly before the first is selected.
select top 1
t.*
from t
where 1=1
and ( -- sample
@sample_percent = 100
or abs(
convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
) % (1000 * 100) < (1000 * @sample_percent)
)
-- ONLY the sampled rows are ordered, which improves scalability.
order by newid()
请参阅这篇文章:从数据库表中随机选择一行的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
用于MySQL获取随机记录
SELECT name
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
详情http://jan.kneschke.de/projects/mysql/order-by-rand/
你没说你用的是哪台服务器。在旧版本的SQL Server中,你可以使用这个:
select top 1 * from mytable order by newid()
在SQL Server 2005及以上版本中,你可以使用TABLESAMPLE来获取一个可重复的随机样本:
SELECT FirstName, LastName
FROM Contact
TABLESAMPLE (1 ROWS) ;
推荐文章
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- random.seed():它做什么?
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- Java中生成UUID字符串的有效方法(UUID. randomuuid ().toString()不带破折号)
- SQL Server中User和Login的区别
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- 设置NOW()为datetime数据类型的默认值?
- 在MySQL中Datetime等于或大于今天
- 如何从字典中获得一个随机值?
- 删除MySQL中的主键
- 对象'DF__*'依赖于列'*' -将int改为double