小数点以下があるフィールド同士の除算における有効桁数について

Question

こんにちは、sugarwingと申します。

単価を計算する算術式で、小数点以下2桁の値を算出するときに精度を落とさずに結果を出すにはどうすればよいでしょうか。

バージョン：SQL Server2008 Std
参照元フィールド：金額（DECIMAL(14,0)）と数量（DECIMAL(14,2)）
更新先フィールド：単価（DECIMAL(14,2)）
計算式：ROUND(金額÷数量,2,1)=単価
補足事項：小数点以下3桁目を切り捨て

考えている計算式：CAST(CAST(ROUND(金額÷数量,2,1)*100 AS BIGINT) AS DECIMAL(14,2))/100 =単価

※この計算式ですと、算出される結果の小数点以下が6桁になってしまい、単価フィールドにセットすることができません。
ex（数量を12.2、金額を63とした場合）
    CAST(CAST(ROUND(63÷12.2,2,1)*100 AS BIGINT) AS DECIMAL(14,2))/100=5.160000

以上、ご教授をお願い致します。

Answer 1

最後の "/100" の計算を行うことで、有効桁数が変わってしまっているのではないでしょうか？

一番外側の計算の時には、516.00 / 100 という形になっていますが、
516.00 はdecimal型、100はint型ですので、演算する際に有効桁数が変わってしまいます。
以下の資料が参考になると思います。(decimal型の説明あたりから、e1 / e2の演算結果の有効桁数と小数点以下桁数のあたりまで)

有効桁数、小数点以下桁数、および長さ (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms190476.aspx

上記資料の e1/e2の式に今回のものを当てはめると、小数点以下桁数は6となるので、結果が5.160000になるのだと思います。

今回の計算なら 単価 = CAST(ROUND(金額/数量, 2,1) as DECIMAL(14, 2)) でいいのではないかと思います。

Answer 2

最後の "/100" の計算を行うことで、有効桁数が変わってしまっているのではないでしょうか？

一番外側の計算の時には、516.00 / 100 という形になっていますが、
516.00 はdecimal型、100はint型ですので、演算する際に有効桁数が変わってしまいます。
以下の資料が参考になると思います。(decimal型の説明あたりから、e1 / e2の演算結果の有効桁数と小数点以下桁数のあたりまで)

有効桁数、小数点以下桁数、および長さ (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms190476.aspx

上記資料の e1/e2の式に今回のものを当てはめると、小数点以下桁数は6となるので、結果が5.160000になるのだと思います。

今回の計算なら 単価 = CAST(ROUND(金額/数量, 2,1) as DECIMAL(14, 2)) でいいのではないかと思います。

yottun8さん

返信ありがとうございます。

提示していただいた資料を参考にさせていただきます。

また、
＞今回の計算なら 単価 = CAST(ROUND(金額/数量, 2,1) as DECIMAL(14, 2)) でいいのではないかと思います。
ですが、私も最初はこの式で行おうと思っていました。
しかし、この式を実行すると、オーバーフローエラーが発生してしまいます。

  SELECT CAST(ROUND(63/12.2, 2,1) as DECIMAL(14, 2))

  メッセージ 8115、レベル 16、状態 7、行 1
  numeric をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。

申し訳ございませんが、引き続き、情報をお願い致します。

Answer 3

自己レスです。

DBのオプション設定でSET NUMERIC_ROUNDABORTの設定がONになっているため
今回の現象が発生しておりました。

DBのオプション設定がこの設定になっているのには理由があるため、DB自体のこの
オプションを変更することはできません。

なので、SET NUMERIC_ROUNDABORTをSQL内で実行し、考えている計算式を下記のように
して行えば、一応単価フィールドにはセットできることがわかりました。

SET NUMERIC_ROUNDABORT OFF

INSERT INTO hogeins
SELECT CAST(ROUND(金額÷数量,2,1) AS DECIMAL(14,2))
FROM hoge

ただし、このオプション設定を行うと、端数は丸め処理になるという説明がありました。
計算結果の小数点以下3桁目以降が、ROUND関数によりすべて0になっているので
丸め処理が発生しても精度的に保証されるものなのでしょうか。

参考ページ：SET NUMERIC_ROUNDABORT

Answer 4

ROUNDの引数で指定されている小数点以下桁数と、
最終的に格納する値(今回の場合は単価)の小数点以下桁数が同じである限りは
精度に問題は無いでしょうね。
(結果を格納する値の方が小数点以下桁数が少ない場合は、精度が低くなります。)

丸められる位置の値は、それまでにROUNDの結果によって必ず0になっていますので、
結果が変わることは無いでしょう。
(「保証される」とまで言えるのかどうかは分かりませんが)

Answer 5

ROUNDの引数で指定されている小数点以下桁数と、
最終的に格納する値(今回の場合は単価)の小数点以下桁数が同じである限りは
精度に問題は無いでしょうね。
(結果を格納する値の方が小数点以下桁数が少ない場合は、精度が低くなります。)

丸められる位置の値は、それまでにROUNDの結果によって必ず0になっていますので、
結果が変わることは無いでしょう。
(「保証される」とまで言えるのかどうかは分かりませんが)

yottun8さん

 

返信が遅くなり、大変申し訳ございません。

 

「保証される」とまで行かないまでもかなりの精度になることがわかりました。

 

ありがとうございました。