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