Selecting a random sample of data using PL/SQL
John L Dunn
Anybody written PL/SQL code to select records from a table randomly? For
instance, I've got a 2,000,000 row table and I want a random sample of
10,000; how do I do it using PL/SQL? Or is it better to punt and use a
3GL/4GL for this purpose?
Any help appreciated! TIA,
John Dunn
The University Physicians
Muhammad A. Malik
With Form 3.0, ORACLE did not had random function. I had to use Pro*C to
do this. I guess they don't have this function even with forms 4.5.
--
Muhammad A. Malik
mam...@cs.wmich.edu
+1 (616) 342-4569
Lee Levy
>John L Dunn wrote:
>>
>> Anybody written PL/SQL code to select records from a table randomly? For
>> instance, I've got a 2,000,000 row table and I want a random sample of
>> 10,000; how do I do it using PL/SQL? Or is it better to punt and use a
>> 3GL/4GL for this purpose?
A re-post:
From: rsel...@oracle.com (Russ Seligman)
Newsgroups: comp.databases.oracle
Subject: Re: PL/SQL Random Number Generator
Date: 01 Nov 1995 19:37:48 GMT
Organization: Oracle Corporation, Redwood Shores, CA
I saw that someone else had posted the PL/SQL code that they had typed in.
Here's the code as I originally created it (and presumably as Oracle support
is distributing it)...
--Russ
-----Begin Package Spec-----
package random is
/* Linear congruential random number generator */
/* Returns random integer between [0, r-1] */
function rndint(r in number) return number;
/* Returns random real between [0, 1] */
function rndflt return number;
end; /* package random */
-----End Package Spec-----
-----Begin Package Body-----
package body random is
/* Linear congruential random number generator */
m constant number:=100000000; /* initial conditions */
m1 constant number:=10000; /* (for best results) */
b constant number:=31415821; /* */
a number; /* seed */
the_date date; /* */
days number; /* for generating initial seed */
secs number; /* */
/*-------------------------- mult ---------------------------*/
/* Private utility function */
function mult(p in number, q in number) return number is
p1 number;
p0 number;
q1 number;
q0 number;
begin
p1:=trunc(p/m1);
p0:=mod(p,m1);
q1:=trunc(q/m1);
q0:=mod(q,m1);
return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m));
end; /* mult */
/*-------------------------- rndint --------------------------*/
/* Returns random integer between [0, r-1] */
function rndint (r in number) return number is
begin
/* generate a random number and set it to be the new seed */
a:=mod(mult(a,b)+1,m);
/* convert it to integer between [0, r-1] and return it */
return(trunc((trunc(a/m1)*r)/m1));
end; /* rndint */
/*-------------------------- rndflt --------------------------*/
/* Returns random real between [0, 1] */
function rndflt return number is
begin
/* generate a random number and set it to be the new seed */
a:=mod(mult(a,b)+1,m);
/* return it */
return(a/m);
end; /* rndflt */
begin /* package body random */
/* Generate an initial seed "a" based on system date */
/* (Must be connected to database.) */
the_date:=sysdate;
days:=to_number(to_char(the_date, 'J'));
secs:=to_number(to_char(the_date, 'SSSSS'));
a:=days*24*3600+secs;
end; /* package body random */
-----End Package Body-----
---------------------------------------------------
Lee Levy, ISSD Technical Dream Team, Del Code (34)
BHP Information Technology, ACN 006 476 213
PO Box 261, Warrawong, NSW 2502, Australia
PH: +61 42 75-5485 Fax: -5500 Tie: 8855-
Internet : levy....@bhp.com.au
---------------------------------------------------
Opinions expressed are mostly my own, so give me some credit.
Beverley Williams
asdasdsad
Beverley Williams
dfsdfsd
Beverley Williams
sefsdfsdfsdf
Robert Walters
Beverley Williams <b...@starfleet.bt.co.uk> wrote:
>
> sefsdfsdfsdf
>
I hate it when the keyboard goes off like that!!!
:)
Mike Ziemann East Su
but when I have wanted to get a sample of data I just decided to take 1
in every X samples, so if you want 10000 from 2,000,000 take 1 in every
200, the easiest way to do this is to use the mod function eg where
mod(N,200)=0 where N is a counter variable.
Mike Ziemann