我有一个SQL Server表,其中有大约50,000行。我想随机选择大约5000行。我想到了一种复杂的方法,创建一个带有“随机数”列的临时表,将我的表复制到其中,循环遍历临时表并使用RAND()更新每一行,然后从该表中选择随机数列< 0.1的列。我正在寻找一种更简单的方法,如果可能的话,在一个单一的声明中。

本文建议使用NEWID()函数。这看起来很有希望,但我不知道如何可靠地选择一定百分比的行。

有人做过这个吗?什么好主意吗?


当前回答

试试这个:

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

其他回答

这个链接在Orderby(NEWID())和其他方法之间进行了有趣的比较,这些方法用于具有1,700万行和1,300万行的表。

通常,当讨论组中询问如何选择随机行时,会提出NEWID查询;它很简单,适用于小表。

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

但是,当您将newd查询用于大型表时,它有一个很大的缺点。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上阅读全文。

从MSDN上的大表中随机选择行有一个简单的、明确的解决方案,可以解决大规模性能问题。

  SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10
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())

这样做的成本将是值的关键扫描加上连接成本,对于一个选择百分比较小的大表来说,这应该是合理的。

这是最初的种子思想和校验和的组合,在我看来,它可以给出适当的随机结果,而不需要NEWID()的代价:

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

如果你(不像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不接受变量)