NORMAL

Declaration

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

Description

The NORMAL function returns a floating-point number that follows a normal distribution (also known as the Gaussian distribution).

• mean specifies the mean, and stddev specifies the standard deviation.
• mean and stddev must be scalar values that do not change with row iteration. For example, they can be integer or float constants, or scalar functions.
  • mean and stddev can be 1, 2, ABS(-10), or expressions like @v1 or 1+@v3 in PL.
  • mean and stddev cannot be RAND(), RANDOM(), RAND(2), t1.c1, ABS(-t1.c1), or 1+c2.
• The random value generation algorithm is based on std::normal_distribution and uses the "Marsaglia Polar Method," which consumes a significant amount of CPU resources.
• gen is a value generation function, typically generated using the RANDOM() function. If the input value is a constant, the NORMAL() function returns a constant value.

Examples

The following example generates 1 million random numbers that follow a normal distribution, rounds them to one decimal place, and counts the occurrences.

SELECT v, COUNT(*) FROM (SELECT ROUND(NORMAL(3, 0.1, RANDOM()), 1) v FROM TABLE(GENERATOR(1000000))) x GROUP BY v ORDER BY v;
+------+----------+
| v    | COUNT(*) |
+------+----------+
|  2.5 |        4 |
|  2.6 |      238 |
|  2.7 |     5979 |
|  2.8 |    60889 |
|  2.9 |   241991 |
|  3.0 |   382951 |
|  3.1 |   241306 |
|  3.2 |    60533 |
|  3.3 |     5861 |
|  3.4 |      243 |
|  3.5 |        5 |
+------+----------+
11 rows in set (2.567 sec)

The following example generates 1 million random numbers that follow a normal distribution, which takes 1.5 seconds.

SELECT COUNT(NORMAL(0, 1, RANDOM())) v FROM TABLE(GENERATOR(1000000));
+---------+
| V       |
+---------+
| 1000000 |
+---------+
1 row in set (1.812 sec)