RANDNORM
Updated 2023-10-18 16:07:18.233000
Syntax
SELECT [westclintech].[wct].[RANDNORM](
<@mu, float,>
,<@sigma, float,>)
Description
Use the scalar function RANDNORM to calculate a pseudo-random number based on the normal distribution.
Arguments
@mu
is the 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 Type
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.
Examples
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.
{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SQL Server RAND","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"XLDB RANDNORM","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.132515769270168"},{"seq":"2","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.55422759842865"},{"seq":"3","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.00279640603651261"},{"seq":"4","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"1.20615188572666"},{"seq":"5","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.258279276822493"},{"seq":"6","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.328259483474435"},{"seq":"7","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.0704535933678625"},{"seq":"8","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"-0.325716938056977"},{"seq":"9","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"-0.276812639860543"},{"seq":"10","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.487789164610815"},{"seq":"11","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.303768736287741"},{"seq":"12","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.79775224936946"},{"seq":"13","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.478204215978753"},{"seq":"14","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"0.745115412390468"},{"seq":"15","SQL Server RAND":"0.786185655285411","XLDB RANDNORM":"-0.561231528988071"}]}
Your results will be different.
In this example 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.
{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"A","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"C","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"D","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","A":"-0.37438455959187","B":"112.197012137904","C":"70.0460586334768","D":"49452.9037458025"},{"seq":"2","A":"0.35242562693047","B":"96.2771935371399","C":"72.017519378787","D":"52025.4333293082"},{"seq":"3","A":"0.58187159757270","B":"93.8010204479432","C":"73.6484751036427","D":"66384.5057201813"},{"seq":"4","A":"-0.24507083494989","B":"100.552442469468","C":"72.3968994750811","D":"49969.1110176925"},{"seq":"5","A":"0.67990016539522","B":"103.492544687236","C":"69.7743425613628","D":"49654.2304364637"},{"seq":"6","A":"-0.94473559022718","B":"101.760233294853","C":"71.1030513177572","D":"42593.7391076126"},{"seq":"7","A":"-0.57363162397797","B":"100.0563965629","C":"66.4380195801886","D":"49856.9245265609"},{"seq":"8","A":"-0.08180582927527","B":"87.3615395039761","C":"72.6962990907136","D":"58191.1279599142"},{"seq":"9","A":"1.0092680429425","B":"80.8457950480969","C":"73.8710030361076","D":"46528.7518703674"},{"seq":"10","A":"2.66764331693742","B":"118.639172077512","C":"72.5444920000896","D":"80447.1037280331"},{"seq":"11","A":"0.12854384378103","B":"104.837932040327","C":"74.0798610244449","D":"109923.572201717"},{"seq":"12","A":"-0.77086257411754","B":"96.3579139091106","C":"71.8940299672826","D":"56044.7478108594"},{"seq":"13","A":"0.37903147013382","B":"134.378620126305","C":"71.5551686460212","D":"39432.7239847204"},{"seq":"14","A":"-0.07790490300863","B":"87.7613538547814","C":"70.994167886532","D":"47844.105807051"},{"seq":"15","A":"-0.59210431507451","B":"103.671932190145","C":"73.852650712671","D":"44921.2034130292"}]}
Your results will be different.