[SQL] One [AllotToTable] for the allocation function

** Applicable environment.** MSSQL 2005+. Where 05 requires a change in the way some of the statements are written to work, e.g.

--Variable declaration and assignment need to be written separately DECLARE @scale INT = 0 - needs to be changed to the following DECLARE @scale INT; SET @scale = 0 --05 does not support compound operators like += SET @scale += 1 - needs to be changed to the following SET @scale = @scale + 1

Assigns a number (integer or finite decimal) into specified copies, one row per copy, returning a single column table.

As can be seen, this is not a division, but an attempt to put the numbers as fairly as possible** finished distribution** The allocation method has the following

- There is no such thing as inexhaustion
- It is guaranteed that the sum of all copies is equal to the original number
- The accuracy of the result is the same as the accuracy of the original number (the assigned number). 3.The precision of 470 is considered to be 0.01, so it divides into 3 parts as 1.16, 1.16, 1.15, not 1.157, 1.157, 1.156
- The result will be at most two values, with the difference between the larger and smaller values being 1 unit of precision of the original number. That is, the difference between copies is guaranteed to be minimal within the accuracy of the original number, thus achieving
**As fair as possible**The distribution of the As in the above example 1.16 differs from 1.15 by 0.01 - If the allocated number is NULL, or the number of copies is NULL or 0, return 0 rows

Distribution methods like this one are used in multiple scenarios I guess, it should have a name, I just don't know, and I would appreciate if any apes who do know would let me know, thanks.

/*-------------------- Function: Assign 0.01 Author:AhDung Update:201504211157 --------------------*/ ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT) RETURNS @t TABLE(Val DECIMAL(16,4)) BEGIN IF @num IS NULL OR @div IS NULL OR @div=0 RETURN IF @div=1 - divide by 1, return 1 line directly BEGIN INSERT @t SELECT @num RETURN END IF @num=0 -- divisor is 0, return @div row 0 directly BEGIN INSERT @t SELECT 0 FROM FMakeRows(@div) RETURN END /*Get the true precision of @num*/ DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@num WHILE @numToInt % 1 <> 0 BEGIN SET @numToInt *= 10 SET @scale += 1 END /* Filled elements (rounded) */ DECLARE @roundDownItem DECIMAL(16,4)=ROUND(@num / @div, @scale, 1) INSERT @t SELECT @roundDownItem FROM FMakeRows(@div) /* Divide the remainder evenly over top n rows. The reason why the remainder is not @num % @roundDownItem is because the latter would be incorrect at 1 */ DECLARE @mod DECIMAL(16,4) = @num - @roundDownItem*@@ROWCOUNT IF @mod<>0 BEGIN DECLARE @pow INT = POWER(10, @scale) UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t SET Val += SIGN(@mod)/@pow END RETURN END

The function uses a custom table-value function called FMakeRows, which you have to move to this blog post to take.http://www.cnblogs.com/ahdung/p/4195509.html , and of course you can make small modifications so that it doesn't depend on external functions.

There were attempts to implement it with the ranking function NTILE, with the help of its group allocation capabilities, but the performance was not satisfactory, as it was not worth the space and time to construct a table with as many rows as the number being allocated. Also, I would like to ask the veteran birds to point out improvements, thanks.

-Bunbi-