Random Assignment of Groups for Simulation
-
I am working on a simulation which takes 120-300 records, randomly assigns them to 10 arbitrary groups and then performs a statistical analysis. The statistical analysis is not problematic, but the random group assignment requires some additional work. Note: the groups must be evenly distributed as in each group should have the same number of records in it. For example, if there were 100 records and ten groups, each group would have ten records. I would also like to use the best random number generator possible. Finally, this portion of the simulation exercise will be repeated for 50K to 1M iterations (TBD). As such, having the most performant solution, would be best.
My current solution is to assign a random number to each row, sort the recordset on the random number, and assign the groups based on rank. I can only imagine that there is a better solution.
I have found this article insightful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04c1.asp
The above article also highlights a key problem with the Rand() function in that Rand() is only evaluated once per query AND that Rand() cannot be used in a UDF. Even in SQL Server 2005, use of Rand() in a UDF returns the following: "Invalid use of side-effecting or time-dependent operator in 'rand' within a function." I have not experimented with a .Net assembly yet.
I also did not know if it would be worthwhile to use the SSIS "Row Sampling Transformation" in an iterative fashion to perform the sampling. The only catch is that all records have to be assigned to a group. Therefore, only picking one sample does not help.
Any ideas?
Note: I am using SQL Server 2005 and can also make use of .Net CLR.
-
Here's a self standing sample that you may run with impunity...
This turned out to be a bit long winded because an ORDER BY in an UPDATE in the presence of IS NULL just doesn't sort correctly. So, I had to revert to a temporary table to hold the randomized sample...
In order to make the "perfect" distribution you wanted with a possibility that the number of records may not be evenly divisible by the number of groups, I had to make two distributions... one for the "perfect" distribution, and one for the stragglers.
Sorry it's not 100% loopless although each loop (1 for each group) is set based. It's still pretty fast. I haven't played with some of the new ranking features of 2005 but this may give you an idea...
I sure do hope this is what you meant... lemme know, eh?
--========================================================
-- Setup for the experiment
--========================================================
--===== Supress the autodisplay of rowcounts for appearance
SET NOCOUNT ON
--===== Make sure the temp table doesn't exist so can run
-- more than once
IF OBJECT_ID('TempDB..#Records') IS NOT NULL
DROP TABLE #Records
--===== This simulates some group of records from 120-300
-- I use temp tables for these type of experiments.
-- You would use permanent tables.
SELECT TOP 123 --<< Change this number to simulate diff group sizes
IDENTITY(INT,1,1) AS RecNo,
CAST(NULL AS INT) AS GroupID
INTO #Records
FROM Master.dbo.SysComments sc1,
Master.dbo.SysComments sc2
--========================================================
-- All set... demo the solution
-- This makes perfect distributions. If the number
-- of records is not an even product of 10, the
-- remaining records are NOT assigned...
--========================================================
--===== Create a "sample" table to hold random RecNo's
-- that will will be updated with the same GroupID.
-- This is necessary because including an ORDER BY
-- in a sub-query of an update is basically useless
-- when you look for IS NULL... it always returns
-- things in order by RecNo for some reason.
IF OBJECT_ID('TempDB..#Sample') IS NOT NULL
DROP TABLE #Sample
CREATE TABLE #SAMPLE (RecNo INT)
--===== Create a variable to hold the number of groups
DECLARE @Groups INT --<< parameterize this if making a proc
SET @Groups = 10
--===== Determine the "perfect" number of records for each group
DECLARE @GroupSize INT
SELECT @GroupSize = COUNT(*)/@Groups
FROM #Records
--===== This is our "distribution/group" counter
DECLARE @CurrentGroup INT
--===== This forces the update to only update a certain
-- number of records based on group size
SET ROWCOUNT @GroupSize
--===== Perform the "perfect" distribution randomly
SET @CurrentGroup = 1
WHILE @CurrentGroup <= @Groups
BEGIN
TRUNCATE TABLE #Sample
INSERT INTO #Sample (RecNO)
SELECT RecNO
FROM #Records
WHERE GroupID IS NULL
ORDER BY NEWID()
UPDATE #Records
SET GroupID = @CurrentGroup
FROM #Records r,
#Sample s
WHERE r.RecNo=s.RecNo
SET @CurrentGroup = @CurrentGroup+1
END
--========================================================
-- This picks up the rest of the records... there
-- will always be less than the group of 10 here so
-- each record must be given a different number. The
-- lower groups are favored here.
--========================================================
--===== This forces the update to only affect 1 row per
-- loop
SET ROWCOUNT 1
--===== Perform the remaining distribution, 1 rec per group,
-- randomly by RecNo but favors the lower numbered groups.
SET @CurrentGroup = 1
WHILE @CurrentGroup <= @Groups
BEGIN
--===== Create the random sample
TRUNCATE TABLE #Sample
INSERT INTO #Sample (RecNO)
SELECT RecNo
FROM #Records
WHERE GroupID IS NULL
ORDER BY NEWID()
--===== Distribute the random sample
UPDATE #Records
SET GroupID = @CurrentGroup
FROM #Records r,
#Sample s
WHERE r.RecNo=s.RecNo
SET @CurrentGroup = @CurrentGroup+1
END
--===== All done... put things back to normal
SET ROWCOUNT 0
--===== Display the membership count by GroupID
SELECT 'This is the distribution of GroupID''s.'
SELECT GroupID,COUNT(*) AS MembershipCount
FROM #Records
GROUP BY GroupID
--===== Display the records and the assigned groups
SELECT 'Here are the records and the randomized distributed groups.'
SELECT * FROM #Records
p.s. The "Best" random number generator is the NEWID() function.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I do agree with Jeff that for perromance sake you should use the newID but if you need ramdoness quality rand() is the way to go.
You and that article said:
"The above article also highlights a key problem with the Rand() function in that Rand() is only evaluated once per query AND that Rand() cannot be used in a UDF"
That is not entirely accurate
This you can use to trick SQL into using rand() the way you want!
1. Setup a view like:
create view vwRand as
select rand() r
2. Create a UDF from that View
create function dbo.myrand()
returns float
as
begin
return(select r from vwRand)
end
3. just try it:
select name, dbo.myrand() Rnd
from master.dbo.sysobjects
order by name
Mind you that this is not very fast. If using SQL2005 then retrieving the random number from a CLR udf will improve performance by orders of magnitude.
Good Luck,
* Noel -
That's cool...
You can also use NEWID() as the seed for Rand... in the algo below, "RANGE" is how many numbers you want in the range of random numbers and "OFFSET" is what the starting number should be...
SELECT RAND(CAST(NewID() AS VARBINARY))*range+offset
FROM sometable
For random numbers from 200 to 299, for example, you would use...
SELECT RAND(CAST(NewID() AS VARBINARY))*100+200
FROM sometable
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
Is this sufficient?
DECLARE @NumberOfGroups INT = 10
SELECT a.[name],
a.Rn%@NumberOfGroups + 1 AS Grp
FROM (
SELECT [name], ROW_NUMBER() OVER (ORDER BY NEWID()) AS Rn
FROM SYS.OBJECTS
) AS a -
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply