Steve Atkins wrote: > > On Feb 23, 2009, at 2: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. > > Nope, it's nothing to do with random(), it's that your maths is wrong. > > There are 9 possible cases. In 3 of them you return 1. In 2 of them you > return 2. In the remaining 4 cases you return 3. > > If you were to run this 72451 times I'd expect to see > 1: 24150 = 72451 * 3/9 > 2: 16100 = 72451 * 2/9 > 3: 32200 = 72451 * 4/9 > > Which, unsurprisingly, is fairly close to what you get. > > Cheers, > Steve this looks like an attempt to understand the monty hall problem. http://en.wikipedia.org/wiki/Monty_Hall_problem except that there's no goat :( cheers, raf -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
