This question is about generating N random numbers whose sum is M(constant) using SQL.

For example we have a number M=9.754. We need 10 random numbers whose sum is 9.754.

It should be done in SQL Server environment.

Can anybody help me?

CodePudding user response：

While the @Squirrel answer is interesting but numbers here is more random here is the code:

DECLARE @s INT=1,
        @k FLOAT=0,
        @final FLOAT=9.917,
        @sum FLOAT =0,
        @min FLOAT=1,
        @max FLOAT=9.917
        
BEGIN
    WHILE (@sum <> @final)
    BEGIN
        WHILE (@s <= 10)
        BEGIN
            
            SET @k =
            (
                SELECT ROUND(RAND(CHECKSUM(NEWID())) * (@max - @min)   @min,3)
            );
            PRINT (CONCAT('random: ',@k));
                    
            IF(@sum @k <=@final)
                SET @sum =@k;
            SET @max=@final-@sum;

            PRINT (CONCAT('computed sum: ',@k));
            IF(@max>1) SET @min=1 ELSE SET @min=0;

            IF(@sum=@final)
                BREAK;

            SET @s = @s   1;
            SET @k = @k   0;
        END;
        
         PRINT (CONCAT('final', @final))
         PRINT (CONCAT('sum', @sum))

         IF(@sum<>@final)--force stop if after 10 try the sum not match with final
          BEGIN
          PRINT(CONCAT('final random number:',@final-@sum))
          SET @sum=@final;
          END;

         SET @s=0;

         IF(@sum=@final)
            BEGIN
            PRINT('****************************DONE****************************')
            BREAK;
            END
    END;
    PRINT ('end');
END;

CodePudding user response：

Interesting requirement.

Query below uses tally table / number table to generate 10 random numbers after that find the ratio. Final query check for case where sum of the numbers is not equal to @m and make the adjustment on the biggest number.

declare @m decimal(10,3) = 9.754,
        @n int           = 10;

with 
-- using recursive CTE to generate a number table
numbers as
(
    select  n   = 1
    union all
    select  n   = n   1
    from    numbers
    where   n   < @n
),
-- generate random positive numbers using newid()
-- Note : 100 is chosen arbitrary 
num as
(
    select  n = abs(checksum(newid())) % 100
    from    numbers
),
-- calculate the ratio
ratio as
(
    select  r, 
            rn     = row_number() over (order by r desc),
            sum_r  = sum(r) over()
    from
    (
        select  r = convert(decimal(10,3), n * 1.0 / sum(n) over() * @m)
        from    num
    ) r
)
-- sum(r) may not equal to @m due to rounding
-- find the difference and adjust it to the biggest r
select  r, rn, sum_r,
        adj_r     = r   case when rn = 1 then @m - sum_r else 0 end,
        sum_adj_r = sum(r   case when rn = 1 then @m - sum_r else 0 end) over()
from    ratio

dbfiddle demo