3 ms·
Wouldn't you typically solve this kind of problem with rejection sampling? Pick a random ID - if it's already in the result set or deleted, try again. I suppose
by MauranKilom 5y ago
Wouldn't you typically solve this kind of problem with rejection sampling? Pick a random ID - if it's already in the result set or deleted, try again. I suppose this cannot be neatly expressed in SQL though?
- im3w1l 5y agoI think this is a good approach and I see a few ways to make it even better. First you could overfetch to reduce risk of needing another query. Secondly you could rewrite ids occasionally to reduce gaps.
- contravariant 5y agoDepends on how big a percentage of the set you're planning to pick but yeah that would work.
- kwillets 5y agoGenerate a temp table with a sufficient number of random values and join. Add an ORDER BY RANDOM() to those results to subsample. Another approach would be to create a table with a small sample of the main one and refresh it periodically -- each query just needs to do a random reordering on the small sample, and the results will be truly random over time.
- nine_k 5y ago> try again This where you can spend a surprising amount of time unpredictably. Having a pre-shuffled array helps much more.