NORMAL

Syntax

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

Purpose

NORMAL() returns a floating-point number from a normal distribution, or Gaussian distribution.

• mean indicates the mean and stddev indicates the standard deviation.
• mean and stddev must be scalar values that do not change with row iteration. For example, they can be integral or floating-point constants and scalar functions.
  • The values of mean and stddev can be 1, 2 or ABS(-10). In PL, they can also be @v1 or 1+@v3.
  • The values of mean and stddev cannot be RAND(), RANDOM(), RAND(2), t1.c1, ABS(-t1.c1), or 1+c2.
• The random value generation algorithm works based on std::normal_distribution and uses the Marsaglia polar method. This algorithm consumes significant CPU resources.
• gen is a numeric value generation function. Generally, the RANDOM() function is used. If the input value is a constant, NORMAL() returns a fixed value.

Examples

The following example rounds 1 million random numbers from a normal distribution to one decimal place and then 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 |      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

The following example generates 1 million random numbers from a normal distribution. The process takes about 1.5 seconds.

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