Tuesday, January 6, 2009

Creating a gaussian random number in SQL Server 2005

I've been looking into this for a while, but so far I couldn't find anyone having posted a working sample of Gaussian random number generator ( and I need it for my probject)

As a bonus I also show how to modify the box-muller algorithm so that the resulting random numbers will conform to a given mean and standard deviation. It's very simple although it wasn't as intuitive as I wished, personally.

-------------------------CODE BELOW---------------------------

-- =============================================
-- Author: Alwyn Aswin
-- Create date: 01/02/2009
-- Description: Generate a normally distributed random number.
-- NOTE: Please leave the author's attribution, if you copy this code.
-- =============================================
@Mean float = 0
,@StdDev float = 1
,@BMRand float out
--@choice is the variable used to store the random number to return
declare @choice float, @store float, @choiceid uniqueidentifier

--checks to see if a box muller random number was already cached from previous call.
select top 1 @choiceid = randomid, @choice = random from boxmullercache
if(@choice is not null) -- if we do, delete that entry, since it's useable only once.
print 'loading from cache'
delete from boxmullercache
where randomid = @choiceid
else --otherwise, generate a pair of box muller random number.
print 'generate new ones'
declare @MethodChoiceRand float
set @MethodChoiceRand = rand()

--We re-roll if we get a 0, and use 0.5 as the cutoff point.
while @MethodChoiceRand = 0
set @MethodChoiceRand= rand()

-- Reroll if @MethodChoiceRand = 0, this will ensure that the interval, may be divided into 2 groups with equal number of members.
-- AND it has the advantage of removing the problematic ln(0) error from the Box-Muller equation.
declare @rand1 float, @rand2 float
select @rand1 = rand(), @rand2 = rand()
while @rand1 = 0 or @rand2 = 0
select @rand1 = rand(), @rand2 = rand()

declare @normalRand1 float, @normalRand2 float
SELECT @normalRand1 = sqrt(-2 * log(@rand1)) * cos(2*pi()*@rand2)
,@normalRand2 = sqrt(-2 * log(@rand1)) * sin(2*pi()*@rand2)

print 'box muller no 1:' + convert(varchar,@normalRand1) + ', box muller no 2:' + convert(varchar,@normalRand2)
--RandomlySelects which one to store, which one to save.

if @MethodChoiceRand <= 0.5
print 'choice 1'
select @choice = @normalRand1, @store = @normalRand2
else if @MethodChoiceRand > 0.5
print 'choice 2'
select @choice = @normalRand2, @store = @normalRand1

--stores the other pair into the cache to be retrieved during subsequent call to this method.
insert into boxmullercache (randomid, random)
values (newid(),@store)

--fix up the random number, so that it should have the correct mean and standard deviation.
set @BMRand = @choice * @stddev + @mean


I leave out the creation of the cachetable to the reader. You need to create a table to hold the other of the 2 values created via the Box-Muller algorithm! It should be fairly straight forward, it just needs an ID and a float column, which can be deduced from the code above.

Please feel free to comment or suggest ways on improving the code.

No comments:

Post a Comment