Generate Random Ints in a Range (including negs)

[JimKnicely]

Hi,

Say we wanted to generate a random integer between -5 and 0, inclusive. Our first thought might be to try something like this:

Code: Select all

dbadmin=> SELECT randomint(-5);
ERROR 4163:  Non-positive value supplied to randomint: -5

As we can see the RANDOMINT function does not accept negative values as a parameter.

Turns out there is a simple formula that can be evaluated to produce a random integer that occurs within a range defined by any upper and lower bounds when the upper bounds >= lower bounds, including negatives. The formula is:

• RANDOMINT(upper bound value - (lower bound value) + 1)) + (lower bound value)

Examples:

To generate random integers between -5 and 0, inclusive, we could use this query:

Code: Select all

dbadmin=> SELECT RANDOMINT(0 - (-5) + 1) + (-5) random1,
dbadmin->        RANDOMINT(0 - (-5) + 1) + (-5) random2,
dbadmin->        RANDOMINT(0 - (-5) + 1) + (-5) random3,
dbadmin->        RANDOMINT(0 - (-5) + 1) + (-5) random4;
random1 | random2 | random3 | random4
---------+---------+---------+---------
      -2 |       0 |      -3 |      -5
(1 row)

To generate random integers between -5 and +5, inclusive, we could use this query:

Code: Select all

dbadmin=> SELECT RANDOMINT(5 - (-5) + 1) + (-5) random_1,
dbadmin->        RANDOMINT(5 - (-5) + 1) + (-5) random_2,
dbadmin->        RANDOMINT(5 - (-5) + 1) + (-5) random_3,
dbadmin->        RANDOMINT(5 - (-5) + 1) + (-5) random_4;
random_1  | random_2 | random_3 | random_4
----------+----------+----------+----------
       -5 |       -1 |        2 |        5
(1 row)

To generate random integers between -10 and 0, inclusive, we could use this query:

Code: Select all

dbadmin=> SELECT RANDOMINT(0 - (-10) + 1) + (-10) random_1,
dbadmin->        RANDOMINT(0 - (-10) + 1) + (-10) random_2,
dbadmin->        RANDOMINT(0 - (-10) + 1) + (-10) random_3,
dbadmin->        RANDOMINT(0 - (-10) + 1) + (-10) random_4;
random_1 | random_2 | random_3 | random_4
----------+----------+----------+----------
       -9 |        0 |      -10 |       -6
(1 row)

To generate random integers between -10 and -5, inclusive, we could use this query:

Code: Select all

dbadmin=> SELECT RANDOMINT(-5 - (-10) + 1) + (-10) random_1,
dbadmin->        RANDOMINT(-5 - (-10) + 1) + (-10) random_2,
dbadmin->        RANDOMINT(-5 - (-10) + 1) + (-10) random_3,
dbadmin->        RANDOMINT(-5 - (-10) + 1) + (-10) random_4;
random_1 | random_2 | random_3 | random_4
----------+----------+----------+----------
       -8 |       -5 |       -6 |      -10
(1 row)

The generic formula also works with all positive range values, so to generate random integers between 20 and 30, inclusive, we could use a query like this:

Code: Select all

dbadmin=> SELECT RANDOMINT(30 - (20) + 1) + (20) random_1,
dbadmin->        RANDOMINT(30 - (20) + 1) + (20) random_2,
dbadmin->        RANDOMINT(30 - (20) + 1) + (20) random_3,
dbadmin->        RANDOMINT(30 - (20) + 1) + (20) random_4;
random_1 | random_2 | random_3 | random_4
----------+----------+----------+----------
       25 |       28 |       30 |       22
(1 row)

The idea for the post was inspired by a question here http://www.vertica-forums.com/viewtopic.php?f=48&t=633.

Have fun!

[JimKnicely]

Hi,

A friend suggested to create a UDF that can be used to generate a random int that falls within a specified range. Great idea!

Here is an example function we can create...

Code: Select all

CREATE FUNCTION randomint_range(lower_bound INT, upper_bound INT) RETURN INT
As
BEGIN
  RETURN CASE
           WHEN upper_bound >= lower_bound THEN
             RANDOMINT(upper_bound - lower_bound + 1) + (lower_bound)
           ELSE
             NULL
         END;
END;

Here's a test of the new function:

Code: Select all

dbadmin=> CREATE FUNCTION randomint_range(lower_bound INT, upper_bound INT) RETURN INT
dbadmin-> As
dbadmin-> BEGIN
dbadmin->   RETURN CASE
dbadmin->            WHEN upper_bound >= lower_bound THEN
dbadmin->              RANDOMINT(upper_bound - lower_bound + 1) + (lower_bound)
dbadmin->            ELSE
dbadmin->              NULL
dbadmin->          END;
dbadmin-> END;
CREATE FUNCTION

Code: Select all

dbadmin=> SELECT randomint_range(-5, 5);
 randomint_range
-----------------
              -5
(1 row)

dbadmin=> SELECT randomint_range(-5, 5);
 randomint_range
-----------------
              -3
(1 row)

Code: Select all

dbadmin=> SELECT randomint_range(-10, 0);
 randomint_range
-----------------
              -8
(1 row)

dbadmin=> SELECT randomint_range(-10, 0);
 randomint_range
-----------------
             -10
(1 row)

Have fun!

[Julie]

Thank you, this is very useful.