Functions for generating random numbers

These functions generate various types of random numbers and strings.

When an optional argument is provided to a random function, its primary purpose is to ensure that multiple calls to the same random function within a single row of a query produce distinct random values, preventing common subexpression elimination.

The random numbers generated by these functions use non-cryptographic algorithms. They are suitable for simulations and general data generation but should not be used for security-sensitive applications.

rand

Generates a random 32-bit unsigned integer with a uniform distribution.

Alias: rand32

Syntax

rand()

Arguments

None.

Returns

A random UInt32 number.

Example

SELECT rand()

Result:

┌─rand()─────┐
│ 1569354847 │
└────────────┘

rand64

Generates a random 64-bit unsigned integer with a uniform distribution.

Syntax

rand64()

Arguments

None.

Returns

A random UInt64 number.

Example

SELECT rand64()

Result:

┌──────────rand64()─┐
│ 15030268859237645412 │
└────────────────────┘

randCanonical

Generates a random floating-point number between 0 (inclusive) and 1 (exclusive).

Syntax

randCanonical()

Arguments

None.

Returns

A random Float64 value.

Example

SELECT randCanonical()

Result:

┌─randCanonical()─┐
│ 0.3452178901234567 │
└─────────────────┘

randConstant

Generates a single random 32-bit unsigned integer that remains constant across all rows in the result set of a query. An optional argument can be provided, but the output value will still be constant for the entire query execution.

Syntax

randConstant([x])

Arguments

  • x (Optional): An expression that can influence the generated random value. The resulting value will still be constant for all rows in the current query execution.

Returns

A UInt32 column where every row contains the same random value.

Example

SELECT randConstant() AS random_value

Result:

┌─random_value─┐
│   1234567890 │
└──────────────┘

randUniform

Generates a random floating-point number uniformly distributed within a specified range.

Syntax

randUniform(min, max)

Arguments

  • min: Float64. The lower bound of the desired range (inclusive).
  • max: Float64. The upper bound of the desired range (inclusive).

Returns

A random Float64 number within the specified range.

Example

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

Result:

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

randNormal

Generates a random floating-point number from a normal (Gaussian) distribution.

Syntax

randNormal(mean, variance)

Arguments

  • mean: Float64. The mean (average) of the distribution.
  • variance: Float64. The variance of the distribution.

Returns

A random Float64 number.

Example

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

Result:

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

randLogNormal

Generates a random floating-point number from a log-normal distribution.

Syntax

randLogNormal(mean, variance)

Arguments

  • mean: Float64. The mean of the underlying normal distribution.
  • variance: Float64. The variance of the underlying normal distribution.

Returns

A random Float64 number.

Example

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

Result:

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

randBinomial

Generates a random unsigned 64-bit integer from a binomial distribution, representing the number of successes in a series of independent experiments.

Syntax

randBinomial(experiments, probability)

Arguments

  • experiments: UInt64. The total number of trials or experiments.
  • probability: Float64. The probability of success for each individual experiment, a value between 0 and 1.

Returns

A random UInt64 number.

Example

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

Result:

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

randNegativeBinomial

Generates a random unsigned 64-bit integer from a negative binomial distribution, representing the number of failures before a specified number of successes.

Syntax

randNegativeBinomial(experiments, probability)

Arguments

  • experiments: UInt64. The number of successes to achieve.
  • probability: Float64. The probability of failure in each individual experiment, a value between 0 and 1.

Returns

A random UInt64 number.

Example

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

Result:

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

randPoisson

Generates a random unsigned 64-bit integer from a Poisson distribution, representing the number of events occurring in a fixed interval of time or space.

Syntax

randPoisson(n)

Arguments

  • n: UInt64. The average number of events (lambda) expected in the interval.

Returns

A random UInt64 number.

Example

SELECT randPoisson(10) FROM numbers(3)

Result:

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

randBernoulli

Generates a random unsigned 64-bit integer (0 or 1) from a Bernoulli distribution, representing the outcome of a single trial with a given probability of success.

Syntax

randBernoulli(probability)

Arguments

  • probability: Float64. The probability of success (returning 1), a value between 0 and 1.

Returns

A random UInt64 number (0 or 1).

Example

SELECT randBernoulli(.75) FROM numbers(3)

Result:

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

randExponential

Generates a random floating-point number from an exponential distribution, often used to model the time until an event occurs.

Syntax

randExponential(lambda)

Arguments

  • lambda: Float64. The rate parameter (λ) of the exponential distribution.

Returns

A random Float64 number.

Example

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

Result:

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

randChiSquared

Generates a random floating-point number from a Chi-squared distribution.

Syntax

randChiSquared(degree_of_freedom)

Arguments

  • degree_of_freedom: Float64. The degrees of freedom for the distribution.

Returns

A random Float64 number.

Example

SELECT randChiSquared(10) FROM numbers(3)

Result:

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

randStudentT

Generates a random floating-point number from a Student's t-distribution.

Syntax

randStudentT(degree_of_freedom)

Arguments

  • degree_of_freedom: Float64. The degrees of freedom for the distribution.

Returns

A random Float64 number.

Example

SELECT randStudentT(10) FROM numbers(3)

Result:

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

randFisherF

Generates a random floating-point number from an F-distribution (Fisher-Snedecor distribution).

Syntax

randFisherF(d1, d2)

Arguments

  • d1: Float64. The first degree of freedom.
  • d2: Float64. The second degree of freedom.

Returns

A random Float64 number.

Example

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

Result:

┌──randFisherF(10, 3)─┐
│   7.286287504216609 │
│ 0.26590779413050386 │
│ 0.22207610901168987 │
└─────────────────────┘

randomString

Generates a string of a specified length, filled with random bytes. The resulting string may contain non-printable characters.

Syntax

randomString(length)

Arguments

  • length: UInt64. The desired length of the string in bytes. Must be a positive integer.

Returns

A String filled with random bytes.

Example

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 FixedString of a specified length, filled with random bytes. The resulting string may contain non-printable characters.

Syntax

randomFixedString(length)

Arguments

  • length: UInt64. The desired length of the FixedString in bytes.

Returns

A FixedString filled with random bytes.

Example

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

Result:

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

randomPrintableASCII

Generates a string of a specified length containing only random printable ASCII characters.

Syntax

randomPrintableASCII(length)

Arguments

  • length: UInt64. The desired length of the string in bytes. Must be a positive integer.

Returns

A String containing random printable ASCII characters.

Example

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

Result:

┌─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, ensuring the result contains valid UTF-8 code points.

Syntax

randomStringUTF8(length)

Arguments

  • length: UInt64. The desired length of the string in code points.

Returns

A String containing random UTF-8 characters.

Example

SELECT randomStringUTF8(13)

Result:

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

fuzzBits

Flips bits in a given string (String or FixedString) with a specified probability for each bit.

Syntax

fuzzBits(s, prob)

Arguments

  • s: String or FixedString. The input string whose bits will be fuzzed.
  • prob: Float32 or Float64. The probability (between 0.0 and 1.0) that any individual bit in the string will be flipped.

Returns

A string of the same type as s with some bits potentially flipped.

Example

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

Result:

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