如何从Oracle数据库随机获取记录？

Question

如何从Oracle数据库随机获取记录？

oracleselectrandom

103

我需要从Oracle数据库中随机选择行。

例如：假设有一个包含100行的表，我如何从这100行中随机返回20条记录。

- Bhadra

10个回答

67

SAMPLE()不能保证返回恰好20行，但对于大型表格而言，可能更加适用（且性能显著优于完整的随机排序查询）：

SELECT *
FROM   table SAMPLE(20);

注意: 这里的20是大约的百分比，不是期望的行数。在这种情况下，由于您有100行数据，要获取大约20行，您需要请求一个20%的样本。

- Jeffrey Kemp

2

样本速度很快，但似乎不太随机。表格顶部/开头的记录往往更受青睐。 - craigrs84

1

如果在查询完成整个表之前停止查询，那么就会发生这种情况。 - Jeffrey Kemp

2

抱歉，我犯了一个错误，您的帖子很好，结果是均匀分布的。只有在将“where rownum <= 20”与sample(20)结合使用时，数据才开始变得不那么随机。 - craigrs84

14

SELECT * FROM table SAMPLE(10) WHERE ROWNUM <= 20;

这种方法更有效率，因为它不需要对表格进行排序。

- grokster

9

在表格中查询前20行并停止将导致非随机结果（较早出现在表格中的行将比后面的行更频繁地返回）。此外，这并不能保证返回20行。 - Jeffrey Kemp

10

SELECT column FROM
( SELECT column, dbms_random.value FROM table ORDER BY 2 )
where rownum <= 20;

- Bishan

8

对于大表格，使用 dbms_random.value 排序的标准方法并不高效，因为你需要扫描整个表格，并且 dbms_random.value 是一个相当慢的函数，它需要上下文切换。针对这种情况，有三种附加方法：

1：使用 sample 子句：

例如：

select *
from s1 sample block(1)
order by dbms_random.value
fetch first 1 rows only

如果ie获得了1%的所有区块，那么对它们进行随机排序并只返回一行。

2: 如果在服从正态分布的列上拥有索引/主键，则可以获取最小值和最大值，在此范围内获取随机值，并获取第一行具有大于或等于该随机生成值的值。

示例：

--big table with 1 mln rows with primary key on ID with normal distribution:
Create table s1(id primary key,padding) as 
   select level, rpad('x',100,'x')
   from dual 
   connect by level<=1e6;

select *
from s1 
where id>=(select 
              dbms_random.value(
                 (select min(id) from s1),
                 (select max(id) from s1) 
              )
           from dual)
order by id
fetch first 1 rows only;

获取随机数据表块，生成行ID，然后通过该行ID从表中获取行。

select * 
from s1
where rowid = (
   select
      DBMS_ROWID.ROWID_CREATE (
         1, 
         objd,
         file#,
         block#,
         1) 
   from    
      (
      select/*+ rule */ file#,block#,objd
      from v$bh b
      where b.objd in (select o.data_object_id from user_objects o where object_name='S1' /* table_name */)
      order by dbms_random.value
      fetch first 1 rows only
      )
);

- Sayan Malakshinov

“sample”示例非常好！ - Si7ius

6

总之，介绍了两种方法

1) using order by DBMS_RANDOM.VALUE clause
2) using sample([%]) function

第一种方法具有“正确性”的优势，这意味着如果结果实际存在，您将永远不会失败获得结果，而在第二种方法中，即使存在满足查询条件的案例，由于采样时信息减少，您也可能没有结果。

第二种方法具有“效率”的优势，这意味着您可以更快地获得结果并给数据库带来轻负载。我收到了 DBA 的警告，因为使用第一种方法的查询会给数据库带来负载。

您可以根据自己的兴趣选择其中一种方法！

- Jinwu Seo

5

为了随机选择20行，我认为你最好先随机选择所有行，然后从中选择前20行。类似这样的代码：

Select *
  from (select *
          from table
         order by dbms_random.value) -- you can also use DBMS_RANDOM.RANDOM
 where rownum < 21;

最好用于小型表格，以避免选择大量数据只为丢弃其中大部分。

- Nishant Sharma

0

-- Q. 如何从表中找到随机的50%记录？

当我们需要按百分比随机数据时，

SELECT * FROM ( SELECT * FROM table_name ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum <= (select count(*) from table_name) * 50/100;

- M J

如果您只想随机获取20条记录，则使用以下查询语句：SELECT * FROM ( SELECT * FROM table_name ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum <=20; - M J

如果您只想随机获取20条记录，请使用以下查询语句：SELECT * FROM ( SELECT * FROM table_name ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum <=20; - M J

0

以下是如何从每个组中随机抽取样本的方法：

SELECT GROUPING_COLUMN, 
       MIN (COLUMN_NAME) KEEP (DENSE_RANK FIRST ORDER BY DBMS_RANDOM.VALUE) 
         AS RANDOM_SAMPLE
FROM TABLE_NAME
GROUP BY GROUPING_COLUMN
ORDER BY GROUPING_COLUMN;

我不确定它有多高效，但如果你有很多类别和子类别，这似乎可以很好地完成工作。

- SMerrill8

-1

从emp表中随机选择一条记录，并按照随机顺序排序，然后返回第一条记录。

- Nikesh Ajalkar

1

这只是一个未格式化的已接受答案的副本，只是表名和行号不同而已。请不要重复回答。 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cagcowboy · Accepted Answer

144

SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.RANDOM)
WHERE  rownum < 21;

- cagcowboy

1

抢先一步了。但是这只会从表中选择前20行并随机排序。 - Nishant Sharma

16

请注意，这是对大型表格的一项非常繁重的操作，因为它会首先为每一行分配一个随机数，然后按照这个值进行排序，最后从中选取一些记录。 - Roeland Van Heddegem

11

@NishantSharma，行已被随机排列，然后再限制数量 - 您的评论不正确。 - Simon MᶜKenzie

8

这种方法非常慢。 - Evan Kroske

1

@JonBetts，我认为这个示例更快，更节省资源：https://dev59.com/Omkw5IYBdhLWcg3wdKfD#9920431 - Evan Kroske

显示剩余2条评论