Declaration
NORMAL(<mean> , <stddev> , <gen>)
Description
This function returns a floating-point number that follows a normal distribution (also known as a Gaussian distribution).
meanindicates the mean, andstddevindicates the standard deviation.- The values of
meanandstddevmust be scalar values that do not change during row iteration. For example, integer or floating-point constants, and scalar functions.- The values of
meanandstddevcan be1,2, orABS(-10). In PL, they can also be@v1,1+@v3, and so on. - The values of
meanandstddevcannot be expressions such asRAND(),RANDOM(),RAND(2),t1.c1,ABS(-t1.c1), or1+c2.
- The values of
- The random value generation algorithm is based on
std::normal_distributionand uses the "Marsaglia Polar Method," which consumes a significant amount of CPU. genis a numeric generation function, typically generated using theRANDOM()function. If the input value is a constant, the return value of theNORMAL()function is also a fixed value.
Examples
The following example fetches values from one million normally distributed random numbers (retaining only one decimal place) and counts the occurrences.
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 shows generating one million random values following a normal distribution, which 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)