home
-- Generate weighted random test data.
Author Nigel Rivett
/*
This is the reference data.
Generate an entry for the names in the proportion of the weights
So here more entries will be for Richard than anyone else
*/
set nocount on
declare @customer table (name varchar(20), weight int)
insert @customer select 'John', 2
insert @customer select 'Paul', 5
insert @customer select 'Peter', 1
insert @customer select 'George', 13
insert @customer select 'Richard', 20
-- This is thet able to populate
declare @TestData table (seq int, name varchar(20), value decimal(10,2), randval int)
-- value will be a random number between
declare @minval int = 5
declare @maxval int = 20
-- This calculates some values to be used in the random selector
declare @weightRef table (name varchar(20), minweight int, maxweight int, totweight int)
;with cte as (select totweight = sum(weight) from @customer)
, cte1 as
(
select name
, minweight=coalesce((select sum(weight) from @customer t2 where t2.name < t1.name),0)+1
, maxweight=(select sum(weight) from @customer t2 where t2.name <= t1.name)
, cte.totweight
from @customer t1
cross join cte
)
insert @weightRef
select name, minweight, maxweight, totweight
from cte1
declare @Totweight int
select @Totweight = max(maxweight) from @weightRef
declare @i int
select @i = 0
while @i < 1000
begin
declare @randval1 int
declare @randval2 decimal(10,2)
select @randval1 = rand() * @totweight + 1 -- random number for ref entry
select @randval2 = rand() * (@maxval - @minval) + @minval -- random value
select @i = @i + 1
insert @TestData (seq, name, value, randval)
select @i, name, @randval2, @randval1
from @weightRef
where @randval1 between minweight and maxweight -- get the entry for the random number
end
--select * from @TestData
-- check the results
select name, count(*), 1.0 * count(*) / (select count(*) from @TestData)
, minval = min(value), maxval = max(value)
from @TestData
group by name
order by name
select *, 1.0 * weight / @totweight
from @customer
order by name
select top 20 * from @TestData
/*
name minval maxval
-------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
George 309 0.309000000000 5.15 19.97
John 44 0.044000000000 5.25 19.93
Paul 126 0.126000000000 5.14 20.00
Peter 25 0.025000000000 5.41 19.92
Richard 496 0.496000000000 5.02 19.96
name weight
-------------------- ----------- ---------------------------------------
George 13 0.317073170731
John 2 0.048780487804
Paul 5 0.121951219512
Peter 1 0.024390243902
Richard 20 0.487804878048
seq name value randval
----------- -------------------- --------------------------------------- -----------
1 Richard 8.58 23
2 Richard 10.82 28
3 George 12.41 13
4 George 9.90 5
5 George 13.62 13
6 Richard 7.78 41
7 Richard 14.39 29
8 Richard 19.06 30
9 Richard 7.43 22
10 Richard 5.38 24
11 Richard 13.01 28
12 John 13.75 15
13 Richard 13.88 40
14 George 7.92 1
15 John 17.96 15
16 Richard 15.63 27
17 Richard 6.86 23
18 George 14.08 10
19 George 15.51 3
20 Richard 6.22 32
*/
home