SQLと浮動小数点演算の誤差の話

最近BigQueryでSQLの勉強を始めました。
基本的な内容を一通り把握し、問題演習を行う中でちょっとしたトラブルに見舞われたので、備忘録として以下の記事を作成しました。

起きた事象

以下の様な架空の販売データを元に、テーブル全体の税込み売上を算出する……という問題を解く中でトラブルが発生しました。

販売データ

$$
\begin{array}{|c|c|c|} \hline
フィールド名& 種類  & 説明 \\ \hline
date & DATETIME & 販売日 \\ \hline
sales & INTEGER & 売上（税抜き） \\ \hline
\end{array}
$$

解答の前提

上記テーブルには2019年10月1日以前のデータが含まれており、販売日によって税率を8%・10%として分けて計算する必要があります。

作成したクエリ

税率が変わった2019年10月1日を基準に前後の売上を税込みにして合算するだけの簡単なクエリです。

select
  sum(if(
    date < "2019-10-01", sales * 1.08, sales *1.1
  )) as sales_with_tax
from "販売データ"

実行の結果

上記クエリを実行したところ○○○○○.9999999……と大量の小数が発生しました。
販売データ中の売上金額には、100円単位でデータが入っていた為、税率8％の計算（*1.08）でも小数が生じることはないはず……

調査

何が起きているのか把握する為に、先程のクエリを修正し、中間値を出力して見てみることにしました。

確認用のクエリ

sumでの合算を行わず、それぞれ販売日と元の金額も見えるように書き換えています。

selet
  date,
  sales,
  if(
    date < "2019-10-01", sales * 1.08, sales * 1.1
  ) as sales_with_tax
from "販売データ"

実行の結果

一部の計算で税率に関係なく誤差が発生しているようでした。
以下2件を例に取ると、それぞれ期待される結果は

• 2018/04/15 : 1800*1.08 =1944

• 2019/10/30 : 1800*1.1 = 1980

となりますが、実際には、小数点以下に誤差が生じています。

原因

小数部分で生じた誤差についてその後も調べたところ、データ型に起因する事象だと判明しました。

データ型

今回のクエリでは、各salesの値にそれぞれ「1.08」「1.1」をかけていました。BigQuery では、数値に小数点を打つと FLOAT64 型に変換されます。
このFLOAT64 型は、公式のドキュメントを参照すると「小数部分を含む近似数値」と説明されています。
この辺りの詳しい説明は自分の力には余るので割愛しますが、FLOAT64を含む計算は精度が限られている為、元から誤差が生じうるようです。

今回の事象に関連するケースとして、税率8%移行時のセブンイレブンでも「301円問題」として支払金額に誤差が生じる問題が話題となっていたようです。

解決策

今回のケースのように、正確なデータを扱いたい場合NUMERIC型を使用して計算を行うことで誤差の発生を回避できます。

解消版のクエリ

castを使用してNUMERIC型へ変換して計算を実施します。
結果として無事に誤差なく正しい数字が表示されました。

select
  sum(if(
    date < "2019-10-01", sales * cast(1.08 as NUMERIC), sales * cast(1.1 as NUMERIC)
  )) as sales_with_tax
from "販売データ"

最後に

今回のケースにおいて誤差は微々たるものでしたが、扱う額や数値が大きくなるほど誤差の値は大きく、許容し難いものになります。
これまであまり意識せずにいたデータ型・数値型ですが、その特性を理解した上で使用しないと意図せぬ誤差につながる事を今回の事象から学べたのはとてもラッキーでした。