3 ms·
Wow, OmniRef is cool. StackOverflow-style Q & A embedded right into source code. Reservoir sampling: I'm not sure that applying this algorithm to database samp
by thaumaturgy 12y ago
Wow, OmniRef is cool. StackOverflow-style Q & A embedded right into source code.
Reservoir sampling: I'm not sure that applying this algorithm to database sampling is the right thing to do. By its nature, the algorithm has to touch every single row in a database, and it does that because it's designed for data streams where you don't know in advance the size of the stream -- which isn't the case with database tables.
Assuming a uniform distribution in your database, you could instead do something like:
SELECT *
FROM (
SELECT
@row := @row +1 AS rownum, [column, column, columns...]
FROM (
SELECT @row :=0) r, [table name]
) ranked
WHERE rownum % [n] = 1
to get every nth record of your table, calculating n ahead of time for n ~= (table size)/(sample size). That should be a little bit faster and in most cases still provide acceptable results.
- screwedup 12y agoClever, but that gives you every nth row - you might want a truly uniform sample.