`
chengyue2007
  • 浏览: 1488446 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

Oracle中随机抽取N条记录

阅读更多

转载来源:http://www.orafans.org/2006/09/oracle-random-record.html

转载来源:http://kb.cnblogs.com/a/1443619/

 

一、Oracle取随机数据
1、Oracle访问数据的基本方法:
1)、全表扫描(Full table Scan):执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Oracle顺序的读分配给该表的每一个数据块,且每个数据块Oracle只读一次.这样全表扫描能够受益于多块读.
 
2)、采样表扫描(sample table scan):扫描返回表中随机采样数据,这种访问方式需要在FROM语句中包含SAMPLE选项或者SAMPLE BLOCK选项. 
 
注:从Oracle8i开始Oracle提供采样表扫描特性 

 
2、使用sample获得随机结果集
2.1、语法: SAMPLE [ BLOCK ](sample_percent)[ SEED (seed_value) ] 
SAMPLE选项:表示按行采样来执行一个全表扫描,Oracle从表中读取特定百分比的记录,并判断是否满足WHERE子句以返回结果。
BLOCK: 表示使用随机块例举而不是随机行例举。
sample_percent:是随机获取一张表中记录的百分比。比如值为10,那就是表中的随机的百分之10的记录。
               值必须大于等于.000001,小于100。
SEED:表示从哪条记录返回,类似于预先设定例举结果,因而每次返回的结果都是固定的。该值必须介于0和4294967295之间。 
 
2.2、举例说明
   创建测试临时表: 

Sql代码 复制代码
  1. SQL>create table zeeno as select * from dba_objects;      
SQL>create table zeeno as select * from dba_objects;    

 
1)、sample(sample_percent):

Sql代码 复制代码
  1. -- 从表zeeno中“全表扫描”随机抽取10%的记录,随机查询5条记录    
  2. SQL>select object_name from zeeno sample(10) where rownum<6;    
  3.   
  4. OBJECT_NAME    
  5. --------------------------------------------------------------------------------    
  6. UET$    
  7. VIEW$    
  8. I_SUPEROBJ2    
  9. TRIGGERCOL$    
  10. I_VIEW1       
  11.                              
  12. SQL&gt; /    
  13.   
  14. OBJECT_NAME    
  15. --------------------------------------------------------------------------------    
  16. I_FILE1    
  17. IND$    
  18. CLU$    
  19. FET$    
  20. I_COBJ#    
  21.     
-- 从表zeeno中“全表扫描”随机抽取10%的记录,随机查询5条记录 
SQL>select object_name from zeeno sample(10) where rownum<6; 

OBJECT_NAME 
-------------------------------------------------------------------------------- 
UET$ 
VIEW$ 
I_SUPEROBJ2 
TRIGGERCOL$ 
I_VIEW1    
                          
SQL&gt; / 

OBJECT_NAME 
-------------------------------------------------------------------------------- 
I_FILE1 
IND$ 
CLU$ 
FET$ 
I_COBJ# 
  

 
2)、sample block(sample_percent) 

Sql代码 复制代码
  1. -- 从表zeeno中“采样表扫描”随机抽取10%的记录,随机查询5条记录    
  2. SQL> select object_name from zeeno sample block(10) where rownum<6;    
  3.   
  4. OBJECT_NAME    
  5. --------------------------------------------------------------------------------    
  6. URIFACTORY    
  7. DBMS_XMLGEN    
  8. DBMS_XMLGEN    
  9. DBMS_XMLSTORE    
  10. DBMS_XMLSTORE    
  11.     
-- 从表zeeno中“采样表扫描”随机抽取10%的记录,随机查询5条记录 
SQL> select object_name from zeeno sample block(10) where rownum<6; 

OBJECT_NAME 
-------------------------------------------------------------------------------- 
URIFACTORY 
DBMS_XMLGEN 
DBMS_XMLGEN 
DBMS_XMLSTORE 
DBMS_XMLSTORE 
  

 

 3)、sample block(sample_percent) seed(seed_value) 

Sql代码 复制代码
  1. -- 使用seed,返回固定的结果集。从表zeeno中“采样表扫描”随机抽取10%的记录,随机查询5条记录。    
  2. SQL> select object_name from zeeno sample(10) seed(10) where rownum<6;    
  3.   
  4. OBJECT_NAME    
  5. --------------------------------------------------------------------------------    
  6. UET$    
  7. I_CON1    
  8. I_FILE2    
  9. FET$    
  10. I_COL1    
  11.   
  12. SQL&gt; select object_name from zeeno sample(10) seed(10) where rownum&lt;6;    
  13.   
  14. OBJECT_NAME    
  15. --------------------------------------------------------------------------------    
  16. UET$    
  17. I_CON1    
  18. I_FILE2    
  19. FET$    
  20. I_COL1    
  21.     
-- 使用seed,返回固定的结果集。从表zeeno中“采样表扫描”随机抽取10%的记录,随机查询5条记录。 
SQL> select object_name from zeeno sample(10) seed(10) where rownum<6; 

OBJECT_NAME 
-------------------------------------------------------------------------------- 
UET$ 
I_CON1 
I_FILE2 
FET$ 
I_COL1 

SQL&gt; select object_name from zeeno sample(10) seed(10) where rownum&lt;6; 

OBJECT_NAME 
-------------------------------------------------------------------------------- 
UET$ 
I_CON1 
I_FILE2 
FET$ 
I_COL1 
  

 

注意以下几点:

1.sample只对单表生效,不能用于表连接和远程表
2.sample会使SQL自动使用CBO
 

 

 

3、使用DBMS_RANDOM包
  DBMS_RANDOM有两种主要的使用方法分别是:DBMS_RANDOM.VALUE()和DBMS_RANDOM.RANDOM

 

3.1、取随机数

Sql代码 复制代码
  1. SQL> select dbms_random.value() from dual;   
  2.     
  3. DBMS_RANDOM.VALUE()   
  4. -------------------   
  5.   0.146123095968043   
  6.     
  7. SQL> select dbms_random.value() from dual;   
  8.     
  9. DBMS_RANDOM.VALUE()   
  10. -------------------   
  11.    0.90175764902345  
SQL> select dbms_random.value() from dual;
 
DBMS_RANDOM.VALUE()
-------------------
  0.146123095968043
 
SQL> select dbms_random.value() from dual;
 
DBMS_RANDOM.VALUE()
-------------------
   0.90175764902345

 

Sql代码 复制代码
  1. SQL> select dbms_random.value(1,10) from dual;   
  2.     
  3. DBMS_RANDOM.VALUE(1,10)   
  4. -----------------------   
  5.        9.86601968210438   
  6.     
  7. SQL> select dbms_random.value(1,10) from dual;   
  8.     
  9. DBMS_RANDOM.VALUE(1,10)   
  10. -----------------------   
  11.        3.43475105499398  
SQL> select dbms_random.value(1,10) from dual;
 
DBMS_RANDOM.VALUE(1,10)
-----------------------
       9.86601968210438
 
SQL> select dbms_random.value(1,10) from dual;
 
DBMS_RANDOM.VALUE(1,10)
-----------------------
       3.43475105499398

 

 3.2、举例说明

  

Sql代码 复制代码
  1. SQL> select * from (select object_name from zeeno order by dbms_random.random) where rownum<6;   
  2.     
  3. OBJECT_NAME   
  4. --------------------------------------------------------------------------------   
  5. /6dd0fe0e_CertificateCertifica   
  6. /cf5224d7_SunJSSE_a4   
  7. KU$_PARSED_ITEMS   
  8. javax/swing/text/IconView   
  9. oracle/xml/jdwp/XSLJDWPString   
  10.     
  11. SQL> select * from (select object_name from zeeno order by dbms_random.random) where rownum<6;   
  12.     
  13. OBJECT_NAME   
  14. --------------------------------------------------------------------------------   
  15. java/io/ObjectOutputStream$1   
  16. sun/security/krb5/KrbAsReq   
  17. /2d52a21c_Last   
  18. SYS_YOID0000006594$   
  19. /308fbfa1_BeanContextServices  
SQL> select * from (select object_name from zeeno order by dbms_random.random) where rownum<6;
 
OBJECT_NAME
--------------------------------------------------------------------------------
/6dd0fe0e_CertificateCertifica
/cf5224d7_SunJSSE_a4
KU$_PARSED_ITEMS
javax/swing/text/IconView
oracle/xml/jdwp/XSLJDWPString
 
SQL> select * from (select object_name from zeeno order by dbms_random.random) where rownum<6;
 
OBJECT_NAME
--------------------------------------------------------------------------------
java/io/ObjectOutputStream$1
sun/security/krb5/KrbAsReq
/2d52a21c_Last
SYS_YOID0000006594$
/308fbfa1_BeanContextServices

 

Sql代码 复制代码
  1. SQL> select * from (select object_name from zeeno order by trunc(dbms_random.value(1,3))) where rownum<6;   
  2.     
  3. OBJECT_NAME   
  4. --------------------------------------------------------------------------------   
  5. ICOL$   
  6. C_COBJ#   
  7. PROXY_ROLE_DATA$   
  8. I_OBJ#   
  9. UET$   
  10.     
  11. SQL> select * from (select object_name from zeeno order by trunc(dbms_random.value(1,3))) where rownum<6;   
  12.     
  13. OBJECT_NAME   
  14. --------------------------------------------------------------------------------   
  15. ICOL$   
  16. UNDO$   
  17. I_PROXY_ROLE_DATA$_1   
  18. I_CDEF2   
  19. UET$  
SQL> select * from (select object_name from zeeno order by trunc(dbms_random.value(1,3))) where rownum<6;
 
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
C_COBJ#
PROXY_ROLE_DATA$
I_OBJ#
UET$
 
SQL> select * from (select object_name from zeeno order by trunc(dbms_random.value(1,3))) where rownum<6;
 
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
UNDO$
I_PROXY_ROLE_DATA$_1
I_CDEF2
UET$

 

Sql代码 复制代码
  1. SQL> select trunc(dbms_random.value(0, 1000)) randomNum from dual; --(0-1000的整数)   
  2.     
  3.  RANDOMNUM   
  4. ----------   
  5.        790   
  6.     
  7. SQL> select dbms_random.value(0, 1000) randomNum from dual; --(0-1000的浮点数)   
  8.     
  9.  RANDOMNUM   
  10. ----------   
  11. 997.876726  
SQL> select trunc(dbms_random.value(0, 1000)) randomNum from dual; --(0-1000的整数)
 
 RANDOMNUM
----------
       790
 
SQL> select dbms_random.value(0, 1000) randomNum from dual; --(0-1000的浮点数)
 
 RANDOMNUM
----------
997.876726

 

4、使用内部函数sys_guid()  

Sql代码 复制代码
  1. SQL>  select * from (select OBJECT_NAME from zeeno order by sys_guid()) where rownum < 6;    
  2.     
  3. OBJECT_NAME   
  4. --------------------------------------------------------------------------------   
  5. /6bedadd5_KeyManagerFactory1   
  6. /ffd795c8_AddCRIF   
  7. TABLE_EXPORT_OBJECTS   
  8. /278cd3a4_CGParselet   
  9. KU$_REFCOL_T   
  10.     
  11. SQL>  select * from (select OBJECT_NAME from zeeno order by sys_guid()) where rownum < 6;   
  12.     
  13. OBJECT_NAME   
  14. --------------------------------------------------------------------------------   
  15. sun/awt/InputMethodSupport   
  16. V_$RESTORE_POINT   
  17. COLORSLIST   
  18. java/util/WeakHashMap$Entry   
  19. DBMSOUTPUT_LINESARRAY  
SQL>  select * from (select OBJECT_NAME from zeeno order by sys_guid()) where rownum < 6; 
 
OBJECT_NAME
--------------------------------------------------------------------------------
/6bedadd5_KeyManagerFactory1
/ffd795c8_AddCRIF
TABLE_EXPORT_OBJECTS
/278cd3a4_CGParselet
KU$_REFCOL_T
 
SQL>  select * from (select OBJECT_NAME from zeeno order by sys_guid()) where rownum < 6;
 
OBJECT_NAME
--------------------------------------------------------------------------------
sun/awt/InputMethodSupport
V_$RESTORE_POINT
COLORSLIST
java/util/WeakHashMap$Entry
DBMSOUTPUT_LINESARRAY

 

注:

       在使用sys_guid() 这种方法时,有时会获取到相同的记录,即和前一次查询的结果集是一样的,查找相关资料,有些说是和操作系统有关,在windows平台下正常,获取到的数据是随机的,而在linux等平台下始终是相同不变的数据集,有些说是因为sys_guid()函数本身的问题,即sys_guid()会在查询上生成一个16字节的全局唯一标识符,这个标识符在绝大部分平台上由一个宿主标识符和进程或进程的线程标识符组成,这就是说,它很可能是随机的,但是并不表示一定是百分之百的这样。
      
       所以,为确保在不同的平台每次读取的数据都是随机的,我们大多采用使用sample函数或者DBMS_RANDOM包获得随机结果集,其中使用sample函数更常用,因为其查询时缩小了查询范围,在查询大表,且要提取数据不是很不多的情况下,会对查询速度上有明显的提高。


 

二、其他数据库随机取出n条记录:

1、SqlServer中随机提取数据库记录

select top n * from 表 order by newid()

--------------------------------------------------------------------------------
select top 10 * from tablename order by NEWID()  
select top 10 * from tablename order by NEWID()

2、mysql中随机提取数据库记录

Select * From 表 order By rand() Limit n

-------------------------------------------------------------------------------
select * from tablename order by rand() limit 10
select * from tablename order by rand() limit 10

3、Access中随机提取数据库记录

Select top n * FROM 表 orDER BY Rnd(id)

-------------------------------------------------------------------------------
SELECT top 10 * FROM tablename ORDER BY Rnd(FId)  
SELECT top 10 * FROM tablename ORDER BY Rnd(FId)
FId:为你当前表的ID字段名

 

分享到:
评论

相关推荐

    SQL随机提取N条记录

    这里我们将深入探讨如何在SQL中实现这一功能,以满足“SQL随机抽取N条记录”的需求。 首先,我们需要理解SQL的基本查询结构。SQL查询通常包括SELECT语句,用于指定要从数据库中检索哪些列;FROM语句,指定数据源;...

    Oracle里抽取随机数的多种方法

    在 Oracle 中抽取随机数是许多应用场景中常见的问题,例如在某个活动中需要随机取出一些符合条件的用户,以颁发获奖通知或其它消息。本文将通过实例讲解如何抽取随机数的多种方法。 首先,我们可以使用 Oracle 自带...

    oracle常用问题解答

    **问题**: 如何在Oracle中随机抽取前N条记录? **解答**: 可以使用`RAND`函数配合`RANK`函数来实现。 - **示例**: 抽取前5条记录: ```sql SELECT * FROM ( SELECT t.*, RANK() OVER (ORDER BY DBMS_RANDOM.VALUE...

    富士通内部培训ORACLE资料

    9. **随机抽取记录**:要随机抽取前N条记录,可以结合`ROWNUM`和`ORDER BY DBMS_RANDOM.VALUE`实现。 10. **指定范围抽取记录**:从N行到M行的记录可通过子查询配合`ROWNUM`实现,如 `(SELECT * FROM table WHERE ...

    Oracle高级sql学习与练习

    25. 随机值查询处理在数据库中生成随机数据的需求,如随机数或随机抽取记录等。 在学习这些高级SQL技能的过程中,建议通过大量的实践练习,结合具体的应用场景来加深理解,从而有效地掌握这些复杂的数据库操作技术...

    基于oracle的在线考试系统的设计

    题目表(QUESTION)应包含题目ID、题目内容、答案、分值等,便于随机抽取试题。最后,成绩表(SCORE)用于存储用户考试成绩,包括用户ID、考试ID、得分等字段。 二、创建视图 视图是数据库中的虚拟表,它根据用户...

    Oracle维护常用SQL语句

    #### 三、随机抽取N条记录 在Oracle中,随机抽取数据可以通过`SYS_GUID()`或`DBMS_RANDOM.VALUE`函数实现。以下是两种方法: 1. 使用`SYS_GUID()`函数: ```sql SELECT * FROM ( SELECT * FROM TABLENAME ORDER...

    oracle chm帮助文件下载

    9. **随机抽取记录**:可以使用`ROWNUM`结合子查询来随机抽取前N条记录。抽取特定范围的记录可以使用`ROWNUM BETWEEN`。 10. **抽取重复记录**:使用`GROUP BY`和`HAVING`结合可以找出表中的重复记录。 11. **自治...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part1

    实例132 随机抽取数组中元素 161 实例133 二维数组的输出 162 实例134 获取数组当前的键名和值 162 实例135 检测数组中是否存在某个值 163 实例136 获取数组中的当前单元 164 实例137 从数组中随机取出元素 165 实例...

    PHP开发实战1200例(第1卷).(清华出版.潘凯华.刘中华).part2

    实例132 随机抽取数组中元素 161 实例133 二维数组的输出 162 实例134 获取数组当前的键名和值 162 实例135 检测数组中是否存在某个值 163 实例136 获取数组中的当前单元 164 实例137 从数组中随机取出元素 165 实例...

    数据分析师面试试题汇总.docx

    - **详细说明**: 常见的缺失值处理方法包括删除含有缺失值的记录、使用平均值/中位数填充等。对于半结构化数据,XML或JSON等格式提供了灵活的数据组织方式,便于使用Python等语言进行处理。 #### 12. 编程语言偏好 ...

Global site tag (gtag.js) - Google Analytics