MySQL8.0 新機能 WITH句、共通テーブル式(CTE)のSQLの書き方教えます！

MySQL8.0にするなら、WITH句で！共通テーブル式を使いましょう！

MySQL8.0を使いたい理由は、CTEに対応したからなんです！！

そもそも、MySQL 8.0を使いたかった理由って、共通テーブル式が実装されてからなんです！ 私自身は、 IBM DB2でかなりお世話になりました。やがて年は過ぎ、MySQLしか触らなくなったのですが、WITH句ってどんなRDBMSにも付いているものだとばかり思っていたのですが、無い。。MySQLは無かったんですよね（汗） おかげさまで、サブクエリーのサブクエリをごりごり書く生活に慣れてしまいました。

今回は、このCTEを利用して、どれだけ、SQLが綺麗になるのかを見せたいと思います！

テーブル

今回、CTEとして利用するテーブル定義です

CREATE TABLE `loglog` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `action` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

テーブルの内容について

内容にはまったく意味はありません（笑） userr_idの人が何時何分に、どんなactionをしたかのログが書かれていると思ってください

• Idはたんなる連番

+----------+---------------------+-----------------+---------+
| id       | created_at          | user_id         | action  |
+----------+---------------------+-----------------+---------+
| 20942439 | 2017-11-10 05:18:58 | 695112183860794 | action1 |
| 20942440 | 2017-11-10 05:18:58 | 253222068806330 | action1 |
| 20942441 | 2017-11-10 05:18:58 | 551552791477175 | action1 |
| 20942442 | 2017-11-10 05:18:58 | 543662114380014 | action1 |
| 20942443 | 2017-11-10 05:19:00 | 468660469653058 | action1 |
| 20942444 | 2017-11-10 05:19:00 | 468880469653058 | action1 |
| 20942445 | 2017-11-10 05:19:00 | 253882068806330 | action1 |
| 20942446 | 2017-11-10 05:19:00 | 415880351752174 | action1 |
| 20942447 | 2017-11-10 05:19:00 | 415770351752174 | action1 |
| 20942448 | 2017-11-10 05:19:01 | 298212855261204 | action2 |
+----------+---------------------+-----------------+---------+

求めたい結果について

• user_id=298212855261204の人が、アクションを起こした最終日時とアクション名
• アクションを起こした合計件数

このような結果出力したい（うーん、例が悪いかな・・・・）

+-----------------+---------------------+--------------------+-------------+
| user_id         | last_created_at     | total_action_count | last_action |
+-----------------+---------------------+--------------------+-------------+
| 298212855261204 | 2017-11-10 14:15:45 |                  6 | action2     |
+-----------------+---------------------+--------------------+-------------+

CTEが無い頃のSQLの書き方

WITH が無い時のSQLの書き方は、サブクエリーでこういう風に書きます。え？サブクエリー使わなくてもかけるでしょ？というツッコミはなしでお願いします

ご覧の通り、SQLのネスティング、そしてネスティングって感じで、SQL 書いている時は、そんなに気にならないのですが、数日経って、再度みたりすると、書いた本人も何書いているのかわからない時hがあります（汗

SELECT
  w1.user_id AS user_id,
  log1.created_at AS last_created_at,
  w1.cnt AS total_action_count,
  log1.action AS last_action
FROM
(
SELECT 
  user_id,
  MAX(Id) AS max_id,
  COUNT(Id) AS cnt
FROM
  loglog
WHERE
  user_id = 298212855261204
GROUP BY
  user_id
) w1
INNER JOIN
  loglog log1
ON
  w1.max_id = log1.id
;

MySQL 8.0 CTEでのイケてる書き方

こんな書き方になります。もちろん、サブクエリーの時と結果は同じです。 SQLの長さとしては、冗長ではありますが、はっきり、テンポラリテーブルを作る箇所と、 そのテンポラリテーブルを利用した、SQLとで、別れているので、サブクエリーのように、SQLの入れ子になっていないのが特徴です。CTEで書く場合、単純に、WITH w1 (user_id, max_id, cnt) だけを見ればいいので、考えやすいですよね。

-- ここで、テンポラリテーブルを作るよー！
WITH w1 (user_id, max_id, cnt) 
AS
(
SELECT 
  user_id,
  MAX(Id) AS max_id,
  COUNT(Id) AS cnt
FROM
  loglog
WHERE
  user_id = 298212855261204
GROUP BY
  user_id
) 
-- ここでテンポラリテーブル使ってSQLを実行するよー！
SELECT
  w1.user_id AS user_id,
  log1.created_at AS last_created_at,
  w1.cnt AS total_action_count,
  log1.action AS last_action  
FROM
  w1
INNER JOIN
  loglog log1
ON
  w1.max_id = log1.id
;

今回、1テーブルのみ、共通テーブル式で書きましたので、あまりメリットを感じないかもしれません。本来CTEの効果を発揮する時って、複数のテンポラリを作成する時なんですよね。上記の例を拡張して説明すると、こんなイメージで書きます（実行してないから動くかわかりませんが）

WITH w1 (user_id, max_id, cnt) 
AS
(
SELECT 
  user_id,
  MAX(Id) AS max_id,
  COUNT(Id) AS cnt
FROM
  loglog
WHERE
  user_id = 298212855261204
GROUP BY
  user_id
) ,
w2 (user_id, col1, col2) 
AS
(
SELECT 
  user_id,
  col1,
  col2
FROM
  loglog2
WHERE
  user_id = 298212855261204
GROUP BY
  user_id
)
-- ここでテンポラリテーブル使ってSQLを実行するよー！
SELECT
  w1.user_id AS user_id,
  log1.created_at AS last_created_at,
  w1.cnt AS total_action_count,
  log1.action AS last_action,
  w2.col1 AS column1,
  w2.col2 AS column2
FROM
  w1
INNER JOIN
  loglog log1
ON
  w1.max_id = log1.id
LEFT JOIN
  w2
ON
  w1.user_id = w2.user_id
;

スッキリわかるSQL入門 ドリル215問付き！ スッキリわかるシリーズ

要はSQLを分けて書いているので、以下の部分だけに集中してSQLを見れるってことを説明したいだけなんです（笑）

-- ここでテンポラリテーブル使ってSQLを実行するよー！
SELECT
  w1.user_id AS user_id,
  log1.created_at AS last_created_at,
  w1.cnt AS total_action_count,
  log1.action AS last_action,
  w2.col1 AS column1,
  w2.col2 AS column2
FROM
  w1
INNER JOIN
  loglog log1
ON
  w1.max_id = log1.id
LEFT JOIN
  w2
ON
  w1.user_id = w2.user_id
;

CTEを利用したSELECT 〜 INSERT

一瞬わからなくなりましたが、これで正解っぽいです

INSERT INTO tab2
(
  col1,
  col2,
  col3
)
WITH work_table AS
(
略
.
.
)
SELECT
  col1,
  col2,
  col3
FROM
   work_table w
LEFT JOIN
  tab1 t
ON
  w.id = t.id
WHERE
  t.id IS NULL
;

WITHはSQLの途中でもいいんだー。。。