# Functions for Generating Random Numbers

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

note

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

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()

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()

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

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

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

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

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

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

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

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

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

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 in X = (S1 / d1) / (S2 / d2),
• d2 - Float64 - d2 degree of freedom in X = (S1 / d1) / (S2 / d2),

Returned value

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 f6len: 30Row 2:──────str: 9 ,]    ^   )  ]??  8len: 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)

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

• String with a random set of ASCII printable characters. String

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)

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 or FixedString,
• prob - constant Float32/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                                │└───────────────────────────────────────┘`