- Categories:
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 themean
andstddev
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 | +--------------------+