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
