Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Rand() function error Expand / Collapse
Author
Message
Posted Monday, February 15, 2010 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #865738
Posted Monday, February 15, 2010 2:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:15 PM
Points: 25, Visits: 48
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 :)

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9, Visits: 0
Thank you for your posts
Post #1385426
Posted Thursday, November 15, 2012 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9, Visits: 0
thank you




Post #1385427
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse