Categories:

Data Generation Functions

RANDOM

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

Syntax

RANDOM([seed])

Arguments

Optional:

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.

  • If a statement calls RANDOM multiple times with the same seed (for example, SELECT RANDOM(42), RANDOM(42);), RANDOM returns the same value for each call made during the execution of that statement.

    See the example below.

  • If a statement calls RANDOM (either with or without the seed), there is no guarantee that RANDOM will generate the same set of values for every execution of that statement.

    See the note in Examples in this topic.

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

  • 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

The following examples demonstrate how to use the RANDOM function. The values displayed in the output below might differ from the values returned when you run these examples yourself.

Note

Do not depend on RANDOM returning the same values on each execution.

The same example executed by different worker threads can produce different values for RANDOM.

The following example calls RANDOM without a seed:

SELECT random() FROM table(generator(rowCount => 3));

+----------------------+
|             RANDOM() |
|----------------------|
|  -962378740685764490 |
|  2115408279841266588 |
| -3473099493125344079 |
+----------------------+

The following example calls RANDOM with a seed:

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

As explained in the Usage Notes, if you call RANDOM multiple times in the same statement and pass in the same seed to each call, RANDOM returns the same value for all calls in that statement execution.

For example, executing this statement generates the same value for both calls to RANDOM:

SELECT random(4711), random(4711);
+----------------------+----------------------+
|         RANDOM(4711) |         RANDOM(4711) |
|----------------------+----------------------|
| -3581185414942383166 | -3581185414942383166 |
+----------------------+----------------------+

If the statement is executed for multiple rows, RANDOM returns a different value for each row. For example, in a statement that generates multiple table rows, RANDOM produces a different value for each row:

SELECT random(4711), random(4711) FROM table(generator(rowCount => 3));
+----------------------+----------------------+
|         RANDOM(4711) |         RANDOM(4711) |
|----------------------+----------------------|
| -3581185414942383166 | -3581185414942383166 |
|  1570543588041465562 |  1570543588041465562 |
| -6684111782596764647 | -6684111782596764647 |
+----------------------+----------------------+

As shown in the example above, the calls in the first row generate a different value than the calls in the second and third rows.

Note that this only occurs if you pass the same seed to RANDOM. If you don’t pass in a seed, RANDOM returns a different value for each call in the statement.

SELECT random(), random() FROM table(generator(rowCount => 3));

+----------------------+----------------------+
|             RANDOM() |             RANDOM() |
|----------------------+----------------------|
|  3150854865719208303 | -5331309978450480587 |
| -8117961043441270292 |   738998101727879972 |
|  6683692108700370630 |  7526520486590420231 |
+----------------------+----------------------+