15.00 - RANDOM - Teradata Database

RANDOM

Purpose  

Returns a random integer number for each row of the results table.

Syntax  

where:

Syntax element …	Specifies …
lower_bound	an integer literal to define the lower bound on the closed interval over which a random number is to be selected. The limits for lower_bound range from -2147483648 to 2147483647, inclusive. lower_bound must be less than or equal to upper_bound.
upper_bound	an integer literal to define the upper bound on the closed interval over which a random number is to be selected. The limits for upper_bound range from -2147483648 to 2147483647, inclusive. upper_bound must be greater than or equal to lower_bound.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Result Type and Attributes

The data type, format, and title for RANDOM(x,y) are as follows.

Data Type	Format	Title
INTEGER	Default format for INTEGER	Random(x,y)

For information on default data type formats, see SQL Data Types and Literals.

Computation

RANDOM uses the linear congruential algorithm and 48-bit integer arithmetic.

The algorithm works by generating a sequence of 48-bit integer values, X_i, using the following equation:

where:

This variable …	Represents …
X	a random number over a defined closed interval
n	an integer >= 0
a	0x5DEECE66D
c	0xB
%	the modulo operator
m	248

Multiple RANDOM Calls Within a SELECT List

You can call RANDOM any number of times in the SELECT list, for example:

   SELECT RANDOM(1,100), RANDOM(1,100);

Each call defines a new random value.

Restrictions

The following rules and restrictions apply to the use of the RANDOM function.

RANDOM can only be called in one of the following SELECT query clauses:

WHERE

GROUP BY

ORDER BY

HAVING/QUALIFY

RANDOM cannot be referenced by position in a GROUP BY or ORDER BY clause.

RANDOM cannot be nested inside aggregate or ordered analytical functions.

RANDOM cannot be used in the expression list of an INSERT statement to create a primary index or partitioning column value.

For example:

   INSERT t1 (RANDOM(1,10),...) 

RANDOM causes an error to be reported in this case if the first column in the table is a primary index or partitioning column.

Using RANDOM as a Condition on an Index

Because the RANDOM function is evaluated for each selected row, a condition on an index column that includes the RANDOM function results in an all-AMP operation.

For example, consider the following table definition:

   CREATE TABLE t1

      (c1 INTEGER

      ,c2 VARCHAR(9))

   PRIMARY INDEX ( c1 );

The following SELECT statement results in an all-AMP operation:

   SELECT * 

   FROM t1 

   WHERE c1 = RANDOM(1,12);

Example  

Suppose you have a table named sales_table with the following subset of columns.

Store_ID	Product_ID	Sales
1003	C	20000
1002	C	35000
1001	C	60000
1002	D	50000
1003	D	50000
1001	D	35000
1001	A	100000
1002	A	40000
1001	E	30000

The following SELECT statement returns a random number between 1 and 3, inclusive, for each row in the results table.

   SELECT store_id, product_id, sales, RANDOM(1,3)

   FROM sales_table;

The results table might look like this.

Store_ID	Product_ID	Sales	RANDOM(1,3)
1003	C	20000	1
1002	C	35000	2
1001	C	60000	2
1002	D	50000	3
1003	D	50000	2
1001	D	35000	3
1001	A	100000	2
1002	A	40000	1
1001	E	30000	2