DB/集計テーブルにクエリ一発でレコードを作る(更新する）方法

集計テーブルとかを作る場合にはSELECTしたものをINSERTしたり、SELECTしたものでUPDATEしたりすると、1つのクエリで処理でき便利です。

確認環境：MySQL5.6.21

以下のようなテーブルがあったとします。t_salesは売上の生データ、t_sales_summaryは商品毎の売上数の集計テーブルです。

CREATE TABLE IF NOT EXISTS `t_sales` (
  `sales_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL,
  PRIMARY KEY (`sales_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `t_sales` (`sales_id`, `product_id`, `amount`, `timestamp`) VALUES
(1, 1, 10, '2016-10-15 10:44:14'),
(2, 1, 20, '2016-10-15 10:44:14'),
(3, 2, 5, '2016-10-15 10:44:14'),
(4, 2, 10, '2016-10-15 10:44:14'),
(5, 3, 50, '2016-10-15 10:44:14');

CREATE TABLE IF NOT EXISTS `t_sales_summary` (
  `product_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#SELECTしたものをINSERT
これらのテーブルに対して、以下のクエリを実行すると、t_salesに対して集計処理が実行され、その結果がt_sales_summaryに格納されます。

INSERT INTO t_sales_summary(product_id, amount) 
  SELECT product_id, SUM(amount) FROM t_sales GROUP BY product_id

#SELECTしたものでREPLACE
以下のクエリを実行すると、t_sales_summaryが上書き更新されます。ただし、t_salesからレコードが削除された結果、特定の製品の売上が無くなった場合、t_sales_summaryからレコードが消えないことに注意が必要です。事前にTRUNCATEでサマリテーブルを空にするか、DELETEするか、UPDATEでamountを0にしましょう。

REPLACE INTO t_sales_summary(product_id, amount) 
  SELECT product_id, SUM(amount) FROM t_sales GROUP BY product_id

#SELECTしたものでUPDATE
以下のクエリを実行すると、t_sales_summaryがUPDATEされます。
t_sales_summaryが他のフィールドを持っていて、値を保持する必要がある場合はこの方法で更新しましょう。

UPDATE 
  t_sales_summary, 
  (SELECT product_id, sum(amount) total_amount FROM t_sales GROUP BY product_id) tbl 
SET 
  t_sales_summary.amount = tbl.total_amount 
WHERE 
  tbl.product_id = t_sales_summary.product_id 

JOIN を使って以下のように書くこともできます。上記のクエリと等価です。

UPDATE t_sales_summary
JOIN (SELECT product_id, SUM( amount ) total_amount FROM t_sales GROUP BY product_id) tbl 
  ON tbl.product_id = t_sales_summary.product_id
SET 
  t_sales_summary.amount = tbl.total_amount 

#まとめ
SELECTの結果を使って、INSERT・UPDATE・REPLACEすることで簡単に集計テーブルを作成・メンテンナスできます。
SELECT対象のテーブルからレコードが減った場合にゴミデータが残る場合があるので以下のような方法で回避しましょう。

• 事前にTRUNCATEもしくはDELETEしてしまう。
• 事前に集計対象フィールドをUPDATEで消去しておく。
• 集計結果と、マスターテーブルを外部結合することで必ず全てのレコードが更新されるようにする。（マスター消えたら知らん）
• 集計結果と、集計結果テーブルを外部結合することで必ず全てのレコードが更新されるようにする。