List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 21 2006 7:42pm
Subject:Re: Different result with subquery
View as plain text  
Neroux,

RAND() gives a roughly rectangular distribution, not a normal 
distribution, so samples of fewer than 100 or so values from it are 
likely to show large but statistically _insignificant_ differences.  In 
larger samples, I don't see any tendency of RAND() to produce more 
values near its mean (.5) than near its limits (0,1). You can check this 
yourself by populating a table with RAND() values then running a simple 
crosstab query eg

SELECT
  SUM(IF(r<0.1,1,0)) AS P1,
  SUM(IF(r>=0.1 AND r<0.2,1,0)) AS P2,
  SUM(IF(r>=0.2 AND r<0.3,1,0)) AS P3,
  SUM(IF(r>=0.3 AND r<0.4,1,0)) AS P4,
  SUM(IF(r>=0.4 AND r<0.5,1,0)) AS P5,
  SUM(IF(r>=0.5 AND r<0.6,1,0)) AS P6,
  SUM(IF(r>=0.6 AND r<0.7,1,0)) AS P7,
  SUM(IF(r>=0.7 AND r<0.8,1,0)) AS P8,
  SUM(IF(r>=0.8 AND r<0.9,1,0)) AS P9,
  SUM(IF(r>=0.9 AND r<1.0,1,0)) AS P10
FROM randresults;

PB

-----

neroux wrote:
> Hello,
>
> I am having a table with an integer column which I use for probability
> calculations (the higher a value the more often it is chosen).
>
> Now I am having the following query, which should actually incorporate
> these probabilities, however it seems to prefer values from the middle
> range
>
>   SELECT * FROM table WHERE field>=(SELECT RAND()*MAX(field) FROM
> table) ORDER BY field LIMIT 1
>
> If I split it up it still seems to prefer mid-range values over values
> closer to the higher end, however it doesnt "completely" ignore the
> highest value anymore.
>
>   random_value = SELECT RAND()*MAX(field) FROM table;
>   SELECT * FROM table WHERE field>=random_value ORDER BY field LIMIT 1
>
> Does anyone have an explanation for that or a better solution? Thanks
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006

Thread
Different result with subqueryneroux21 Feb
  • Re: Different result with subqueryPeter Brawley21 Feb
    • Re: Different result with subqueryneroux22 Feb