--- title: Random Number Functions reference meta: description: Functions for generating random numbers. headingMaxLevels: 2 --- # 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. {% callout type="info" %} 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. {% /callout %} ## rand Generates a random 32-bit unsigned integer with a uniform distribution. Alias: `rand32` ### Syntax ```sql rand() ``` ### Arguments None. ### Returns A random `UInt32` number. ### Example ```sql SELECT rand() ``` Result: ```result ┌─rand()─────┐ │ 1569354847 │ └────────────┘ ``` ## rand64 Generates a random 64-bit unsigned integer with a uniform distribution. ### Syntax ```sql rand64() ``` ### Arguments None. ### Returns A random `UInt64` number. ### Example ```sql SELECT rand64() ``` Result: ```result ┌──────────rand64()─┐ │ 15030268859237645412 │ └────────────────────┘ ``` ## randCanonical Generates a random floating-point number between 0 (inclusive) and 1 (exclusive). ### Syntax ```sql randCanonical() ``` ### Arguments None. ### Returns A random `Float64` value. ### Example ```sql SELECT randCanonical() ``` Result: ```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 ```sql 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 ```sql SELECT randConstant() AS random_value ``` Result: ```result ┌─random_value─┐ │ 1234567890 │ └──────────────┘ ``` ## randUniform Generates a random floating-point number uniformly distributed within a specified range. ### Syntax ```sql 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 ```sql SELECT randUniform(5.5, 10) FROM numbers(3) ``` Result: ```result ┌─randUniform(5.5, 10)─┐ │ 8.094978491443102 │ │ 7.3181248914450885 │ │ 7.177741903868262 │ └──────────────────────┘ ``` ## randNormal Generates a random floating-point number from a normal (Gaussian) distribution. ### Syntax ```sql 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 ```sql SELECT randNormal(10, 2) FROM numbers(3) ``` Result: ```result ┌──randNormal(10, 2)─┐ │ 13.389228911709653 │ │ 8.622949707401295 │ │ 10.801887062682981 │ └────────────────────┘ ``` ## randLogNormal Generates a random floating-point number from a log-normal distribution. ### Syntax ```sql 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 ```sql SELECT randLogNormal(100, 5) FROM numbers(3) ``` Result: ```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 ```sql 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 ```sql SELECT randBinomial(100, .75) FROM numbers(3) ``` Result: ```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 ```sql 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 ```sql SELECT randNegativeBinomial(100, .75) FROM numbers(3) ``` Result: ```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 ```sql randPoisson(n) ``` ### Arguments - `n`: `UInt64`. The average number of events (lambda) expected in the interval. ### Returns A random `UInt64` number. ### Example ```sql SELECT randPoisson(10) FROM numbers(3) ``` Result: ```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 ```sql 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 ```sql SELECT randBernoulli(.75) FROM numbers(3) ``` Result: ```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 ```sql randExponential(lambda) ``` ### Arguments - `lambda`: `Float64`. The rate parameter (λ) of the exponential distribution. ### Returns A random `Float64` number. ### Example ```sql SELECT randExponential(1/10) FROM numbers(3) ``` Result: ```result ┌─randExponential(divide(1, 10))─┐ │ 44.71628934340778 │ │ 4.211013337903262 │ │ 10.809402553207766 │ └────────────────────────────────┘ ``` ## randChiSquared Generates a random floating-point number from a Chi-squared distribution. ### Syntax ```sql randChiSquared(degree_of_freedom) ``` ### Arguments - `degree_of_freedom`: `Float64`. The degrees of freedom for the distribution. ### Returns A random `Float64` number. ### Example ```sql SELECT randChiSquared(10) FROM numbers(3) ``` Result: ```result ┌─randChiSquared(10)─┐ │ 10.015463656521543 │ │ 9.621799919882768 │ │ 2.71785015634699 │ └────────────────────┘ ``` ## randStudentT Generates a random floating-point number from a Student's t-distribution. ### Syntax ```sql randStudentT(degree_of_freedom) ``` ### Arguments - `degree_of_freedom`: `Float64`. The degrees of freedom for the distribution. ### Returns A random `Float64` number. ### Example ```sql SELECT randStudentT(10) FROM numbers(3) ``` Result: ```result ┌─────randStudentT(10)─┐ │ 1.2217309938538725 │ │ 1.7941971681200541 │ │ -0.28192176076784664 │ └──────────────────────┘ ``` ## randFisherF Generates a random floating-point number from an F-distribution (Fisher-Snedecor distribution). ### Syntax ```sql randFisherF(d1, d2) ``` ### Arguments - `d1`: `Float64`. The first degree of freedom. - `d2`: `Float64`. The second degree of freedom. ### Returns A random `Float64` number. ### Example ```sql SELECT randFisherF(10, 3) FROM numbers(3) ``` Result: ```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 ```sql 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 ```sql SELECT randomString(30) AS str, length(str) AS len FROM numbers(2) FORMAT Vertical ``` Result: ```text 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 ```sql randomFixedString(length) ``` ### Arguments - `length`: `UInt64`. The desired length of the `FixedString` in bytes. ### Returns A `FixedString` filled with random bytes. ### Example ```sql SELECT randomFixedString(13) as rnd, toTypeName(rnd) ``` Result: ```text ┌─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 ```sql 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 ```sql SELECT number, randomPrintableASCII(30) as str, length(str) FROM system.numbers LIMIT 3 ``` Result: ```text ┌─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 ```sql randomStringUTF8(length) ``` ### Arguments - `length`: `UInt64`. The desired length of the string in code points. ### Returns A `String` containing random UTF-8 characters. ### Example ```sql SELECT randomStringUTF8(13) ``` Result: ```text ┌─randomStringUTF8(13)─┐ │ 𘤗𙉝д兠庇󡅴󱱎󦐪􂕌𔊹𓰛 │ └──────────────────────┘ ``` ## fuzzBits Flips bits in a given string (`String` or `FixedString`) with a specified probability for each bit. ### Syntax ```sql 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 ```sql SELECT fuzzBits(materialize('abacaba'), 0.1) FROM numbers(3) ``` Result: ```result ┌─fuzzBits(materialize('abacaba'), 0.1)─┐ │ abaaaja │ │ a*cjab+ │ │ aeca2A │ └───────────────────────────────────────┘ ```