public note

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リファレンスの浮動小数点データ型に、わかりやすい解説がありました。

8.1. 数値データ型

通常の数値に加え、浮動小数点型では以下の特殊な値を取ります。

Infinity

-Infinity

NaN

これらはそれぞれ、IEEE 754の特殊な値、「無限大」、「負の無限大」、「非数値」を表します。

IEEE754 については以下のとおり。

IEEE 754 - Wikipedia

IEEE 754(アイトリプルイーななごおよん、アイトリプルイーななひゃくごじゅうよん)は、その標記 IEEE Standard for Floating-Point Arithmetic のとおり、IEEE標準のひとつであり、浮動小数点算術に関する標準である。

そしてWikipediaに、NaN を返す演算例が出ていました。

NaN - Wikipedia

これに該当するような計算をSQLでやったことはないな...というか普通にやったら例外が発生するのでは...

NaN を算出する

NaNを算出するため、まずは素直に0割りしてみました。

> SELECT 0/0

division by zero 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は奥が深い...