[SQL] One [AllotToTable] for the allocation function_Intefrankly

[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```

## Function.

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

## Example of use.

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

## Features.

• 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
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-

Recommended>>
1、The fresh piece of hard bone how to gnaw Jingdong
2、Learning Seven Days to SAS 3 Basic Module Calls Format Counting Summary Statistics Sorting etc Up
3、Trends and issues how cryptocurrencies and blockchain will fix the global remittance industry
4、21 Processes and threads
5、www88jmpcomI99O883666I Creating Container Open Standards for Linux

已推荐到看一看 和朋友分享想法
最多200字，当前共 发送

已发送

确定
分享你的想法...
取消

确定
最多200字，当前共

发送中