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)

以此類推

 

不過要測試隨機數的機率平均值還得寫個小程式

 

-- 建立一個暫存資料表，只需要一個整數欄位valdrop table #testRandgocreate table #testRand(  val int null)goselect * from #testRanddeclare @i intset @i = 1-- 迴圈 25萬次，也就是插入250萬條隨即數記錄while @i <= 250000begin  -- 取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 + 1end-- 列出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，也還行，以上就是SQL隨機數函數的測試結果，也供大家參考。

 

文章來源

Q群討論：236201801

.