Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rita_cano_bika(リタ カノ)

MySQLの小数点以下の計算でつまづいたのでメモ

Last updated at Posted at 2015-10-19

SELECT文の小数点の乗算で変な挙動になってつまづいたのでメモ。

バージョン：mysql Ver 14.14 Distrib 5.6.23, for linux-glibc2.5 (x86_64) using EditLine wrapper

テーブル

CREATE TABLE hoge (id varchar(5), group_id varchar(5), value double);
INSERT INTO hoge (id, group_id, value) VALUES ('A1', 'A',  0.0001234567);
INSERT INTO hoge (id, group_id, value) VALUES ('A1', 'A',  0.0001111111);
INSERT INTO hoge (id, group_id, value) VALUES ('A1', 'B',    0.02222222);
INSERT INTO hoge (id, group_id, value) VALUES ('A1', 'A',   0.003333333);
INSERT INTO hoge (id, group_id, value) VALUES ('A1', 'B',   0.004444444);
INSERT INTO hoge (id, group_id, value) VALUES ('A2', 'B', 0.00005555555);

SELECT * FROM hoge;
+------+----------+---------------+
| id   | group_id | value         |
+------+----------+---------------+
| A1   | A        |  0.0001234567 |
| A1   | A        |  0.0001111111 |
| A1   | B        |    0.02222222 |
| A1   | A        |   0.003333333 |
| A1   | B        |   0.004444444 |
| A2   | B        | 0.00005555555 |
+------+----------+---------------+
6 rows in set (0.00 sec)

やりたいこと

やりたいことは指定したidのみを抽出し、カウントしたgroup_idごとの値とそのレコードのvalueを乗算する。
例えばidは'A1'を指定するとする。
group_idが'A'のレコードは３つあるので「value * 3」を各'A'のレコードに行わせる。
group_idが'B'のレコードは２つある（３つあるが、idが'A2'のgroup_ID'B'は除外）ので「value * 2」を各'B'のレコードに行わせたい。

1レコード目は0.0001234567 * 3を行い、その結果をresult列に出力する。
2レコード目は0.0001111111 * 3を行い、同じくresult列に出力する。
同じようなことを他レコードにも行う。
この作成したSELECT文はこちら。

SELECT文
SELECT root.id,
       root.group_id,
       root.value,
       cnt.valuecount,
       root.value * cnt.valuecount AS result
  FROM hoge root 
  INNER JOIN (SELECT id, group_id, COUNT(value) AS valuecount
              FROM hoge
              WHERE id = 'A1'
              GROUP BY id, group_id) cnt
   ON cnt.id = root.id
  AND cnt.group_id = root.group_id;

結果

結果
+------+----------+--------------+------------+----------------------+
| id   | group_id | value        | valuecount | result               |
+------+----------+--------------+------------+----------------------+
| A1   | A        | 0.0001234567 |          3 |         0.0003703701 |
| A1   | A        | 0.0001111111 |          3 |         0.0003333333 |
| A1   | B        |   0.02222222 |          2 |           0.04444444 |
| A1   | A        |  0.003333333 |          3 | 0.009999998999999999 |
| A1   | B        |  0.004444444 |          2 |          0.008888888 |
+------+----------+--------------+------------+----------------------+
5 rows in set (0.00 sec)

idが'A1'だけはうまく抽出されている。group_idごとのカウント（valuecount）もよし。
あとは、SELECT文の中に書いた「value * valuecount」の結果「result」を見る。
1レコード目の「0.0001234567 * 3」の結果は「0.0003703701」なのでOK。
2レコード目の「0.0001111111 * 3」の結果は「0.0003333333」なのでOK。
しかし、読み進めてみると、4レコード目のresultが明らかにおかしい。
「0.003333333 * 3」は「0.009999999」なのにresultは「0.009999998999999999」。なんじゃこら。

試行錯誤

試しに素の値で計算してみる。

select 0.003333333 * 3;
+-----------------+
| 0.003333333 * 3 |
+-----------------+
|     0.009999999 |
+-----------------+
1 row in set (0.00 sec)

こちらはちゃんと計算される・・。どういうことなの・・。
先ほどのSELECT文の中に無理やり素の値を入れてみる。

試行錯誤
SELECT root.id,
     root.group_id,
     root.value,
     cnt.valuecount,
     root.value * 3 AS result
  FROM hoge root
  INNER JOIN (SELECT id, group_id, COUNT(value) AS valuecount
              FROM hoge
              WHERE id = 'A1'
              GROUP BY id, group_id) cnt
   ON cnt.id = root.id
  AND cnt.group_id = root.group_id;
+------+----------+--------------+------------+----------------------+
| id   | group_id | value        | valuecount | result               |
+------+----------+--------------+------------+----------------------+
| A1   | A        | 0.0001234567 |          3 |         0.0003703701 |
| A1   | A        | 0.0001111111 |          3 |         0.0003333333 |
| A1   | B        |   0.02222222 |          2 |           0.06666666 |
| A1   | A        |  0.003333333 |          3 | 0.009999998999999999 |
| A1   | B        |  0.004444444 |          2 |          0.013333332 |
+------+----------+--------------+------------+----------------------+
5 rows in set (0.01 sec)

な・・なんだと・・・。
もしかするとroot.valueの方が悪いのかな？

SELECT root.id,
       root.group_id,
       root.value,
       cnt.valuecount,
       0.0003333333 * cnt.valuecount AS result
  FROM hoge root 
  INNER JOIN (SELECT id, group_id, COUNT(value) AS valuecount
              FROM hoge
              WHERE id = 'A1'
              GROUP BY id, group_id) cnt
   ON cnt.id = root.id
  AND cnt.group_id = root.group_id;
+------+----------+--------------+------------+--------------+
| id   | group_id | value        | valuecount | result       |
+------+----------+--------------+------------+--------------+
| A1   | A        | 0.0001234567 |          3 | 0.0009999999 |
| A1   | A        | 0.0001111111 |          3 | 0.0009999999 |
| A1   | B        |   0.02222222 |          2 | 0.0006666666 |
| A1   | A        |  0.003333333 |          3 | 0.0009999999 |
| A1   | B        |  0.004444444 |          2 | 0.0006666666 |
+------+----------+--------------+------------+--------------+
5 rows in set (0.00 sec)

今度はちゃんと動いた。root.valueの方が何かよくないっぽい。
そういうことならroot.valueを文字列→数値に変換すれば動くんじゃないかな？（またこれか

試行錯誤の末

試行錯誤の末
SELECT root.id,
       root.group_id,
       root.value,
       cnt.valuecount,
       CAST(CONCAT(root.value) AS DECIMAL(32,24)) * cnt.valuecount AS result
  FROM hoge root 
  INNER JOIN (SELECT id, group_id, COUNT(value) AS valuecount
              FROM hoge
              WHERE id = 'A1'
              GROUP BY id, group_id) cnt
   ON cnt.id = root.id
  AND cnt.group_id = root.group_id;
結果
+------+----------+--------------+------------+----------------------------+
| id   | group_id | value        | valuecount | result                     |
+------+----------+--------------+------------+----------------------------+
| A1   | A        | 0.0001234567 |          3 | 0.000370370100000000000000 |
| A1   | A        | 0.0001111111 |          3 | 0.000333333300000000000000 |
| A1   | B        |   0.02222222 |          2 | 0.044444440000000000000000 |
| A1   | A        |  0.003333333 |          3 | 0.009999999000000000000000 |
| A1   | B        |  0.004444444 |          2 | 0.008888888000000000000000 |
+------+----------+--------------+------------+----------------------------+
5 rows in set (0.00 sec)

できた！
「CONCAT(root.value)」でdouble型のroot.valueを一度文字列に変換して、「CAST(◆ AS DECIMAL(32,24))」で全体桁32桁、小数点以下24桁（桁は適当）のdecimal型に変換。

けど、これは原因がわかってないし、なんなんだろう。

#追記
単純に0.3を2進数で処理するために起こっていた誤差でした。
表示上は「0.003333333」だけど内部値は違うということですね。
なんと初歩的なミス・・。
小数計算で発生する「誤差」

DECIMALが10進の固定小数点で処理するとのことなので、こういう扱い方をするときはカラムをこの型にしてしまうと良いね。
https://dev.mysql.com/doc/refman/5.6/ja/precision-math-decimal-characteristics.html

あまり型変換はしたくないんだけどね。

もしわかる人いればコメントお願いします（ﾍﾟｺﾘ
追記：コメントありがとうございました！

最後は「drop table if exists hoge;」でテーブルを消しておいてね。

6
10
5

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?