Categories:

Data Generation Functions

RANDOM

Each call returns a pseudo-random 64-bit integer.

Syntax

RANDOM([seed])

Arguments

seed

Seed is an integer. Different seeds cause RANDOM to produce different output values.

Usage Notes

  • The optional seed argument must be an integer constant. If no value for seed is provided, a random seed is chosen in a platform-specific manner.

  • Calling RANDOM repeatedly with no seed produces different values for each call.

  • Calling RANDOM repeatedly with the same seed produces the same value each time.

  • Random values are not necessarily unique values. Although duplicates are rare for a small number of calls, the odds of duplicates go up as the number of calls goes up. If you need unique values, consider using a sequence (SEQ1 / SEQ2 / SEQ4 / SEQ8) rather than a call to RANDOM. Choose a sequence with enough bits that it is unlikely to wrap around.

  • Because the output is a finite integer and the values are generated by an algorithm rather than truly randomly, the function eventually “wraps around” and starts repeating sequences of values. However, the “period” (number of calls before wrapping) is extremely large: 2^19937 - 1.

  • Calling RANDOM more than once inside the same SQL statement causes each call within that execution of the statement to return the same value. Essentially, the function is called once and the result is re-used for the remainder of the statement execution.

    If the statement processes multiple rows, then RANDOM will be called once for each row; within a row, all calls to RANDOM will see the same value. However, each row will see a different value.

    For an example, see the Examples section below.

  • The output is only pseudo-random; the output can be predicted given enough information (including the algorithm and the seed).

  • RANDOM implements a 64-bit Mersenne twister algorithm known as MT19937-64.

  • Generating pseudo-random numbers is somewhat expensive computationally; large numbers of calls to this function can consume significant resources.

Examples

Typically, RANDOM is used without a seed. However, most of these examples use a seed so that the customers who run these commands get the same values that the documentation shows:

Here is a simple example of calling RANDOM with a seed:

SELECT random(4711) FROM table(generator(rowCount => 5));
+----------------------+
|         RANDOM(4711) |
|----------------------|
| -3581185414942383166 |
|  1570543588041465562 |
| -6684111782596764647 |
| -3878722496384921389 |
| -1136601514902083621 |
+----------------------+

These examples show that if you use the same seed(s), you get the same value(s):

SELECT random(100);
+---------------------+
|         RANDOM(100) |
|---------------------|
| -707166433115721098 |
+---------------------+
SELECT random(100);
+---------------------+
|         RANDOM(100) |
|---------------------|
| -707166433115721098 |
+---------------------+
SELECT random(100);
+---------------------+
|         RANDOM(100) |
|---------------------|
| -707166433115721098 |
+---------------------+
SELECT random(100);
+---------------------+
|         RANDOM(100) |
|---------------------|
| -707166433115721098 |
+---------------------+
SELECT random(101);
+---------------------+
|         RANDOM(101) |
|---------------------|
| 1126233572059560367 |
+---------------------+
SELECT random(100);
+---------------------+
|         RANDOM(100) |
|---------------------|
| -707166433115721098 |
+---------------------+
SELECT random(101);
+---------------------+
|         RANDOM(101) |
|---------------------|
| 1126233572059560367 |
+---------------------+

This shows that if you call RANDOM more than once inside the same execution of the same statement, each row gets the same value for each call to RANDOM, but different rows get different values:

First, create the table and insert some values.

SET RANDOM_NUMBER_SEED = 100;
CREATE OR REPLACE TABLE random1 (i tinyint, j tinyint);
INSERT INTO random1 (i, j) VALUES (-1, -1);
INSERT INTO random1 (i, j) VALUES (-2, -2);

Replace the values with random values.

UPDATE random1 set i = RANDOM($RANDOM_NUMBER_SEED), j = RANDOM($RANDOM_NUMBER_SEED);

Display the values. Note that within a row, all values generated by RANDOM are the same, but across rows, the values are different:

SELECT i, j FROM random1;
+---------------------+---------------------+
|                   I |                   J |
|---------------------+---------------------|
| -707166433115721098 | -707166433115721098 |
| 5969071622678286091 | 5969071622678286091 |
+---------------------+---------------------+