Seeding a Random Integer: Quick Tip

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

The RANDOM function returns a uniformly-distributed random number x, where 0 <= x < 1 and the RANDOMINT functions returns one of the n integers from 0 through n – 1.

In yesterday’s tip we learned that we can use the SEEDED_RANDOM function to re-use generated numbers. There is not a SEEDED_RANDOMINT function, but that’s not a problem. Create your own!

Example: dbadmin=> SELECT random() random1, random() random2, random() random3; random1 | random2 | random3 -------------------+-------------------+------------------- 0.206615947652608 | 0.308142327470705 | 0.124399376800284 (1 row) 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) dbadmin=> CREATE OR REPLACE FUNCTION seeded_randomint (x INT, y INT) RETURN INT dbadmin-> AS dbadmin-> BEGIN dbadmin-> RETURN FLOOR(SEEDED_RANDOM(x)*(y)); dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT seeded_randomint(1, 100) random1, seeded_randomint(1, 100) random1, dbadmin-> seeded_randomint(2, 100) random2, seeded_randomint(2, 100) random2; random1 | random1 | random2 | random2 ---------+---------+---------+--------- 41 | 41 | 43 | 43 (1 row) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm

Have fun!