8.4.8 RANDOM_NORMAL

Returns pseudorandom numbers that follow a normal distribution with an average μ, and a standard deviation σ.

There are some scalar functions, including RANDOM_NORMAL, that return pseudorandom numbers. Check the differences in the specifications among those scalar functions that return pseudorandom numbers, and then use the scalar function that is most suitable for your purpose. For details about the differences in the specifications among the scalar functions that return pseudorandom numbers, see (6) List of scalar functions that return pseudorandom numbers in 8.4.5 RANDOM.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Notes
(5) Example

(1) Specification format

scalar-function-RANDOM_NORMAL ::= RANDOM_NORMAL([average-μ,standard-deviation-σ])
 
  average-μ ::= value-expression
  standard-deviation-σ ::= value-expression

To Page Top

(2) Explanation of specification format

average-μ:

Specifies an average, μ. If this argument is omitted, average-μ is assumed to be 0.

The following rules apply:

Specify average-μ in the form of a value expression. For details about value expressions, see 7.20 Value expression.
Specify numeric data for average-μ. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.
If you specify only a single dynamic parameter for average-μ, the DOUBLE PRECISION type is assumed.

standard-deviation-σ:

Specifies a standard deviation, σ. If the argument is omitted, standard-deviation-σ is assumed to be 1 (standard normal distribution).

The following rules apply:

Specify standard-deviation-σ in the form of a value expression. For details about value expressions, see 7.20 Value expression.
Specify numeric data for standard-deviation-σ. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.
Specify a value greater than or equal to 0 for standard-deviation-σ.
If you specify only a single dynamic parameter for standard-deviation-σ, the DOUBLE PRECISION type is assumed.

To Page Top

(3) Rules

The data type of the execution result is the DOUBLE PRECISION type.
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If average-μ or standard-deviation-σ is a null value, the execution result will also be a null value.
After converting both average-μ and standard-deviation-σ to the DOUBLE PRECISION type, calculate the execution result.
If the execution result can no longer be expressed as the data type of the execution result, an overflow error occurs.

To Page Top

(4) Notes

This scalar function is not suitable for use in encryption.

To Page Top

(5) Example

Example

For table T1, determine the DOUBLE PRECISION-type values that follow a normal distribution whose average-μ is 30 and whose standard-deviation-σ is 20.

Note that every time you execute the SELECT statement, the values of the execution results change.

SELECT RANDOM_NORMAL(30,20) FROM "T1"

[Figure]

To Page Top