Donate to support site

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