読者です 読者をやめる 読者になる 読者になる

計算で総積を求める際の注意点

SQL

項目の総積を求める。 - だらだらやるよ。

あー、計算で総積を求める場合、注意しなければならない点が 2 つほどあります。
あ、ちなみに SQL Server では loge は log 関数を使います。で、log10 は log10 関数。

  • log 関連の関数には 0 や負の数が渡せない
  • 誤差を覚悟する必要がある

log 関連の関数には 0 や負の数が渡せない

log の定義は簡単に書くと、


y = a^x

としたときの

x = log_a y

なので、y に 0 や負の数を渡した場合、

0 = a^x



-n = a^x

を満たす x の存在を認めることになる。
数学詳しくないからそんな x がなんかあるのかもしれない (定義できるのかもしれない) けど、RDBMS の log 関連の関数はこれらをエラーとするのが普通。
なので、

exp(sum(ln(target_column)))
項目の総積を求める。 - だらだらやるよ。

だと、target_column に 0 や負の数が含まれるとエラーになる。


これの解決策としては、NULLIF 関数を使って 0 は NULL に変換しておき、負の数は絶対値を取ることで回避、負の数の数を数えて奇数なら後でマイナスを付ける、とかかな。
0 が含まれた場合は結果が NULL になるので、COALESCE 関数で囲って、COALESCE(..., 0) としておくといい。
追記:
SUM の中に NULLIF 置いたってムダじゃん・・・
下のエントリで実際に書いてみたんでよければ参考にして下さい。


あ、なんでこれで総積が求められるかは、上の定義と


log(a \: \times \: b) = log a \: + \: log b

から、明らか。
sum をばらして、

e^{log_e \text{target_1} \: + \: log_e \text{target_2} \: \dots \: + \: log_e \text{target_n}}

log 同士の足し算を掛け算に変換して、

e^{log_e(\text{target_1} \: \times \: \text{target_2} \: \dots \: \times \: \text{target_n})}

あとは、上の定義を使うと、

y = e^x

の x に

x = log_e y

を代入して、

y = e^{log_e y}

なので、

e^{log_e(\text{target_1} \: \times \: \text{target_2} \: \dots \: \times \: \text{target_n})}

はつまり

\text{target_1} \: \times \: \text{target_2} \: \dots \: \times \: \text{target_n}

と等しくなる。
ただし、これは log が 0 以下の場合は上で言ったように成り立たないので、注意が必要。

誤差を覚悟する必要がある

全てが正の数だったとしても、log の計算途中で浮動小数点数を使うことになるので、誤差はどうしても避けられない。
なので、誤差が発生する可能性があるということは覚えておく必要がある。

あ、

ちなみに SQL Server 2005 からはユーザ定義の集約関数が定義できるから、まぁ普通はそっちに流れるんでしょうね。
つか、そっちの方がいいと思うよ、うん。