mercredi 17 juin 2015

SQL Server random number generator is not random

Can someone explain why the following code does not produce a even distribution of values from 1 to 10:

declare @tbl table (id int, nm int)

;with src(id) as (
select 1 union all select id+1 from src where id+1 <= 100000
)
insert @tbl(id, nm)
select id, ROUND(((9) * RAND(cast(newid() as varbinary)) + 1), 0)
from src
option (maxrecursion 0)

select nm, count(9)qty
from @tbl
group by nm

Example Output:

nm  qty
1   5523
2   11079
3   11190
4   11016
5   11026
6   11239
7   11149
8   11054
9   11243
10  5481

Notice that the quantity of 1's and 10's is about half the other numbers.

I have resorted to the following method to fix this flaw:

declare @tbl table (id int, nm int)

;with src(id) as (
select 1 union all select id+1 from src where id+1 <= 100000
)
insert @tbl(id, nm)
select id, (abs(checksum(newid()))%10)+1
from src
option (maxrecursion 0)

select nm, count(9)qty
from @tbl
group by nm

Example Output:

nm  qty
1   10053
2   10146
3   10123
4   9939
5   9804
6   9895
7   9887
8   9907
9   10193
10  10053

As you can see the numbers 1 and 10 equally represented. Does anyone know why the first method fails?

Keith




Aucun commentaire:

Enregistrer un commentaire