从SQL Server获取随机数据但不重复值

Question

我需要每次从表格中获取10行随机数据，但在重复查询时这些行不应重复。

如果我获取所有行，则会从第一行重新开始重复，例如表格有20行，第一次获取10行随机数据，第二次我需要获取剩下的10行，第三次查询时我需要再次获取10行随机数据。

目前我用以下语句来获取10行随机数据：

SELECT TOP 10 * 
FROM tablename
ORDER BY NEWID() 

但是MSDN建议使用以下查询：

SELECT TOp 10 * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10 

为了获得良好的性能，但是这个查询并不能返回恒定的行数。请您提出一些建议。

- Manikandan

---

请展示样例数据。 - NEER

Answer 1

由于第二个查询所需的结果取决于第一个查询的（随机）结果，因此查询无法是无状态的。您需要在某个地方存储状态（有关先前查询的信息）。

最简单的解决方案可能是将已检索的行或其ID存储在临时表中，然后在第二个查询中查询... where id not in (select id from temp_table)。

- Jiri Tousek

---

数据非常大，比如我们有20000行或更多，那么如何将数据存储到临时表中并反复运行呢？这会降低性能，我说得对吗？ - Manikandan

是的，随着你往临时表里添加记录，性能会下降，但如果你想得到所需的结果，你唯一的选择是将已选择的行存储在某个地方，也许一个带索引的表对于你的情况提供足够的性能？ - Doliveras

2万行数据并不算太多。例如，如果您的行具有整数ID，并且只存储已使用行的ID，则临时表可能只需要100kB的空间，这可以轻松地缓存在内存中，即使没有索引也可以快速访问。 - Jiri Tousek

Msdn建议针对此查询使用以下代码： SELECT * FROM Table1 WHERE (ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 10 for performance这里我有一个简单的问题，就是我可以在这里添加额外的where条件吗？比如id>10，我需要在Binary_checksum之前还是之后添加这个where条件？ - Manikandan

Answer 2

如Jiri Tousek所说，您运行的每个查询都必须知道先前查询返回了什么。

不要将先前返回行的ID插入表中，然后检查新结果是否尚未在该表中，而是简单地向表中添加一个带有随机数字的列，该数字将定义行的新随机顺序。

您只需一次填充此列即可。

这将记住行的随机顺序并使其稳定，因此您在查询之间需要记住的只是您已请求多少个随机行。然后，从上一个查询停止的位置开始获取所需数量的行。

---

在表中添加一个名为RandomNumber binary(8)的列。您可以选择不同的大小，但8个字节应该足够了。

使用随机数填充它。只需一次即可。

UPDATE tablename
SET RandomNumber = CRYPT_GEN_RANDOM(8)

在RandomNumber列上创建一个唯一索引。如果出现重复的随机数（对于20000行和8字节长的随机数来说，这是不太可能的），则重新生成随机数（再次运行UPDATE语句），直到它们全部都是唯一的。

请求前10个随机行：

SELECT TOP(10) *
FROM tablename
ORDER BY RandomNumber

当您处理/使用这10个随机行时，请记住上次使用的随机数。最好的方法取决于您如何处理这10个随机行。

DECLARE @VarLastRandomNumber binary(8);
SET @VarLastRandomNumber = ... 
-- the random number from the last row returned by the previous query

请求下10个随机行：

SELECT TOP(10) *
FROM tablename
WHERE RandomNumber > @VarLastRandomNumber
ORDER BY RandomNumber

处理它们并记住上次使用的随机数。

重复。作为奖励，您可以在每次迭代中请求不同数量的随机行（不必每次都是10行）。

- Vladimir Baranov

---

如果 a) 提问者能够修改表本身，b) 表在整个过程中保持稳定（没有新的或删除的行），c) 只有一个会话同时执行此过程，则这是一种可行的方法。由于提问者没有提供任何具体细节，因此我们并不真正知道。 - Jiri Tousek

Answer 3

我会添加两个新字段，SELECTED（整数）和TimesSelected（整数），然后……

UPDATE tablename SET SELECTED = 0;

WITH CTE AS (SELECT TOP 10 * 
FROM tablename
ORDER BY TimesSelected  ASC, NEWID())
UPDATE CTE SET SELECTED = 1,  TimesSelected  = TimesSelected  + 1;

SELECT * from  tablename  WHERE SELECTED = 1; 

如果每次使用它，一旦选择了记录，它就会移到堆栈顶部，下面的记录将随机选择。

您可能希望在SELECTED上放置索引并执行以下操作：

UPDATE tablename SET SELECTED = 0 WHERE SELECTED = 1;   -- for performance

- Cato

---

是的，我认为这将起作用。一旦选择了记录并变为 1，直到所有其他记录都为 1，它就不能被重新选择，当发生这种情况时，某些记录将变为 2，然后变得不合格，直到其他记录赶上。 - Cato

Answer 4

如果您在特定时间内进行连续查询，最优雅的解决方案是使用游标：

DECLARE rnd_cursor CURSOR FOR 
  SELECT col1, col2, ... 
  FROM tablename
  ORDER BY NEWID(); 

OPEN rnd_cursor;  
FETCH NEXT FROM rnd_cursor;  -- Repeat ten times

保持光标打开状态，按需获取行。完成后关闭光标：

CLOSE rnd_cursor;  
DEALLOCATE rnd_cursor;  

关于你问题的第二部分，一旦你获取到最后一行，打开一个新的游标：

IF @@FETCH_STATUS <> 0
BEGIN
  CLOSE rnd_cursor;
  OPEN rnd_cursor;
END;

- Twinkles