SQL Server 寻求返回随机的10%记录。

12 浏览
0 Comments

SQL Server 寻求返回随机的10%记录。

我有一个包含大约5万行的SQL Server表。我想随机选择其中约5000行。我想到了一个复杂的方法，创建一个带有"随机数"列的临时表，将我的表复制到其中，循环遍历临时表并使用RAND()更新每一行，然后从该表中选择随机数列小于0.1的行。我正在寻找一种更简单的方法，如果可能的话，在一个语句中完成。

这篇文章建议使用NEWID()函数。这看起来很有希望，但我无法确定如何可靠地选择一定比例的行。

有人以前做过这个吗？有什么想法吗？

0

2 答案

0 Comments

在SQL Server中，如果需要返回一个随机的10%的记录，有几种方法可以实现。一种方法是使用newid()和order by子句，但对于大结果集来说，这将非常耗费资源，因为需要为每一行生成一个id，并对它们进行排序。另一种方法是使用TABLESAMPLE()函数，从性能的角度来看是比较好的选择，但结果可能会出现聚集性（返回同一页中的所有行）。为了得到性能更好的真正随机样本，最好的方法是随机过滤行。下面是在SQL Server Books Online文章《Limiting Results Sets by Using TABLESAMPLE》中找到的示例代码：

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)

这个查询使用了NEWID函数来返回Sales.SalesOrderDetail表的大约1%的行。为了实现按行进行抽样，CHECKSUM表达式中包含了SalesOrderID列，使得NEWID()每行只计算一次。表达式`CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)`会计算一个0到1之间的随机浮点值。

当对一个有1,000,000行的表运行这些查询时，得到以下结果：

/* newid()
   返回行数: 10000
   逻辑读取次数: 3359
   CPU时间: 3312 ms
   经过时间: 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()
/* TABLESAMPLE
   返回行数: 9269 (变化)
   逻辑读取次数: 32
   CPU时间: 0 ms
   经过时间: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)
/* Filter
   返回行数: 9994 (变化)
   逻辑读取次数: 3359
   CPU时间: 641 ms
   经过时间: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

如果可以使用TABLESAMPLE，它将提供最好的性能。否则，可以使用newid()/filter的方法。如果结果集很大，newid()/order by应该是最后的选择。

需要注意的是，有人指出使用newid()函数时，它只会被计算一次，而不是每行计算一次，这可能会导致一些问题。

0
0 Comments

问题的原因：该问题的原因是需要从SQL Server数据库中返回随机的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())

第一种方法会返回随机的10%的记录，而第二种方法在大表中选择小百分比的数据时性能更好。这两种方法都使用了newid()函数来实现随机排序。

需要注意的是，newid()函数并不是一个真正好的伪随机数生成器，至少不如rand()函数好。但如果只需要一些近似随机的样本，而不关心数学特性等，newid()函数就足够了。

另外，使用rand()函数而不是newid()函数还允许设置一个种子，这在需要查询结果可重复时非常有用。

有关在大表上使用NEWID()的成本的评论并不是“纯垃圾”。官方的Microsoft文档中也提到了这一点。使用ORDER BY子句会导致将表中的所有行复制到tempdb数据库中，然后对它们进行排序。在大表上使用NEWID()会产生很高的排序估计I/O成本，从而影响性能。

GUID作为一个整体被设计为唯一的，但是NEWID()确实是一个随机数。它是一个v4 UUID，你可以通过其第三组的开头始终是4来判断。在v4 UUID中，除了第三组的开头（始终是4）和第四组的开头（始终是8、9、A或B，因为这是版本变体），其他所有位都是完全随机的数字。v4 UUID只是一些花哨的~122位随机数。SQL Server文档说明NEWID()是RFC4122兼容的，并且RFC4122明确指出了其随机性：tools.ietf.org/html/rfc4122#section-4.4

最后，需要注意的是[yourPk]是一个占位符，需要根据实际情况替换为数据库表中的主键字段名。

0