SQLの計算およびインサート

Question

SQL文で登録されているデータを計算し、
計算したデータを同じテーブルに登録するような処理を自動で行うことは可能でしょうか。

1つ目      ２つ目     ３つ目
100         100        100
200         300        200
400         400        200

という様なテーブルがあったとしたら

1つ目      ２つ目     ３つ目     合計
100         100        100       300
200         300        200       700
400         400        200       1000

こんな感じで合計をだしたカラムを作りたいです。

追記--
合計のカラムには最初は値が入ってない想定です。（１つ目２つ目３つ目が変更されるのでそれに準じて合計の値も変更が自動でされる）
よろしくお願いします。

Comments

[maisumakun]

データベースエンジンは何でしょうか。

[退会済みユーザー]

合計という列は、初めからテーブルに存在するのですか？1つ目〜3つ目が既に登録されているところから、合計を計算して、合計列に入れる（更新する）のですか？

Answer 1

MySQL5.7 をお使いでしたら、ジェネレーテッド・カラムを使うのが良さそうです。
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_generated_column

A column whose value is derived on demand by computing a set of expressions or functions.

これを使用すると、
「１つ目２つ目３つ目が変更されるのでそれに準じて合計の値も変更が自動でされる」
を、実現できます。

以下の例ではcol_sumがジェネレーテッド・カラムで、col_1 + col_2 + col_3の結果を表現しています。

mysql> CREATE TABLE tbl (
    ->   col_1 int,
    ->   col_2 int,
    ->   col_3 int,
    ->   col_sum int AS (col_1 + col_2 + col_3)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO tbl (col_1, col_2, col_3) VALUES 
    ->   (100, 100, 100),
    ->   (200, 300, 200),
    ->   (400, 400, 200);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl;
+-------+-------+-------+---------+
| col_1 | col_2 | col_3 | col_sum |
+-------+-------+-------+---------+
|   100 |   100 |   100 |     300 |
|   200 |   300 |   200 |     700 |
|   400 |   400 |   200 |    1000 |
+-------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE tbl SET col_3 = col_3 * 2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tbl;
+-------+-------+-------+---------+
| col_1 | col_2 | col_3 | col_sum |
+-------+-------+-------+---------+
|   100 |   100 |   200 |     400 |
|   200 |   300 |   400 |     900 |
|   400 |   400 |   400 |    1200 |
+-------+-------+-------+---------+
3 rows in set (0.00 sec)

MySQL5.6 以前をお使いであれば、トリガーを使用すれば同様のことが実現できます。
https://dev.mysql.com/doc/refman/5.6/ja/create-trigger.html

トリガーとは、テーブルに関連付けられ、そのテーブルに対して特定のイベントが発生するとアクティブ化される名前付きデータベースオブジェクトのことです。

対象のテーブルに対して INSERT および UPDATE が実行された際にcol_sumの値を再計算するトリガーを作成すればよいです。

mysql> CREATE TABLE tbl_2 (
    ->   col_1 int,
    ->   col_2 int,
    ->   col_3 int,
    ->   col_sum int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON tbl_2 FOR EACH ROW SET NEW.col_sum = NEW.col_1 + NEW.col_2 + NEW.col_3;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER upd_sum BEFORE UPDATE ON tbl_2 FOR EACH ROW SET NEW.col_sum = NEW.col_1 + NEW.col_2 + NEW.col_3;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tbl_2 (col_1, col_2, col_3) VALUES
    ->   (100, 100, 100),
    ->   (200, 300, 200),
    ->   (400, 400, 200);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl_2;
+-------+-------+-------+---------+
| col_1 | col_2 | col_3 | col_sum |
+-------+-------+-------+---------+
|   100 |   100 |   100 |     300 |
|   200 |   300 |   200 |     700 |
|   400 |   400 |   200 |    1000 |
+-------+-------+-------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE tbl_2 SET col_3 = col_3 * 2;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tbl_2;
+-------+-------+-------+---------+
| col_1 | col_2 | col_3 | col_sum |
+-------+-------+-------+---------+
|   100 |   100 |   200 |     400 |
|   200 |   300 |   400 |     900 |
|   400 |   400 |   400 |    1200 |
+-------+-------+-------+---------+
3 rows in set (0.00 sec)

追記

MySQLでは、col_1, col_2, col_3のいずれかが NULL の場合、

col_1 + col_2 + col_3 = NULL

となります。
https://dev.mysql.com/doc/refman/5.6/ja/problems-with-null.html

NULL を含む式は、式に関連する演算子および関数のドキュメントに示されている場合を除き、常に NULL 値を生成します。

"合計をだしたカラム"に NULL が格納されるのを避けたい場合、
yambejp様のコメントにある通り、col_1, col_2, col_3に NOT NULL 制約をつけるか、
NULL を適切な値に変換した上で合計を計算するなどの対処をしてください。

Comments

[yambejp]

KiyoshiMotokiさんの書き方を支持しますが
可能であればcol_1~3にNOT NULL属性をつけてデフォルトを0にするか
仕様上col_1~3のいずれかもしくは全部がNULLになる場合が想定されるなら
それでも合計が出せるように
SET NEW.col_sum = COALESCE(NEW.col_1,0) + COALESCE(NEW.col_2,0) + COALESCE(NEW.col_3,0);
のような処理をいれると良いかもしれませんね

[KiyoshiMotoki]

yambejp様

ご指摘ありがとうございます。
確かに、おっしゃる通りですね。

回答に補足を追記させていただきました。

Answer 2

あえて自動登録する、という主題から観点を外した回答をしてみます。

もしも見せ方としてそ合計列が欲しいだけで実テーブルへのこだわりがないとしたら、
一般的なDBMSが漏れなくサポートしてるであろうビュー機能を用いるという解決策もあると思います。
(Oracle、SQL Sever、MySQLなどいずれもサポートされており、標準SQLの範囲なのでベンダ依存もほとんどありません。)

MySQLでビューを用いるなら下記のような感じで定義します。

CREATE OR REPLACE VIEW sample_view AS 
SELECT 
    col1
,   col2
,   col3
,   col1 + col2 + col3 AS total
FROM
    sample_table

利用するときはビュー名で下記のようにアクセスします。

SELECT 
    col1
,   col2
,   col3
,   total
FROM
    sample_view

ビューのメリットとしては、
実体を持たないという特徴があるため、
実テーブル側の値やデータ数が変わった場合も、自動計算して出力してくれる点です。

ただしデメリットもまた実体を持たないことで、
内部的にはビューで定義したSELECT句が起動されるだけなので、
下手な使い方をするとパフォーマンス的に不利となります。

ちなみにビューを経由したINSERTやUPDATEなどには色々と制約があるので、
その際は素直に実テーブルに対して行うと良いでしょう。

INSERT INTO sample_table (col1, col2, col3) VALUES (100, 500, 400)

尚ビューを用いる場合も例に漏れず、
KiyoshiMotokiさんの回答にあるようなNULL演算の問題が起こり得ます。
これはMySQLに限った話ではなく、
一般的DBMSは全てこのような挙動を取ります。
参考までにNULLの話題を扱ったQAもありますので、
興味があるようでしたら一読して下さい。

対策方法としては、
実テーブル側にカラムにNOT NULL制約をかけるか、
SELECTする際にベンダ拡張のIFNULL関数か標準SQL関数のCOALESCE関数を利用してNULLをエスケープするかしておきましょう。

Answer 3

追記
自動でという前提を忘れていたので、下記は忘れてください。

---

合計のカラムは最初からあるという前提ですよね。
単純に全レコードをそれぞれけいさんするのであれば次のようになります。
（1つ目：n1、2つ目：n2、3つ目：n3、合計：total、とする）

update tbl set total=n1+n2+n3

特定のレコードのみなど条件を付けたいのであれば、WHERE句を指定することもできます。

update tbl set total=n1+n2+n3 where 条件...

Answer 4

Oracle12.1.0EE 64bit for Windows

CREATE TABLE TAB1
(
NUM1 NUMBER(5)
, NUM2 NUMBER(5)
, NUM3 NUMBER(5)
) ;

INSERT INTO TAB1(NUM1, NUM2, NUM3) VALUES(100, 100, 100) ;
INSERT INTO TAB1(NUM1, NUM2, NUM3) VALUES(200, 300, 200) ;
INSERT INTO TAB1(NUM1, NUM2, NUM3) VALUES(400, 400, 200) ;
COMMIT ;

-- と、ここまでが準備

-- 合計を取得する時に計算するので合計は通常列では持ちません。

COLUMN NUM1 HEADING "1つ目"
COLUMN NUM2 HEADING "2つ目"
COLUMN NUM3 HEADING "3つ目"
COLUMN GOKEI HEADING "合計"
SELECT T1.NUM1
, T1.NUM2
, T1.NUM3
, T1.NUM1 + T1.NUM2 + T1.NUM3 AS GOKEI
FROM TAB1 T1 ;

    1つ目         2つ目         3つ目          合計

---

      100           100           100           300
      200           300           200           700
      400           400           200          1000

Comments

[freeair.]

ありがとうございます。可能であれば、計算した値を実際にテーブルに登録するところまでの
動きを作りたいと思ってます。よろしくお願いします。

[Orlofsky]

テーブル TAB1 に GOKEI 列があったとして、
UPDATE TAB1 T1 SET
T1.GOKEI = T1.NUM1 + T1.NUM2 + T1.NUM3 ;