Assign values to records randomly based on given distributions

I have a t-sql procedure that generates test data and as part of this, several columns need to be set randomly to a set of values following a predefined distribution. I currently have a method for doing this using an identity column and the remainder operator % but I wondered if th开发者_运维技巧ere was a more elegant way of designing a solution to this problem that would allow me to parameterise the values and distribution that the columns are assigned.

For example,

I want to assign Column A one of three values {Horse, Donkey, Pony} and column B one of two values {Big, Small}. In this case I want the distributions to be equal, {Horse:1/3, Donkey 1/3, Pony, 1/3}, {Big:1/2, Small:1/2} but Column B's distribution should depend on Column A (i.e. 1/2 of the Ponies should be Big)

Record | ColumnA | ColumnB | OtherData
1 | Horse | Big |...
2 | Horse | Small |...
3 | Donkey | Big |...
4 | Donkey | Small |...
5 | Pony | Big |...
6 | Pony | Small |...

I would prefer to have a semi-random allocation of Column A and B with respect to record numbers but this is not essential.

In the case where a distribution is not exactly possible, all remaining records should be assigned one of the possible values. It is assumed that in large data sets this will be work itself out.

Not sure if this will help in your case, but this will work on SQL Server 2005+. This is a set of 300 records, yielding 100 of each animal, and 50 of each size per animal.

There may be a similar way to approach this in other SQL languages.

DECLARE @MaxCount INT
SET @MaxCount = 300

SET NOCOUNT ON;
DECLARE @Numbers TABLE (Number INT NOT NULL IDENTITY);
INSERT @Numbers DEFAULT VALUES;
WHILE SCOPE_IDENTITY() < @MaxCount INSERT @Numbers DEFAULT VALUES;

SELECT  *
FROM    (
    SELECT  RandomSize.Number
    ,   RandomSize.Animal
    ,   CASE RandomSize.RowNumber % 2
            WHEN 0 THEN 'Big'
            WHEN 1 THEN 'Small'
        END Size
    FROM    (
        SELECT  DerivedAnimal.Number
        ,   DerivedAnimal.Animal
        ,   ROW_NUMBER() OVER (PARTITION BY DerivedAnimal.Animal ORDER BY NEWID()) RowNumber
        FROM    (
            SELECT  RandomAnimals.Number
            ,   CASE RandomAnimals.RowNumber % 3
                    WHEN 0 THEN 'Horse'
                    WHEN 1 THEN 'Donkey'
                    WHEN 2 THEN 'Pony'
                END Animal
            FROM    (
                SELECT  Number
                ,   ROW_NUMBER() OVER (ORDER BY NEWID()) RowNumber
                FROM    @Numbers
                ) RandomAnimals
            ) DerivedAnimal
        ) RandomSize
    ) FinalList
ORDER BY FinalList.Number