Easy random quotes database table generator#

WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) MSSQL version: SELECT * FROM Sales.SalesOrderDetail Non-MSSQL version - I did not test this SELECT * FROM Sales.SalesOrderDetail I tested this method to be much faster than ORDER BY RAND(), hence it runs in O(n) time, and does so impressively fast. If you were really concerned about efficiency, you might consider doing the random key generation in some sort of procedural language and inserting the results in the database, as almost anything other than SQL would probably be better at the sort of looping and random number generation required. Join table t ON r.RandomKey = t.UniqueKey If not exists (select * from RandomKeys where RandomKey = NextAttempt) with luck (and m much less than n), this won't be necessary as long as we don't have enough, keep generating new keys, Insert RandomKeys select distinct RandomKey from RandomKeysAttempt Insert RandomKeysAttempt select rand()*n + 1 I don't have mysql or anything in front of me right now, so in slightly pseudocode this would look something like:Д¬reate table RandomKeysAttempt (RandomKey int) Given the three assumptions, the basic idea is to generate m unique random numbers between 1 and n, and then select the rows with those keys from the table.

Easy random quotes database table generator#

With a nice random number generator that guaranteed no duplicates when generating m numbers in a row, an O(m) solution would be possible. Assumption 3 would be an easy nice additional property to work with. If we can ADDITIONALLY assume something else nice about the table, we can do the task in O(m log m). With only assumptions 1 and 2 I think this can be done in O(n), though you'll need to write a whole index to the table to match assumption 3, so it's not necesarily a fast O(n). The unique primary key is an integer that ranges from 1 to n with no gaps The number of random rows you want to select (m) is much smaller than the number of rows in the table (n) There is a unique, indexed, primary key in the table But for the sort of numbers you posted, m is bigger than lg n anyway. Though actually with a good optimizer or a slightly different technique the query you list may be a bit better, O(m*n) where m is the number of random rows desired, as it wouldn't necesssarily have to sort the whole large array, it could just search for the smallest m times. I think with absolutely no assumptions about the table that your O(n lg n) solution is the best. There's a very interesting discussion of this type of issue here: While this makes some sacrifices, it allows me to sample the database down using an index scan, until it's small enough to ORDER BY RAND() again.

(My actual implementation involves more work to make sure I don't undersample, and to manually wrap rand_high around, but the basic idea is 'randomly cut your N down to a few thousand.') WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s To take a 1000-item sample of a table, I count the rows and sample the result down to, on average, 10,000 rows with the the frozen_rand column: SELECT COUNT(*) FROM table - Use this to determine rand_low and rand_high (If your data set isn't very update-heavy, you may need to find another way to keep this column fresh.) Save the result of RAND() to an indexed column on every insert/update.Sample the rows to 2-5x my desired sample size, to cheaply ORDER BY RAND().I ran into this problem again with a bigger table, and ended up using a version of solution, with two tweaks: Note: As Andrew Mao points out in the comments, If you're using this approach on SQL Server, you should use the T-SQL function NEWID(), because RAND() may return the same value for all rows. Is there a way to do this faster than O(n)? The 'obvious' answer is to: SELECT * FROM table ORDER BY RAND() LIMIT 10000ДЇor large tables, that's too slow: it calls RAND() for every row (which already puts it at O(n)), and sorts them, making it O(n lg n) at best.

How do I take an efficient simple random sample in SQL? The database in question is running MySQL my table is at least 200,000 rows, and I want a simple random sample of about 10,000.