SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rand() function error


Rand() function error

Author
Message
db042188
db042188
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 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.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12773 Visits: 18584
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)
go
select rand(1)
go
select rand(1)
go
select rand(1)
go
select rand(1)
go
select 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?
db042188
db042188
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 69
thanks for clarifying Matt.
goran.biljetina
goran.biljetina
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 50
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.vRand
AS
SELECT RAND() as [RANDOM]


Step 2:

USE SomeDB
GO

/*
=========================================================================================================================
Author: Goran Biljetina
Create date: 2012-11-13
Description: Random number generator between 1 and base^power
LIMITS: 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 bigint
AS
BEGIN

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
declare @r int, @ef int, @i int
set @r= @exp+1 --> range or maximum sequence number, how many multiplication loops?
set @ef= 0 --> initial multiplication factor
set @i= 1 --> increment of multiplication factor and decrement of range or sequence

declare @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 bigint

set @rseqmax = @exp --> get sequence or power limit
set @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) --> get random sequence w/in limit
set @rseed = (select number from @Power10 where Sequence = @rseq) --> get number seed w/in sequence

set @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) --> get rand span w/in rand number seed
set @rnumber = FLOOR((select * from vRand)*@rspan)+1 --> get rand number w/in span

RETURN(@rnumber);

END
GO




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 Smile

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);


starshayiz
starshayiz
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 0
Thank you for your posts
starshayiz
starshayiz
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 0
thank you





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search