Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server RANDNORM function


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.
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service