Categories:

Data generation functions

RANDOM

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

Syntax

RANDOM([seed])
Copy

Arguments

Optional:

seed

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

If no seed is provided, a random seed is chosen in a platform-specific manner.

Usage notes

  • If a SQL statement calls RANDOM with the same seed for each row, then RANDOM returns a different value for each row, even though the seed is the same.

  • If a SQL statement calls RANDOM more than once with the same seed for the same row, then RANDOM returns the same value for each call for that row. For example, the following returns the same value twice for each row: select random(42), random(42) from table1.

    See the example below.

  • If a statement that calls RANDOM is executed more than once, there is no guarantee that RANDOM will generate the same set of values each time. This is true whether or not you specify a seed.

    Even if the same statement is called with the same data, RANDOM can produce different values. For example, this can occur when:

    • The number of worker threads is different.

    • The rows are processed in a different order.

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

The following example calls RANDOM without a seed. The output for each row is different.

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

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

The following example calls RANDOM with the same seed for each row. Although the seed is a constant, the output for each row is still different.

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

The following example calls RANDOM multiple times within a single statement and does not use a seed. RANDOM returns different values within each row, as well as different values for different rows:

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

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

The following example calls RANDOM multiple times within a single statement and uses the same seed for each of those calls. RANDOM returns the same value within each row, but different values for different rows:

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