首页 / 知识

如何在SQL中请求随机行？

2023-04-12 17:11:00

How to request a random row in SQL?

如何在纯SQL中请求随机行(或尽可能接近真正的随机行)？

参见这篇文章：SQL从数据库表中选择一个随机行。它介绍了在MySQL，PostgreSQL，Microsoft SQL Server，IBM DB2和Oracle中执行此操作的方法(以下内容从该链接复制)：

使用MySQL选择一个随机行：

1
2
3

SELECT COLUMN FROM TABLE
ORDER BY RAND()
LIMIT 1

使用PostgreSQL选择一个随机行：

1
2
3

SELECT COLUMN FROM TABLE
ORDER BY RANDOM()
LIMIT 1

使用Microsoft SQL Server选择一个随机行：

1 2	SELECT TOP 1 COLUMN FROM TABLE ORDER BY NEWID()

使用IBM DB2选择随机行

1
2
3

SELECT COLUMN, RAND() AS IDX
FROM TABLE
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

使用Oracle选择一个随机记录：

1
2
3
4

SELECT COLUMN FROM
( SELECT COLUMN FROM TABLE
ORDER BY dbms_random.value )
WHERE rownum = 1

杰里米(Jeremies)等解决方案：

1	SELECT * FROM TABLE ORDER BY RAND() LIMIT 1

可以，但是它们需要对所有表进行顺序扫描(因为需要计算与每一行关联的随机值-这样才能确定最小的行)，即使对于中等大小的表也可能相当慢。我的建议是使用某种索引数字列(许多表将它们作为主键)，然后编写类似以下内容的内容：

1
2
3

SELECT * FROM TABLE WHERE num_value >= RAND() *
( SELECT MAX (num_value ) FROM TABLE )
ORDER BY num_value LIMIT 1

如果对num_value进行了索引，则无论表大小如何，它都可以以对数时间工作。一个警告：假设num_value在0..MAX(num_value)范围内平均分布。如果您的数据集严重偏离此假设，您将得到歪斜的结果(某些行会比其他行更频繁地出现)。

我不知道这有多有效，但是我以前用过：

1	SELECT TOP 1 * FROM MyTable ORDER BY newid()

由于GUID非常随机，因此排序意味着您将获得随机行。

1	ORDER BY NEWID()

需要7.4 milliseconds

1	WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM TABLE)

需要0.0065 milliseconds！

我绝对会选择后一种方法。

您没有说要使用哪个服务器。在旧版本的SQL Server中，可以使用以下命令：

1	SELECT TOP 1 * FROM MyTable ORDER BY newid()

在SQL Server 2005及更高版本中，可以使用TABLESAMPLE来获取可重复的随机样本：

1
2
3

SELECT FirstName, LastName
FROM Contact
TABLESAMPLE (1 ROWS) ;

对于SQL Server

newid()/ order by可以工作，但是对于大型结果集来说非常昂贵，因为它必须为每一行生成一个id，然后对其进行排序。

从性能的角度来看，TABLESAMPLE()很好，但是您会得到成堆的结果(将返回页面上的所有行)。

为了获得性能更好的真实随机样本，最好的方法是随机过滤出行。我在SQL Server联机丛书文章"使用TABLESAMPLE限制结果集"中找到以下代码示例：

If you really want a random sample of
individual rows, modify your query to
filter out rows randomly, instead of
using TABLESAMPLE. For example, the
following query uses the NEWID
function to return approximately one
percent of the rows of the
Sales.SalesOrderDetail table:

1
2
3
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS FLOAT)
/ CAST (0x7fffffff AS INT)

The SalesOrderID column is included in
the CHECKSUM expression so that
NEWID() evaluates once per row to
achieve sampling on a per-row basis.
The expression CAST(CHECKSUM(NEWID(),
SalesOrderID) & 0x7fffffff AS float /
CAST (0x7fffffff AS int) evaluates to
a random float value between 0 and 1.

当对具有1,000,000行的表运行时，这是我的结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

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

如果可能，请使用存储的语句来避免RND()上的两个索引都无效，并创建记录号字段。

1
2
3

PREPARE RandomRecord FROM"SELECT * FROM table LIMIT ?,1";
SET @n=FLOOR(RAND()*(SELECT COUNT(*) FROM TABLE));
EXECUTE RandomRecord USING @n;

由于不鼓励使用RAND()，因此您可以简单地获得最大ID(= Max)：

1	SELECT MAX(ID) FROM TABLE;

获得1..Max(= My_Generated_Random)之间的随机数

1	My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

然后运行以下SQL：

1	SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

请注意，它将检查ID等于或大于所选值的任何行。
还可以在表中搜寻该行，并获得一个等于或低于My_Generated_Random的ID，然后按如下所示修改查询：

1	SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

最好的方法是为此目的在新列中放入一个随机值，并使用如下代码(伪代码+ SQL)：

1 2	randomNo = random() execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

这是MediaWiki代码采用的解决方案。当然，对于较小的值存在一些偏差，但是他们发现，在没有获取任何行的情况下，将随机值包装为零就足够了。

newid()解决方案可能需要全表扫描，以便可以为每行分配一个新的guid，这将大大降低性能。

rand()解决方案可能根本无法工作(例如，使用MSSQL)，因为该函数只会被评估一次，并且每一行都将被分配相同的"随机"数字。

对于SQL Server 2005和2008，如果我们要随机获取单个行的样本(来自联机丛书)：

1
2
3

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS FLOAT)
/ CAST (0x7fffffff AS INT)

正如@BillKarwin对@??cnu的答案的评论所指出的...

当与LIMIT结合使用时，我发现以随机顺序加入JOIN的效果要好得多(至少在PostgreSQL 9.1中)，而不是直接对实际行进行排序：

只要确保" r"会为复杂查询中与之相连的每个可能的键值生成一个" rand"值，但仍然在可能的情况下限制" r"的行数。

作为整数的CAST对PostgreSQL 9.2特别有用，它对整数和单精度浮点类型进行了特定的排序优化。

在SQL Server中，可以将TABLESAMPLE与NEWID()结合使用以获得很好的随机性，并且仍然具有速度。如果您确实只需要1行或少量行，则此功能特别有用。

1
2
3

SELECT TOP 1 * FROM [TABLE]
TABLESAMPLE (500 ROWS)
ORDER BY NEWID()

最近，但是是通过Google到达的，因此为了后代，我将添加一个替代解决方案。

另一种方法是两次交替使用TOP。我不知道它是否是"纯SQL"，因为它在TOP中使用了变量，但是它在SQL Server 2008中有效。如果想要一个随机单词，这是我对字典单词表使用的示例。

1
2
3
4
5
6
7
8
9
10
11
12

SELECT TOP 1
word
FROM (
SELECT TOP(@idx)
word
FROM
dbo.DictionaryAbridged WITH(NOLOCK)
ORDER BY
word DESC
) AS D
ORDER BY
word ASC

当然，@ idx是目标表上包含1到COUNT(*)在内的一些随机生成的整数。如果您的列已建立索引，您也会从中受益。另一个优点是您可以在函数中使用它，因为不允许使用NEWID()。

最后，以上查询在同一张表上运行的时间是NEWID()类型查询的执行时间的大约1/10。 YYMV。

在MSSQL中(在11.0.5569上测试)使用

1	SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

明显比

1	SELECT TOP 100 * FROM employee ORDER BY NEWID()

您也可以尝试使用new id()函数。

只需编写查询并使用new id()函数使用订单即可。它相当随机。

让MySQL获得随机记录

1
2
3
4
5
6
7
8
9

SELECT name
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1

更多细节http://jan.kneschke.de/projects/mysql/order-by-rand/

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

对于MS SQL：

最小示例：

1
2
3

SELECT top 10 percent *
FROM TABLE_NAME
ORDER BY rand(checksum(*))

标准化执行时间：1.00

NewId()示例：

1
2
3

SELECT top 10 percent *
FROM TABLE_NAME
ORDER BY newid()

标准化执行时间：1.02

NewId()比rand(checksum(*))慢很多，因此您可能不想在大型记录集上使用它。

选择初始种子：

1
2
3
4
5
6

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 */

如果需要给定种子选择同一组，这似乎可行。

这里的大多数解决方案旨在避免排序，但是它们仍然需要对表进行顺序扫描。

还有一种方法可以通过切换到索引扫描来避免顺序扫描。如果您知道随机行的索引值，则几乎可以立即获得结果。问题是-如何猜测索引值。

以下解决方案适用于PostgreSQL 8.4：

1
2
3
4

EXPLAIN analyze SELECT * FROM cms_refs WHERE rec_id IN
(SELECT (random()*(SELECT last_value FROM cms_refs_rec_id_seq))::BIGINT
FROM generate_series(1,10))
LIMIT 1;

我在上面的解决方案中，您猜测范围为0 .. [id的最后值]的10个各种随机索引值。

数字10是任意的-您可以使用100或1000，因为(令人惊奇地)它对响应时间没有太大的影响。

还有一个问题-如果您的ID稀疏，则可能会错过。解决方案是有一个备份计划：)在这种情况下，可以通过random()查询获得纯旧订单。当组合的ID如下所示：

1
2
3
4
5

EXPLAIN analyze SELECT * FROM cms_refs WHERE rec_id IN
(SELECT (random()*(SELECT last_value FROM cms_refs_rec_id_seq))::BIGINT
FROM generate_series(1,10))
UNION ALL (SELECT * FROM cms_refs ORDER BY random() LIMIT 1)
LIMIT 1;

不是union ALL子句。在这种情况下，如果第一部分返回任何数据，则永远不会执行第二部分！

对于火鸟：

1	SELECT FIRST 1 COLUMN FROM TABLE ORDER BY RAND()

对于Oracle，有一个更好的解决方案，而不是使用dbms_random.value，尽管它需要对dbms_random.value进行完全扫描以对行进行排序，并且对于大型表而言它的速度相当慢。

使用此代替：

1
2
3

SELECT *
FROM employee sample(1)
WHERE rownum=1

似乎列出的许多想法仍然使用排序

但是，如果使用临时表，则可以分配一个随机索引(如许多解决方案所建议的那样)，然后获取第一个大于0到1之间任意数字的索引。

例如(对于DB2)：

1
2
3
4

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

注意，因为TableSample实际上不会返回行的随机样本。它指示您的查询查看构成行的8KB页面的随机样本。然后，针对这些页面中包含的数据执行查询。由于在这些页面上数据的分组方式(插入顺序等)，这可能导致数据实际上不是随机样本。

请参阅：http://www.mssqltips.com/tip.asp?tip=1308

这个用于TableSample的MSDN页面包括一个如何生成实际随机数据样本的示例。

http://msdn.microsoft.com/en-us/library/ms189108.aspx

我必须同意CD-MaN：使用" ORDER BY RAND()"对于小型表或仅执行几次SELECT时将很好地工作。

我还使用" num_value> = RAND()* ..."技术，如果我真的想获得随机结果，则在表中有一个特殊的"随机"列，该列每天大约更新一次。一次UPDATE运行将花费一些时间(特别是因为您必须在该列上有一个索引)，但是它比每次运行选择为每行创建随机数要快得多。

来自http://akinas.com/pages/en/blog/mysql_random_row/的一种简单有效的方法

1	SET @i = (SELECT FLOOR(RAND() * COUNT()) FROM TABLE); PREPARE get_stmt FROM 'SELECT FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

使用SQL Server 2012+，您可以使用OFFSET FETCH查询对单个随机行执行此操作

1	SELECT * FROM MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

其中id是一个标识列，n是您想要的行-计算为介于0和表的count()-1之间的随机数(偏移量0毕竟是第一行)

只要您有一个用于ORDER BY子句的索引，它就可以处理表数据中的孔。这对于随机性也非常好-当您努力使自己通过时，却没有其他方法的麻烦。此外，性能还不错，在较小的数据集上，它可以很好地保持性能，尽管我没有尝试对几百万行进行严格的性能测试。

对于SQL Server 2005及更高版本，对于num_value没有连续值的情况，扩展@GreyPanther的答案。对于我们没有均匀分布的数据集并且num_value不是数字而是唯一标识符的情况，这也适用。

1
2
3
4
5
6
7
8
9

WITH CTE_Table (SelRow, num_value)
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM TABLE
)

SELECT * FROM TABLE WHERE num_value = (
SELECT TOP 1 num_value FROM CTE_Table WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)