Skip site navigation (1) Skip section navigation (2)

Re: surprising results with random()

From: "Brent Wood" <b(dot)wood(at)niwa(dot)co(dot)nz>
To: <jberkelhammer(at)desc(dot)org>, <pg(at)rvt(dot)dds(dot)nl>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: surprising results with random()
Date: 2009-02-23 23:24:05
Message-ID: 49A3E6E60200007B00019651@gwia1.ham.niwa.co.nz (view raw or flat)
Thread:
Lists: pgsql-general
Or perhaps:

CREATE OR REPLACE VIEW test_view AS
SELECT (random()*3)::int as test_value;

At least in this case, that should give the same result.

in this case 1/3 should be 1,  1/3 = 2 & 1/3=3

in your case 1/3 = 1, 1/2 the remainder (1/2 * 2/3 = 1/3) = 2, remaining 1/3 = 3

Although I'm guessing the original intent is to NOT generate an equal distribution, but I'm not sure what distribution is required.


Cheers,

   Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> ries van Twisk <pg(at)rvt(dot)dds(dot)nl> 02/24/09 12:13 PM >>>

Jessi,

should the function not look like this???

CREATE OR REPLACE VIEW test_view AS
SELECT
	CASE
		WHEN random() < .333333333 THEN '1'
		WHEN random() < .5 THEN '2'
		ELSE '3'
	END AS test_value

FROM client;

On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote:

> Hi,
>
> I have a view in which I want to randomly assign values if certain
> conditions hold. I was getting surprising results. Here is a (very)
> simplified version of the view, which seems to indicate the problem:
>
> CREATE OR REPLACE VIEW test_view AS
> SELECT
> 	CASE
> 		WHEN random() < .3333 THEN '1'
> 		WHEN random() < .3333 THEN '2'
> 		ELSE '3'
> 	END AS test_value
>
> FROM client ;
>
> It seems this should generate a random number between 0 and 1, and set
> test_value to '1' if this first generated number is less than .3333.
> Otherwise, it should generate another random number, and set  
> test_value
> to '2' if this is less than .3333. And if neither of the random  
> numbers
> are less than .3333, it should set test_value to '3'. It seems to me
> that there should be a relative even distribution of the 3 values.
>
> However when I run this, the values are always similar to what is  
> below:
>
> X_test=>  select test_value, count(*) from test_view group by 1  
> order by 1;
> test_value | count
> ------------+-------
> 1          | 23947
> 2          | 16061
> 3          | 32443
>
> Why are there significantly fewer 2s? I understand that random() is  
> not
> truly random, and that the seed affects this value. But it still
> confuses me that, no matter how many times I run this, there are  
> always
> so few 2s. If it is generating an independent random number in the
> second call to random(), then I don't know why there are more so many
> more 1s than 2s.
>
> Thanks!
> -jessi
>
> -- 
> Jessi Berkelhammer
> Downtown Emergency Service Center
> Computer Programming Specialist







-- 
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.

pgsql-general by date

Next:From: Tom LaneDate: 2009-02-24 00:16:02
Subject: Re: [Fwd: Re: surprising results with random()]
Previous:From: John R PierceDate: 2009-02-23 23:21:50
Subject: [Fwd: Re: surprising results with random()]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group