Re: Random Sample in SQL ???

From: Alf-Kenneth Aabel <alf-ka_at_online.no>
Date: 1996/10/15
Message-ID: <53vmhe$spu@o.online.no>#1/1

aje_at_lys.vnet.net (Andrew Joseph Esposito) wrote:

>Here is a brain twister:
 

>I select a subset of the data from our database, let's say, everyone
>who has been more than 90 days late on payment since 1992. I'd get
>somewhere close to a million rows returned. How would I RANDOMLY
>select only 10% of those rows? Can it be done in one step without
>creating additional tables? PL/SQL is all I really have to work
>with.

Since you have access to PL/SQL, you could use this package I wrote last time I needed a random number generator. I strongly recommend that you use the default values for m, a and c, since these numbers will give you a pretty uniform distribution (I don't think it will give you a full period). Use InitInt and RandInt to get integer values you can use as a where-condition on the rownum in you table. You might want to optimize this code, this is just the simplest implementation, so it's pretty slow.

BTW, does anybody know if Oracle has a random number generator?

Here's the package:

• Linear Congruent Random Number Generator CREATE OR REPLACE PACKAGE Random AS
  • all seed and other parameter used for generating random number
  • are stored in arrays so we can have several generators at once. TYPE NumArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

	mA	NumArray;
	aA	NumArray;
	cA	NumArray;
	seedA NumArray;

	p1A	NumArray;
	p2A	NumArray;

	
	-- InitInt
	-- After calling InitInt, RandInt will return
	-- integers in the range p1+1, p1+2, ..., p_p2
	PROCEDURE InitInt(	genNo	IN NUMBER,
						seed	IN NUMBER,
						p1		IN NUMBER DEFAULT 1,
						p2		IN NUMBER DEFAULT 10,
						m		IN NUMBER DEFAULT 2147483647,
						a		IN NUMBER DEFAULT 630360016,
						c		IN NUMBER DEFAULT 0);

	PROCEDURE InitSeed(	genNo	IN NUMBER,
						seed IN NUMBER,
						m IN	NUMBER DEFAULT 2147483647,
						a IN	NUMBER DEFAULT 630360016,
						c IN	NUMBER DEFAULT 0);

	FUNCTION Rand(genNo IN NUMBER) RETURN NUMBER;

	FUNCTION RandInt(genNo IN NUMBER) RETURN NUMBER;

END Random;
/

CREATE OR REPLACE PACKAGE BODY Random AS

	PROCEDURE InitInt(	genNo	IN NUMBER,
						seed	IN NUMBER, 
						p1		IN NUMBER DEFAULT 1,
						p2		IN NUMBER DEFAULT 10,
						m		IN NUMBER DEFAULT 2147483647,
						a		IN NUMBER DEFAULT 630360016,
						c		IN NUMBER DEFAULT 0) IS
	BEGIN
		InitSeed(genNo, seed, m, a, c);
		p1A(genNo) := p1;
		p2A(genNo) := p2;
	END InitInt;

	PROCEDURE InitSeed(	genNo	IN NUMBER,
						seed	IN NUMBER,
						m IN	NUMBER DEFAULT 2147483647,
						a IN	NUMBER DEFAULT 630360016,
						c IN	NUMBER DEFAULT 0) IS
	BEGIN
		mA(genNo) := m;
		aA(genNo) := a;
		cA(genNo) := c;
		seedA(genNo) := seed;
	END InitSeed;


	FUNCTION Rand(genNo IN NUMBER) RETURN NUMBER IS
	BEGIN
		seedA(genNo) := MOD(aA(genNo) * seedA(genNo) + cA(genNo), mA(genNo));
		RETURN seedA(genNo) / mA(genNo);
	END Rand;

	FUNCTION RandInt(genNo IN NUMBER) RETURN NUMBER IS
	BEGIN
		RETURN TRUNC(p1A(genNo) + 1 + ((p2A(genNo) - p1A(genNo)) *
Rand(genNo)));
	END RandInt;

END Random;
/

/****************************************************

-- Testing the LCG

create table randomtst (random number null);

DECLARE
        v NUMBER;
BEGIN

	Random.InitInt(1,1,1,10000);
	FOR i IN 1..10000 LOOP
		v := Random.RandInt(1);
		INSERT INTO randomtst VALUES (v);
	END LOOP;

END;
/

select random, count(*) from randomtst
group by random
having count(*) > 5;

*/

Hope this helps.

alf

---

Name:		Alf-Kenneth Aabel
Title:		Senior Software Engineer
Company name:	PRIDE AS
Address:	Wdm. Thranesgt. 77
		0175 OSLO

Ph work:	+47 22 20 12 50
Fax:		+47 22 20 70 39
Ph private:	+47 22 35 64 17
Pager:		+47 967 16998
Email:		alf-ka_at_online.no
_____________________________________________________________

>Thanks for reading this.
>--
>Andrew Joseph Esposito
>aje_at_vnet.net
>http://www.vnet.net/users/aje
Received on Tue Oct 15 1996 - 00:00:00 CDT