【SQL】WITH句を使って、クエリの可読性をUP！

データ分析時、超複雑なクエリを創造してしまうことってありますよね...。
後でそのクエリを読み返した際、

あれ、このクエリってどんなデータ抽出してたっけ？？

本当にこのクエリは合ってるのか？

...複雑すぎて分からん！
(てか自分よくこんなクエリ書けたなwww)

となるのが世の常...。

今日はそんな悩みを打開する、『WITH句』という超・便利構文を紹介します！

1 WITH句をマスターしよう！
- 1.1 WITH句とは
- 1.2 WITH句を使ったクエリ例

WITH句をマスターしよう！

WITH句とは

WITH句とは、テーブルを一時作成できる文のことです。
ニュアンスとしては、サブクエリとよく似ていますが、WITH句の方が可読性が高いというメリットがあります。

WITH句の構文

■テーブルが一つの場合

WITH
つけたい名前 AS (
    好きなクエリ
)

SELECT
    〇〇〇
FROM
    つけた名前

■テーブルが二つの場合

WITH
つけたい名前1 AS (
    好きなクエリ1
),

つけたい名前2 AS (
    好きなクエリ2
),

SELECT
    〇〇〇
FROM
    つけた名前1
    INNER JOIN つけた名前2
        on ...

WITH句を使ったクエリ例

以下の2つのテーブルから、2022年1月1日以降の、日別の登録者数と購入者数を横並びに抽出するとします。

id	name	user_created_date
1	太郎	2019-04-06
2	よしお	2019-04-06
3	SAKI	2019-04-06
...	...	...
22300	ジム	2022-03-26

usersテーブル（毎日1人以上はユーザー登録があるものとします）

id	item	purchased_date
1	干し柿	2019-04-06
2	干し柿	2019-04-06
3	干し柿まんじゅう	2019-04-07
...	...	...
5600	幻の干し柿	2022-3-26

purchasesテーブル（たまに購入数が0の日もあるものとします）

この場合WITH句を使うと、以下のようなクエリになります。

WITH
u AS (
    SELECT
        user_created_date,
        COUNT(id) AS n_of_users
    FROM
        users
    WHERE
        user_created_date >= "2022-01-01"
    GROUP BY
        user_created_date
),

p AS (
    SELECT
        purchased_date,
        COUNT(id) AS n_of_purchases
    FROM
        purchases
    WHERE
        purchased_date >= "2022-01-01"
    GROUP BY
        purchased_date
)

SELECT
    u.user_created_date AS days,
    u.n_of_users,
    p.n_of_purchases
FROM
    u
    LEFT OUTER JOIN  p
        ON u.user_created_date = p.purchased_date
ORDER BY
    days asc

days	n_of_users	n_of_purchases
2022-01-01	210	20
2022-01-02	335	24
2022-01-03	299	31
...	...	...
2022-03-26	415	43

上記クエリの抽出結果

ちなみに、WITH句ではなくサブクエリを使うと、以下のようになります。

SELECT
    u.user_created_date AS days,
    u.n_of_users,
    p.n_of_purchases
FROM (
    SELECT
        user_created_date,
        COUNT(id) AS n_of_users
    FROM
        users
    WHERE
        user_created_date >= "2022-01-01"
    GROUP BY
        user_created_date
    ) AS u
    LEFT OUTER JOIN (
        SELECT
            purchased_date,
            COUNT(id) AS n_of_purchases
        FROM
            purchases
        WHERE
            purchased_date >= "2022-01-01"
        GROUP BY
            purchased_date
        ) AS p
            ON u.user_created_date = p.purchased_date
ORDER BY
    days asc

上記の2つのクエリを見比べると、WITH句を使ったクエリの方が可読性が高いことが一目瞭然ですね！

ぜひWITH句を活かして、可読性の高いクエリを書いてみてください(^^)/