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
