代码之家 › 专栏 › 技术社区 › Clyde

如何克服T-SQL分割过程中的舍入误差?

tsql

Clyde · 技术社区 · 16 年前

我有一个总值,需要在SQL表中的几行之间分配:

DECLARE @total numeric(38,5);
DECLARE @count int;

SET @total=123.10000

SET @count = SELECT COUNT(*) FROM mytable WHERE condition=@val;
-- let's say @count is now 3

UPDATE mytable SET my_part=@total/@count WHERE condition=@val;

--each record now has 41.03333

SELECT SUM(my_part) FROM mytable where condition = @val;

-- the sum is 123.09999, not my original 123.10000

我希望其中一行UPDATEd包含41.03334,另外两行包含41.03333。我不在乎哪一个向上,哪一个向下。但我关心的是,这些值可以重新求和,得到原始的总和。这可能吗?有已知的算法来做这类事情吗?

4 回复 | 直到 16 年前

Kaniu 16 年前

把剩下的钱存入一个秘密账户,慢慢积累零碎的便士。..然后等几年。..

TOP 1 UPDATE中的子句,以限制更新的行。所以也许:

DECLARE @EPSILON numeric(38,5);
DECLARE @T1 numeric(38,5);
DECLARE @T2 numeric(38,5);
SET @T1 = 1;
SET @T2 = 3;
SET @T1 = @T1/@T2;
SET @T2 = 3 * @T1;
SET @EPSILON = 1 - @T2;


DECLARE @total numeric(38,5);
DECLARE @count int;

DECLARE @REMAINDER numeric(38,5);
DECLARE @PARTIAL numeric(38,5);
DECLARE @RESUM numeric(38,5);
DECLARE @LIMITN Integer;

SET @total=123.10000;

SELECT @count = COUNT(*) FROM mytable WHERE condition=@val;

SET @PARTIAL = @TOTAL / @COUNT;
SET @RESUM = @PARTIAL * @COUNT;
SET @REMAINDER = @TOTAL - @RESUM;
IF @REMAINDER < 0 SET @EPSILON = -@EPSILON;
SET @LIMITN = @REMAINDER / @EPSILON;    

UPDATE mytable SET my_part=@PARTIAL WHERE condition=@val;

UPDATE TOP @LIMITN mytable SET my_part = my_part + @EPSILON WHERE condition=@val;

SELECT SUM(my_part) FROM mytable where condition = @val;

Joe Koberg 16 年前

Austin Salonen gmlacrosse 16 年前

下面是一个黑客,但它适用于这种情况:

DECLARE @total numeric(38,5);
DECLARE @count int;

declare @mytable table
(
   my_part numeric(38,6)  --note the scale is +1
)

insert into @mytable values (0)
insert into @mytable values (0)
insert into @mytable values (0)

SET @total=123.10000

SELECT @count = COUNT(*) FROM @mytable 
-- let's say @count is now 3

UPDATE @mytable SET my_part=@total/@count;

--each record now has 41.03333

-- note the cast
SELECT cast(SUM(my_part) as numeric(38,5)) FROM @mytable;

Charles Bretana 16 年前

试试这个

DECLARE @total numeric(38,5);
DECLARE @count int;
SET @total=123.10000
SELECT @count = COUNT(*) 
FROM mytable 
WHERE condition=@val;
-- let's say @count is now 3
UPDATE mytable SET 
    my_part = @total/@count
WHERE condition=@val;
Update mytable SET 
    my_part = my_part +  
        @total - (Select Sum(My_Part)
                  From mytable 
                  Where condition=@val)
Where PK = (Select Max(PK) From mytable 
            Where condition=@val)
-- each record except one with highest PK now has 41.03333
-- the one with highest PK has 41.03334 (or whatever)
SELECT SUM(my_part) 
FROM mytable 
where condition = @val;
-- the sum should be the original 123.10000