Skip to main content
The SHUFFLE clause returns the rows of the query result in a uniformly random order. It is used in place of the ORDER BY clause and cannot be combined with it. It returns the same result as ORDER BY rand(), but works in O(n) time instead of O(n * log(n)). SHUFFLE can be used without LIMIT, to randomize the order of all rows, or with LIMIT n, to take a uniformly random sample of n rows (also returned in a random order). With LIMIT n, every stream keeps only n rows, so the memory usage is O(n) regardless of the number of rows in the query result. OFFSET and LIMIT BY can be used as with ORDER BY.
This is an experimental feature. Enable it with the setting allow_experimental_shuffle_query.

​
Implementation details

The random choices are made just in time: no random values are generated or stored for the rows in advance. Without LIMIT, every row is sent to a random one of max_threads streams, every stream is shuffled in parallel with the Fisher-Yates algorithm (in parts that fit in the CPU cache), and the streams are concatenated. With LIMIT n, every stream keeps a uniformly random sample of n rows by reservoir sampling, which skips the rows that cannot get into the sample without looking at them, and the samples are merged: the number of rows taken from every stream is drawn as sampling without replacement from the numbers of rows of the streams. In a distributed query, the remote servers shuffle their data and attach sorted random keys to the rows, and the initiator merges the shuffled streams by the keys, which takes O(1) memory. Without LIMIT, SHUFFLE keeps all rows in memory. Unlike ORDER BY, it does not spill data to disk. ORDER BY rand() and ORDER BY rand64() can be executed the same way as SHUFFLE (the setting query_plan_use_shuffle_for_order_by_rand) when the random value is not used anywhere else in the query and external sorting is disabled. When external sorting is configured, this optimization is disabled even with a finite LIMIT, because the sample may still exceed memory and SHUFFLE cannot spill to disk. ORDER BY rand64() with a finite limit retains sorting to preserve all bits of the random values.

​
Examples

A random sample of three rows:
SHUFFLE is a keyword after the FROM clause. To use shuffle as a table alias, write AS shuffle.