NORMAL

Syntax

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

Purpose

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 floating-point constants or scalar functions.
  • mean and stddev can be 1, 2, or ABS(-10). In PL, they can also be @v1 or 1+@v3.
  • 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 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 following a normal distribution, retains only one decimal place, and counts the numbers.

obclient> 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 |      237 |
|  2.7 |     6053 |
|  2.8 |    60273 |
|  2.9 |   241467 |
|    3 |   383862 |
|  3.1 |   241592 |
|  3.2 |    60447 |
|  3.3 |     5837 |
|  3.4 |      224 |
|  3.5 |        4 |
+------+----------+
11 rows in set

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

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