【SQL】WITH句を使って複雑なクエリを実行する

DB

SQLを使ってデータ抽出をする際、欲しい形のデータにするために複雑な処理をしなければならないケースも多々あります。その処理において副問い合わせ（サブクエリ）を用いるケースもあると思います。

通常のサブクエリは、その処理内容が複雑になると、元のクエリ自体の可読性が下がってしまいます。そこで使えるのがWITH句を使った副問合せです。

今回はWITH句を使ったSQLの作成の流れを紹介していこうと思います。

目次

環境・前提

DB：PostgreSQL

以下のテーブルを使います。

  • payment
  • customer
payment
payment
customer
customer

WITH句とは

WITH句を使うと、サブクエリに名前を付けて別管理することができます。サブクエリでの抽出内容を一時テーブルとして管理できるイメージです。

WITH句内で作成した一時テーブルは、そのクエリ内で何度でも呼び出すことができます。そのため、同一クエリ内で何度も副問合せとしてDBにアクセスする必要がなくなります。

ただしこの一時テーブルは、実行クエリ内でしか使うことができません。

クエリを書いてみる

百聞は一見に如かず。実際にWITH句を使ったクエリを書いてみましょう。上のpaymentテーブルとcustomerテーブルから顧客ごとの支払い合計を算出し、顧客名と共に取得してみようと思います。

まずSQL全体は以下のようになります。

WITH test1 AS
    (
    SELECT customer_id, SUM(amount) AS total_payment FROM payment 
    GROUP BY customer_id
    )


SELECT test1.customer_id, test1.total_payment, customer.first_name
FROM test1 
INNER JOIN customer 
ON test1.customer_id=customer.customer_id
WHERE test1.total_payment>150

WITH句を使ってtest1を作成しています。test1はpaymentテーブルのamountをcuntomer_idごとに集計したテーブルです。test1の実行結果は以下のようになります（一部）。

test1
test1

下のSELECT文ではtest1の結果にcustomerテーブルのfirst_nameを結合し、さらにtotal_paymentが150以上の顧客に絞って抽出しています。実行結果は以下のようになります。

SQL実行結果

このように、WITH句を用いることで、クエリ内で一時的に作成したテーブルを利用することができるようになります。

複数の一時テーブルを作りたいとき

WITH句内で複数の一時テーブルを作成することも可能です。その場合は、以下のルールで作成します。

WITH テーブル1 AS(
--処理--
),
テーブル2 AS(
--処理--
),
テーブル3 AS(
--処理--
),...

注意点

便利なWITH句ですが、注意すべき点もあります。

WITH句で作成した一時テーブルはメモリ上で保持されることになります。そして、そのクエリのセッション中はずっと保持されます。

この特性上、データ量が膨大な一時テーブルを作成したり、一時テーブルの数が増えすぎたりしてメモリを圧迫してしまうとパフォーマンスが落ちてしまう可能性があります。

まとめ

SQLでのWITH句の使い方を紹介しました。WITH句を使うと複雑なクエリも可読性を下げることなくコーディングができるのでおすすめです。

しかし、メモリサイズも考慮して大きなデータを扱う際は注意してください。

ではでは👋

DBの最新記事8件