プログラマーの時給とバグ修正に要した時間からバグ修正のコストを算出してみる

# アカウントテーブル
CREATE TABLE `Accounts` ( 
  `account_id` int(10) unsigned NOT NULL,
  `hourly_rate` FLOAT, #時給
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# バグ管理テーブル
CREATE TABLE `Bugs` ( 
  `bug_id` int(10) unsigned NOT NULL,
  `assigned_to` int(10) unsigned NOT NULL, #修正担当
  `hours` FLOAT, #バグの修正時間
  PRIMARY KEY (`bug_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

上記のようなテーブルがあったとして、下記のようなSQLを組むことでバグごとの修正コストは算出できる。

SELECT b.bug_id, b.hours * a.hourly_rate AS cost_per_bug
FROM Bugs AS b
 INNER JOIN Accounts AS a ON b.assigned_to = a.account_id;

さて、ここでhours(バグの修正時間)やhourly_rate(時給)にFLOAT型を使うと何が起きるのか、というのが今回のお話。

9.1 目的：整数の代わりに小数値を使用する

小数を使えば時給はセント単位まで計算できるし、時間も分、秒単位まで計算できて正確・・なはず・・

9.2 アンチパターン：FLOATデータ型を使用する

9.2.1 丸めが避けられない

無限小数を扱った途端に誤差が発生する。だってプログラムは有限小数しか扱えないから
1/3は0.333などの値に丸めなければならないため。

10進数の有限小数を2進数で表現すると無限小数になってしまう問題

例：10進数の0.3を2進数で表現すると・・

0.3
x 2
----
0.6
x 2
----
1.2
x 2
----
0.4
x 2
----
0.8
x 2
----
1.6
x 2
----
1.2

0.01001...

9.2.2 SQLでのFLOATの使用

SELECT hourly_rate FROM Accounts WHERE account_id = 123;
59.95

SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id =123;
59950000762.939

SELECT * FROM Accounts WHERE hourly_rate = 59.95
Empty set

SELECT * FROM Accounts WHERE ABS(hourly_rate - 59.95) < 0.000001;
これなら値は出るがしきい値を適宜設定する必要がある

浮動小数点計算は掛け算を使用した際に誤差がさらに大きくなる。

例)0.999を1000回掛けると0.36769..になる。複利計算などで起きがち

9.3 アンチパターンの見つけ方

FLOAT,REAL,DOUBLE,PRECISIONなど小数を扱う型の設定があれば誤差は生じると考えて良い

9.4 アンチパターンを用いても良い場合

科学技術計算

9.5 解決策：NUMERICデータ型を使用する

DECIMALでもOK。つまりは固定小数点を使えという話。
無限小数は扱えないが、2進数変換による誤差は避けられる。

CREATE TABLE `Accounts` ( #アカウントテーブル
  `account_id` int(10) unsigned NOT NULL,
  `hourly_rate` NUMERIC(9,2), #時給
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Bugs` ( #バグ管理テーブル
  `bug_id` int(10) unsigned NOT NULL,
  `assigned_to` int(10) unsigned NOT NULL, #修正担当
  `hours` NUMERIC(9,2), #バグの修正時間
  PRIMARY KEY (`bug_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

さきほどのFLOATのときと同じ計算をしてみると・・

SELECT hourly_rate FROM Accounts WHERE account_id = 123;
59.95

SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id =123;
59950000000.00 #FLOATのときは59950000762.939だった

SELECT * FROM Accounts WHERE hourly_rate = 59.95
+------------+-------------+
| account_id | hourly_rate |
+------------+-------------+
|        123 |       59.95 |
+------------+-------------+
1 row in set (0.00 sec)

まとめ

概算以外で小数点計算を行う場合はFLOAT型ではなくNUMERIC型(DECIMAL型)を使いましょう。