| Author |
 Topic  |
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
 Posted - 04/15/2002 : 10:38:41
|
Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.
create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on
declare @numbers table (number_id int identity(1,1), value int)
if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end
set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))
while (select count(*)
from @numbers) < (@upper - @lower + 1)
begin
insert into @numbers (value)
select (@upper - @lower + 1) * rand() + @lower
end
select @number = value
from @numbers
order by newid()
go
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/16/2002 : 12:38:54
|
Er, maybe you could explain what this is doing, cause I've looked at it a few times and it still doesn't make sense to me.
|
 |
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 04/16/2002 : 15:24:15
|
you mean comments?! :-p
first it creates a table with @n number of entries where @n = @upper - @lower + 1 ... each entry is a random number between @lower and @upper. next a random entry is selected from the table and returned.
the reason it had to be done this way is that the first call to RAND() usually happened so fast from a calling function (random_password) that it returned the same value since the time didn't really change. So this way it goes and selects a random spot from the table.
Edited by - onamuji on 04/16/2002 15:25:42 |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/17/2002 : 04:24:53
|
(Trying not to think about what happens when you want a number between 1 and 1000000...)
If you go to the other extreme, and consider the common case where it's selecting 0 or 1 -- flipping a virtual coin -- then there's still the problem of speed. Try this, for example:
DECLARE @r int, @i int
CREATE TABLE #RandTest (
id int IDENTITY(1,1) PRIMARY KEY,
r int
)
SET NOCOUNT ON
SET @i = 0
WHILE @i < 10000
BEGIN
EXEC random_number 0, 1, @r OUTPUT
--SET @r = FLOOR(RAND()*2.0)
INSERT INTO #RandTest (r) VALUES (@r)
SET @i = @i + 1
END
SET NOCOUNT OFF
select T1.r, T2.r, COUNT(*)
FROM #RandTest AS T1
INNER JOIN #RandTest AS T2 ON T1.id = T2.id - 1
GROUP BY T1.r, T2.r
DROP TABLE #RandTest
Creates a table of 10000 samples, then self joins it to compare each value with the next. Now since 0 and 1 are equally likely outcomes, the sequences 00, 01, 10 and 11 should be equally likely if the numbers are independent. However, I get significant skew towards the value staying the same for adjacent samples:
r1 r2 ct
0 0 3588
1 1 3698
1 0 1356
0 1 1357
Compare this with what happens when the line using RAND() is uncommented instead of the call to random_number. This is still a noticeable effect when it's selecting from 0 to 9.
Edited by - Arnold Fribble on 04/17/2002 04:26:03 |
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 04/17/2002 : 10:22:18
|
best result: xp_random_number ... write one... heh heh :-p
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/17/2002 : 10:39:04
|
quote:
best result: xp_random_number ... write one... heh heh :-p
Eh?
|
|
|
|
robvolk
SQLTeam MVY/MIA
USA
12325 Posts |
Posted - 04/17/2002 : 10:49:19
|
Don't know if this helps, but I tried this and it seems to give some decent results:
SELECT convert(varbinary, newID()) ^ 10000
You can use any bitwise operator, and change the 2nd operand to suit your tastes. If you use this, for example:
SELECT convert(varbinary, newID()) & 127
You'll get values between 0 and 127. The only problem with bitwise AND (&) is filling out the bit pattern properly.
|
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 04/17/2002 : 10:59:41
|
great solution ... great possibilities
Edited by - onamuji on 04/17/2002 11:07:49 |
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 04/17/2002 : 11:17:07
|
so then a possibility being:
set nocount on
declare @compare int
if @lower = @upper
begin
set @number = @lower
return
end
if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end
- proper bit map
set @compare = power(ceiling(log(@upper)/log(2)), 2) - 1
set @number = convert(int, convert(varbinary, newid()) & @compare)
-- until a valid number is returned
while @number < @lower or @number > @upper
begin
set @number = convert(int, convert(varbinary, newid()) & @compare) + @lower
end
go
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/17/2002 : 11:23:01
|
create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on
declare @r float
if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end
set @number = FLOOR(RAND(CONVERT(varbinary, NEWID()) & -1) *
(@upper-@lower+1)) + @lower
go
|
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 04/17/2002 : 11:31:07
|
anything is possible ;-)
|
|
|
|
robvolk
SQLTeam MVY/MIA
USA
12325 Posts |
Posted - 04/17/2002 : 11:31:07
|
I added a little to the original, I'm sticking with bitwise OR (^):
SELECT (convert(varbinary, newid()) ^ 127) % 100
It will give you values from -99 to +99. You can add an offset to it, or use ABS() on it.
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/17/2002 : 11:45:45
|
Rob, this will pick 0 too frequently for small modulus values.
If you put the ABS around the convert it will fix most of the problem.
Why the ^ 127, though?
Edited by - Arnold Fribble on 04/17/2002 11:54:05 |
|
|
|
robvolk
SQLTeam MVY/MIA
USA
12325 Posts |
Posted - 04/17/2002 : 12:45:07
|
Yeah, it really seems to like those zeros 
I only used 127 to fill out 7 bits with 1's. If I used 100, for example, I was getting a lot of even numbers and not too many odds. Try using 10; all I got was 0, 2, 8, and 10 as values. Actually, that was using bitwise AND instead of OR. It doesn't appear to matter as much using OR. You can use any number at all really.
It works much better with larger mod values (10,000+), which you could mod again with the upper limit:
SELECT ((convert(varbinary, newid()) ^ 127) % 1234567) % 10
I was really looking for a way to do it without needing any interative code; it's certainly random enough for me. I also tried this:
SELECT ((convert(varbinary, newid()) ^ convert(int, rand(9) * 100)) % 1234567) % 100
And it produces pretty nice distributions of all the possible values (on multiple runs) except for the zeros. I also tried it with a sequence table:
SELECT ((convert(varbinary, newid()) ^ convert(int, rand(seq) * 100)) % 1234567-seq) % 10
FROM Sequence WHERE seq<=100
And those damn zeros still show up!
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/17/2002 : 15:08:15
|
It's because % produces values in the range -n+1 ... n-1.
n%m for negative n will produce values in the range -m+1..0
n%m for non-negative n will produce values in the range 0..m-1
So the zeros show up twice.
quote:
Try using 10; all I got was 0, 2, 8, and 10 as values. Actually, that was using bitwise AND instead of OR.
Odd, that 
|
|
|
|
php95saj
Starting Member
United Kingdom
43 Posts |
Posted - 04/23/2002 : 07:24:35
|
If I wanted to get a random number between 0 and 10000000, what would be the best way to do it. I try this:
CONVERT(INT, ((rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate())))* 100000000))
php95saj
|
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1957 Posts |
Posted - 04/23/2002 : 07:57:34
|
You mean 0 and 99999999.
GETDATE is treated as a constant throughout a select, so this only works in situations where one value per select is required.
GETDATE can only change 300 times a second at most since that's the precision of datetime.
The initial value emitted by RAND correlates very strongly with the seed value.*
CONVERT(int, RAND(CONVERT(binary(4), NEWID())) * 100000000)
* If you plot n vs RAND(n), you'll find that you get a sawtooth wave with a period somewhere near 53668. e.g. (Numbers is a table of integers, as usual)
SELECT n, RAND(n) r
FROM Numbers
WHERE n BETWEEN 15373 AND 69040
Oh, marvellous! I've just noticed that
RAND(2147483563) to RAND(2147483647) and
RAND(-2147483563) to RAND(-2147483648) are all the same (and equal to RAND(0)).
And since the period of the wave doesn't divide the 32-bit space exactly, plugging a uniform-distributed 32-bit seed into RAND doesn't produce a uniform distributed float.
Edited by - Arnold Fribble on 04/23/2002 09:05:56 |
|
|
| |
Topic |
|
Random Number
