Generate Gaussian Random Numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate Gaussian Random Numbers

Author

Topic

badman3k
Starting Member

15 Posts

Posted - 2009-06-30 : 11:41:49

I need a query that I can run one time only to populate a table with random data, based on some parameters that I wish to set.

However, I'm struggling with the first hurdle, generating a random number based on a gaussian distribution, where I can provide the mean and standard deviation.

Does anyone know how to do a random number based on the gaussian distribution in an 'insert' query?

Any help would be greatly appreciated. Many thanks in advance,
Richard

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-30 : 12:36:15

[code]
DECLARE @x1 real
DECLARE @x2 real
DECLARE @y1 real
DECLARE @y2 real
-- I'm not sure if these will alwaysbe between 0 and 1
SET @x1 = (select ABS(checksum(newid())/10000000000.0))
SET @x2 = (select ABS(checksum(newid())/10000000000.0))

SET @y1 = sqrt( - 2 * log(@x1) )* cos( 2 *pi() *@x2 )

SET @y2 = sqrt( - 2* log*(@x1) ) sin( 2 *pi* @x2 )
[/code]

Jim

badman3k
Starting Member

15 Posts

Posted - 2009-06-30 : 12:47:25

quote:

Originally posted by jimf


SET @x1 = (select ABS(checksum(newid())/10000000000.0))  
SET @x2 = (select ABS(checksum(newid())/10000000000.0))

can't i use the select RAND() for the x1 and x2?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-30 : 12:59:16

You can, but shortly Peso or Visakh or someone else smart will educate us both as to the best way to generate a random number. I remember reading somewhere that checksum(newid()) is more random than RAND, but again, I'd wait for the smarter answer.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:06:24

Rand always start with same seed.
You probably get same first five random numbers if you restart SQL Server, do select rand() five times, write the numbers down, restart SQL Server and do the same select 5 times.
Do the numbers match?

N 56°04'39.26"
E 12°55'05.63"

badman3k
Starting Member

15 Posts

Posted - 2009-07-01 : 04:05:08

quote:
Originally posted by Peso

Rand always start with same seed.
You probably get same first five random numbers if you restart SQL Server, do select rand() five times, write the numbers down, restart SQL Server and do the same select 5 times.
Do the numbers match?

Peso, thanks for giving the reason to using checksum, I haven't done the restart 5 times thing. However, as I only need to run the query once and only once, just to populate the table, will RAND not be sufficient?

As I intend to run the 'gen_gaussian_num' query within a loop for 890+ times without restarting the db and as I'm sure there'll be several attempts at getting the query correct, I'll probably have a couple of 100 RAND() calls well before I get through to generating my data, shouldn't RAND be sufficiently random?

I realise that if I want a truly random number that I'd need to look at the best possible "rand" function, but as I just need random data to pad a table with sufficient data samples so my analysis of new data works correctly, I'd say that RAND would suffice.

Jimf, thanks for the info. Am I right that you're doing the Box Muller transformation, to turn a uniform distribution to a Gaussian?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources