【MySQL】sumのようにカラム同士を掛け算する方法｜神戸のホームページ制作会社ユニファースの代表ブログ

カラム同士を足し算する関数のSUMはあるんですが、掛け算する関数はMySQLには無いんですよね。Excelならば、PRODUCT関数に相当するものです。

それでも使う必要が生じまして、ネットで色々調べたのの、中々ビンゴの情報が出て来なかったのでメモしておきます。

テーブルの作成

まずは、このSQLを実行して、テーブルを作成して下さい。

CREATE TABLE test(
  date TEXT,
  num DOUBLE
);
INSERT
INTO
  test
VALUES('2015-1-1', '2');
INSERT
INTO
  test
VALUES('2015-3-1', '5');
INSERT
INTO
  test
VALUES('2015-2-1', '2.5');

CREATE TABLE test(

date TEXT,

num DOUBLE

);

INSERT

INTO

test

VALUES('2015-1-1', '2');

INSERT

INTO

test

VALUES('2015-3-1', '5');

INSERT

INTO

test

VALUES('2015-2-1', '2.5');

こんなテーブルが作成されると思います。日付の順番を替えているのはわざとです。

累計の計算

まずはこのテーブルについて、1月から順に累計を求めてみます。累計を求めるSQLは以下の通りです。

SELECT *,
(SELECT SUM(b.num) FROM test b
  WHERE b.date <= a.date) AS ruikei
FROM test a
ORDER BY a.date ASC;

SELECT *,

(SELECT SUM(b.num) FROM test b

WHERE b.date <= a.date) AS ruikei

FROM test a

ORDER BY a.date ASC;

キチンと日付順に累積が求められました。この累計については、まだよく見るアルゴリズムです。

累積（カラム同士の掛け算）の計算

カラム同士の足し算は、累計で良いんですが、掛け算は、何て言うのでしょう？累積では無いような？分かる方は教えて下さい。

さて、SQLは以下の通りです。小数点第2位以下切り捨てにしています。

参考：TRUNCATE

SELECT *,
(SELECT TRUNCATE(EXP(SUM(log(b.num)))+0.005,2) FROM test b
  WHERE b.date <= a.date) AS ruiseki
FROM test a
ORDER BY a.date ASC;

SELECT *,

(SELECT TRUNCATE(EXP(SUM(log(b.num)))+0.005,2) FROM test b

WHERE b.date <= a.date) AS ruiseki

FROM test a

ORDER BY a.date ASC;

無事にカラム同士の掛け算が出来ました。

EXP(SUM(log(b.num)))+0.005

1	EXP(SUM(log(b.num)))+0.005

の部分については、私も分かっていません。掛け算を、SUMを使って可能にするおまじない、位に考えて下さい。笑

“【MySQL】sumのようにカラム同士を掛け算する方法” への1件のフィードバック

EXP(SUM(log(b.num)))
は、対数関数を使ってかけ算を足し算にする、程度に捉えるとよいと思います。
たとえば、「1×2×3×4」は、対数関数を使うとにすると「e^{ log(1)+log(2)+log(3)+log(4) }」となります。
かけ算を足し算の形にすると扱い安い場合などによく行われます。

ちなみに、 TRUNCATE(EXP(SUM(log(b.num)))+0.005,2)
と、0.005を足し、小数点以下２桁で切捨て(TRUNCATE)しているのは、四捨五入するためです。

返信