pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
More fun with random selects
2+ messages / 2 participants
[nested] [flat]

* More fun with random selects
@ 1999-06-30 12:27  Darren Greer <dgreer@websightsolutions.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Darren Greer @ 1999-06-30 12:27 UTC (permalink / raw)
  To: pgsql-sql

Hello all.

I have a question that has to do with getting a random number of rows from a
table.  I am already using oidrand(oid, #), to get a random percentage of rows
from a table.  What I want to do now is get a rand # of rows, where I pick the
number.  

For example, lets say I want a random 125 rows from an existing table.  Is
there an easy way I can do that where I actually am getting a decent sample
from the table?

As always, any help is appreciated.

Darren

--
Darren Greer
System Administrator -
Applications Development Manager
Websight Solutions Inc.
Phone: (414) 790-9327 | Fax: (414) 790-5952



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] More fun with random selects
@ 1999-06-30 14:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Darren Greer <dgreer@websightsolutions.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-06-30 14:28 UTC (permalink / raw)
  To: dgreer@websightsolutions.com; +Cc: pgsql-sql

Darren Greer <dgreer@websightsolutions.com> writes:
> For example, lets say I want a random 125 rows from an existing table.  Is
> there an easy way I can do that where I actually am getting a decent sample
> from the table?

I don't think there's any way to do that with a simple SQL command.

It's possible to draw exactly N items from a set fully randomly if you
are willing to write a little code.  The algorithm looks like this:

	itemsRemaining = size of set (# rows in table);
	itemsStillNeeded = N (# rows wanted, 125 in your example);
	foreach (item in set)
	{
		generate random value X that is 'true' with probability
			itemsStillNeeded / itemsRemaining;
		if (X)
		{
			emit current item as a selected item;
			decrement itemsStillNeeded;
			if (itemsStillNeeded == 0) done;
		}
		decrement itemsRemaining;
	}

The random choice is typically done with a random number generator
that generates output G between say 0 and M; you make X 'true' if
G <= M * itemsStillNeeded / itemsRemaining.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-06-30 14:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-30 12:27 More fun with random selects Darren Greer <dgreer@websightsolutions.com>
1999-06-30 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox