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