获得随机订购的最佳方法是什么？

#1 楼

ORDER BY NEWID（）将对记录进行随机排序。此处的示例

SELECT *
FROM Northwind..Orders 
ORDER BY NEWID()

#2 楼

这是一个古老的问题，但我认为讨论的一个方面缺失-性能。 ORDER BY NewId()是一般答案。当有人喜欢时，他们补充说，您应该将NewID()真正包装在CheckSum()中，以提高性能！
这种方法的问题在于，您仍然可以保证对索引进行完整的扫描，然后再对数据进行完整的排序。如果您处理的数据量很大，则可能很快变得昂贵。查看这个典型的执行计划，并注意该排序如何占用您96％的时间...



为了让您了解这种扩展的方式，我ll给您两个我使用的数据库的示例。

TableA-在2500个数据页中有50,000行。随机查询在42毫秒内产生145次读取。
表B-在114,000个数据页中有120万行。在此表上运行Order By newid()会产生53,700次读取，并花费16秒。

故事的寓意是，如果您有大表（认为数十亿行）或需要频繁运行此查询，则newid()方法会中断下。那么，男孩是怎么做的呢？
遇到TABLESAMPLE（）
在SQL 2005中，创建了一个称为TABLESAMPLE的新功能。我只看过一篇文章讨论它的用途...应该有更多。 MSDN文件在这里。首先是一个示例：

SELECT Top (20) *
FROM Northwind..Orders TABLESAMPLE(20 PERCENT)
ORDER BY NEWID()

表示例背后的想法是大致为您提供所需的子集大小。 SQL为每个数据页编号，然后选择这些页的X％。您返回的实际行数会根据所选页面中存在的内容而有所不同。
那么我该如何使用它呢？选择一个子集大小，使其超过您需要的行数，然后添加Top()。这样做的想法是，您可以使巨大的桌子在昂贵的排序之前显得更小。
我个人一直在使用它来实际上限制我的桌子的大小。因此，在执行top(20)...TABLESAMPLE(20 PERCENT)的百万行表上，查询在1600毫秒内下降到5600次读取。还有一个REPEATABLE()选项，您可以在其中传递“种子”来进行页面选择。这样应该可以稳定地选择样本。
无论如何，只是认为应该将其添加到讨论中。希望对别人有帮助。

#3 楼

Pradeep Adiga的第一个建议ORDER BY NEWID()很好，这也是我过去使用过的建议。

请谨慎使用RAND()-在许多情况下，每个语句仅执行一次，因此ORDER BY RAND()将具有没有效果（因为您从RAND（）中获得的每一行结果相同）。

例如：

SELECT display_name, RAND() FROM tr_person

返回每一个我们的人员表中的名称和一个“随机”数字，每一行都相同。每次运行查询时，数字的确会有所不同，但是每次的每一行都是相同的。

为了证明RAND()子句中使用的ORDER BY也是这种情况，我尝试： />

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

结果仍然按名称排序，表明较早的排序字段（预期为随机的字段）无效，因此推测总是具有相同的值。 />
但是按NEWID()的排序确实可以工作，因为如果不总是重新评估NEWID（），则在一个状态行中插入许多具有唯一标识符的新行时，UUID的目的将被破坏，因此：

SELECT display_name FROM tr_person ORDER BY NEWID()

命令名称“随机”。

其他DBMS

以上内容适用于MSSQL（2005和2008年：最少，如果我还记得2000年的话）。每次在所有DBMS中都要评估返回新UUID的函数，NEWID（）处于MSSQL下，但是值得在文档和/或您自己的测试中对此进行验证。其他任意结果函数（例如RAND（））的行为在DBMS之间更可能有所不同，因此请再次检查文档。

我还看到在某些情况下，由于UUID值的排序被忽略，因为数据库假定该类型没有有意义的排序。如果您发现这种情况，则在订购子句中将UUID显式转换为字符串类型，或在其周围包装其他函数，例如SQL Server中的CHECKSUM()（也可能与此有小的性能差异，因为订购将完成）在32位值而不是128位值上，尽管这样做的好处是否首先超过了每个值运行CHECKSUM()的成本，我就让您进行测试。）

侧面说明

如果想要任意但有点可重复的排序，请按行本身中一些相对不受控制的数据子集进行排序。例如，或者这些将以任意但可重复的顺序返回名称：

SELECT display_name FROM tr_person ORDER BY CHECKSUM(display_name), display_name -- order by the checksum of some of the row's data
SELECT display_name FROM tr_person ORDER BY SUBSTRING(display_name, LEN(display_name)/2, 128) -- order by part of the name field, but not in any an obviously recognisable order)

任意但可重复的顺序在应用程序中通常不有用，尽管在测试中可能有用如果您想以各种顺序测试结果上的某些代码，但又希望能够以相同的方式重复几次每次运行（以获取多次运行中的平均计时结果，或者测试对代码所做的修复，消除以前由特定输入结果集突出显示的问题或效率低下的问题，或者仅用于测试您的代码“稳定”，因为如果按给定顺序发送相同的数据，每次返回的结果相同。）

此技巧还可用于从函数中获得更多任意结果，这些函数不允许在其体内进行非确定性调用，例如NEWID（）。再说一次，这在现实世界中可能不会经常有用，但是如果您希望函数返回随机的东西并且“ random-ish”就足够了，那么它可能会派上用场（但是要小心记住确定规则评估用户定义的函数时（即通常每行仅一次，否则结果可能不符合您的期望/要求）。

性能

正如EBarr指出的那样，上述任何一项都可能存在性能问题。对于多于几行的数据，您几乎肯定会看到输出以正确的顺序读回请求的行数之前已被缓存到tempdb，这意味着即使您正在寻找前十行，您也可能会找到完整的索引扫描（或更糟糕的是，表扫描）与对tempdb的大量写入一起发生。因此，与大多数情况一样，在生产中使用实际数据进行基准测试至关重要。

#4 楼

许多表都有一个相对密集的索引值数字ID列（缺少一些缺失值）。这使我们能够确定现有值的范围，并使用该范围内随机生成的ID值选择行。当要返回的行数相对较小并且ID值的范围被密集填充（因此生成缺失值的机会足够小）时，此方法最有效。

为了说明这一点，以下代码从用户的Stack Overflow表中选择了100个不同的随机用户，该表具有8,123,937行。

第一步是确定ID值的范围，这是通过索引进行的有效操作：

DECLARE 
    @MinID integer,
    @Range integer,
    @Rows bigint = 100;

--- Find the range of values
SELECT
    @MinID = MIN(U.Id),
    @Range = 1 + MAX(U.Id) - MIN(U.Id)
FROM dbo.Users AS U;

该计划从索引的每一端读取一行。

现在我们生成100个不同的随机变量范围内的ID（在用户表中具有匹配的行）并返回这些行：

WITH Random (ID) AS
(
    -- Find @Rows distinct random user IDs that exist
    SELECT DISTINCT TOP (@Rows)
        Random.ID
    FROM dbo.Users AS U
    CROSS APPLY
    (
        -- Random ID
        VALUES (@MinID + (CONVERT(integer, CRYPT_GEN_RANDOM(4)) % @Range))
    ) AS Random (ID)
    WHERE EXISTS
    (
        SELECT 1
        FROM dbo.Users AS U2
            -- Ensure the row continues to exist
            WITH (REPEATABLEREAD)
        WHERE U2.Id = Random.ID
    )
)
SELECT
    U3.Id,
    U3.DisplayName,
    U3.CreationDate
FROM Random AS R
JOIN dbo.Users AS U3
    ON U3.Id = R.ID
-- QO model hint required to get a non-blocking flow distinct
OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

该计划显示了这一点情况需要601个随机数才能找到100个匹配的行。很快：

Table 'Users'. Scan count 1, logical reads 1937, physical reads 2, read-ahead reads 408
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 9 ms.

在Stack Exchange Data Explorer上尝试一下。

#5 楼

正如我在本文中所解释的，为了重新整理SQL结果集，您需要使用特定于数据库的函数调用。


请注意，使用RANDOM函数对大型结果集进行排序可能会变得非常慢，因此请确保对较小的结果集执行此操作。

如果必须随机整理较大的结果集，然后再对其进行限制，则最好使用SQL Server TABLESAMPLE在SQL Server中，而不是在ORDER BY子句中使用随机函数。


因此，假设我们具有以下数据库表：



以及song表中的以下行：

| id | artist                          | title                              |
|----|---------------------------------|------------------------------------|
| 1  | Miyagi & Эндшпиль ft. Рем Дигга | I Got Love                         |
| 2  | HAIM                            | Don't Save Me (Cyril Hahn Remix)   |
| 3  | 2Pac ft. DMX                    | Rise Of A Champion (GalilHD Remix) |
| 4  | Ed Sheeran & Passenger          | No Diggity (Kygo Remix)            |
| 5  | JP Cooper ft. Mali-Koa          | All This Love                      |

在SQL Server上，您需要使用NEWID函数，如以下示例所示：

SELECT
    CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY NEWID()

在SQL Server上运行上述SQL查询时，我们将获得以下结果集：

| song                                              |
|---------------------------------------------------|
| Miyagi & Эндшпиль ft. Рем Дигга - I Got Love      |
| JP Cooper ft. Mali-Koa - All This Love            |
| HAIM - Don't Save Me (Cyril Hahn Remix)           |
| Ed Sheeran & Passenger - No Diggity (Kygo Remix)  |
| 2Pac ft. DMX - Rise Of A Champion (GalilHD Remix) |

请注意，歌曲是以随机顺序列出的，即t感谢ORDER BY子句使用的NEWID函数调用。

#6 楼

这是一个旧线程，但是最近才遇到这个问题。因此，更新对我有用的方法并提供良好的性能。这假定您的表具有IDENTITY或类似的列：

DECLARE @r decimal(8,6) = rand()
SELECT @r

SELECT  TOP 100 *
FROM    TableA
ORDER BY ID % @r