Re: Random Sample Using SQL?

> I've seen examples of where every Nth row is selected, but that is not
> really random because data is usually sorted or ordered in some way
> before it is loaded into the database.
>
> Another ideas is to use a random number. For example, if you had a
> random number function that returned a random float between 0 and 1 and
> you wanted to sample 10% of a table you could write
>
> SELECT * FROM EMP
> WHERE RANDOM() <= .10
>

How about using a random function of ROWNUM? That would provide you with a unique integer for each row, so you should be able to get a decent pseudo-random sequence out of them.

eg:

SELECT * FROM EMP
WHERE RANDOM_FN( ROWNUM ) <= .10

You only need a seed if you don't want RANDOM_FN to repeat over subsequent queries. For example, in the above setup RANDOM_FN(3) would return the same value on every query, unless we find a way to put in random seed before we start (like using RANDOM_FN( ROWNUM + SEED ) where SEED is changed on each query).

I'm not sure what algorithm to use for RANDOM_FN. Using your package, try doing something like SRAND( N ) followed by return RAND()/2^16.

That comes down to

       function random_fn( N in number) return number
        is
        begin
              tmp := mod( multiplier * N + increment, "2^32" );
              return bitand( tmp/"2^16", "0x7fff" )/"2^16";
        end

(no guarantees on syntax, data types, etc.)

I believe the above will give randomness equivalent to the original package. Received on Mon Jun 30 1997 - 00:00:00 CDT