MySQLのWITH句を使ってみた

はじめに

こちらの記事はMySqlに触れたことがあり、WITH句の初心者向けになります。
自身も使用したり、他の方が書かれたクエリを見たりしたときに「読みやすなー」と思ったので書かせていただきました。

WITH句

公式サイト：MySQL 8.0 リファレンスマニュアル / ... / WITH (共通テーブル式)より

WITH句はMySQL8.0の新機能です。
WITH句は、単一ステートメントのスコープ内に存在し、あとでそのステートメント内で複数回参照できる名前付き一時結果セットです。

また、記事内でJOIN句なども使いますが、説明は割愛します。

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

このような形で使えます。cte1は一度の指定で何度でも使えます。
計算を伴うクエリを書くとこのようなのもかけます。

WITH
  -- 女性のユーザー
  user_women AS (SELECT user_id FROM user WHERE sex = 1),
  -- 2022年09月以降に買い物をしたユーザー
  shopping_202209 AS (SELECT user_id FROM shopping WHERE date_ym >= '202209'),
  -- 2022年09月以降にアンケート1に答えたユーザー
  questionary AS (SELECT user_id FROM questionary_1 WHERE date_ym >= '202209'),
  -- 女性のユーザー数
  women AS (SELECT COUNT(DISTINCT user_id)　AS count_user FROM user WHERE sex = 1),
  -- 買い物をした女性ユーザー数
  user_shopping AS (SELECT COUNT(DISTINCT user_women.user_id) AS count_user FROM user_women 
           INNNER JOIN shopping_202209 ON user_women.user_id = shopping_202209.user_id),
  -- アンケートを答えた女性ユーザー数
  user_questionary AS (SELECT COUNT(DISTINCT user_women.user_id) AS count_user FROM user_women 
           INNNER JOIN questionary ON user_women.user_id = questionary.user_id),
  -- 買い物とアンケートをどちらもした女性ユーザー数
  user_shopping_questionary AS (SELECT COUNT(DISTINCT user_shopping.user_id) AS count_user FROM user_shopping 
           INNNER JOIN user_questionary ON user_shopping.user_id = user_questionary.user_id) 
-- 各ユーザ数と割合
SELECT 
w.count_user AS w_count, -- 女性のユーザー数
s.count_user AS w_count, -- 買い物をした女性ユーザー数
q.count_user AS w_count, -- アンケートを答えた女性ユーザー数
sq.count_user AS w_count, -- 買い物とアンケートをどちらもした女性ユーザー数
s.u_id * 100 DIV w.u_id AS s_ratio, -- 買い物をした女性ユーザー数の全体の割合
q.u_id * 100 DIV w.u_id AS q_ratio, -- アンケートを答えた女性ユーザー数の全体の割合
sq.u_id * 100 DIV w.u_id AS sq_ratio -- 買い物とアンケートをどちらもした女性ユーザー数の全体の割合
FROM women AS w
JOIN user_shopping AS s
JOIN user_questionary AS q
JOIN user_shopping_questionary AS sq;

終わりに

WITH句を使うことで可読性が向上するので、たくさんJOINがあるクエリなどにおすすめです。
また、WITH句はサブクエリやJOINと違い最初に仮想テーブルのようなもの作る仕様になりますので、メモリをサブクエリなどより使います。

こちらの方の記事でもメモリに負担がかかるがとありますが、大容量のデータを作りすぎるのはよくないかもしれないです。
まずは蝋の翼から。WITH句かサブクエリか＞パフォーマンス＞メモリ観点

MySQL8.0からしか使うことはできないですが、使える環境の方はぜひ使ってみてください。