Relational Data - Functions - Random Functions: Difference between revisions
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:30 GMT-0400 (Eastern Daylight Time)) |
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 15:05:58 GMT-0400 (Eastern Daylight Time)) |
||
Line 1: | Line 1: | ||
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> | <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 === | === Arguments === | ||
Line 17: | Line 137: | ||
* <code>max</code> - <code>Float64</code> - right boundary of the range. | * <code>max</code> - <code>Float64</code> - right boundary of the range. | ||
<span id="returned-value"></span> | <span id="returned-value-4"></span> | ||
=== Returned value === | === Returned value === | ||
A random number of type [[Relational_Data_-_Data_Types_-_Float|Float64]]. | A random number of type [[Relational_Data_-_Data_Types_-_Float|Float64]]. | ||
<span id="example"></span> | <span id="example-3"></span> | ||
=== Example === | === Example === | ||
Latest revision as of 19:05, 28 August 2024
All functions in this section accept zero or one arguments. The only use of the argument (if provided) is to prevent common subexpression elimination such that two different executions within a row of the same random function return different random values.
Related content
The random numbers are generated by non-cryptographic algorithms.
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.
Syntax
rand()
Alias: rand32
Arguments
None.
Returned value
Returns a number of type UInt32.
Example
SELECT rand();
1569354847 -- Note: The actual output will be a random number, not the specific number shown in the example
rand64
Returns a random UInt64 integer (UInt64) number
Syntax
rand64()
Arguments
None.
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.
Example
SELECT rand64();
15030268859237645412 -- Note: The actual output will be a random number, not the specific number shown in the example.
randCanonical
Returns a random Float64 number.
Syntax
randCanonical()
Arguments
None.
Returned value
Returns a Float64 value between 0 (inclusive) and 1 (exclusive).
Example
SELECT randCanonical();
0.3452178901234567 - Note: The actual output will be a random Float64 number between 0 and 1, not the specific number shown in the example.
randConstant
Generates a single constant column filled with a random value. Unlike rand
, 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.
Syntax
randConstant([x]);
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.
Returned value
Returns a column of type UInt32 containing the same random value in each row.
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 randConstant
alone.
Examples
SELECT randConstant() AS random_value;
| random_value | |--------------| | 1234567890 |
SELECT randConstant(10) AS random_value;
| random_value | |--------------| | 9876543210 |
randUniform
Returns a random Float64 drawn uniformly from interval [min
, max
].
Syntax
randUniform(min, max)
Arguments
min
-Float64
- left boundary of the range,max
-Float64
- right boundary of the range.
Returned value
A random number of type Float64.
Example
SELECT randUniform(5.5, 10) FROM numbers(5)
┌─randUniform(5.5, 10)─┐ │ 8.094978491443102 │ │ 7.3181248914450885 │ │ 7.177741903868262 │ │ 6.483347380953762 │ │ 6.122286382885112 │ └──────────────────────┘
randNormal
Returns a random Float64 drawn from a normal distribution.
Syntax
randNormal(mean, variance)
Arguments
mean
-Float64
- mean value of distribution,variance
-Float64
- variance of the distribution.
Returned value
- Random number. Float64.
Example
SELECT randNormal(10, 2) FROM numbers(5)
Result:
┌──randNormal(10, 2)─┐ │ 13.389228911709653 │ │ 8.622949707401295 │ │ 10.801887062682981 │ │ 4.5220192605895315 │ │ 10.901239123982567 │ └────────────────────┘
randLogNormal
Returns a random Float64 drawn from a log-normal distribution.
Syntax
randLogNormal(mean, variance)
Arguments
mean
-Float64
- mean value of distribution,variance
-Float64
- variance of the distribution.
Returned value
- Random number. Float64.
Example
SELECT randLogNormal(100, 5) FROM numbers(5)
Result:
┌─randLogNormal(100, 5)─┐ │ 1.295699673937363e48 │ │ 9.719869109186684e39 │ │ 6.110868203189557e42 │ │ 9.912675872925529e39 │ │ 2.3564708490552458e42 │ └───────────────────────┘
randBinomial
Returns a random UInt64 drawn from a binomial distribution.
Syntax
randBinomial(experiments, probability)
Arguments
experiments
-UInt64
- number of experiments,probability
-Float64
- probability of success in each experiment, a value between 0 and 1.
Returned value
- Random number. UInt64.
Example
SELECT randBinomial(100, .75) FROM numbers(5)
Result:
┌─randBinomial(100, 0.75)─┐ │ 74 │ │ 78 │ │ 76 │ │ 77 │ │ 80 │ └─────────────────────────┘
randNegativeBinomial
Returns a random UInt64 drawn from a negative binomial distribution.
Syntax
randNegativeBinomial(experiments, probability)
Arguments
experiments
-UInt64
- number of experiments,probability
-Float64
- probability of failure in each experiment, a value between 0 and 1.
Returned value
- Random number. UInt64.
Example
SELECT randNegativeBinomial(100, .75) FROM numbers(5)
Result:
┌─randNegativeBinomial(100, 0.75)─┐ │ 33 │ │ 32 │ │ 39 │ │ 40 │ │ 50 │ └─────────────────────────────────┘
randPoisson
Returns a random UInt64 drawn from a Poisson distribution.
Syntax
randPoisson(n)
Arguments
n
-UInt64
- mean number of occurrences.
Returned value
- Random number. UInt64.
Example
SELECT randPoisson(10) FROM numbers(5)
Result:
┌─randPoisson(10)─┐ │ 8 │ │ 8 │ │ 7 │ │ 10 │ │ 6 │ └─────────────────┘
randBernoulli
Returns a random UInt64 drawn from a Bernoulli distribution.
Syntax
randBernoulli(probability)
Arguments
probability
-Float64
- probability of success, a value between 0 and 1.
Returned value
- Random number. UInt64.
Example
SELECT randBernoulli(.75) FROM numbers(5)
Result:
┌─randBernoulli(0.75)─┐ │ 1 │ │ 1 │ │ 0 │ │ 1 │ │ 1 │ └─────────────────────┘
randExponential
Returns a random Float64 drawn from a exponential distribution.
Syntax
randExponential(lambda)
Arguments
lambda
-Float64
- lambda value.
Returned value
- Random number. Float64.
Example
SELECT randExponential(1/10) FROM numbers(5)
Result:
┌─randExponential(divide(1, 10))─┐ │ 44.71628934340778 │ │ 4.211013337903262 │ │ 10.809402553207766 │ │ 15.63959406553284 │ │ 1.8148392319860158 │ └────────────────────────────────┘
randChiSquared
Returns a random Float64 drawn from a Chi-square distribution - a distribution of a sum of the squares of k independent standard normal random variables.
Syntax
randChiSquared(degree_of_freedom)
Arguments
degree_of_freedom
-Float64
- degree of freedom.
Returned value
- Random number. Float64.
Example
SELECT randChiSquared(10) FROM numbers(5)
Result:
┌─randChiSquared(10)─┐ │ 10.015463656521543 │ │ 9.621799919882768 │ │ 2.71785015634699 │ │ 11.128188665931908 │ │ 4.902063104425469 │ └────────────────────┘
randStudentT
Returns a random Float64 drawn from a Student’s t-distribution.
Syntax
randStudentT(degree_of_freedom)
Arguments
degree_of_freedom
-Float64
- degree of freedom.
Returned value
- Random number. Float64.
Example
SELECT randStudentT(10) FROM numbers(5)
Result:
┌─────randStudentT(10)─┐ │ 1.2217309938538725 │ │ 1.7941971681200541 │ │ -0.28192176076784664 │ │ 0.2508897721303792 │ │ -2.7858432909761186 │ └──────────────────────┘
randFisherF
Returns a random Float64 drawn from a F-distribution.
Syntax
randFisherF(d1, d2)
Arguments
d1
-Float64
- d1 degree of freedom inX = (S1 / d1) / (S2 / d2)
,d2
-Float64
- d2 degree of freedom inX = (S1 / d1) / (S2 / d2)
,
Returned value
- Random number. Float64.
Example
SELECT randFisherF(10, 3) FROM numbers(5)
Result:
┌──randFisherF(10, 3)─┐ │ 7.286287504216609 │ │ 0.26590779413050386 │ │ 0.22207610901168987 │ │ 0.7953362728449572 │ │ 0.19278885985221572 │ └─────────────────────┘
randomString
Generates a string of the specified length filled with random bytes (including zero bytes). Not all characters may be printable.
Syntax
randomString(length)
Arguments
length
— String length in bytes. Positive integer.
Returned value
- String filled with random bytes. String.
Example
Query:
SELECT randomString(30) AS str, length(str) AS len FROM numbers(2) FORMAT Vertical;
Result:
Row 1:
──────
str: 3 G : pT ?w тi k aV f6
len: 30
Row 2:
──────
str: 9 ,] ^ ) ]?? 8
len: 30
randomFixedString
Generates a binary string of the specified length filled with random bytes (including zero bytes). Not all characters may be printable.
Syntax
randomFixedString(length);
Arguments
length
— String length in bytes. UInt64.
Returned value(s)
- String filled with random bytes. FixedString.
Example
Query:
SELECT randomFixedString(13) as rnd, toTypeName(rnd)
Result:
┌─rnd──────┬─toTypeName(randomFixedString(13))─┐
│ j▒h㋖HɨZ'▒ │ FixedString(13) │
└──────────┴───────────────────────────────────┘
randomPrintableASCII
Generates a string with a random set of ASCII characters. All characters are printable. If you pass length < 0
, the behavior of the function is undefined.
Syntax
randomPrintableASCII(length)
Arguments
length
— String length in bytes. Positive integer.
Returned value
Example
SELECT number, randomPrintableASCII(30) as str, length(str) FROM system.numbers LIMIT 3
┌─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 │
└────────┴────────────────────────────────┴──────────────────────────────────┘
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
randomStringUTF8(length);
Arguments
length
— Length of the string in code points. UInt64.
Returned value(s)
- UTF-8 random string. String.
Example
Query:
SELECT randomStringUTF8(13)
Result:
┌─randomStringUTF8(13)─┐
│ 𘤗д兠庇 │
└──────────────────────┘
fuzzBits
Syntax
Flips the bits of String or FixedString s
, each with probability prob
.
Syntax
fuzzBits(s, prob)
Arguments
s
-String
orFixedString
,prob
- constantFloat32/64
between 0.0 and 1.0.
Returned value
Fuzzed string with same type as s
.
Example
SELECT fuzzBits(materialize('abacaba'), 0.1)
FROM numbers(3)
Result:
┌─fuzzBits(materialize('abacaba'), 0.1)─┐ │ abaaaja │ │ a*cjab+ │ │ aeca2A │ └───────────────────────────────────────┘