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 source code.
Function: Assign 0.01
ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT)
RETURNS @t TABLE(Val DECIMAL(16,4))
IF @num IS NULL OR @div IS NULL OR @div=0 RETURN
IF @div=1 - divide by 1, return 1 line directly
INSERT @t SELECT @num
IF @num=0 -- divisor is 0, return @div row 0 directly
INSERT @t SELECT 0 FROM FMakeRows(@div)
/*Get the true precision of @num*/
DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@num
WHILE @numToInt % 1 <> 0
SET @numToInt *= 10
SET @scale += 1
/* 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
DECLARE @pow INT = POWER(10, @scale)
UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t
SET Val += SIGN(@mod)/@pow
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.
>>1、The fresh piece of hard bone how to gnaw Jingdong2、Learning Seven Days to SAS 3 Basic Module Calls Format Counting Summary Statistics Sorting etc Up3、Trends and issues how cryptocurrencies and blockchain will fix the global remittance industry4、21 Processes and threads5、www88jmpcomI99O883666I Creating Container Open Standards for Linux