Random number functions provide a way to generate random values for various data types.

These functions are useful for a wide range of applications, including:

  • Data simulation
  • Testing
  • Statistical analysis

This section covers different random number generation functions, each designed to produce random values with specific characteristics or distributions.

​
ClickHouse function reference

​
rand

Generates a random UInt32 number with uniform distribution.

Syntax:

rand()

Alias:

  • rand32

Arguments: None.

Returns: A random UInt32 number.

Example:

SELECT
  rand() AS random_taco_number;

Result:

| random_taco_number |
|--------------------|
| 1569354847         |

In this example, we generate a random number that could represent a unique identifier for a taco order. Note that the actual output will be different each time you run the query.

This function uses a linear congruential generator with an initial state obtained from the system. While it appears random, it’s not cryptographically secure. For scenarios requiring true randomness, consider using alternative methods.

​
rand64

Generates a random UInt64 number with uniform distribution.

Syntax:

rand64()

Arguments: None.

Returns: A random UInt64 number.

Example:

SELECT
	rand64() AS large_taco_order_id;

Result:

| large_taco_order_id |
|---------------------|
| 15030268859237645412|

This example generates a large random number that could be used as a unique identifier for a very large taco order. The actual output will be different each time you run the query.

Like rand(), this function uses a linear congruential generator and is not suitable for cryptographic purposes.

​
randCanonical

Returns a random Float64 number between 0 (inclusive) and 1 (exclusive) with uniform distribution.

Syntax:

randCanonical()

Arguments:

This function takes no arguments.

Returns:

A Float64 value in the range [0, 1).

Example:

SELECT
	randCanonical() AS random_taco_rating;

Result:

| random_taco_rating |
|--------------------|
| 0.7654321098765432 |

In this example, we generate a random rating for a taco between 0 and 1. The actual output will be a different random Float64 number each time the query is executed.

This function is useful for generating random floating-point numbers for various purposes, such as simulations, random sampling, or creating test data. Keep in mind that the generated numbers are not cryptographically secure and should not be used for cryptographic purposes.

​
randConstant

Generates a constant random UInt32 value that remains the same for all rows in the query result.

Syntax:

randConstant([x])

Arguments:

  • x (optional): Any value. If provided, it influences the generated random value, but the result will still be constant within the same query execution.

Returns:

A UInt32 constant random value.

Example:

SELECT
	randConstant() AS random_taco_id,
	'Taco #' || toString(random_taco_id) AS taco_name
FROM
  numbers(3);

Result:

| random_taco_id | taco_name           |
|----------------|---------------------|
| 1234567890     | Taco #1234567890    |
| 1234567890     | Taco #1234567890    |
| 1234567890     | Taco #1234567890    |

In this example, randConstant() generates the same random taco ID for all rows, which is then used to create unique taco names.

  • The actual random value will be different for each query execution.
  • Using different arguments may not significantly change the generated value compared to using randConstant() without arguments.
  • This function is useful when you need a consistent random value across all rows in a single query, such as for generating test data or creating a random identifier for a batch operation.

​
randUniform

Generates a random Float64 number uniformly distributed between the specified minimum and maximum values.

Syntax:

randUniform(min, max)

Arguments:

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

Returns:

A random Float64 number uniformly distributed between min and max.

Example:

SELECT
	randUniform(1.5, 8.5) AS random_taco_price
FROM
	numbers(5);

Result:

| random_taco_price |
|-------------------|
| 6.2384729103      |
| 3.9271856492      |
| 7.1039284756      |
| 2.5847291038      |
| 5.7392847561      |

In this example, we generate 5 random taco prices between 1.50and1.50 and 8.50.

The actual output will consist of different random numbers within the specified range.

​
randNormal

Generates a random Float64 number drawn from a normal distribution (also known as Gaussian distribution).

Syntax:

randNormal(mean, variance)

Arguments:

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

Returns:

  • A random number from the normal distribution. [Float64]

Example:

SELECT
	randNormal(10, 2) AS taco_weight
FROM
	numbers(5);

Result:

| taco_weight         |
|---------------------|
| 9.123456789012345   |
| 11.987654321098765  |
| 10.543210987654321  |
| 8.765432109876543   |
| 10.234567890123456  |

In this example, we generate 5 random taco weights with a mean of 10 ounces and a variance of 2. The actual output will be different random numbers each time the query is executed.

The normal distribution is symmetric around its mean, and the variance determines how spread out the values are. Approximately 68% of the values will fall within one standard deviation (square root of variance) of the mean.

​
randLogNormal

Returns a random Float64 number drawn from a log-normal distribution.

Syntax:

randLogNormal(mean, variance)

Arguments:

  • mean (Float64): Mean value of the distribution.
  • variance (Float64): Variance of the distribution.

Returns:

  • A random number from the log-normal distribution. Float64.

Example:

SELECT
	randLogNormal(5.0, 1.5) AS taco_weight
FROM
	numbers(5);

Result:

| taco_weight         |
|---------------------|
| 148.41349067762387  |
| 297.13249572347734  |
| 72.96883249221828   |
| 124.32629487951127  |
| 203.46489763960544  |

In this example, we generate random taco weights following a log-normal distribution with a mean of 5.0 and a variance of 1.5. The resulting weights are in grams and represent the variation you might see in a batch of tacos from a particularly inconsistent taco chef.

The actual output will consist of different random numbers each time the query is executed.

​
randBinomial

Generates a random UInt64 number 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.

Returns:

  • A random number following the binomial distribution. (UInt64)

Example:

SELECT
	randBinomial(100, 0.5) AS taco_orders_count
FROM
	numbers(5);

Result:

| taco_orders_count |
|-------------------|
| 51                |
| 47                |
| 53                |
| 49                |
| 52                |

In this example, we simulate the number of taco orders received in a day, assuming there’s a 50% chance of receiving an order for each of 100 potential customers. The function returns different random values for each row, representing possible daily order counts.

The actual output will consist of random numbers and may differ from the example shown.

​
randNegativeBinomial

Returns a random UInt64 number 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.

Returns:

  • A random number following the negative binomial distribution. (UInt64)

Example:

SELECT
	randNegativeBinomial(100, 0.25) AS taco_failures
FROM
	numbers(5);

Result:

| taco_failures |
|---------------|
| 28            |
| 35            |
| 31            |
| 29            |
| 33            |

In this example, we simulate the number of failures (taco_failures) before achieving 100 successful taco sales, with a 25% chance of failure for each sale attempt. The actual output will vary due to the random nature of the function.

The negative binomial distribution models the number of failures before a specified number of successes occurs in a sequence of independent trials. It’s useful for modeling scenarios like the number of attempts needed to achieve a certain number of successful outcomes.

​
randPoisson

Generates a random UInt64 number drawn from a Poisson distribution.

Syntax:

randPoisson(n)

Arguments:

  • n (UInt64): Mean number of occurrences.

Returns:

  • A random number following the Poisson distribution. [UInt64]

Example:

SELECT
	randPoisson(5) AS poisson_tacos
FROM
	numbers(10);

Result:

| poisson_tacos |
|---------------|
| 7             |
| 4             |
| 6             |
| 3             |
| 5             |
| 4             |
| 6             |
| 5             |
| 3             |
| 8             |

In this example, we generate 10 random numbers from a Poisson distribution with a mean of 5. Each number represents a hypothetical count of tacos ordered in a given time period, assuming the average order is 5 tacos.

The actual output will vary due to the random nature of the function.

​
randBernoulli

Generates a random UInt64 value drawn from a Bernoulli distribution.

Syntax:

randBernoulli(probability)

Arguments:

  • probability (Float64): A value representing the probability of success, between 0 and 1.

Returns:

  • A random UInt64 number: 1 (success) or 0 (failure).

Example:

SELECT
	randBernoulli(0.7) AS taco_success
FROM
	numbers(5);

Result:

| taco_success |
|--------------|
| 1            |
| 1            |
| 0            |
| 1            |
| 1            |

In this example, we simulate the success rate of making perfect tacos. Each row represents an attempt, with 1 indicating a successful taco and 0 indicating a less-than-perfect taco. The probability of 0.7 suggests a 70% chance of making a perfect taco on each attempt.

The actual results will vary due to the random nature of the function.

​
randExponential

Generates a random Float64 number drawn from an exponential distribution.

Syntax:

randExponential(lambda)

Arguments:

  • lambda (Float64): Parameter of the exponential distribution. Must be greater than 0.

Returns:

A random Float64 number from the exponential distribution.

Example:

SELECT
	randExponential(0.1) AS random_wait_time
FROM
	numbers(5);

Result:

| random_wait_time |
|------------------|
|  5.4983749281893 |
| 13.879544619440  |
|  7.1356781211228 |
|  0.8707323061913 |
|  2.0590847934677 |

In this example, we generate 5 random wait times (in minutes) for a taco order, assuming an average wait time of 10 minutes (λ = 1/10 = 0.1).

The exponential distribution is often used to model the time between independent events that happen at a constant average rate, such as customer arrivals or equipment failures.

​
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.

Returns:

A random number of type Float64.

Example:

SELECT
	randChiSquared(10) AS chi_squared_value
FROM
	numbers(5);

Result:

| chi_squared_value |
|-------------------|
| 8.723456789012345 |
| 11.98765432109876 |
| 7.345678901234567 |
| 13.21098765432109 |
| 9.876543210987654 |

In this example, we generate 5 random values from a Chi-square distribution with 10 degrees of freedom. Each row represents a different random draw from the distribution.

The actual output will consist of different random numbers each time the query is executed, not the specific numbers shown in the example.

​
randStudentT

Generates a random Float64 number drawn from a Student’s t-distribution.

Syntax:

randStudentT(degree_of_freedom)

Arguments:

  • degree_of_freedom (Float64): A Float64 value representing the degrees of freedom for the t-distribution.

Returns:

  • A random Float64 number from the Student’s t-distribution.

Example:

SELECT
	randStudentT(10) AS taco_rating
FROM
	numbers(5);

Result:

| taco_rating         |
|---------------------|
|  1.2217309938538725 |
|  1.7941971681200541 |
| -0.28192176076784664|
|  0.2508897721303792 |
| -2.7858432909761186 |

In this example, we generate 5 random taco ratings using the Student’s t-distribution with 10 degrees of freedom. This could represent a scenario where taco ratings follow a t-distribution, allowing for more extreme values than a normal distribution.

The Student’s t-distribution is often used in statistical analysis, particularly when dealing with small sample sizes or when the population standard deviation is unknown.

Syntax:

randFisherF(d1, d2)

Arguments:

  • d1 (Float64): First degree of freedom for the F-distribution.
  • d2 (Float64): Second degree of freedom for the F-distribution.

Returns:

A random number of type Float64.

Example:

SELECT
	randFisherF(10, 3) AS taco_quality_score
FROM
	taco_orders
LIMIT 5;

Result:

| taco_quality_score |
|--------------------|
| 2.3456789012345    |
| 0.9876543210987    |
| 3.2109876543210    |
| 1.5432109876543    |
| 4.8765432109876    |

In this example, we generate random F-distributed values that could represent quality scores for tacos, where d1 (10) might represent the number of taste testers and d2 (3) the number of taco varieties being compared.

The F-distribution is often used in statistical analysis, particularly in analysis of variance (ANOVA) and regression analysis. In the context of our taco example, it could be used to model the variability in taco quality ratings across different taco shops or recipes.

​
randomString

Generates a string of the specified length filled with random bytes (including zero bytes).

Syntax:

randomString(length)

Arguments:

  • length (UInt64): String length in bytes. Positive integer.

Returns:

A string filled with random bytes. [String].

Not all characters in the generated string may be printable.

Example:

SELECT
	randomString(10) AS random_taco_name,
	length(random_taco_name) AS name_length
FROM numbers(3)

Result:

| random_taco_name | name_length |
|------------------|-------------|
| Xм8Iц╗gн╩       | 10          |
| ╬yuщЁ╟Б▒Г       | 10          |
| Ч╢m╥╧╞йЁ╟      | 10          |

In this example, we generate random taco names of 10 bytes each. The length function confirms that each generated string is indeed 10 bytes long.

The generated strings contain random bytes, which may include non-printable characters. In practice, you might want to use randomPrintableASCII for generating readable random strings.

​
randomFixedString

Generates a binary string of the specified length filled with random bytes (including zero bytes).

Syntax:

randomFixedString(length)

Arguments:

  • length (UInt64): String length in bytes.

Returns:

  • A string filled with random bytes. [FixedString]

Example:

SELECT
	randomFixedString(13) AS random_taco_sauce,
	toTypeName(random_taco_sauce) AS type

Result:

| random_taco_sauce | type            |
|-------------------|-----------------|
| j▒hキHɨZ'▒        | FixedString(13) |

In this example, we generate a random 13-byte string that could represent a unique identifier for a taco sauce. The result contains random bytes, which may include non-printable characters.

The generated string may contain any byte values, including null bytes and non-ASCII characters. Not all characters in the result may be printable.

​
randomPrintableASCII

Generates a string with a random set of printable ASCII characters.

Syntax:

randomPrintableASCII(length)

Arguments:

  • length (UInt64): Desired string length in bytes. Positive integer.

Returns:

A string containing random printable ASCII characters. [String]

Example:

SELECT
	randomPrintableASCII(10) AS random_taco_code
FROM
	numbers(3);

Result:

| random_taco_code |
|------------------|
| TacoFiesta       |
| YummyGuac!       |
| SalsaDance       |

This example generates three random 10-character strings that could be used as fun taco-themed promotional codes.

If you pass a negative length, the behavior of the function is undefined.

​
randomStringUTF8

Generates a random string of a specified length containing valid UTF-8 code points.

Syntax:

randomStringUTF8(length)

Arguments:

  • length (UInt64): Length of the string in code points.

Returns:

  • A random UTF-8 string. [String]

Example:

SELECT
  randomStringUTF8(5) AS random_taco_name
FROM
  numbers(3);

Result:

| random_taco_name |
|------------------|
| 𨂣𿪯               |
| 𿪒𐆗               |
| 𿪒𐆗               |

This example generates three random taco names, each consisting of 5 UTF-8 code points. The resulting strings may contain characters from various Unicode ranges, including those outside of commonly used scripts.

The generated code points may be outside the range of assigned Unicode characters, potentially resulting in strings with unusual or non-printable characters.

​
fuzzBits

Flips random bits in a string with a specified probability.

Syntax:

fuzzBits(s, prob)

Arguments:

  • s (String or FixedString): The input string to be fuzzed.
  • prob (Float32 or Float64): The probability of flipping each bit, a constant value between 0.0 and 1.0.

Returns:

A fuzzed string of the same type as the input s.

Example:

SELECT
	fuzzBits('Crunchy Taco', 0.1) AS fuzzed_taco
FROM
	numbers(3);

Result:

| fuzzed_taco   |
|---------------|
| Crunchy Taco  |
| Crunchz Taco  |
| Crunchy Taco  |

In this example, we apply fuzzBits to the string ‘Crunchy Taco’ with a 10% probability of flipping each bit. The results show how random bits in the string may be flipped, potentially altering characters.

The function modifies bits randomly, so the output may contain non-printable characters or invalid UTF-8 sequences. The example above shows printable results for clarity, but actual outputs may vary.

This function is useful for generating fuzzed data for testing, simulating data corruption, or creating variations of strings for machine learning tasks.

Was this page helpful?

OtherReplacing in Strings