T-SQL ROUND Function Bug?

先看一個例子：

   1: DECLARE @Value float

   2: SET @Value = 12.1785

   3: SELECT '12.1785' as ValueToRound, ROUND(@Value,3) as RoundedValue

   4: SET @Value = 12.1745

   5: SELECT '12.1745' as ValueToRound,ROUND(@Value,3) as RoundedValue 

猜猜看，結果是多少？這個例子源自：T-SQL ROUND Function Bug

 

1. 問題起因

    一個老系統，每個月要對系統發生的每筆業務資料進行匯總，產生應收款；但是每個月累計下來，總會與每天的連續累加匯總差(相差幾分錢，或者幾毛錢)。

    同樣的問題還有：倉庫裡面一批按重量計數的物資，每次出庫的時候銷賬；但偶爾會出現：需要出庫的數量，與庫存中現有數量一致，但依然無法出庫的情況。

    問題的根源，就在於資料的儲存類型。系統中，選擇了使用float來儲存資料。但電腦中，是使用近似值來表示float/double的，在這種+/-過程中，誤差不斷地累積，就出現了上述的這些問題。關於浮點數的儲存格式，可以參考我之前的文章：“精確”判斷一個浮點數是否等於0

 

2. Linq to SQL中的Round

     知道了問題的原因，我們就可以針對性地進行處理：對資料進行四捨五入後再進行求和。

    C#中，提供了兩種控制四捨五入的計算方式：MidpointRounding.ToEven和MidpointRounding.AwayFromZero；預設為MidpointRounding.ToEven，即當一個數字是其他兩個數位中間值時，會將其舍入為最接近的偶數。有關舍入方式，可以參考MSDN：“MidpointRounding 枚舉”。

    值得一提的是，裡面對MidpointRounding.AwayFromZero的翻譯有誤，翻譯原文為：“AwayFromZero 當一個數字是其他兩個數位中間值時，會將其舍入為兩個值中絕對值較小的值。”但英文原文為：“When a number is halfway between two others, it is rounded toward the nearest number that is away from zero.” 文檔描述得相當糟糕，不知道是不是我理解能力太差，反正那一坨東西，繞來繞去，還是看裡面舉的例子比較靠譜。

   1: // 3.4 = Math.Round( 3.45, 1)

   2: //-3.4 = Math.Round(-3.45, 1)

   3:  

   4: // 3.4 = Math.Round( 3.45, 1, MidpointRounding.ToEven)

   5: // 3.5 = Math.Round( 3.45, 1, MidpointRounding.AwayFromZero)

   6:  

   7: //-3.4 = Math.Round(-3.45, 1, MidpointRounding.ToEven)

   8: //-3.5 = Math.Round(-3.45, 1, MidpointRounding.AwayFromZero)

 

    L2S中，如果直接用Math.Round(T.Amount, 2, MidpointRounding.AwayFromZero)，則解析成T-SQL就是直接調用Round函數；如果用Math.Round(T.Amount, 2, MidpointRounding.ToEven)，則會解析為：

   1: (CASE 

   2: WHEN ((([t0].[Amount]) * 2) = round(([t0].[Amount]) * 2, 2)) AND (([t0].[Amount]) <> round([t0].[Amount], 2)) THEN round(([t0].[Amount]) / 2, 2) * 2

   3: ELSE round([t0].[Amount], 2)

   4: END)

    3. 解決方案

3.1 兩次Round

    使用一次Round可以解決小數點取捨問題，但無法處理浮點數精度帶來的誤差，下面是一次Round帶來的結果：

   這是因為，譬如浮點數2053.345在電腦內部儲存的格式可能為2053.2449999999……，一次Round的結果就直接把後面49999…..的給舍掉了。但我們可以使用兩次Round來達到需要的舍入效果：Round(Round(Value, 6), 2)

3.2 Decimal

    處理精度問題，將資料類型定義為Decimal才是王道。

     Decimal在計算及中的儲存格式：Decimal 值長度是128位，由 1 位符號、96 位整數以及比例因素組成，比例因素用作 96 位整數的除數並指定整數的哪一部分為小數。比例因素隱式地定為數字 10 的冪，指數範圍從 0 到 28。因此，Decimal 值的二進位表示形式為：((-2^96 到 2^96) / 10^(0 到 28))。比例因素還保留 Decimal 數字中的所有尾數零。

標籤: Round,Decimal,Linq to Sql