Categories:

Data Generation Functions

NORMAL

Returns a normal-distributed floating point number, with specified mean and stddev (standard deviation).

This function is related to, but different from, the similar RANDOM function.

• When RAND is called a large number of times, the results are more or less evenly distributed over the range of possible values. For example, the number of results with values between 1000 and 2000 is similar to the number of values between 2000 and 3000.

• When NORMAL is called a large number of times, the distribution of the results is likely to approximate a “normal” curve (sometimes called a “bell-shaped curve”). The center of the curve and the “breadth” of the curve are influenced by the mean and stddev parameters. Values closer to the specified mean are more likely to occur than values far from the mean.

Syntax

NORMAL( <mean> , <stddev> , <gen> )

Arguments

mean

This is the value that you would like the output values centered around.

stddev

This specifies the width of one standard deviation. For example, if you choose a mean of 0.0 and a standard deviation of 1.0, then if you call this function many times, approximately 68.2% of returned values will be between -1.0 and +1.0 (i.e. within one standard deviation of the mean).

Similarly, if you choose a mean of 5.0 and a standard deviation of 2, then approximately 68.2% of values will be between 3.0 and 7.0.

gen

This specifies the generator expression for the function. For more information, see Usage Notes.

Usage Notes

As the number of calls increases, the accumulated results tend to be closer to a true “normal” distribution, assuming that the gen function generates evenly-distributed values.

Examples

This shows typical usage with a mean of 0 and a standard deviation of 1:

SELECT normal(0, 1, random()) FROM table(generator(rowCount => 5));

+------------------------+
| NORMAL(0, 1, RANDOM()) |
|------------------------|
|           0.227384164  |
|           0.9945290748 |
|          -0.2045078571 |
|          -1.594607893  |
|          -0.8213296842 |
+------------------------+

This shows that if the gen parameter is a constant, then the output is a constant:

SELECT normal(0, 1, 1234) FROM table(generator(rowCount => 5));

+--------------------+
| NORMAL(0, 1, 1234) |
|--------------------|
|      -0.6604156716 |
|      -0.6604156716 |
|      -0.6604156716 |
|      -0.6604156716 |
|      -0.6604156716 |
+--------------------+