从数据库表中随机获取一条记录 (T-SQL)

Question

从数据库表中随机获取一条记录 (T-SQL)

sql-servert-sqlrandom

101

是否有一种简洁的方法从sql server表中检索一个随机记录？

我想要随机化我的单元测试数据，因此正在寻找一种简单的方式从表中选择一个随机id。用英语来说，查询应为“从表中选择一个id，其中id是在表中最低id和最高id之间的随机数。”

我无法想出一种不必运行查询、测试空值，然后重新运行的方法。

有什么好的想法吗？

- Jeremy

这里有几种方法：http://www.brettb.com/SQL_Help_Random_Numbers.asp - Mesh

2

你确定要采用这种方法吗？单元测试数据不应该是随机的 - 实际上，无论您执行多少次单元测试，都应该保证获得相同的结果。使用随机数据可能会违反单元测试的这一基本原则。 - rein

@Mesh提供的链接已经失效。 - Robert Sievers

6个回答

30

对于较大的表，您也可以使用 TABLESAMPLE 来避免扫描整个表。

SELECT  TOP 1 *
FROM YourTable
TABLESAMPLE (1000 ROWS)
ORDER BY NEWID()

为了避免仅返回出现在数据页上的行，仍需使用ORDER BY NEWID。

需要仔细选择用于表的大小和定义的数字，并且如果未返回任何行，则可能考虑重试逻辑。该技术背后的数学原理以及为什么不适用于小表在此处进行了讨论。

- Martin Smith

我在微软网站上找到了这个：当满足以下任一条件时，您可以使用TABLESAMPLE快速从大表中返回样本：

样本不必是单行级别的真正随机样本。
表格各页面上的行与同一页上的其他行之间没有相关性。

- Mark Entingh

1

@MarkEntingh - 在 TOP 1 的情况下，同一页上的行是否相关并不重要。你只需要选择其中一个。 - Martin Smith

这可以用于选择例如前X个（50个或预先设置的数量），并使用来自表格的计数为依据的TABLESAMPLE (@Rows ROWS)吗？ - PerPlexSystem

10

也尝试使用你的方法获取介于MIN（Id）和MAX（Id）之间的随机Id，然后

SELECT TOP 1 * FROM table WHERE Id >= @yourrandomid

它将始终获取一行数据。

- Sklivvz

2

-1，只有在最小值和最大值之间没有缺失的ID时才能正常工作。如果有一个被删除了，那么同样的ID将由随机函数生成，你将得到零条记录。 - Neil N

6

@Neil，不完全是这样——如果存在缺失的Id，则它将获取具有大于随机数的第一行Id。问题在于每行出现的概率并不是恒定的。但是话说回来，在大多数情况下，这已经足够了。 - Sklivvz

1

+1。针对单元测试而言，应该命中不同的值，这已经足够好了——如果您需要真正的随机数，那就另当别论了。但在 OP 上下文中，这应该已经足够好了。 - TomTom

7

如果你想选择大量数据，我所知道的最好方法是：

SELECT * FROM Table1
WHERE (ABS(CAST(
    (BINARY_CHECKSUM
    (keycol1, NEWID())) as int))
    % 100) < 10

Source: MSDN

- hmfarimani

我不确定，但我认为在生成真正随机数时使用RAND()而不是NEWID()可能更好，因为在选择过程中使用NEWID()存在缺点。 - QMaster

我尝试使用确切数量的记录而不是百分比基础来使用这种方法，我使用扩展选择范围并通过TOP n进行限制，是否有建议？ - QMaster

我发现这种情况还有另一个问题，如果你使用 group by，你将总是得到相同顺序的随机选择行，所以在小表中，@skilvvz 的方法似乎是最合适的。 - QMaster

0

我正在寻找改进我尝试过的方法，并偶然看到了这篇文章。我意识到它很旧，但是这种方法并未列出。我正在创建和应用测试数据；这展示了一个名为 @st （两个字符州）的存储过程中“地址”字段的方法。

Create Table ##TmpAddress (id Int Identity(1,1), street VarChar(50), city VarChar(50), st VarChar(2), zip VarChar(5))
Insert Into ##TmpAddress(street, city, st, zip)
Select street, city, st, zip 
From tbl_Address (NOLOCK)
Where st = @st


-- unseeded RAND() will return the same number when called in rapid succession so
-- here, I seed it with a guaranteed different number each time. @@ROWCOUNT is the count from the most recent table operation.

Set @csr = Ceiling(RAND(convert(varbinary, newid())) * @@ROWCOUNT)

Select street, city, st, Right(('00000' + ltrim(zip)),5) As zip
From ##tmpAddress (NOLOCK)
Where id = @csr

- user2788934

0

如果你真的想要一个随机的个别行样本，修改你的查询以随机过滤行，而不是使用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之间的随机浮点数。"

来源：http://technet.microsoft.com/en-us/library/ms189108(v=sql.105).aspx

这是如何工作的？让我们拆分WHERE子句并解释一下。

CHECKSUM函数计算列表中项目的校验和。是否需要SalesOrderID是有争议的，因为NEWID()是返回新的随机GUID的函数，因此将随机数字乘以常数应该在任何情况下都会得到随机数字。事实上，排除SalesOrderID似乎没有任何影响。如果您是一个热衷于统计学的人，并且可以证明包含它的合理性，请使用下面的评论部分并让我知道我错在哪里！

CHECKSUM函数返回VARBINARY。通过与0x7fffffff进行按位AND操作（相当于二进制中的111111111...），得到的十进制值实际上是0和1的随机字符串的表示形式。将其除以系数0x7fffffff有效地将此十进制数规范化为介于0和1之间的数字。然后，为了决定每行是否值得包含在最终结果集中，使用了1/x的阈值（在本例中为0.01），其中x是要检索为样本的数据的百分比。

来源：https://www.mssqltips.com/sqlservertip/3157/different-ways-to-get-random-data-for-sql-server-data-sampling

- XpiritO

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Sklivvz · Accepted Answer

有没有一种简洁的方式从SQL Server表中检索随机记录？

是的。

SELECT TOP 1 * FROM table ORDER BY NEWID()

解释

对于每个行，都会生成一个NEWID()，然后按照它进行排序。返回第一条记录（即具有“最低”GUID的记录）。

备注

从第四版开始，GUID被生成为伪随机数：
第4版UUID用于从真正随机或伪随机数生成UUID。

算法如下：
- 将clock_seq_hi_and_reserved的最高有效位（位6和7）分别设置为零和一。
- 将time_hi_and_version字段的四个最高有效位（位12到15）设置为第4.1.3节中的4位版本号。
- 将所有其他位设置为随机（或伪随机）选择的值。
—通用唯一标识符(UUID) URN命名空间-RFC 4122
替代方案SELECT TOP 1 * FROM table ORDER BY RAND()不能按照人们的想法工作。RAND()每次查询只返回一个单一值，因此所有行将共享相同的值。
虽然GUID值是伪随机的，但对于更苛刻的应用程序，您需要更好的PRNG。
典型性能为大约1,000,000行少于10秒 - 当然取决于系统。请注意，无法命中索引，因此性能将相对有限。