从SQL Server表中选择n个随机行

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及更高版本上可用。

表列 将从随机页而不是随机行返回数据,因此DEOS甚至不会检索它不会返回的数据。

我在一张很大的桌子上测试过

select top 1 percent * from [tablename] order by newid()

花了20多分钟。

select * from [tablename] tablesample(1 percent)

花了2分钟。

性能也将改善较小的样品在 表列 但是它不会 newid() .

请记住,这并不像 NWID() 方法,但会给你一个体面的抽样。

见 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列包含在 校验和表达式,以便 newid()每行计算一次 按行取样。 表达式强制转换(checksum(newid(), salesorderid)&0x7fffffff,浮点数/ CAST(0x7fffffff as int)的计算结果为 介于0和1之间的随机浮点值。

当针对具有1000000行的表运行时,以下是我的结果:

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应该是最后的选择。

Selecting Rows Randomly from a Large Table 在msdn上有一个简单、清晰的解决方案,可以解决大规模的性能问题。

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

如果您(与OP不同)需要特定数量的记录(这使得校验和方法变得困难),并且希望获得比Tablesample本身提供的更随机的样本,并且希望获得比校验和更快的速度,则可以将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不接受变量)

只需按随机数排序表,并使用 TOP .

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

更新

刚试过,然后 newid() 电话就足够了-不需要所有的演员和所有的数学。

对于具有1、7和1300万行的表,此链接对orderby(newid())和其他方法进行了有趣的比较。

通常,当在讨论组中询问有关如何选择随机行的问题时,会提出newid查询;它很简单,对于小表来说非常有效。

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

但是,当您将newid查询用于大型表时,它有一个很大的缺点。ORDER BY子句会将表中的所有行复制到tempdb数据库中,并在其中进行排序。这会导致两个问题:

1. 排序操作通常具有较高的相关成本。 排序可以使用大量的磁盘I/O,并且可以运行很长时间。
2. 在最坏的情况下,tempdb可能会耗尽空间。在 最佳情况下,tempdb可能占用大量磁盘空间 如果没有手动收缩命令,永远无法回收。

您需要的是一种随机选择行的方法,这些行不会使用tempdb,并且不会随着表变大而变慢。下面是一个关于如何做到这一点的新想法:

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

这个查询背后的基本思想是,我们希望为表中的每一行生成一个介于0和99之间的随机数,然后选择随机数小于指定百分比值的所有行。在本例中,我们希望随机选择大约10%的行;因此,我们选择随机数小于10的所有行。

请阅读 MSDN .

在MySQL中,您可以这样做:

SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;

这是初始种子思想和校验和的组合,在我看来,它可以在不花费newid()的情况下给出适当的随机结果:

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

试试这个:

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

在答案中还没有看到这种变化。我在需要的地方有一个额外的约束,给定一个初始种子,每次选择相同的行集。

对于SQL 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 */

如果您需要在给定种子的情况下选择相同的集合,这似乎是可行的。

似乎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

然后我解决了将父表变量包含在

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

注意哪种情况

未指定正在使用的服务器端处理语言(如php、.net等),但如果是php,则获取所需的数字(或所有记录),而不是在查询中随机使用php的shuffle函数。我不知道.NET是否有等价的函数,但如果它有,那么如果您使用.NET,请使用它。

按rand()排序可能会有相当大的性能损失,这取决于涉及的记录数。

这对我很有用:

SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]