【MySQL 小ネタ】四則演算について

慣れていないうちはただのたし算かけ算でもミスしがちです。
簡単な計算問題で練習しましょう。

まずは簡単なたし算

次のようなテーブルを用意します。

CREATE TABLE calc_test (
num_a int(10) DEFAULT NULL,
num_b int(10) DEFAULT NULL,
num_c int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into calc_test values (1,2,3),(4,5,6),(7,8,9);

まずはたし算をしてみます。

select num_a + num_b + num_c from calc_test;

行ごとにたし算が行われて、6,15,24という数値が行ごとに表示されるでしょうか？
このように、MySQL（に限ったものではないですが）では行ごとに計算が行われます。
これらは他の四則演算についても同様です。
では合計値を表示するsum関数を使ってみます。

select sum(num_a),sum(num_b),sum(num_c) from calc_test;

12,15,18という数値が列ごとにならんで表示されるはずです。
sum()のような関数を実行すると列で計算されています。

かけ算を行なう時の注意点

では次のようにかけ算を行なうとどうなるでしょう？

select sum(num_a * num_b * num_c) from calc_test;

630という数値が表示されます。わかりやすくすると
(1*2*3) + (4*5*6) + (7*8*9)
という計算が行われます。
実際の動きとして、まず行の中身が計算され、最終的に足し合わされます。
この順番を理解していないと思わぬ結果になりがちです。
意図的に行なう場合を除き、次のような計算をしないように注意しましょう。

select sum(num_a)*sum(num_b)*sum(num_c) from calc_test;

足し上げた結果を掛け算するので、かなり数字が大きくなってしまいます。

例えば、商品単価と個数の合計を計算する場合などで、
sum(単価*個数)と書くのとsum(単価)*sum(個数)と書くのでは大きく計算結果が変わります。
前者が求めたい結果となるはずです。

NULLが混在した場合の注意点

NULLがある場合は特に注意が必要です。先ほどのテーブルに次のデータを追加します。

insert into calc_test values (10,11,null)

列ごとに合計値を出す時は

select sum(num_a),sum(num_b),sum(num_c) from calc_test;

ですね。それぞれ「22,26,18」になると思います。
さて、これらを足し合わせると66という数値になりますが、計算をするには次のように実行します。

select sum(num_a)+sum(num_b)+sum(num_c) from calc_test;#・・・①

これは実行すると66になると思いますが、次のように実行するとどうでしょう。

select sum(num_a + num_b + num_c) from calc_test;#・・・②

これだと45という数値になってしまいます。

試しにsum関数を外してみましょう

select num_a + num_b + num_c from calc_test;

「6,15,24,NULL」という表示がされたと思います。
sum()で実行した時には正しく計算されるのに、NULLを含めて四則演算を行なうとNULLという結果が返ってきてしまいます。
①の時はsum()の結果をたし算しているので正しい答えが返ってきます。

慣れてないうちは間違えやすいので注意が必要です。

もし計算を行いたい時は次のようにcoalesce()を使うのも手です。
（ちょっと書くのは面倒ですね）

select coalesce(num_a,0)+coalesce(num_b,0)+coalesce(num_c,0) from calc_test

NULLだった部分が計算されていることを確認しましょう。

まとめ

SQLが実行されるときは、行ごとに処理が行われるため、四則演算など順番を間違えないように注意しましょう。

また、NULLを含む場合は扱いに注意が必要です。
特に四則演算を行なうことがある場合にうっかりしてしまわないようにしましょう。
もしNULLである必要がなければ、他の値や文字列で予め変換をしておくなどが必要になります。
適切な方法で計算を行いましょう。