Functions for generating random numbers - ClickHouse Documentation

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

Guide: Generating random data in ClickHouse
Blog: Generating random data in ClickHouse

The random numbers are generated by non-cryptographic algorithms.

The documentation below is generated from the system.functions system table.

fuzzBits

Introduced in: v20.5.0 Flips the bits of the input string s, with probability p for each bit. Syntax

fuzzBits(s, p)

Arguments

s — String or FixedString to perform bit fuzzing on String or FixedString
p — Probability of flipping each bit as a number between 0.0 and 1.0 Float*

Returned value Returns a Fuzzed string with same type as s. String or FixedString Examples Usage example

Query

SELECT fuzzBits(materialize('abacaba'), 0.1)
FROM numbers(3)

Response

┌─fuzzBits(materialize('abacaba'), 0.1)─┐
│ abaaaja                               │
│ a*cjab+                               │
│ aeca2A                                │
└───────────────────────────────────────┘

rand

Introduced in: v1.1.0 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([x])

Aliases: rand32 Arguments

x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random number of type UInt32. UInt32 Examples Usage example

Query

SELECT rand();

Response

1569354847

rand64

Introduced in: v1.1.0 Returns a random distributed 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. Syntax

rand64([x])

Arguments

x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random UInt64 number with uniform distribution. UInt64 Examples Usage example

Query

SELECT rand64();

Response

15030268859237645412

randBernoulli

Introduced in: v22.10.0 Returns a random Float64 number drawn from a Bernoulli distribution. Syntax

randBernoulli(probability[, x])

Arguments

probability — The probability of success as a value between 0 and 1. Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified Bernoulli distribution. UInt64 Examples Usage example

Query

SELECT randBernoulli(.75) FROM numbers(5)

Response

┌─randBernoulli(0.75)─┐
│                   1 │
│                   1 │
│                   0 │
│                   1 │
│                   1 │
└─────────────────────┘

randBinomial

Introduced in: v22.10.0 Returns a random Float64 number drawn from a binomial distribution. Syntax

randBinomial(experiments, probability[, x])

Arguments

experiments — The number of experiments UInt64
probability — The probability of success in each experiment as a value between 0 and 1 Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified binomial distribution. UInt64 Examples Usage example

Query

SELECT randBinomial(100, .75) FROM numbers(5)

Response

┌─randBinomial(100, 0.75)─┐
│                      74 │
│                      78 │
│                      76 │
│                      77 │
│                      80 │
└─────────────────────────┘

randCanonical

Introduced in: v22.11.0 Returns a random distributed Float64 number with uniform distribution between 0 (inclusive) and 1 (exclusive). Syntax

randCanonical([x])

Arguments

x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number. Float64 Examples Usage example

Query

SELECT randCanonical();

Response

0.345217890123456

randChiSquared

Introduced in: v22.10.0 Returns a random Float64 number drawn from a chi-square distribution. Syntax

randChiSquared(degree_of_freedom[, x])

Arguments

degree_of_freedom — Degrees of freedom. Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified chi-square distribution. Float64 Examples Usage example

Query

SELECT randChiSquared(10) FROM numbers(5)

Response

┌─randChiSquared(10)─┐
│ 10.015463656521543 │
│  9.621799919882768 │
│   2.71785015634699 │
│ 11.128188665931908 │
│  4.902063104425469 │
└────────────────────┘

randConstant

Introduced in: v1.1.0 Generates a single random value that remains constant across all rows in the current query execution. This function:

Returns the same random value for every row within a single query
Produces different values across separate query executions

It is useful for applying consistent random seeds or identifiers across all rows in a dataset Syntax

randConstant([x])

Arguments

x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a column of type UInt32 containing the same random value in each row. UInt32 Examples Basic usage

Query

SELECT randConstant() AS random_value;

Response

| random_value |
|--------------|
| 1234567890   |

Usage with parameter

Query

SELECT randConstant(10) AS random_value;

Response

| random_value |
|--------------|
| 9876543210   |

randExponential

Introduced in: v22.10.0 Returns a random Float64 number drawn from an exponential distribution. Syntax

randExponential(lambda[, x])

Arguments

lambda — Rate parameter or lambda value of the distribution Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified exponential distribution. Float64 Examples Usage example

Query

SELECT randExponential(1/10) FROM numbers(5)

Response

┌─randExponential(divide(1, 10))─┐
│              44.71628934340778 │
│              4.211013337903262 │
│             10.809402553207766 │
│              15.63959406553284 │
│             1.8148392319860158 │
└────────────────────────────────┘

randFisherF

Introduced in: v22.10.0 Returns a random Float64 number drawn from an F-distribution. Syntax

randFisherF(d1, d2[, x])

Arguments

d1 — d1 degree of freedom in X = (S1 / d1) / (S2 / d2). Float64
d2 — d2 degree of freedom in X = (S1 / d1) / (S2 / d2). Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified F-distribution Float64 Examples Usage example

Query

SELECT randFisherF(10, 3) FROM numbers(5)

Response

┌─randFisherF(10, 20)─┐
│  0.7204609609506184 │
│  0.9926258472572916 │
│  1.4010752726735863 │
│ 0.34928401507025556 │
│  1.8216216009473598 │
└─────────────────────┘

randLogNormal

Introduced in: v22.10.0 Returns a random Float64 number drawn from a log-normal distribution. Syntax

randLogNormal(mean, stddev[, x])

Arguments

mean — The mean value of distribution. Float64
stddev — The standard deviation of the distribution. Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified log-normal distribution. Float64 Examples Usage example

Query

SELECT randLogNormal(100, 5) FROM numbers(5)

Response

┌─randLogNormal(100, 5)─┐
│  1.295699673937363e48 │
│  9.719869109186684e39 │
│  6.110868203189557e42 │
│  9.912675872925529e39 │
│ 2.3564708490552458e42 │
└───────────────────────┘

randNegativeBinomial

Introduced in: v22.10.0 Returns a random Float64 number drawn from a negative binomial distribution. Syntax

randNegativeBinomial(experiments, probability[, x])

Arguments

experiments — The number of experiments. UInt64
probability — The probability of failure in each experiment as a value between 0and1. [Float64`](/reference/data-types/float)
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified negative binomial distribution UInt64 Examples Usage example

Query

SELECT randNegativeBinomial(100, .75) FROM numbers(5)

Response

┌─randNegativeBinomial(100, 0.75)─┐
│                              33 │
│                              32 │
│                              39 │
│                              40 │
│                              50 │
└─────────────────────────────────┘

randNormal

Introduced in: v22.10.0 Returns a random Float64 number drawn from a normal distribution. Syntax

randNormal(mean, stddev[, x])

Arguments

mean — The mean value of distribution Float64
stddev — The standard deviation of the distribution Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified normal distribution. Float64 Examples Usage example

Query

SELECT randNormal(10, 2) FROM numbers(5)

Response

┌──randNormal(10, 2)─┐
│ 13.389228911709653 │
│  8.622949707401295 │
│ 10.801887062682981 │
│ 4.5220192605895315 │
│ 10.901239123982567 │
└────────────────────┘

randPoisson

Introduced in: v22.10.0 Returns a random Float64 number drawn from a Poisson distribution distribution. Syntax

randPoisson(n[, x])

Arguments

n — The mean number of occurrences. UInt64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified Poisson distribution. UInt64 Examples Usage example

Query

SELECT randPoisson(10) FROM numbers(5)

Response

┌─randPoisson(10)─┐
│               8 │
│               8 │
│               7 │
│              10 │
│               6 │
└─────────────────┘

randStudentT

Introduced in: v22.10.0 Returns a random Float64 number drawn from a Student’s t-distribution. Syntax

randStudentT(degree_of_freedom[, x])

Arguments

degree_of_freedom — Degrees of freedom. Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random Float64 number drawn from the specified Student’s t-distribution. Float64 Examples Usage example

Query

SELECT randStudentT(10) FROM numbers(5)

Response

┌─────randStudentT(10)─┐
│   1.2217309938538725 │
│   1.7941971681200541 │
│ -0.28192176076784664 │
│   0.2508897721303792 │
│  -2.7858432909761186 │
└──────────────────────┘

randUniform

Introduced in: v22.10.0 Returns a random Float64 number drawn uniformly from the interval

[\min, \max]

. Syntax

randUniform(min, max[, x])

Arguments

min — Left boundary of the range (inclusive). Float64
max — Right boundary of the range (inclusive). Float64
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a random number drawn uniformly from the interval formed by min and max. Float64 Examples Usage example

Query

SELECT randUniform(5.5, 10) FROM numbers(5)

Response

┌─randUniform(5.5, 10)─┐
│    8.094978491443102 │
│   7.3181248914450885 │
│    7.177741903868262 │
│    6.483347380953762 │
│    6.122286382885112 │
└──────────────────────┘

randomFixedString

Introduced in: v20.5.0 Generates a random fixed-size string with the specified number of character. The returned characters are not necessarily ASCII characters, i.e. they may not be printable. Syntax

randomFixedString(length)

Arguments

length — Length of the string in bytes. UInt*

Returned value Returns a string filled with random bytes. FixedString Examples Usage example

Query

SELECT randomFixedString(13) AS rnd, toTypeName(rnd)

Response

┌─rnd──────┬─toTypeName(randomFixedString(13))─┐
│ j▒h㋖HɨZ'▒ │ FixedString(13)                 │
└──────────┴───────────────────────────────────┘

randomPrintableASCII

Introduced in: v20.1.0 Generates a random ASCII string with the specified number of characters. If you pass length < 0, the behavior of the function is undefined. Syntax

randomPrintableASCII(length[, x])

Arguments

length — String length in bytes. (U)Int*
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a string with a random set of ASCII printable characters. String Examples Usage example

Query

SELECT number, randomPrintableASCII(30) AS str, length(str) FROM system.numbers LIMIT 3

Response

┌─number─┬─str────────────────────────────┬─length(randomPrintableASCII(30))─┐
│      0 │ SuiCOSTvC0csfABSw=UcSzp2.`rv8x │                               30 │
│      1 │ 1Ag NlJ &RCN:*>HVPG;PE-nO"SUFD │                               30 │
│      2 │ /"+<"with:=LjJ Vm!c&hI*m#XTfzz │                               30 │
└────────┴────────────────────────────────┴──────────────────────────────────┘

randomString

Introduced in: v20.5.0 Generates a random string with the specified number of characters. The returned characters are not necessarily ASCII characters, i.e. they may not be printable. Syntax

randomString(length[, x])

Arguments

length — Length of the string in bytes. (U)Int*
x — Optional and ignored. The only purpose of the argument is to prevent common subexpression elimination when the same function call is used multiple times in a query. Any

Returned value Returns a string filled with random bytes. String Examples Usage example

Query

SELECT randomString(5) AS str FROM numbers(2)

Response

���
�v6B�

randomStringUTF8

Introduced in: v20.5.0 Generates a random UTF-8 string with the specified number of codepoints. No codepoints from unassigned planes (planes 4 to 13) are returned. It is still possible that the client interacting with ClickHouse server is not able to display the produced UTF-8 string correctly. Syntax

randomStringUTF8(length)

Arguments

length — Length of the string in code points. (U)Int*

Returned value Returns a string filled with random UTF-8 codepoints. String Examples Usage example

Query

SELECT randomStringUTF8(13)

Response

┌─randomStringUTF8(13)─┐
│ 𘤗𙉝д兠庇󡅴󱱎󦐪􂕌𔊹𓰛       │
└──────────────────────┘

SQL Reference

Documentation Index

​fuzzBits

​rand

​rand64

​randBernoulli

​randBinomial

​randCanonical

​randChiSquared

​randConstant

​randExponential

​randFisherF

​randLogNormal

​randNegativeBinomial

​randNormal

​randPoisson

​randStudentT

​randUniform

​randomFixedString

​randomPrintableASCII

​randomString

​randomStringUTF8

fuzzBits

rand

rand64

randBernoulli

randBinomial

randCanonical

randChiSquared

randConstant

randExponential

randFisherF

randLogNormal

randNegativeBinomial

randNormal

randPoisson

randStudentT

randUniform

randomFixedString

randomPrintableASCII

randomString

randomStringUTF8