Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Rand() function error Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, February 15, 2010 2:10 PM
 Grasshopper Group: General Forum Members Last Login: Monday, June 17, 2013 9:13 PM Points: 21, Visits: 69
 Thanks Jeff.That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing.
Post #865715
 Posted Monday, February 15, 2010 2:52 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 11:06 AM Points: 7,032, Visits: 14,259
 stan.teitelbaum (2/15/2010)Thanks Jeff.That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing.I've actually used both. If you think about it, using RAND(Checksum(NewID())) will only generate as many unique values as Checksum(NewID()) in any given run since Checksum(NewID()) is what generates the "unique seed". So it comes down to what kind of numbers do you need? Is it better to work from a float and muliply it out, or start with a big number and make it smaller to fit your actual allowable range. Since they're both basically the same, I tned to work with what is fastest to generate (i.e. Jeff's method listed above).By the way - RAND() isn't random at all. For example, try running this:`select rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)`In other words - it generates a predictable result for the same seed, even on separate runs and on different sessions. The only thing that makes it "seem" random is that it will "pick" a seed during different runs if you don't pass it a seed.It also apparently has something that only uses positive seeds , since RAND(1) = RAND(-1)So again, you really don't get anything other that somewhat slower perf out of using RAND(). ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #865738
 Posted Monday, February 15, 2010 2:57 PM
 Grasshopper Group: General Forum Members Last Login: Monday, June 17, 2013 9:13 PM Points: 21, Visits: 69
 thanks for clarifying Matt.
Post #865745
 Posted Thursday, November 15, 2012 12:28 PM
 SSC Rookie Group: General Forum Members Last Login: Friday, December 06, 2013 3:18 PM Points: 25, Visits: 43
 Thought I'd share ... use it as you see fit.I wrote this for something else I need to poc, etc.Step 1:`CREATE VIEW dbo.vRandASSELECT RAND() as [RANDOM]`Step 2:`USE SomeDBGO/* =========================================================================================================================Author: Goran BiljetinaCreate date: 2012-11-13Description: Random number generator between 1 and base^powerLIMITS: Returns positive integers, base and exponent inputs are integers as well (that can be modified as needed)========================================================================================================================= */CREATE FUNCTION dbo.fnRandNumber( @base bigint = 10 --> base number, 10 by default ,@exp int --> exponent value, e.g. max 18 when base = 10)RETURNS bigintASBEGIN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------declare @r int, @ef int, @i intset @r= @exp+1 --> range or maximum sequence number, how many multiplication loops?set @ef= 0 --> initial multiplication factorset @i= 1 --> increment of multiplication factor and decrement of range or sequencedeclare @Power10 table (Number bigint, Sequence int); --> generate sequence buckets based on base number and power value input begin while @r >= 0 begin insert into @Power10 (Number,Sequence) select power(@base,@ef),(@ef) set @r = (@r - @i) set @ef = (@ef + @i) if @r <= 0 BREAK else CONTINUE end end--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> GENERATE RANDOM NUMBER FROM 1 to base^power (max exp=18 when base=10, 8 byte int limit)declare @rspan bigint, @rnumber bigint, @rseqmax int, @rseq int, @rseed bigintset @rseqmax = @exp --> get sequence or power limitset @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) --> get random sequence w/in limitset @rseed = (select number from @Power10 where Sequence = @rseq) --> get number seed w/in sequenceset @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) --> get rand span w/in rand number seedset @rnumber = FLOOR((select * from vRand)*@rspan)+1 --> get rand number w/in spanRETURN(@rnumber);ENDGO`Step 3.`--> SAMPLE SETS:select dbo.fnRandNumber(10,3)[(10,3)];select dbo.fnRandNumber(10,5)[(10,5)];select dbo.fnRandNumber(10,9)[(10,9)]; --> max for base 10 (if return 8 byte signed integer)select dbo.fnRandNumber(10,18)[(10,18)];select dbo.fnRandNumber(2,8)[(2,8)];select dbo.fnRandNumber(2,16)[(2,16)];select dbo.fnRandNumber(2,32)[(2,32)]; --> max for base 2 (if return 8 byte signed integer)select dbo.fnRandNumber(2,62)[(2,62)];`And this, basically the same thing in JS:`// get random number within provided base + exponent// by Goran Biljetina// code as is :)function isEmpty(value){ return (typeof value === "undefined" || value === null);}var numSeq = new Array();function add(num,seq){ var toAdd = new Object(); toAdd.num = num; toAdd.seq = seq; numSeq[numSeq.length] = toAdd;}function fillNumSeq (num,seq){ var n; for(i=0;i<=seq;i++){ n = Math.pow(num,i); add(n,i); }}function getRandNum(base,exp){ if (isEmpty(base)){ console.log("Specify value for base parameter"); } if (isEmpty(exp)){ console.log("Specify value for exponent parameter"); } fillNumSeq(base,exp); var emax; var eseq; var nseed; var nspan; emax = (numSeq.length); eseq = Math.floor(Math.random()*emax)+1; nseed = numSeq[eseq].num; nspan = Math.floor((Math.random())*(Math.random()*nseed))+1; return Math.floor(Math.random()*nspan)+1;}console.log(getRandNum(10,20),numSeq);//getRandNum(-10,20);//console.log(getRandNum(-10,20),numSeq);//console.log(numSeq);`
Post #1385306
 Posted Thursday, November 15, 2012 7:44 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, November 15, 2012 7:33 PM Points: 9, Visits: 0