NaN というSQL演算結果
はじめに
先日、BigQueryで「SUM関数の演算対象にNULLが含まれるときに結果はどうなるか?」という話になって、NULLになるんじゃないかな?とその時思ったけど、リファレンスを見たら普通に違った。
標準 SQL の集計関数 | BigQuery | Google Cloud
NULL 以外の値の合計を返します。
お恥ずかしながら知りませんでした...これまでずっと IFNULL, ISNULL で 0 にしていたな。俺はなんて無駄な変換を... BigQueryに限らず、SQLの集計関数は基本的にこの挙動をとるようです。
AVG関数に書かれた一文
ふぁーーつらーーと思いながら、流れで AVG関数のリファレンスを見ていたところ、気になる一文がありました。
標準 SQL の集計関数 | BigQuery | Google Cloud
NULL 以外の値の平均を返すか、入力に NaN が含まれる場合は NaN を返します。
NaN とは
NaN (Not a Number, 非数値) は、Python の Pandasライブラリで頻出しますが、BigQuery というかデータベースで NaN という表現があるとは、これも知りませんでした。
PostgreSQLリファレンスの浮動小数点データ型に、わかりやすい解説がありました。
通常の数値に加え、浮動小数点型では以下の特殊な値を取ります。
Infinity
-Infinity
NaN
これらはそれぞれ、IEEE 754の特殊な値、「無限大」、「負の無限大」、「非数値」を表します。
IEEE754 については以下のとおり。
IEEE 754(アイトリプルイーななごおよん、アイトリプルイーななひゃくごじゅうよん)は、その標記 IEEE Standard for Floating-Point Arithmetic のとおり、IEEE標準のひとつであり、浮動小数点算術に関する標準である。
そしてWikipediaに、NaN を返す演算例が出ていました。
これに該当するような計算をSQLでやったことはないな...というか普通にやったら例外が発生するのでは...
NaN を算出する
NaNを算出するため、まずは素直に0割りしてみました。
> SELECT 0/0
他に手段があるはずと調べていくと、数学関数の中に IEEE_DIVIDE
といういかにもな関数がありました。
標準 SQL の数学関数 | BigQuery | Google Cloud
> SELECT IEEE_DIVIDE(0, 0) AS IEEE_DIVIDE;
Row | IEEE_DIVIDE |
---|---|
1 | NaN |
NaN を算出することができました。これでようやく冒頭のAVG関数を確認できます。
SELECT AVG(x) AS avg FROM UNNEST([0, 2, 4, 4, IEEE_DIVIDE(0, 0)]) as x;
Row | avg |
---|---|
1 | NaN |
入力に NaN が含まれる場合は NaN を返します
を確認できました。SQLは奥が深い...