RANDNORM
Updated: 31 January 2012
Use RANDNORM to calculate a pseudo-random number based on the normal distribution.
Syntax
SELECT [wctMath].[wct].[RANDNORM](
<@mu, float,>
,<@sigma, float,>)
Arguments
@mu
is mean of a normal distribution. @mu is an expression of type float or of a type that can be implicitly converted to float.
@sigma
is the standard deviation a normal distribution. @sigma is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· RANDNORM is non-deterministic, so results will vary.
· Unlike the built-in SQL Server RAND function, RANDNORM is evaluated for every row in the resultant table.
· To generate a series of pseudo-random normal numbers, consider using the SeriesFloat function.
· To generate a pseudo-random number between zero and one, consider using the XLeratorDB RAND function.
· To generate a pseudo-random integer between two integers, consider using the RANDBETWEEN function.
· If @mu is NULL it will be set to zero.
· If @sigma is NULL it will be set to one.
Example
This example demonstrates the difference between using the built-in SQL Server function RAND and the XLeratorDB RANDNORM function.
with mycte as
(
select 1 as seq
union all
select seq + 1
from mycte
where seq < 15
)
select seq
,rand() as [SQL Server RAND]
,wct.randnorm(NULL, NULL) as [XLDB RANDNORM]
from mycte
This produces the following result.
seq SQL Server RAND XLDB RANDNORM
----------- ---------------------- ----------------------
1 0.786185655285411 0.132515769270168
2 0.786185655285411 0.55422759842865
3 0.786185655285411 0.00279640603651261
4 0.786185655285411 1.20615188572666
5 0.786185655285411 0.258279276822493
6 0.786185655285411 0.328259483474435
7 0.786185655285411 0.0704535933678625
8 0.786185655285411 -0.325716938056977
9 0.786185655285411 -0.276812639860543
10 0.786185655285411 0.487789164610815
11 0.786185655285411 0.303768736287741
12 0.786185655285411 0.79775224936946
13 0.786185655285411 0.478204215978753
14 0.786185655285411 0.745115412390468
15 0.786185655285411 -0.561231528988071
Your results will be different.
In this exampe we will generate 4 random numbers for each row.
with mycte as
(
select 1 as seq
union all
select seq + 1
from mycte
where seq < 15
)
select seq
,wct.RANDNORM(NULL,NULL) as [A]
,wct.RANDNORM(100,15) as [B]
,wct.RANDNORM(72,2) as [C]
,wct.RANDNORM(50000,15000) as [D]
from mycte
This produces the following result
seq A B C D
--- ----------------- ----------------- ----------------- -------------------
1 -0.37438455959187 112.197012137904 70.0460586334768 49452.9037458025
2 0.35242562693047 96.2771935371399 72.017519378787 52025.4333293082
3 0.58187159757270 93.8010204479432 73.6484751036427 66384.5057201813
4 -0.24507083494989 100.552442469468 72.3968994750811 49969.1110176925
5 0.67990016539522 103.492544687236 69.7743425613628 49654.2304364637
6 -0.94473559022718 101.760233294853 71.1030513177572 42593.7391076126
7 -0.57363162397797 100.0563965629 66.4380195801886 49856.9245265609
8 -0.08180582927527 87.3615395039761 72.6962990907136 58191.1279599142
9 1.0092680429425 80.8457950480969 73.8710030361076 46528.7518703674
10 2.66764331693742 118.639172077512 72.5444920000896 80447.1037280331
11 0.12854384378103 104.837932040327 74.0798610244449 109923.572201717
12 -0.77086257411754 96.3579139091106 71.8940299672826 56044.7478108594
13 0.37903147013382 134.378620126305 71.5551686460212 39432.7239847204
14 -0.07790490300863 87.7613538547814 70.994167886532 47844.105807051
15 -0.59210431507451 103.671932190145 73.852650712671 44921.2034130292
Your results will be different.