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

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

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

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

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

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

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

今日はそんな悩みを打開する、『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日以降の、日別の登録者数と購入者数を横並びに抽出するとします。

idnameuser_created_date
1太郎2019-04-06
2よしお2019-04-06
3SAKI2019-04-06
.........
22300ジム2022-03-26
usersテーブル（毎日1人以上はユーザー登録があるものとします）
iditempurchased_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
daysn_of_usersn_of_purchases
2022-01-0121020
2022-01-0233524
2022-01-0329931
.........
2022-03-2641543
上記クエリの抽出結果

ちなみに、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句を活かして、可読性の高いクエリを書いてみてください(^^)/

-SQL
-, ,