非数値（NaN）なら別の値に変換
NANVL ( float_expr1 , float_expr2 ) 
return [ binary_float | binary_double ]
| float_expr1 | 非数値を含む可能性のある浮動小数点式 |
| float_expr2 | 浮動小数点式 |
- 式 float_expr1 が 非数値(NaN) なら 式 float_expr2 の値を戻す
- 式 float_expr1 が 数値、または、無限大なら 式 float_expr1 の値を戻す
NANVL 関数の内容
NANVL は、たぶん Non Available Number Value Logic の略
非数値を含む可能性のある浮動小数点式 float_expr1 が非数値（NaN: Non available Number）の場合に 浮動小数点式 float_expr2 に変換する。
浮動小数点 (BINARY_FLOAT、BINARY_DOUBLE) のみ有効な関数
注意
非数値をデータに含んでいる場合に集計（SUM）や平均（AVG）関数は計算できずに NaN を戻す。そのために Nan を NULL にするため(※) にあるかと思われる表記であったが、実際にテストして 浮動小数点式 float_expr2 に NULL を指定すると戻り値はすべて NULL になってしまう。
(※) 特に AVG 関数は NULL 項目を計算から除外するので 0 に置換した場合と NULL に置換した場合では結果が異なり、マニュアルにも、それをうかがわせる説明もあるので NULL がサポートされるように変更されることを期待する。
NANVL 使用例
SQL> set null '<NULL>'
SQL> select d, num, NANVL(d, 0), NANVL(d, null) from nanvl_sample
D NUM NANVL(D,0) NANVL(D,NULL)
---------- ---------- ---------- -------------
3.0E+000 3 3.0E+000 <NULL>
4.0E+000 4 4.0E+000 <NULL>
5.0E+000 5 5.0E+000 <NULL>
Nan <NULL> 0 <NULL>
Inf <NULL> Inf <NULL>
-Inf <NULL> -Inf <NULL>
⇒ SQL*Plus の NULL 表示
プラス無限大、マイナス無限大、非数値を含む演算を行なうと符号などによって結果が NaN になる。
SQL> select count(*),sum(num), avg(num),
2 sum(d), avg(d) from nanvl_sample;
COUNT(*) SUM(NUM) AVG(NUM) SUM(D) AVG(D)
---------- ---------- ---------- ---------- ----------
6 12 4 Nan Nan
^^^ ^^^
上記のデータから 非数値、無限大を取り除いて合計と平均を求める。
SQL> select count(*),sum(num), avg(num),
2 sum(NANVL(d,0)), avg(NANVL(d,0))
3 from nanvl_sample where d not in ('NaN', '+INF', '-INF');
COUNT(*) SUM(NUM) AVG(NUM) SUM(NANVL(D,0)) AVG(NANVL(D,0))
---------- ---------- ---------- --------------- ---------------
3 12 4 1.2E+001 4.0E+000
^^^ ^^^^^^^^^ ^^^^^^^^
データから正と負の無限大を取り除き「非数値を 0 」にて変換し合計と平均を求める。
SQL> select count(*),sum(num), avg(num),
2 sum(NANVL(d,0)), avg(NANVL(d,0))
3 from nanvl_sample where d not in ('+INF', '-INF');
COUNT(*) SUM(NUM) AVG(NUM) SUM(NANVL(D,0)) AVG(NANVL(D,0))
---------- ---------- ---------- --------------- ---------------
4 12 4 1.2E+001 3.0E+000
^^^ ^^^^^^^^ ^^^^^^^^
NULL 値のため除外 0 に変換したため 3.0
SQL 関数 - NANVL 関連
