以下探討為何MSSQL數值運算結果精度竟然比EXCEL差或不如預期，將以四個整數執行運算式為(11/13)/(17/19)運算為例，資料庫之資料欄位型態使用numeric(15,2)，兩者產生結果，EXCEL為小位數9位，但MSSQL結果卻為6位。如同先前《MSSQL數學運算應注意事項》文章中需額外將第一個運算式轉型為浮點數(float)，即可解決此問題（精度可達15位），本文將探討原由，如下：
Result
|
|
EXCEL
|
 |
MSSQL
|
 |
先前文章或著作中曾探討數值運算之限制，當以運算子（operator）結合兩種不同資料型態之運算式（expressions ）時，會將低優先順序之資料型態，轉換為高優先順序之資料型態，當運算元（operand）均同為numeric時，運算結果仍為numeric，重點在於可用以儲存資料之位數（Precision）及精準度（Scale）。以本案例將使用numeric(15,2)，而(11/13)/(17/19)運算結果為0.945701(小位6位)，首先將拆分為11/13及17/19分別計算結果均為小數18位，再將兩者之結果執行除法運算，即變成小數位6位，SQL及執行結果如下:
SELECT Numerator
/ Denominator Val
, SQL_VARIANT_PROPERTY(Numerator
/ Denominator, 'BaseType') [BaseType]
, SQL_VARIANT_PROPERTY(Numerator
/ Denominator, 'Precision') [Precision]
, SQL_VARIANT_PROPERTY(Numerator
/ Denominator, 'Scale') [Scale]
----------------------------
, Numerator --0.846153846153846153
, SQL_VARIANT_PROPERTY(Numerator, 'Precision')
[N.P]
, SQL_VARIANT_PROPERTY(Numerator, 'Scale')
[N.S]
----------------------------
,
Denominator --0.894736842105263157
, SQL_VARIANT_PROPERTY(Denominator, 'Precision')
[D.P]
, SQL_VARIANT_PROPERTY(Denominator, 'Scale')
[D.S]
FROM
(
SELECT
(CAST(11 AS
NUMERIC(15,2)) / CAST(13 AS NUMERIC(15,2)) ) Numerator
, (CAST(17 AS NUMERIC(15,2)) / CAST(19 AS NUMERIC(15,2)) ) Denominator
) A
由於此運算式之運算元均為numeric資料型態，運算結果之有效位數及小數位決定可參考官網文件所述，如下表：
Operation
|
Result precision
|
result scale *
|
e1 +
e2
|
max(s1, s2) + max(p1-s1, p2-s2)
+ 1
|
max(s1, s2)
|
e1 -
e2
|
max(s1, s2) + max(p1-s1, p2-s2) + 1
|
max(s1, s2)
|
e1 *
e2
|
p1 + p2 + 1
|
s1 + s2
|
e1 / e2
|
p1 - s1 + s2 + max(6, s1 + p2 + 1)
|
max(6, s1 + p2 + 1)
|
e1 {UNION|EXCEPT|INTERSECT} e2
|
max(s1, s2) + max(p1-s1, p2-s2)
|
max(s1, s2)
|
e1 %
e2
|
min(p1-s1, p2 -s2) + max( s1,s2 )
|
max(s1, s2)
|
欄位資料型態為numeric(15,2)，將前述運算(11/13)/(17/19)拆分成二個步驟，有效位數/精度變化如下:
Step
01: 11/13及17/19
除法運算（e1 / e2）
|
長度
|
|
P
|
p1 - s1 + s2 + max(6, s1 + p2 + 1)
15 – 2
+ 2 +
max(6, 2 + 15 + 1) = 33
|
33
|
S
|
max(6, s1 + p2 + 1)
max(6, 2 + 15 +
1) = 18
|
18
|
Step
02: (11/13)
/ (17/19)
除法運算（e1 / e2）
|
長度
|
|
P
|
p1 - s1 + s2 + max(6, s1 +
p2 + 1)
33 – 18 + 18 + max(6, 18 + 33
+ 1) = 85
85超過[38]上限 è取上限38 (85-38=47)，缺47，以小數位補
|
38
|
S
|
max(6, s1 + p2 + 1)
max(6, 18 + 33 + 1) = max(6, 52) = 52
52-47 = 5 (小數位小於下限值[6]，以下限值替代)
|
6
|
結果有效位數及小數位數的絕對最大值為 38。當結果有效位數大於 38 時，會縮減對應的小數位數，以防止截斷結果的整數部分，但小數位數最少不得小於6位，因此當運算多次後將變為numeric(38, 6)。
MSSQL / ORALCE對於運算結果型態取得方式概念上不同，對於ORACLE是先計算再由運算結果以定義其型態，但MSSQL則是參考兩個運算子之型態，以取得運算子優先權最低者或以運算方式取得其可能之位數位及精度，使用上請特別注意。
沒有留言:
張貼留言