Seeding a Random Number

Jim_Knicely

The RANDOM function returns a uniformly-distributed random number x, where 0 <= x < 1.

Example:

dbadmin=> SELECT random() random1, random() random2, random() random3;
      random1      |      random2      |      random3
-------------------+-------------------+-------------------
0.206615947652608 | 0.308142327470705 | 0.124399376800284
(1 row)

dbadmin=> SELECT random() random1;
      random1
-------------------
0.517856472404674
(1 row)

Notice above that each time I called the RANDOM function I got a new random value. But what if I need to generate a random number and use it in multiple places in my query? For this case, you can use the SEEDED_RANDOM function.

dbadmin=> SELECT seeded_random(1) random1, seeded_random(1) random1,
dbadmin->        seeded_random(2) random2, seeded_random(2) random2;
      random1      |      random1      |     random2      |     random2
-------------------+-------------------+------------------+------------------
0.417021998437122 | 0.417021998437122 | 0.43599490262568 | 0.43599490262568
(1 row)

You can even reference the same seeded random number in subsequent SQL statements in the same session! That is, I can get the same random numbers RANDOM1 and RANDOM2 that I generated above in a new query:

dbadmin=> SELECT seeded_random(1) random1, seeded_random(2) random2;
      random1      |     random2
-------------------+------------------
0.417021998437122 | 0.43599490262568
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm

Have fun!