BigQueryのSQLいろいろ (1) 数値型

BigQueryのSQLについて、ドキュメントを読んだり実験したりしながら挙動を解き明かしていこうと思います。第1回は数値型についてです。

はじめに

さいきん業務でBigQuery SQLのコードジェネレーターを書く機会があったので、これを機にBigQuery SQLに詳しくなろうと思って始めることにしました。

4つの数値型

BigQueryの数値型は4つあります。逆に言うと4つしかありません。

整数型 (INT64)
浮動小数点数型 (FLOAT64)
2種類の十進数型 (NUMERIC, BIGNUMERIC)

整数型

整数型は INT64 と呼ばれ、 INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT はその名前に反して全て INT64 と同義です。

範囲は名前の通りで、デフォルトの算術演算はオーバーフローをエラーにします。

浮動小数点数型

浮動小数点数型は FLOAT64 と呼ばれ、名前の通りIEEE754倍精度浮動小数点数を表します。ただし、計算の挙動はSQL向けに調整されています。

IEEE754では通常の実数値セマンティックスを持つ値に加えて、以下の特殊な値があります。

負のゼロ
正負の無限大
NaN (sNaNはここでは考えない)

通常の実数値と負のゼロはまとめて「有限値」と呼びます。IEEE754では計算結果がオーバーフローしたり定義不可能な場合でも、無限大やNaNを返すことで計算が続行されます(sNaNを除く)。しかし、BigQueryでは以下のようなルールがあります。

有限値の入力に対して結果が有限でなかった場合は、エラーとする。

このルールにより、NaNや無限大は自然発生しないようになっています。いっぽう、以下のような例外もあります:

SAFE_DIVIDE のように例外処理を行う関数を使うことで、エラーのかわりにNULLを得ることができます。
IEEE_DIVIDE ではIEEE754の規定通りの計算を行います。

SELECT 0.0 / 0.0 -- Error
SELECT 1.0 / 0.0 -- Error
SELECT
  SAFE_DIVIDE(0.0, 0.0), -- NULL
  SAFE_DIVIDE(1.0, 0.0), -- NULL
  IEEE_DIVIDE(0.0, 0.0), -- NaN
  IEEE_DIVIDE(1.0, 0.0)  -- +inf

非有限値を生成する関数は、調べた限りではIEEE_DIVIDEのみのようです。

無限大の入力に対する振舞いは関数によります。通常、連続性を考慮したIEEE754の規定と整合する振舞いになると考えられます。

NaNの入力に対しては、ほとんどの関数がNaNを返します。特に COALESCE は NaN を消しません。また、AVGなどの集計関数もNaNを計算対象に含めます。

SELECT COALESCE(IEEE_DIVIDE(0.0, 0.0), 42.0) -- NaN
SELECT AVG(x) FROM UNNNEST([1.0, IEEE_DIVIDE(0.0, 0.0)]) -- NaN

例外は以下の通りです。

IS_NAN は NaN に対して TRUE を返します。
POW(1.0, NaN) は 1.0 を返します。
POW(NaN, 0.0) は 1.0 を返します。

また興味深いことに、BigQueryは基本的には負のゼロを通常通りに取り扱うことができますが、テーブルに保存 (INSERT INTO) したときだけ符号が欠損します。

CREATE TABLE IF NOT EXISTS `tmp.ieee754_test` (name STRING, value FLOAT64);

INSERT INTO `tmp.ieee754_test`
SELECT * FROM UNNEST([
  STRUCT('positive infinity' AS name, IEEE_DIVIDE(1.0, 0.0) AS value),
  STRUCT('negative infinity' AS name, IEEE_DIVIDE(-1.0, 0.0) AS value),
  STRUCT('positive zero' AS name, 0.0 AS value),
  STRUCT('negative zero' AS name, IEEE_DIVIDE(1.0, IEEE_DIVIDE(-1.0, 0.0)) AS value),
  STRUCT('nan' AS name, IEEE_DIVIDE(0.0, 0.0) AS value)
])
-- | positive infinity |  Infinity |
-- | negative infinity | -Infinity |
-- | positive zero     |       0.0 |
-- | negative zero     |      -0.0 |
-- | nan               |       NaN |

そのため、中間テーブルに -0 が発生するようなケースでは、そのテーブルをオンメモリで使い回したときと永続化したときで結果が異なるというような現象が起きる可能性があります。

10進数型

BigQueryには2種類の10進数型があります。

NUMERIC (または DECIMAL) は 128bit符号つき整数に 10^-9 をかけ (-10^29, 10^29) の範囲に限定した固定小数点数です。
BIGNUMERIC (または BIGDECIMAL) は 256bit符号つき整数に 10^-38 をかけた固定小数点数です。

内部表現は、データ型の制約の説明や論理サイズ情報からの推定です。

整数と同様、オーバーフローはエラーになります。また、端数は絶対値の大きい方向に四捨五入されます。

SELECT
  NUMERIC '50000000000000000000000000000' +
  NUMERIC '50000000000000000000000000000'
  -- Error

SELECT
  NUMERIC  '0.000000014' / NUMERIC '10', -- 0.000000001
  NUMERIC  '0.000000015' / NUMERIC '10', -- 0.000000002
  NUMERIC  '0.000000025' / NUMERIC '10', -- 0.000000003
  NUMERIC '-0.000000015' / NUMERIC '10', -- -0.000000002
  NUMERIC '-0.000000025' / NUMERIC '10'  -- -0.000000003