SQLで列の要素すべての和を求めるためにSUM関数を使います。
一方で列の要素すべての積を求めるためにはどうすればいいでしょうか？
※以下のSQLはすべてGoogle BigQueryのStandardSQLで動作確認を行っています。
目的
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
上のようなテーブルTが与えられた時、n列の数値すべての積の計算を行うSQLを書く。
解答
とりあえず、答えを書いておきます。
SELECT
CAST(
ROUND(
IF(
COUNTIF(n = 0) > 0,
0,
EXP(SUM(LN(IF(n = 0, NULL, ABS(n))))) * IF(MOD(COUNTIF(SIGN(n) = -1), 2) = 0, 1, -1)
)
) AS INT64
)AS prod
FROM
T
方法
この答えに至る道筋です。
積を和に変換
そのものずばりな関数はないので、工夫して計算する必要があります。
累乗の性質を利用して、$ \prod $ を $ \sum $ に変換しましょう。
計算尺の原理と同じですね。
$$
\prod_n a_n = \exp \left\{ \sum_n \ln \left(a_n \right) \right\} \qquad (1)
$$
この式をSQLにしてみます。
SELECT
EXP(SUM(LN(n))) AS prod
FROM
T
実行してみると、119.99999999999997という結果になりました。
計算途中で浮動小数点型に変換している部分があるので、それに起因する誤差が発生しました。
今回のケースでは、計算結果が整数であることは明らかなので、四捨五入をした後に整数型にキャストしましょう。
計算対象の列が浮動小数点型のときには、この処理は不要です。
SELECT
CAST(ROUND(EXP(SUM(LN(n)))) AS INT64) AS prod
FROM
T
これで、結果が120になりました。
負の数に対応
ところが、上のSQL文ではまだ問題のある場合があります。
それは、かけ合せる要素内に負の数が存在する場合です。
このときにはLN関数引数が定義域の外に出るためエラーになってしまいます。
SIGN関数を使って、この問題を解決してみましょう。
(1)式を $ a_n $ が負の場合も考慮して拡張すると以下のようになります。
$$
\prod_n a_n = \exp \left\{ \sum_n \ln \left| a_n \right| \right\} \times \prod_n sign( a_n )
$$
前半の因子は前節と同じ方法で計算ができます。
後半の因子はSIGN関数の性質から以下のように計算することができます。
なお、あるnに対して $ a_n $ が $ a_n = 0 $ になるケースについては次節で考慮するので、ここでは考えないこととします。
$$
\prod_n sign(a_n) = \left\{
\begin{array}{ll}
1 & \mathrm{the\ number\ of\ } sign(a_n) = -1 \mathrm{\ is\ even} \\
-1 & \mathrm{the\ number\ of\ } sign(a_n) = -1 \mathrm{\ is\ odd}
\end{array}
\right.
$$
これをSQLで表現すると以下のようになります。
SELECT
CAST(
ROUND(
EXP(SUM(LN(ABS(n)))) * IF(MOD(COUNTIF(SIGN(n) = -1), 2) = 0, 1, -1)
) AS INT64
)AS prod
FROM
T
これで一件落着かと思いきや、まだ考慮する必要のある場合があります。
ゼロに対応
前節で紹介したSQL文でも、 $ a_n $ が0の場合にはエラーになってしまいます。
それに対応するためのIF式を挿入します。
1つ以上の因子が0の時には積が0になることをSQLで表現してみます。
SELECT
CAST(
ROUND(
IF(
COUNTIF(n = 0) > 0,
0,
EXP(SUM(LN(ABS(n)))) * IF(MOD(COUNTIF(SIGN(n) = -1), 2) = 0, 1, -1)
)
) AS INT64
)AS prod
FROM
T
これでは、まだエラーになってしまいます。
IF関数の評価が正格なために、第1引数がTRUEの時にも第3引数の評価が行われてしまいます。
LN関数の引数を強制的にNULLにしてしまいましょう。
SELECT
CAST(
ROUND(
IF(
COUNTIF(n = 0) > 0,
0,
EXP(SUM(LN(IF(n = 0, NULL, ABS(n))))) * IF(MOD(COUNTIF(SIGN(n) = -1), 2) = 0, 1, -1)
)
) AS INT64
)AS prod
FROM
T
これで完成です。