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 a different value for each call, both within a row and across rows.

  • Calling RANDOM repeatedly with the same seed produces the same value each time for a given input row. However, different rows in the output have different values.

  • 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

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) for individual rows:

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(<seed>) 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.

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 values generated by calling RANDOM(<seed>).

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

Display the values. 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 |
+---------------------+---------------------+

Note that if you do not pass a seed, then the values are different within each row, as well as different across the rows.