WITH句の使い方

2022年5月6日 12:10

こんにちは、よさそう®です。

SQLの使い方のご紹介、今回はWITH句について書いてみます。WITH句が使えるようになると、SQLの書き方の幅が広がってとても便利になります。

WITH句とは

SQLは通常テーブルに対して、データを取得します。WITH句を使うとテーブルに問い合わせを行った結果を、仮想的な疑似テーブルとして使えるイメージとなります。

過去にはVIEWという機能や副問合せとして行ってきたことが、WITH句を用いることで似たようなことができます。

「じゃ、VIEWでも副問合せもいいじゃん」と思うかもしれません。でもWITH句はこれらを補って余りあるくらいに便利です。

まずVIEWは事前にVIEWを定義する必要があります。探索的に分析をするときって、いろいろな切り口からデータを集計したいので、いちいち定義するのが面倒なんですよね。それに自分にVIEW定義権限がなければ、データベース管理者にいちいちお願いする必要があり、さらに面倒。

副問合せなら事前定義が不要です。でもSQLが読みにくくなるんですよね。
同じ問い合わせを副問合せとWITH句で書いてみます。

まずは副問合せ。

SELECT フィールド1、フィールド2、フィールド3
FROM　
( SELECT　フィールド1、フィールド2、フィールド3 
　FROM テーブル1
　WHERE　フィールド1　= ”あああ“
)

続いてWITH句

WITH w1 AS(
　　SELECT　フィールド1、フィールド2、フィールド3 
　　FROM テーブル1
　　WHERE　フィールド1　= ”あああ“
)
SELECT フィールド1、フィールド2、フィールド3
FROM w1

副問合せの場合、取得しているデータの定義が、取得している箇所よりも後に書くことになります。
一方WITH句の場合は、上から読んでいけば、何をしているのかがわかりやすいのです。

ひとりで分析や開発しているときには副問合せでも問題ないかもしれません。でも往々にして仕事は組織で行うものです。コードのメンテナンス性や、分析の再現性を考える場合、可読性が高い書き方であるWITH句で書くことを強く勧めます。

WITH句はちょっとご紹介したように、「テーブルからデータを取得して、その取得結果に対して問い合わせを書きたい」というケースで使えます。

たとえば「購買履歴のテーブルから顧客IDごとに購入回数の合計をWITH句で取得しておいて、購入回数の分布を知りたい」といったケースですね。
そういう場合はこんな感じで問い合わせます。

WITH　w1 AS(
　　SELECT　会員ID、SUM(購入ID) AS 購入回数
 FROM 購入履歴テーブル
　　GROUP BY 会員ID
)
SELECT 購入回数、COUNT(会員ID）
FROM w1
GROUP BY 購入回数
ORDER BY 購入回数

WITH句でw1という一時的な集計疑似テーブルに会員IDごとの購入回数を集計した情報を作ります。そしてw1に対して、購入回数ごとに会員IDの数をカウントしています。

このようにWITH句を使うときにSUMやCOUNTなどの集計関数を組み合わせて使うことで、データの様子をさまざまな切り口から簡単に集計できます。

ということで今回はWITH句をご紹介しました。ご利用のデータベースやバージョンによっては使えないかもしれませんが、利用できたらぜひ使ってみてください。

最後までお読みいただき、誠にありがとうございました。
よろしければいいね！やフォローをいただけたら、激しく嬉しいです。続けていく励みになります。

それでは、また。