Seeding a Random Integer

Jim_Knicely

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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm

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

Have fun!