Generating Non-uniform Random Numbers with SQL

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • GPO

    SSCarpal Tunnel

    Points: 4556

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

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

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

  • Lowell

    SSC Guru

    Points: 323450

    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

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

  • Sue Amin

    SSC Enthusiast

    Points: 152

    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

    SSC-Addicted

    Points: 474

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

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4281

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

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

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

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

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

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

    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

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

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