如何在SQL中请求随机行？-Java 学习之路

456

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

28 回答

59

您也可以尝试使用 new id() 功能 .

只需编写一个查询并按 new id() 函数使用顺序 . 这很随意 .

回复于 2024-04-24T09:21:26+08:00

为MySQL获取随机记录

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

回复于 2024-04-24T09:21:26+08:00

1

我不得不同意CD-MaN：使用“ORDER BY RAND（）”可以很好地用于小型表格，或者当您只进行几次SELECT时 .

我也使用“num_value> = RAND（）* ...”技术，如果我真的想要随机结果，我在表中有一个特殊的“随机”列，我每天更新一次 . 单个UPDATE运行将花费一些时间（特别是因为您必须在该列上有索引），但它比每次运行select时为每行创建随机数要快得多 .

回复于 2024-04-24T09:21:26+08:00
13
尚未在答案中看到这种变化 . 在给定初始种子的情况下，我需要一个额外的约束来每次选择相同的行集 .

对于MS 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 */
```
如果您需要选择给定种子的相同集合，这似乎有效 .
回复于 2024-04-24T09:21:26+08:00
1
看这篇文章：SQL to Select a random row from a database table . 它通过在MySQL，PostgreSQL，Microsoft SQL Server，IBM DB2和Oracle中执行此操作的方法（以下内容从该链接复制）：

用MySQL选择一个随机行：
```
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
```
使用PostgreSQL选择一个随机行：
```
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
```
使用Microsoft SQL Server选择随机行：
```
SELECT TOP 1 column FROM table
ORDER BY NEWID()
```
使用IBM DB2选择随机行
```
SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
```
使用Oracle选择随机记录：
```
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
```
回复于 2024-04-24T09:21:26+08:00
0
Jeremies等解决方案：
```
SELECT * FROM table ORDER BY RAND() LIMIT 1
```
但是他们需要对所有表进行顺序扫描（因为需要计算与每行相关的随机值 - 以便可以确定最小的一行），这对于中等大小的表来说可能非常慢 . 我的建议是使用某种索引数字列（许多表将这些作为主键），然后编写如下内容：
```
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) 范围内均匀分布 . 如果您的数据集强烈偏离此假设，您将得到偏差的结果（某些行将比其他行更频繁地出现） .
回复于 2024-04-24T09:21:26+08:00
2
我不知道这有多高效，但我以前用过它：
```
SELECT TOP 1 * FROM MyTable ORDER BY newid()
```
因为GUID非常随机，所以排序意味着你得到一个随机行 .
回复于 2024-04-24T09:21:26+08:00
170
```
ORDER BY NEWID()
```
需要 7.4 milliseconds
```
WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)
```
需要 0.0065 milliseconds ！

我一定会选择后一种方法 .
回复于 2024-04-24T09:21:26+08:00
0
你没有说你正在使用哪个服务器 . 在旧版本的SQL Server中，您可以使用：
```
select top 1 * from mytable order by newid()
```
在SQL Server 2005及更高版本中，您可以使用 TABLESAMPLE 获取可重复的随机样本：
```
SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;
```
回复于 2024-04-24T09:21:26+08:00

在MSSQL（在11.0.5569测试）中使用

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

明显快于

SELECT TOP 100 * FROM employee ORDER BY NEWID()

回复于 2024-04-24T09:21:26+08:00

1
在SQL Server中，您可以将TABLESAMPLE与NEWID（）结合使用，以获得非常好的随机性并且仍然具有速度 . 如果您真的只需要1行或少量行，这将特别有用 .
```
SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()
```
回复于 2024-04-24T09:21:26+08:00

654

SELECT * FROM table ORDER BY RAND() LIMIT 1

回复于 2024-04-24T09:21:26+08:00

0
对于SQL Server

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列包含在CHECKSUM表达式中，以便NEWID（）每行计算一次以实现每行的采样 . 表达式CAST（CHECKSUM（NEWID（），SalesOrderID）和0x7fffffff AS float / CAST（0x7fffffff AS int）计算为0到1之间的随机浮点值 .

当针对包含1,000,000行的表运行时，以下是我的结果：
```
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应该是最后的手段 .
回复于 2024-04-24T09:21:26+08:00

如果可能，使用存储语句来避免RND（）上的两个索引效率低下并创建记录号字段 .

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

回复于 2024-04-24T09:21:26+08:00

3
最好的方法是将一个随机值放在一个新列中，仅用于此目的，并使用类似的东西（pseude code SQL）：
```
randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")
```
这是MediaWiki代码使用的解决方案 . 当然，对较小的值存在一些偏差，但是他们发现在没有获取行时将随机值包装为零就足够了 .

newid（）解决方案可能需要进行全表扫描，以便为每一行分配一个新的guid，这样性能将大打折扣 .

rand（）解决方案可能根本不起作用（即使用MSSQL），因为该函数将仅被评估一次，并且每行将被分配相同的"random"数字 .
回复于 2024-04-24T09:21:26+08:00
0
对于SQL Server 2005和2008，如果我们想要一个单独行的随机样本（来自Books Online）：
```
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)
```
回复于 2024-04-24T09:21:26+08:00
3
Insted of using RAND(), as it is not encouraged，您可能只是获得最大ID（=最大）：
```
SELECT MAX(ID) FROM TABLE;
```
得到1..Max（= My_Generated_Random）之间的随机
```
My_Generated_Random = rand_in_your_programming_lang_function(1..Max);
```
然后运行此SQL：
```
SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1
```
请注意，它将检查Ids等于或高于所选值的任何行 . 也有可能寻找下来的行在表中，获取与My_Generated_Random相等或更低的ID，然后像下面这样修改查询：
```
SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1
```
回复于 2024-04-24T09:21:26+08:00
-1
正如@ BillKarwin对@ cnu答案的评论所指出的......

当与LIMIT结合使用时，我发现它使用随机排序对JOIN执行得更好（至少使用PostgreSQL 9.1），而不是直接对实际行进行排序：例如 ``
```
SELECT * FROM tbl_post AS t
JOIN ...
JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand
       FROM tbl_post
       WHERE create_time >= 1349928000
     ) r ON r.id = t.id
WHERE create_time >= 1349928000 AND ...
ORDER BY r.rand
LIMIT 100
```
只需确保'r'为复杂查询中的每个可能的键值生成一个'rand'值，该值与之连接，但仍然限制'r'的行数 .

CAST as Integer对PostgreSQL 9.2特别有用，PostgreSQL 9.2对整数和单精度浮点类型进行了特定的排序优化 .
回复于 2024-04-24T09:21:26+08:00
10
这里的大多数解决方案旨在避免排序，但他们仍然需要在表上进行顺序扫描 .

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

以下解决方案适用于PostgreSQL 8.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的10个各种随机索引值.. [id的最后一个值] .

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

还有一个问题 - 如果你有稀疏的ids you might miss . 解决方案是 have a backup plan :)在这种情况下，通过random（）查询的纯旧订单 . 当组合ID看起来像这样：
```
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 条款 . 在这种情况下，如果第一部分返回任何数据，则第二部分永远不会被执行！
回复于 2024-04-24T09:21:26+08:00
24
最近，但通过谷歌来到这里，所以为了后代，我将添加一个替代解决方案 .

另一种方法是使用TOP两次，交替订单 . 我不知道它是否是“纯SQL”，因为它在TOP中使用了一个变量，但是它在SQL Server 2008中有效 . 这是我用一个字典单词表的例子，如果我想要一个随机单词 .
```
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 .
回复于 2024-04-24T09:21:26+08:00
4

要小心，因为TableSample实际上不会返回随机的行样本 . 它会指示您的查询查看构成行的8KB页面的随机样本 . 然后，针对这些页面中包含的数据执行查询 . 由于数据如何在这些页面上分组（插入顺序等），这可能导致数据实际上不是随机样本 .

见：http://www.mssqltips.com/tip.asp?tip=1308

TableSample的此MSDN页面包含如何生成的示例实际随机数据样本 .

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

回复于 2024-04-24T09:21:26+08:00
1
似乎列出的许多想法仍然使用排序

但是，如果使用临时表，则可以分配随机索引（与许多已建议的解决方案一样），然后抓取第一个大于0到1之间的任意数字的索引 .

例如（对于DB2）：
```
WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY
```
回复于 2024-04-24T09:21:26+08:00

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

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

回复于 2024-04-24T09:21:26+08:00

3
对于Oracle而言，有更好的解决方案，而不是使用dbms_random.value，而它需要完全扫描以按dbms_random.value对行进行排序，并且对于大型表来说它非常慢 .

请改用：
```
SELECT *
FROM employee sample(1)
WHERE rownum=1
```
回复于 2024-04-24T09:21:26+08:00
0
对于Firebird：
```
Select FIRST 1 column from table ORDER BY RAND()
```
回复于 2024-04-24T09:21:26+08:00
1
使用SQL Server 2012，您可以使用OFFSET FETCH query为单个随机行执行此操作
```
select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY
```
其中id是一个标识列，n是你想要的行 - 计算为0和count（）之间的随机数 - 表中的1（偏移0是毕竟的第一行）

这适用于表数据中的漏洞，只要您有一个用于ORDER BY子句的索引即可 . 它也非常适合随机性 - 因为你自己努力传递但其他方法中的小问题不存在 . 此外，性能相当不错，在较小的数据集上它表现良好，但我没有尝试对数百万行进行严格的性能测试 .
回复于 2024-04-24T09:21:26+08:00

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

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)
)

回复于 2024-04-24T09:21:26+08:00

0
来自sql的随机函数可以提供帮助 . 此外，如果您想限制为一行，只需将其添加到最后 .
```
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
```
回复于 2024-04-24T09:21:26+08:00

如何在SQL中请求随机行？

28 回答

相关问题