If you have a large database you often want to sample rows from it. For many uses the sampling should be randomly done. A reasonably fast way to do this completely in SQL is the following:
Where N is the maximum number of rows the table has and k is the number of samples we want
There is an alternative form popular on the internet
where L is the factor deciding how likely it is we pick a particular row.
This form is slower AND has a bias to picking samples from the beginning of the database.
In this method, we go through each row sequentially, deciding if we select that row for the sample, which makes things slow for large L. If we make L small we pick faster, but our sample is biased towards the start of the database.
SELECT * FROM my_table WHERE rowid in (SELECT abs(random()) % N FROM my_table LIMIT k);
Where N is the maximum number of rows the table has and k is the number of samples we want
There is an alternative form popular on the internet
SELECT * FROM my_table WHERE random() % L = 0 LIMIT k;
where L is the factor deciding how likely it is we pick a particular row.
This form is slower AND has a bias to picking samples from the beginning of the database.
In this method, we go through each row sequentially, deciding if we select that row for the sample, which makes things slow for large L. If we make L small we pick faster, but our sample is biased towards the start of the database.
Comments
Post a Comment