Generating Non-uniform Random Numbers with SQL

Question

Generating Non-uniform Random Numbers with SQL

Dwain Camps

SSC Guru

Points: 86948
More actions
July 2, 2012 at 10:24 pm

#271934

Comments posted to this topic are about the item Generating Non-uniform Random Numbers with SQL
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 1

A stunning contribution to the discussion Dwain. The SQL Server world just got a little better.

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

GPO (7/2/2012)
A stunning contribution to the discussion Dwain. The SQL Server world just got a little better.

Thanks GPO, for the kudos and the inspiration! The article was fun to research and to write.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Nice article but a few charts of what the distributions look like would have been nice.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Excellent contribution Dwain; I added this to my toolbox!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Etopap SSC Rookie Points: 46 More actions · Answer 5

Dwain, awesome article! Fun to read. Thanks a lot for sharing it.

Sue Amin SSC Enthusiast Points: 152 More actions · Answer 6

Thank you for showing how to represent Mathematics with SQL. Reminded me of Fortran! Awesome article... I need to study and understand certain areas of this article. Good research topic. Sue

G-Dub Mr or Mrs. 500 Points: 504 More actions · Answer 7

Extremely useful! Thank you, thank you, thank you!

Bryant McClellan SSCarpal Tunnel Points: 4397 More actions · Answer 8

Excellent post, both for the concise definitions and the code explanations.

For the Poisson distributions consider the use of a CLR. That my eliminate the side-effects issue.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

This is excellent Dwain!!! Definite addition to the toolbox.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Lowell and Sean - If you big dogs thought this stuff was good enough for your toolboxes, obviously I have done something right!

About time too, as I've been adding stuff from your posts into my toolbox for a long time now. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

Sue Amin (7/3/2012)
Thank you for showing how to represent Mathematics with SQL. Reminded me of Fortran! Awesome article... I need to study and understand certain areas of this article. Good research topic. Sue

I used to teach FORTRAN and I'm really into applying SQL to non-traditional, mathematically inclined problems. I hope it was useful to you!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

G Bryant McClellan (7/3/2012)
Excellent post, both for the concise definitions and the code explanations.
For the Poisson distributions consider the use of a CLR. That my eliminate the side-effects issue.

Bryant,

There is a way to generate the Poisson using Knuth's algorithm in a stored procedure, however it is then awkward to do anything with the results because you'd need to do something like this:

INSERT INTO #MyPoisson (SeqNo, URN, PoissonRN)

EXEC RN_POISSON RAND(CHECKSUM(NEWIDE())), 1000000

So probably your suggestion of using a CLR, provided there is no other alternative than Knuth, is probably a good suggestion. Does this mean you're volunteering one? Inquiring minds want to know. 😛

Thanks for stopping by!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

Jonathan, Etopap and Gwade,

Thanks for your interest, as well as taking the time to read the article. Hopefully there's something useful for you.

Wanna see my charts of the cumulative distribution functions too?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 14

Don't suppose that picture of the fish is a veiled reference to poisson?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell