Relational Data - Functions - Random Functions

9876543210 |

## randUniform

Returns a random Float64 drawn uniformly from interval [`min`, `max`].

### Syntax

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

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)

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 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                                │
└───────────────────────────────────────┘