I'm basically trying to do,
SELECT
RAND() as foo
FROM tablename
WHERE foo < 0.5
The idea being, I want to generate 1 random number per row and use it both in the SELECT and the WHERE.
I tried doing,
SELECT
r.r as foo
FROM tablename t,
(SELECT RAND() as r) r
WHERE r.r < 0.5
But the RAND() number only gets generated once for the whole table/query.
I know my example is a little contrived. My ultimate goal is, I have an array in one column, say [a,b,c,d], and I want to sample to only 5% of a values but 100% of b, c, and d. I'm using RAND() to sample. The problem is when the array value is [a,b], I want to not sample the item because it has a b, but I want to exclude the a in the SELECT statement via IF(sampling_check_passed, a, 0). But I can't find a way to share the value from RAND() between the SELECT and WHERE clauses while generating a new RAND() per row.
I recognize that I can do the sampling in one query and the non-sampling in another, and do a UNION, but with hundreds of columns, it will be a mess to maintain. I'll end up with,
SELECT
0 as foo,
0 as bar,
... [500 more times]
...
WHERE RAND() < 0.05
...
UNION
SELECT
f as foo,
b as bar,
0 as ..., [500 more times]
...
