はじめに
下記のような小数を使ったクエリを発行したら、結果があるはずなのに結果がないと返ってくる。
データベースから取ってきた小数を使った計算が合わない。
SELECT id FROM Products WHERE amount = 59.95
そんな現象に遭わないために、遭う人を減らすために、「SQLアンチパターン」第9章から得た知識をまとめておきます。
データベースで小数値を使用したい
整数型は便利なデータがですが、その名の通り格納できるのは整数のみです。
例えばドルを基準にした金額計算では59.95ドルのように、小数点2位の値が必要になります。このような数値をデータベースに格納し、うまく計算を行うにはどうしたら良いのでしょうか。
アンチパターン: FLOAT 型の採用
SQLにも実数を表すデータ型があります。FLOATデータ型です。
SQL の FLOAT データ型は実数を 二進数形式 でエンコードします。しかし、「10進数で記述できるすべての値を、2進数で格納できるわけではない」という浮動小数点数の特性を常に意識している人はあまり多くないでしょう。
ここで少し丸めの誤差について話します。
例えば3分の1を小数で表すと0.333333… と無限に続きます。そこで、例えば0.333などの有限精度の値を使って代替します。しかし、計算結果が意図していた値と正確には一致しなくなります。
3分の1を三倍すると1になりますが、0.333を三倍しても0.999です。
二進数形式で無限精度が必要な値は、10進数で表現される値とは異なります。例えば59.95を2進数で正確に表すには無限精度が必要です。FLOATデータ型では無限精度を扱うことはできないため、近似値を使用しています。その近似値を10進数形式で表すと、59.950000762939になります。
これを理解しておけば、計算に誤差が生じることを予測できます。しかし実際のところ、浮動小数点数を使用するたびに計算をするようなことはまずないでしょう。
FLOAT 型の採用による問題点
意図した値と近似値の差はわずかですし、どうやったところで無限精度を実現することはできないのだからあまり問題ないと考えることもできます。
しかし、計算によっては大きな問題になることもあります。
例えば等価性比較です。
| id | amount |
|---|---|
| 1 | 59.95 |
上記のような Products テーブルがあるとします。amount は FLOAT 型です。
SELECT id FROM Products WHERE amount = 59.95
このクエリを発行した人物は、当然 1 という結果が返ってくると考えていたはずですが、実際には「一致する行はない」と返ってきます。
これは、 amount に格納されている値が59.95よりもわずかに大きいからです。
また、浮動小数点数の誤差は、集約計算を行う場合にも生じます。SUM関数を用いて浮動小数点数を合計する場合、一つ一つの誤差が累積されていきます。
さらに、和ではなく積を計算する場合は誤差がさらに大きくなります。
例えば、1に1.0を掛けると結果は常に1ですが、1に1000回0.999を掛けると結果は約0.3677となります。
NUMERIC 型、DECIMAL 型を使用する
では、FLOAT の代わりに何を使えば良いのでしょうか。
FLOAT 型の代わりに、NUMERIC または DECIMAL を用いて (NUMERIC と DECIMAL は同一の振る舞いです ) 固定精度の小数点数を表すようにしましょう。
ALTER TABLE Products MODIFY amount NUMERIC(9,2);
このデータ型は、第一引数に「格納可能な 10進数 の桁の総数」を、第二引数に小数点以下に格納できる桁数を指定します。
長所は、10進数で表現できる値を格納するとき、その値が正確に格納されていることを信頼できることです。
無論、このクエリも成功します。
SELECT id FROM Products WHERE amount = 59.95
NUMERIC や DECIMAL を使用しても、3分の1のような10進数で無限精度が必要な値を格納することはできません。しかし、少なくとも二進数のものよりは10進数の無限小数の扱いに慣れているでしょう。
まとめ
正確な10進数値が必要なときは、NUMERIC や DECIMAL を使い、FLOAT は避けましょう。