SQL SERVER 随机数函数测试

偶然需要在存储过程使用随机数，对其概率有疑虑，故先对SQL的随机数函数做个测试

 

SQL SERVER 的随即数函数是RAND()，会产生一个小于1的浮点数，形式例如0.123456789，如果需要限定大小，可以用乘以一个数再取整即可。

 

例如需要0~99范围内的随机数：select cast(RAND() * 100 as int)

例如需要0~999范围内的随机数：select cast(RAND() * 1000 as int)

以此类推

 

不过要测试随机数的概率平均值还得写个小程序

 

-- 创建一个临时表，只需要一个整数字段val
drop table #testRand
go
create table #testRand
(
  val int null
)
go

select * from #testRand

declare @i int
set @i = 1
-- 循环 25万次，也就是插入250万条随即数记录
while @i <= 250000
begin
  -- 取0至10之间的随机数并插入数据库
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
  insert #testRand([val]) values ((select cast(RAND() * 10 as int)))
set @i = @i + 1
end

-- 列出0至9分别插入多少条记录,并求平均数
select
(select COUNT(*) from #testRand where [val] = 0) as [0],
(select COUNT(*) from #testRand where [val] = 1) as [1],
(select COUNT(*) from #testRand where [val] = 2) as [2],
(select COUNT(*) from #testRand where [val] = 3) as [3],
(select COUNT(*) from #testRand where [val] = 4) as [4],
(select COUNT(*) from #testRand where [val] = 5) as [5],
(select COUNT(*) from #testRand where [val] = 6) as [6],
(select COUNT(*) from #testRand where [val] = 7) as [7],
(select COUNT(*) from #testRand where [val] = 8) as [8],
(select COUNT(*) from #testRand where [val] = 9) as [9],
(select cast(SUM(val) as float) / 2500000 from #testRand) as [average]

 第一次运行结果(0~9各出现的次数统计)

0	1	2	3	4	5	6	7	8	9	average
249715	250738	249181	251119	250237	249910	249324	249997	249787	249992	4.4988408

 

第二次运行结果

0	1	2	3	4	5	6	7	8	9	average
250676	250294	249953	250377	250108	249191	250322	250373	249817	248889	4.4963192

 

第三次运行结果

0	1	2	3	4	5	6	7	8	9	average
249999	250287	250635	249590	249637	249360	249768	250637	249947	250140	4.4998312

 

最大值：251119 ，最小值248889，总量偏差(251119-248889)/250000=0.00892，低于1%，还能接受，平均数也相当趋近于4.5（0~9的平均数），也还行，以上就是SQL随机数函数的测试结果，也供大家参考。

 

文章来源

Q群讨论：236201801

.