RANDOM Number Generator Function Teradata

Compatibility: Teradata Extension

The RANDOM function generates a random number that is inclusive for the numbers specified in the SQL that is greater than or equal to the first argument and less than or equal to the second argument.

The RANDOM function may be used in the SELECT list, in a CASE, in a WHERE clause, in a QUALIFY, in a HAVING and in an ORDER BY.

The syntax for RANDOM is:

RANDOM(<low-literal-value>, <high-literal-value>)

Although RANDOM can be used in many parts of an SQL statement, some constructs would not make sense when used together. For instance, the following is meaningless:

SEL RANDOM(1,20) HAVING RANDOM(1,20) ;

At the same time, used creatively it can provide some powerful functionality within SQL.

The next SELECT uses the RANDOM function to return a random number between 1 and 20:

SELECT RANDOM(1, 20); 1 Row Returned

RANDOM Number Generator Function

The next SELECT uses RANDOM to randomly select 1% of the rows from the table:

SELECT * FROM Sales_tableWHERE RANDOM(1, 100) = 1;
2 Row Returned

SELECT uses RANDOM to randomly select 1% of the rows from the table

There is roughly a 1% (1 out of 100) chance that a row will be returned using RANDOM in the WHERE clause, completely at random. Since SAMPLE randomly selects rows out of spool, currently RANDOM will be faster than SAMPLE. However, SAMPLE will be more accurate regarding the number of rows being returned with both the percent and row count. There is discussion that NCR is changing SAMPLE to randomly select from the AMPs instead of from spool. When this occurs, their performance characteristics should be more similar.

The next example uses RANDOM to randomly set the value in the column to a random number between 1000 and 3000 for a random 10% of the rows:

UPDATE Sales_table SET Daily_Sales = RANDOM(1000, 3000) WHERE RANDOM(1, 100) BETWEEN 1 and 10;

This last example uses RANDOM to randomly generate a number that will determine which rows from the aggregation will be returned:

SELECT Product_ID, COUNT(daily_sales) FROM Sales_table GROUP BY 1 HAVING COUNT(daily_sales) > RANDOM(1, 10) ; 2 Rows Returned

RANDOM to randomly generate a number that will determine which rows from the aggregation will be returned

Then, on the very next execution of the same SELECT, the following might be returned:

No Rows Returned

Whenever a random number is needed within the SQL, RANDOM is a great tool.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics