Seeding a Random Number
Jim_Knicely
- Select Field - Administrator
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!
0