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.

-- =============================================

CREATE PROCEDURE BoxMullerRandom

@Mean float = 0

,@StdDev float = 1

,@BMRand float out

AS

BEGIN

--@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.

begin

print 'loading from cache'

delete from boxmullercache

where randomid = @choiceid

end

else --otherwise, generate a pair of box muller random number.

begin

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

begin

set @MethodChoiceRand= rand()

end

-- 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

begin

select @rand1 = rand(), @rand2 = rand()

end

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

begin

print 'choice 1'

select @choice = @normalRand1, @store = @normalRand2

end

else if @MethodChoiceRand > 0.5

begin

print 'choice 2'

select @choice = @normalRand2, @store = @normalRand1

end

--stores the other pair into the cache to be retrieved during subsequent call to this method.

insert into boxmullercache (randomid, random)

values (newid(),@store)

end

--fix up the random number, so that it should have the correct mean and standard deviation.

set @BMRand = @choice * @stddev + @mean

END

GO

------------------------------------------------------------

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