Search Postgresql Archives

Re: surprising results with random()

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux