Seeding a Random Number: Quick Tip

Posted January 29, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Shot of two businesswomen using a digital tablet together during a collaboration at work
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/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm

Have fun!