View source for Relational Data - Functions - Random Functions

All functions in this section accept zero or one arguments. The only use of the argument (if provided) is to prevent [[Relational_Data_-_Functions#common-subexpression-elimination|common subexpression elimination]] such that two different executions within a row of the same random function return different random values.

Related content

* Blog: [https://clickhouse.com/blog/generating-random-test-distribution-data-for-clickhouse Generating random data in ClickHouse]

<div class="note">

The random numbers are generated by non-cryptographic algorithms.


</div>
== rand ==

Returns a random UInt32 number with uniform distribution.

Uses a linear congruential generator with an initial state obtained from the system, which means that while it appears random, it’s not truly random and can be predictable if the initial state is known. For scenarios where true randomness is crucial, consider using alternative methods like system-level calls or integrating with external libraries.

<span id="syntax"></span>
=== Syntax ===

<syntaxhighlight lang="sql">rand()</syntaxhighlight>
Alias: <code>rand32</code>

<span id="arguments"></span>
=== Arguments ===

None.

<span id="returned-value"></span>
=== Returned value ===

Returns a number of type UInt32.

<span id="example"></span>
=== Example ===

<syntaxhighlight lang="sql">SELECT rand();</syntaxhighlight>
<pre class="response">1569354847 -- Note: The actual output will be a random number, not the specific number shown in the example</pre>
== rand64 ==

Returns a random UInt64 integer (UInt64) number

<span id="syntax-1"></span>
=== Syntax ===

<syntaxhighlight lang="sql">rand64()</syntaxhighlight>
<span id="arguments-1"></span>
=== Arguments ===

None.

<span id="returned-value-1"></span>
=== Returned value ===

Returns a number UInt64 number with uniform distribution.

Uses a linear congruential generator with an initial state obtained from the system, which means that while it appears random, it’s not truly random and can be predictable if the initial state is known. For scenarios where true randomness is crucial, consider using alternative methods like system-level calls or integrating with external libraries.

<span id="example-1"></span>
=== Example ===

<syntaxhighlight lang="sql">SELECT rand64();</syntaxhighlight>
<pre class="response">15030268859237645412 -- Note: The actual output will be a random number, not the specific number shown in the example.</pre>
<span id="randcanonical"></span>
== randCanonical ==

Returns a random Float64 number.

<span id="syntax-2"></span>
=== Syntax ===

<syntaxhighlight lang="sql">randCanonical()</syntaxhighlight>
<span id="arguments-2"></span>
=== Arguments ===

None.

<span id="returned-value-2"></span>
=== Returned value ===

Returns a Float64 value between 0 (inclusive) and 1 (exclusive).

<span id="example-2"></span>
=== Example ===

<syntaxhighlight lang="sql">SELECT randCanonical();</syntaxhighlight>
<pre class="response">0.3452178901234567 - Note: The actual output will be a random Float64 number between 0 and 1, not the specific number shown in the example.</pre>
<span id="randconstant"></span>
== randConstant ==

Generates a single constant column filled with a random value. Unlike <code>rand</code>, this function ensures the same random value appears in every row of the generated column, making it useful for scenarios requiring a consistent random seed across rows in a single query.

<span id="syntax-3"></span>
=== Syntax ===

<syntaxhighlight lang="sql">randConstant([x]);</syntaxhighlight>
<span id="arguments-3"></span>
=== Arguments ===

* '''[x] (Optional):''' An optional expression that influences the generated random value. Even if provided, the resulting value will still be constant within the same query execution. Different queries using the same expression will likely generate different constant values.

<span id="returned-value-3"></span>
=== Returned value ===

Returns a column of type UInt32 containing the same random value in each row.

<span id="implementation-details"></span>
=== Implementation details ===

The actual output will be different for each query execution, even with the same optional expression. The optional parameter may not significantly change the generated value compared to using <code>randConstant</code> alone.

<span id="examples"></span>
=== Examples ===

<syntaxhighlight lang="sql">SELECT randConstant() AS random_value;</syntaxhighlight>
<pre class="response">| random_value |
|--------------|
| 1234567890   |</pre>
<syntaxhighlight lang="sql">SELECT randConstant(10) AS random_value;</syntaxhighlight>
<pre class="response">| random_value |
|--------------|
| 9876543210   |</pre>
<span id="randuniform"></span>
== randUniform ==

Returns a random Float64 drawn uniformly from interval [<code>min</code>, <code>max</code>].

<span id="syntax-4"></span>
=== Syntax ===

<syntaxhighlight lang="sql">randUniform(min, max)</syntaxhighlight>
<span id="arguments-4"></span>
=== Arguments ===

* <code>min</code> - <code>Float64</code> - left boundary of the range,
* <code>max</code> - <code>Float64</code> - right boundary of the range.

<span id="returned-value-4"></span>
=== Returned value ===

A random number of type [[Relational_Data_-_Data_Types_-_Float|Float64]].

<span id="example-3"></span>
=== Example ===

<syntaxhighlight lang="sql">SELECT randUniform(5.5, 10) FROM numbers(5)</syntaxhighlight>
<pre class="response">┌─randUniform(5.5, 10)─┐
│    8.094978491443102 │
│   7.3181248914450885 │
│    7.177741903868262 │
│    6.483347380953762 │
│    6.122286382885112 │
└──────────────────────┘</pre>
<span id="randnormal"></span>
== randNormal ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/Normal_distribution normal distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randNormal(mean, variance)</syntaxhighlight>
'''Arguments'''

* <code>mean</code> - <code>Float64</code> - mean value of distribution,
* <code>variance</code> - <code>Float64</code> - [https://en.wikipedia.org/wiki/Variance variance] of the distribution.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randNormal(10, 2) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌──randNormal(10, 2)─┐
│ 13.389228911709653 │
│  8.622949707401295 │
│ 10.801887062682981 │
│ 4.5220192605895315 │
│ 10.901239123982567 │
└────────────────────┘</pre>
<span id="randlognormal"></span>
== randLogNormal ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/Log-normal_distribution log-normal distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randLogNormal(mean, variance)</syntaxhighlight>
'''Arguments'''

* <code>mean</code> - <code>Float64</code> - mean value of distribution,
* <code>variance</code> - <code>Float64</code> - [https://en.wikipedia.org/wiki/Variance variance] of the distribution.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randLogNormal(100, 5) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randLogNormal(100, 5)─┐
│  1.295699673937363e48 │
│  9.719869109186684e39 │
│  6.110868203189557e42 │
│  9.912675872925529e39 │
│ 2.3564708490552458e42 │
└───────────────────────┘</pre>
<span id="randbinomial"></span>
== randBinomial ==

Returns a random UInt64 drawn from a [https://en.wikipedia.org/wiki/Binomial_distribution binomial distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randBinomial(experiments, probability)</syntaxhighlight>
'''Arguments'''

* <code>experiments</code> - <code>UInt64</code> - number of experiments,
* <code>probability</code> - <code>Float64</code> - probability of success in each experiment, a value between 0 and 1.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randBinomial(100, .75) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randBinomial(100, 0.75)─┐
│                      74 │
│                      78 │
│                      76 │
│                      77 │
│                      80 │
└─────────────────────────┘</pre>
<span id="randnegativebinomial"></span>
== randNegativeBinomial ==

Returns a random UInt64 drawn from a [https://en.wikipedia.org/wiki/Negative_binomial_distribution negative binomial distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randNegativeBinomial(experiments, probability)</syntaxhighlight>
'''Arguments'''

* <code>experiments</code> - <code>UInt64</code> - number of experiments,
* <code>probability</code> - <code>Float64</code> - probability of failure in each experiment, a value between 0 and 1.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randNegativeBinomial(100, .75) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randNegativeBinomial(100, 0.75)─┐
│                              33 │
│                              32 │
│                              39 │
│                              40 │
│                              50 │
└─────────────────────────────────┘</pre>
<span id="randpoisson"></span>
== randPoisson ==

Returns a random UInt64 drawn from a [https://en.wikipedia.org/wiki/Poisson_distribution Poisson distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randPoisson(n)</syntaxhighlight>
'''Arguments'''

* <code>n</code> - <code>UInt64</code> - mean number of occurrences.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randPoisson(10) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randPoisson(10)─┐
│               8 │
│               8 │
│               7 │
│              10 │
│               6 │
└─────────────────┘</pre>
<span id="randbernoulli"></span>
== randBernoulli ==

Returns a random UInt64 drawn from a [https://en.wikipedia.org/wiki/Bernoulli_distribution Bernoulli distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randBernoulli(probability)</syntaxhighlight>
'''Arguments'''

* <code>probability</code> - <code>Float64</code> - probability of success, a value between 0 and 1.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randBernoulli(.75) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randBernoulli(0.75)─┐
│                   1 │
│                   1 │
│                   0 │
│                   1 │
│                   1 │
└─────────────────────┘</pre>
<span id="randexponential"></span>
== randExponential ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/Exponential_distribution exponential distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randExponential(lambda)</syntaxhighlight>
'''Arguments'''

* <code>lambda</code> - <code>Float64</code> - lambda value.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randExponential(1/10) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randExponential(divide(1, 10))─┐
│              44.71628934340778 │
│              4.211013337903262 │
│             10.809402553207766 │
│              15.63959406553284 │
│             1.8148392319860158 │
└────────────────────────────────┘</pre>
<span id="randchisquared"></span>
== randChiSquared ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/Chi-squared_distribution Chi-square distribution] - a distribution of a sum of the squares of k independent standard normal random variables.

'''Syntax'''

<syntaxhighlight lang="sql">randChiSquared(degree_of_freedom)</syntaxhighlight>
'''Arguments'''

* <code>degree_of_freedom</code> - <code>Float64</code> - degree of freedom.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randChiSquared(10) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─randChiSquared(10)─┐
│ 10.015463656521543 │
│  9.621799919882768 │
│   2.71785015634699 │
│ 11.128188665931908 │
│  4.902063104425469 │
└────────────────────┘</pre>
<span id="randstudentt"></span>
== randStudentT ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/Student%27s_t-distribution Student’s t-distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randStudentT(degree_of_freedom)</syntaxhighlight>
'''Arguments'''

* <code>degree_of_freedom</code> - <code>Float64</code> - degree of freedom.

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randStudentT(10) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌─────randStudentT(10)─┐
│   1.2217309938538725 │
│   1.7941971681200541 │
│ -0.28192176076784664 │
│   0.2508897721303792 │
│  -2.7858432909761186 │
└──────────────────────┘</pre>
<span id="randfisherf"></span>
== randFisherF ==

Returns a random Float64 drawn from a [https://en.wikipedia.org/wiki/F-distribution F-distribution].

'''Syntax'''

<syntaxhighlight lang="sql">randFisherF(d1, d2)</syntaxhighlight>
'''Arguments'''

* <code>d1</code> - <code>Float64</code> - d1 degree of freedom in <code>X = (S1 / d1) / (S2 / d2)</code>,
* <code>d2</code> - <code>Float64</code> - d2 degree of freedom in <code>X = (S1 / d1) / (S2 / d2)</code>,

'''Returned value'''

* Random number. [[Relational_Data_-_Data_Types_-_Float|Float64]].

'''Example'''

<syntaxhighlight lang="sql">SELECT randFisherF(10, 3) FROM numbers(5)</syntaxhighlight>
Result:

<pre class="result">┌──randFisherF(10, 3)─┐
│   7.286287504216609 │
│ 0.26590779413050386 │
│ 0.22207610901168987 │
│  0.7953362728449572 │
│ 0.19278885985221572 │
└─────────────────────┘</pre>
== randomString ==

Generates a string of the specified length filled with random bytes (including zero bytes). Not all characters may be printable.

'''Syntax'''

<syntaxhighlight lang="sql">randomString(length)</syntaxhighlight>
'''Arguments'''

* <code>length</code> — String length in bytes. Positive integer.

'''Returned value'''

* String filled with random bytes. [[Relational_Data_-_Data_Types_-_String|String]].

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT randomString(30) AS str, length(str) AS len FROM numbers(2) FORMAT Vertical;</syntaxhighlight>
Result:

<syntaxhighlight lang="text">Row 1:
──────
str: 3 G  :   pT ?w тi  k aV f6
len: 30

Row 2:
──────
str: 9 ,]    ^   )  ]??  8
len: 30</syntaxhighlight>
<span id="randomfixedstring"></span>
== randomFixedString ==

Generates a binary string of the specified length filled with random bytes (including zero bytes). Not all characters may be printable.

'''Syntax'''

<syntaxhighlight lang="sql">randomFixedString(length);</syntaxhighlight>
'''Arguments'''

* <code>length</code> — String length in bytes. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Returned value(s)'''

* String filled with random bytes. [[Relational_Data_-_Data_Types_-_Fixedstring|FixedString]].

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT randomFixedString(13) as rnd, toTypeName(rnd)</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─rnd──────┬─toTypeName(randomFixedString(13))─┐
│ j▒h㋖HɨZ'▒ │ FixedString(13)                 │
└──────────┴───────────────────────────────────┘</syntaxhighlight>
<span id="randomprintableascii"></span>
== randomPrintableASCII ==

Generates a string with a random set of [https://en.wikipedia.org/wiki/ASCII#Printable_characters ASCII] characters. All characters are printable. If you pass <code>length &lt; 0</code>, the behavior of the function is undefined.

'''Syntax'''

<syntaxhighlight lang="sql">randomPrintableASCII(length)</syntaxhighlight>
'''Arguments'''

* <code>length</code> — String length in bytes. Positive integer.

'''Returned value'''

* String with a random set of [https://en.wikipedia.org/wiki/ASCII#Printable_characters ASCII] printable characters. [[Relational_Data_-_Data_Types_-_String|String]]

'''Example'''

<syntaxhighlight lang="sql">SELECT number, randomPrintableASCII(30) as str, length(str) FROM system.numbers LIMIT 3</syntaxhighlight>
<syntaxhighlight lang="text">┌─number─┬─str────────────────────────────┬─length(randomPrintableASCII(30))─┐
│      0 │ SuiCOSTvC0csfABSw=UcSzp2.`rv8x │                               30 │
│      1 │ 1Ag NlJ &RCN:*>HVPG;PE-nO"SUFD │                               30 │
│      2 │ /"+<"wUTh:=LjJ Vm!c&hI*m#XTfzz │                               30 │
└────────┴────────────────────────────────┴──────────────────────────────────┘</syntaxhighlight>
<span id="randomstringutf8"></span>
== randomStringUTF8 ==

Generates a random string of a specified length. Result string contains valid UTF-8 code points. The value of code points may be outside of the range of assigned Unicode.

'''Syntax'''

<syntaxhighlight lang="sql">randomStringUTF8(length);</syntaxhighlight>
'''Arguments'''

* <code>length</code> — Length of the string in code points. [[Relational_Data_-_Data_Types_-_Int_Uint|UInt64]].

'''Returned value(s)'''

* UTF-8 random string. [[Relational_Data_-_Data_Types_-_String|String]].

'''Example'''

Query:

<syntaxhighlight lang="sql">SELECT randomStringUTF8(13)</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─randomStringUTF8(13)─┐
│ 𘤗𙉝д兠庇󡅴󱱎󦐪􂕌𔊹𓰛   │
└──────────────────────┘</syntaxhighlight>
== fuzzBits ==

'''Syntax'''

Flips the bits of String or FixedString <code>s</code>, each with probability <code>prob</code>.

'''Syntax'''

<syntaxhighlight lang="sql">fuzzBits(s, prob)</syntaxhighlight>
'''Arguments'''

* <code>s</code> - <code>String</code> or <code>FixedString</code>,
* <code>prob</code> - constant <code>Float32/64</code> between 0.0 and 1.0.

'''Returned value'''

Fuzzed string with same type as <code>s</code>.

'''Example'''

<syntaxhighlight lang="sql">SELECT fuzzBits(materialize('abacaba'), 0.1)
FROM numbers(3)</syntaxhighlight>
Result:

<pre class="result">┌─fuzzBits(materialize('abacaba'), 0.1)─┐
│ abaaaja                               │
│ a*cjab+                               │
│ aeca2A                                │
└───────────────────────────────────────┘</pre>
[[Category:Relational_Data]]