我有一个包含大约 50,000 行的 SQL Server 表。我想随机选择大约 5,000 行。我想到了一种复杂的方法,创建一个带有“随机数”列的临时表,将我的表复制到其中,循环遍历临时表并使用 RAND()
更新每一行,然后从该表中选择随机数数字列 < 0.1。我正在寻找一种更简单的方法来做到这一点,如果可能的话,在一个语句中。
This article 建议使用 NEWID()
函数。这看起来很有希望,但我看不出我如何能够可靠地选择一定百分比的行。
以前有人这样做过吗?有任何想法吗?
select top 10 percent * from [yourtable] order by newid()
回应有关大表的“纯垃圾”评论:您可以这样做以提高性能。
select * from [yourtable] where [yourPk] in
(select top 10 percent [yourPk] from [yourtable] order by newid())
这样做的成本将是值的键扫描加上连接成本,这在具有小百分比选择的大表上应该是合理的。
根据您的需要,TABLESAMPLE
将为您带来几乎一样随机和更好的性能。这在 MS SQL Server 2005 及更高版本上可用。
TABLESAMPLE
将从随机页面而不是随机行返回数据,因此 deos 甚至不会检索它不会返回的数据。
在我测试的一张非常大的桌子上
select top 1 percent * from [tablename] order by newid()
花了20多分钟。
select * from [tablename] tablesample(1 percent)
花了2分钟。
TABLESAMPLE
中较小样本的性能也会有所提高,而 newid()
则不会。
请记住,这不像 newid()
方法那样随机,但会给您一个不错的采样。
请参阅 MSDN page。
newid()/order by 会起作用,但对于大型结果集来说会非常昂贵,因为它必须为每一行生成一个 id,然后对它们进行排序。
TABLESAMPLE() 从性能的角度来看是好的,但你会得到结块的结果(将返回页面上的所有行)。
为了获得更好的真实随机样本,最好的方法是随机过滤掉行。我在 SQL Server 联机丛书文章 Limiting Results Sets by Using 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 应该是最后的手段。
NewID()
只被评估一次,而不是每行,我不喜欢......
MSDN 上的 Selecting Rows Randomly from a Large Table 有一个简单、清晰的解决方案,可以解决大规模的性能问题。
SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10
RAND()
不会为每一行返回相同的值(这会破坏 BINARY_CHECKSUM()
逻辑)。是因为它是在另一个函数内部调用的,而不是 SELECT 子句的一部分吗?
rand()
的问题或上述问题的组合 - 但出于这个原因,我拒绝了这个解决方案。此外,结果的数量从 1 到 5 不等,因此在某些情况下这也可能是不可接受的。
RAND()
为每一行返回相同的值(这就是该解决方案快速的原因)。但是,具有非常接近的二进制校验和的行很可能生成相似的校验和结果,从而在 RAND()
较小时导致聚集。例如,(ABS(CAST((BINARY_CHECKSUM(111,null,null) * 0.1) as int))) % 100
== SELECT (ABS(CAST((BINARY_CHECKSUM(113,null,null) * 0.1) as int))) % 100
。如果您的数据遇到此问题,请将 BINARY_CHECKSUM
乘以 9923。
此链接对具有 1、7 和 1300 万行的表的 Orderby(NEWID()) 和其他方法进行了有趣的比较。
通常,当在讨论组中询问有关如何选择随机行的问题时,会提出 NEWID 查询;它很简单,非常适合小桌子。
SELECT TOP 10 PERCENT *
FROM Table1
ORDER BY NEWID()
但是,当您将 NEWID 查询用于大型表时,它有一个很大的缺点。 ORDER BY 子句使表中的所有行都复制到 tempdb 数据库中,并在其中进行排序。这会导致两个问题:
排序操作通常具有与之相关的高成本。排序可以使用大量的磁盘 I/O,并且可以运行很长时间。在最坏的情况下,tempdb 可能会耗尽空间。在最好的情况下,tempdb 会占用大量磁盘空间,如果没有手动收缩命令,这些空间永远不会被回收。
您需要的是一种随机选择行的方法,该方法不会使用 tempdb,并且不会随着表变大而变慢。这是一个关于如何做到这一点的新想法:
SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10
这个查询背后的基本思想是,我们要为表中的每一行生成一个 0 到 99 之间的随机数,然后选择所有那些随机数小于指定百分比值的行。在本例中,我们希望随机选择大约 10% 的行;因此,我们选择随机数小于 10 的所有行。
请阅读 MSDN 中的完整文章。
只需按随机数排序表并使用 TOP
获取前 5,000 行。
SELECT TOP 5000 * FROM [Table] ORDER BY newid();
更新
刚刚试了一下,一个 newid()
调用就足够了——不需要所有的演员表和所有的数学。
如果您(与 OP 不同)需要特定数量的记录(这使得 CHECKSUM 方法变得困难)并且希望获得比 TABLESAMPLE 本身提供的更随机的样本,并且还想要比 CHECKSUM 更快的速度,您可以通过合并TABLESAMPLE 和 NEWID() 方法,如下所示:
DECLARE @sampleCount int = 50
SET STATISTICS TIME ON
SELECT TOP (@sampleCount) *
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()
SET STATISTICS TIME OFF
就我而言,这是随机性(我知道这不是真的)和速度之间最直接的折衷。根据需要改变 TABLESAMPLE 百分比(或行) - 百分比越高,样本越随机,但预计速度会线性下降。 (注意 TABLESAMPLE 不接受变量)
这是初始种子想法和校验和的组合,在我看来,它可以在没有 NEWID() 成本的情况下给出适当的随机结果:
SELECT TOP [number]
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())
在 MySQL 中,您可以这样做:
SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;
还没有完全看到答案中的这种变化。在给定初始种子的情况下,我需要一个额外的约束来每次选择相同的行集。
对于 MS SQL:
最小示例:
select top 10 percent *
from table_name
order by rand(checksum(*))
标准化执行时间:1.00
NewId() 示例:
select top 10 percent *
from table_name
order by newid()
标准化执行时间:1.02
NewId()
比 rand(checksum(*))
慢一点,因此您可能不想将它用于大型记录集。
用初始种子选择:
declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */
select top 10 percent *
from table_name
order by rand(checksum(*) % @seed) /* any other math function here */
如果您需要在给定种子的情况下选择相同的集合,这似乎可行。
尝试这个:
SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()
这是一种更新和改进的抽样形式。它基于使用 CHECKSUM
/ BINARY_CHECKSUM
和模数的其他一些答案的相同概念。
使用与此类似的实现的原因,而不是其他答案:
它在庞大的数据集上相对较快,并且可以有效地用于派生查询中/与派生查询一起使用。无需使用 tempdb 即可在几秒钟内对数百万个预先过滤的行进行采样,如果与查询的其余部分保持一致,则开销通常很小。
不受数据运行的 CHECKSUM(*) / BINARY_CHECKSUM(*) 问题的影响。使用 CHECKSUM(*) 方法时,可以在“块”中选择行,而不是“随机”!这是因为 CHECKSUM 更喜欢速度而不是分布。
产生稳定/可重复的行选择,并且可以简单地更改以在后续查询执行中生成不同的行。使用 NEWID() 的方法,例如 CHECKSUM(NEWID()) % 100,永远不会是稳定的/可重复的。
允许提高样本精度并减少引入的统计误差。采样精度也可以调整。 CHECKSUM 只返回一个 int 值。
不使用 ORDER BY NEWID(),因为对于大型输入集,排序可能成为一个重要的瓶颈。避免排序还可以减少内存和 tempdb 的使用。
不使用 TABLESAMPLE,因此与 WHERE 预过滤器一起使用。
缺点/限制:
执行时间稍慢并使用 CHECKSUM(*)。如下所示,使用哈希字节每百万行增加大约 3/4 秒的开销。这是我的数据,在我的数据库实例上:YMMV。如果使用来自 HASHBYTES 的结果“分布良好”的 bigint 值的持久计算列,则可以消除此开销。
与基本的 SELECT TOP n .. ORDER BY NEWID() 不同,这不能保证返回“恰好 N”行。相反,它返回一个百分比行行,其中这样的值是预先确定的。对于非常小的样本量,这可能会导致选择 0 行。此限制与 CHECKSUM(*) 方法共享。
这是要点:
-- Allow a sampling precision [0, 100.0000].
declare @sample_percent decimal(7, 4) = 12.3456
select
t.*
from t
where 1=1
and t.Name = 'Mr. No Questionable Checksum Usages'
and ( -- sample
@sample_percent = 100
or abs(
-- Choose appropriate identity column(s) for hashbytes input.
-- For demonstration it is assumed to be a UNIQUEIDENTIFIER rowguid column.
convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
) % (1000 * 100) < (1000 * @sample_percent)
)
笔记:
虽然 SHA1 自 SQL Server 2016 以来在技术上已被弃用,但它既足以完成任务,又比 MD5 或 SHA2_256 略快。根据需要使用不同的散列函数。如果该表已经包含一个散列列(具有良好的分布),那么它也可能被使用。
bigint 的转换至关重要,因为它允许 2^63 位的“随机空间”应用模数运算符;这远远超过 CHECKSUM 结果的 2^31 范围。这减少了极限处的模数误差,特别是随着精度的提高。
只要适当地乘以模操作数和采样百分比,就可以改变采样精度。在这种情况下,即 1000 * 来解释 @sample_percent 中允许的 4 位精度。
可以将 bigint 值乘以 RAND() 以在每次运行时返回不同的行样本。这有效地改变了固定哈希值的排列。
如果@sample_percent 为 100,则查询计划器可以完全消除较慢的计算代码。记住“参数嗅探”规则。这允许将代码留在查询中,而不管是否启用采样。
计算具有下限/上限的 @sample_percent
,并在查询中添加 TOP
“提示”,因为当样本用于派生表上下文时,可能很有用。
-- Approximate max-sample and min-sample ranges.
-- The minimum sample percent should be non-zero within the precision.
declare @max_sample_size int = 3333333
declare @min_sample_percent decimal(7,4) = 0.3333
declare @sample_percent decimal(7,4) -- [0, 100.0000]
declare @sample_size int
-- Get initial count for determining sample percentages.
-- Remember to match the filter conditions with the usage site!
declare @rows int
select @rows = count(1)
from t
where 1=1
and t.Name = 'Mr. No Questionable Checksum Usages'
-- Calculate sample percent and back-calculate actual sample size.
if @rows <= @max_sample_size begin
set @sample_percent = 100
end else begin
set @sample_percent = convert(float, 100) * @max_sample_size / @rows
if @sample_percent < @min_sample_percent
set @sample_percent = @min_sample_percent
end
set @sample_size = ceiling(@rows * @sample_percent / 100)
select *
from ..
join (
-- Not a precise value: if limiting exactly at, can introduce more bias.
-- Using 'option optimize for' avoids this while requiring dynamic SQL.
select top (@sample_size + convert(int, @sample_percent + 5))
from t
where 1=1
and t.Name = 'Mr. No Questionable Checksum Usages'
and ( -- sample
@sample_percent = 100
or abs(
convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
) % (1000 * 100) < (1000 * @sample_percent)
)
) sampled
on ..
看来 newid() 不能在 where 子句中使用,所以这个解决方案需要一个内部查询:
SELECT *
FROM (
SELECT *, ABS(CHECKSUM(NEWID())) AS Rnd
FROM MyTable
) vw
WHERE Rnd % 100 < 10 --10%
我在子查询中使用它,它在子查询中返回了相同的行
SELECT ID ,
( SELECT TOP 1
ImageURL
FROM SubTable
ORDER BY NEWID()
) AS ImageURL,
GETUTCDATE() ,
1
FROM Mytable
然后我解决了在 where 中包含父表变量
SELECT ID ,
( SELECT TOP 1
ImageURL
FROM SubTable
Where Mytable.ID>0
ORDER BY NEWID()
) AS ImageURL,
GETUTCDATE() ,
1
FROM Mytable
注意 where 条件
未指定使用的服务器端处理语言(例如 PHP、.net 等),但如果是 PHP,请获取所需的数字(或所有记录),而不是在查询中随机化,而是使用 PHP 的 shuffle 函数。我不知道 .net 是否具有等效功能,但如果有,那么如果您使用的是 .net,则使用该功能
ORDER BY RAND() 可能会产生相当大的性能损失,具体取决于涉及的记录数。
select * from table
where id in (
select id from table
order by random()
limit ((select count(*) from table)*55/100))
// to select 55 percent of rows randomly
如果你知道你有大约 N 行并且你想要大约 K 个随机行,你只需要以 K/N 的机会拉出任何给定的行。使用 RAND() 函数可以在 0 和 1 之间进行公平分配,您可以在 PROB = K/N 的情况下执行以下操作。对我来说工作得很快。
SELECT * FROM some_table WHERE RAND() < PROB
这对我有用:
SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]
select top 10 percent from table_name order by rand()
,但这也不起作用,因为 rand() 在所有行上返回相同的值。
[yourPk]
指的是什么?编辑:Nvm,想通了...主键。杜尔newid()
排序估计 I/O 成本将非常高,并且会影响性能。