sql - 使用 ROW_NUMBER() 随机获取记录的问题 (SQL Server 2005)

Question

我想从一个表中随机获取 1000 条记录，所以我使用:

SELECT top 1000 
       mycol1
     , mycol2
     , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
FROM mytable

但是，我不想在我的结果集中看到 rn，所以我这样做:

SELECT mycol1
     , mycol2
FROM (
    SELECT top 1000 
           mycol1
         , mycol2
         , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
    FROM mytable
) a

当我这样做时，结果不再随机出现。它们就好像我只是说前 10000 个没有使用 row_number() 随机化一样。

当我将查询更改为

SELECT mycol1
     , mycol2
     , rn
FROM (
    SELECT top 1000 
           mycol1
         , mycol2
         , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
    FROM mytable
) a

它们又是随机的。

我猜 sql server 做了某种优化，说“嘿，这家伙根本不需要 rn 列，所以忽略它吧”。但在这种情况下，这会导致意外行为。有什么办法可以避免这种情况吗？

PS:我使用 ROW_NUMBER() 技巧，因为 mytable 有 10 mio。行和

SELECT top 10000 *
FROM mytable
ORDER BY NEWID()

永远运行，而使用 ROW_NUMBER() 最多只需要 30 秒。

Answer 1

您也可以尝试在一些小的 where 子句中使用 rn 字段，例如

WHERE rn > 0 在您的外部查询中，这可能会强制编译器通过 RN 字段。

此外，如果您想随机抽样整个数百万条记录，我认为您的整体查询将成为一个问题。这只会抓取“第一个磁盘”记录 block ，虽然不能保证相同，但通常是相同的 10000。

我建议在 MIN(PrimaryKey) 和 MAX(PrimaryKey) 之间创建一组 10,000 个随机数，然后执行 WHERE PrimaryKey IN (...) 或类似操作