【SQL文法基礎7】複数のクエリを組み合わせる 〜 サブクエリ / WIHT 〜
データ分析では、複雑な集計もときには必要です。データを取得する条件が複雑になると、複数のクエリを組み合わせる必要があります。 本章では、そんな複数のクエリを組み合わせて、複雑な条件でデータを取得する方法について学んでいきましょう。
複数のクエリを組み合わせるサブクエリ
サブクエリとは
SQLはテーブルに格納されたデータを取得するときに使う言語で、実行すると、結果として新しいテーブルが出力されます。出力結果が行と列で構成されるテーブルになっているということは、その出力結果テーブルに対して、さらにSQLを実行することができます。このように、SQLの結果に対して、さらにSQLを書くことをサブクエリといいます。「サブ」とついているように、メインのSQLがある中で、補助的に使われるSQLなので、サブクエリと呼ばれています。サブクエリのことを日本語では副問合せとも呼びます。
サブクエリの活用方法
サブクエリの活用方法を理解するために、第8章で解説した購入金額に応じてユーザーをヘビー、ミドル、ライトの3つに分類するSQLを考えてみましょう。
まずは、復習のためにSQL9-1を実行して出力結果を確認してみましょう。SQL9-1は、CASE式を使って年間の購入金額ごとにヘビー、ミドル、ライトの3つにユーザーを分類したSQLです。これによって、ユーザーがヘビー、ミドル、ライトのどれに該当するのか確認することができます。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-1
SELECT
o.user_id,
SUM(p.price) AS total_price,
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) >= 200000 THEN 'ミドル'
ELSE 'ライト'
END AS hml
FROM
orders o
LEFT JOIN products p ON o.product_id = p.product_id
WHERE
o.user_id IS NOT NULL
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND o.is_canceled = 0
GROUP BY
o.user_id・表9-1：SQL9-1の実行結果
user_id | total_price | hml |
|---|---|---|
A000001 | 25400 | ライト |
A000002 | 4200 | ライト |
A000003 | 6100 | ライト |
A000004 | 25600 | ライト |
A000005 | 155290 | ライト |
A000006 | 63300 | ライト |
A000008 | 40000 | ライト |
A000009 | 271200 | ミドル |
A000010 | 30900 | ライト |
A000011 | 38700 | ライト |
・・・ | ・・・ | ・・・ |
では、ここからヘビー、ミドル、ライトのユーザーがそれぞれ何人いるのか確認したい場合は、どうすればよいか考えてみましょう。SQL9-1の実行結果には、ユーザーID（user_id）、合計金額（total_price）、HMLの分類（hml）の3つのカラムが出力されています。これを1つのテーブルだと考えると、集約関数とGROUP BYを使ってヘビー、ミドル、ライトの人数が集計できそうです。
実際にサブクエリを使った方法がSQL9-2になります。まずは、SQL9-2を実行して出力結果を確認してみましょう。SQL9-2では、FROMの直後に指定しているのが通常のテーブルではなく、SELECTで集計した結果なのが重要なポイントです。SQL9-2のFROMの直後のSELECTを見ると、SQL9-1とまったく同じSQLが記述されています。つまり、これは購入ユーザーをヘビー、ミドル、ライトの3つに分類したSQLの結果に対してさらにSQLを記述しているということです。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-2
SELECT
hml,
COUNT(DISTINCT user_id) AS uu
FROM
(
SELECT
o.user_id,
SUM(p.price) AS total_price,
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) >= 200000 THEN 'ミドル'
ELSE 'ライト'
END AS hml
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
o.user_id IS NOT NULL
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND o.is_canceled = 0
GROUP BY
o.user_id
) AS t
GROUP BY
hml・表9-2：SQL9-2の実行結果
hml | uu |
|---|---|
ライト | 855 |
ミドル | 108 |
ヘビー | 22 |
また、FROMの直後でサブクエリを使う場合には、SQL9-2のようにAS tと、SELECTした結果にASで別名をつけています。PostgreSQLでは、FROMの直後でサブクエリを使うときにASで別名をつけないとエラーになるため、必ず別名をつけるようにしましょう。
・図9-3：サブクエリの結果に別名をつける
SQL9-2のようなサブクエリを使ったSQLを確認すると複雑に見えますが、次のようなSQLだと考えるとシンプルになります。FROMのあとに指定しているテーブルが、通常のテーブルではなくヘビー、ミドル、ライトの3つに分類した集計結果テーブルになっています。あとはGROUP BYを使ってhmlでグルーピングし、集約関数のCOUNTを使ってユーザーID（user_id）の重複を排除することで、ヘビー、ミドル、ライトの人数を集計できます。
・SQL9-2の全体像
SELECT
hml,
COUNT(DISTINCT user_id) AS uu
FROM
集計結果テーブル
GROUP BY
hmlこのように、サブクエリを使うと複雑な条件であっても、複数のSQLを組み合わせてデータを集計できます。SELECTの中でさらにSELECTが書かれていたら、サブクエリが使われていると理解しましょう。複雑な集計になると、1つのSQLの中でサブクエリを何個も使うこともありますが、今回のようにSQLを分解して考えることで、どんな集計をしているのかわかりやすくなります。
・図9-4：サブクエリ活用のイメージ
条件式の中で使うサブクエリ
データ分析において、サブクエリを活用する場面は他にもあります。それは条件式でデータを絞り込むときです。例えば、「トートバッグ」を買った人が他にどんな商品を買ってるか知りたい場合に、どのようなSQLを書けばよいか考えてみましょう。
まずは、SQL9-3を実行して、購入履歴テーブルからユーザーがどんな商品を購入したか確認してみましょう。購入履歴テーブル（orders）と商品情報テーブル（products）を結合させて、必要な情報を取得しています。購入履歴テーブルは件数が多いので、データを確認するためにLIMITをつけて、出力件数を絞っています。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-3
SELECT
o.user_id,
p.product_id,
p.name,
p.price
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
LIMIT 10・表9-5：SQL9-3の実行結果
user_id | product_id | name | price |
|---|---|---|---|
A000439 | B000029 | 電子レンジ | 29000 |
A000211 | B000015 | レターセット | 400 |
A000211 | B000049 | 腕時計 | 15000 |
A000276 | B000026 | 美容液 | 13000 |
A000392 | B000009 | 消しゴム | 100 |
A000172 | B000002 | 美味しい水 2l | 120 |
A000172 | B000016 | ノート 5冊セット | 500 |
A000255 | B000049 | 腕時計 | 15000 |
A000255 | B000040 | ホワイトプレート22cm | 4200 |
A000276 | B000059 | 電動シェイバー | 5000 |
・・・ | ・・・ | ・・・ | ・・・ |
では、ここからトートバッグを買ってる人が他にどんな商品を買っているか確認するためには、どうすればよいでしょうか。特定の条件でデータを取得するWHEREを使って、SQL9-3にWHERE user_id IN (トートバッグを購入したユーザーID)という条件を設定すれば、必要なデータが集計できそうです。INは、第5章の「特定の条件をつけてデータを取得する」で解説したWHEREの中で使える条件指定の1つです。INの中では複数の値を設定して、いずれかの値に該当する条件でデータを取得できます。
今回はこの考え方を応用し、サブクエリを使ってデータを取得します。実際に条件式の中でサブクエリを活用したSQLがSQL9-4になります。まずは、SQL9-4を実行してどんな出力結果になるか確認してみましょう。SQL9-4では、WHERE o.user_id IN (トートバッグを購入したユーザーID)のINの中でさらにSELECT文が使われています。つまり、SQL9-4ではサブクエリが使われており、サブクエリの中は、トートバッグを購入したユーザーIDが出力されるSQLになっています。それによって、INの中で出力されたユーザーIDに一致するユーザーの購入履歴を出力することができます。条件でトートバッグを購入したユーザーIDを指定しているので、SQL9-4では、そのユーザーが購入したトートバッグ以外の商品情報も取得することができます。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-4
SELECT
o.user_id,
p.product_id,
p.name,
p.price
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
o.user_id IN (
SELECT DISTINCT
o2.user_id
FROM
orders AS o2
LEFT JOIN products AS p2 ON o2.product_id = p2.product_id
WHERE
p2.name = 'トートバッグ'
AND o2.user_id IS NOT NULL
)
LIMIT 10・表9-6：SQL9-4の実行結果
user_id | product_id | name | price |
|---|---|---|---|
A000211 | B000015 | レターセット | 400 |
A000211 | B000049 | 腕時計 | 15000 |
A000276 | B000026 | 美容液 | 13000 |
A000392 | B000009 | 消しゴム | 100 |
A000172 | B000002 | 美味しい水 2l | 120 |
A000172 | B000016 | ノート 5冊セット | 500 |
A000255 | B000049 | 腕時計 | 15000 |
A000255 | B000040 | ホワイトプレート22cm | 4200 |
A000276 | B000059 | 電動シェイバー | 5000 |
A000276 | B000026 | 美容液 | 13000 |
・・・ | ・・・ | ・・・ | ・・・ |
SQL9-4を確認すると複雑なSQLに見えるので、INの中で使われているSQLだけ切り取って中身を確認してみます。SQL9-5を実行して、出力結果を確認してみましょう。SQL9-5は購入履歴テーブルと商品情報テーブルを結合して、重複排除（DISTINCT）でユーザーIDを出力しているSQLです。トートバッグを購入しているという条件をつけるために、WHEREの中でp2.name = 'トートバッグ'と指定しています。また、AND o2.user_id IS NOT NULLの条件をつけて、ユーザーIDがNULLの場合を除外しています。これでトートバッグを購入したユーザーIDが出力されます。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-5
SELECT DISTINCT
user_id
FROM
orders AS o2
LEFT JOIN products AS p2 ON o2.product_id = p2.product_id
WHERE
p2.name = 'トートバッグ'
AND o2.user_id IS NOT NULL・表9-7：SQL9-5の実行結果
user_id |
|---|
A000009 |
A000011 |
A000015 |
A000019 |
A000020 |
A000023 |
A000026 |
A000028 |
A000031 |
A000032 |
・・・ |
SQL9-5で出力されたユーザーIDをSQL9-4のようにINの中の条件に入れることで、トートバッグを購入したユーザーに限定して購入データを確認することができます。このように、サブクエリを使って条件式でデータを絞り込むことで、複雑な条件でもデータ集計ができます。
SQL9-4のようにサブクエリを使った条件の絞り込みは、複雑なSQLに見えます。その場合は、SQL9-4を図9-8のようなSQLだと思うと理解しやすいかもしれません。ユーザーごとの購入履歴のデータを取得するSQL9-3に、条件式としてWHERE o.user_id IN (トートバッグを購入したユーザーID)が追加されただけです。今回は、このINの中で使われている「トートバッグを購入したユーザーID」をさらにSQLを使って取得していることになります。
・図9-8：条件式の中で使うサブクエリのイメージ
また、条件式でサブクエリを使うときには注意点があります。SQL9-4では、もとのSQLでも購入履歴テーブル（orders）と商品情報テーブル（products）からデータを取得しており、サブクエリの中でも購入履歴テーブルと商品情報テーブルからデータを取得しています。1つのSQLの中で同じテーブルからデータを取得する場合は、それぞれ別々のデータであることを明示するために、もとのSQLではorders AS o、サブクエリではorders AS o2とASで違う名前をつけていますので、ご注意ください。
・図9-9：テーブル名に別名をつける
このように、条件式の中でサブクエリを使うことで、複雑な条件でもデータを取得することができます。条件式のINの中でサブクエリを使う方法は、データ集計でもよく使います。もし条件式の中でサブクエリが使われていて、SQLが複雑だと感じたら、一度SQLを分解して考えてみましょう。
一時テーブル作成のWITH句
SQLで重要な可読性
サブクエリを使うと、複雑な条件でもデータを集計できるというメリットがあります。一方で、サブクエリを使うデメリットとして、SQLが複雑に見えてしまう点があります。サブクエリが含まれているSQLを見ると、どんな集計をしているのか直感的に理解するのは非常に難しいです。SQLを理解するには、サブクエリの解説をしたときのように、サブクエリとメインのクエリを分割して1つずつ読み解いていく必要があります。
データ分析では、SQLの可読性は重要です。なぜなら、データ分析でSQLを使う場合、まったくのゼロベースでSQLを考えることは案外少ないからです。実際のデータ分析では、過去に自分が使ったSQLや、他の人が書いたSQLを参考にしながら、条件を変えてデータを集計することが多いです。そうなったときに、自分が過去に書いたSQLや、他の人が書いたSQLを読み解く必要があります。自分が書いたSQLであれば理解できると思われるかもしれませんが、時間が経つとどんな意図でSQLを書いたのか忘れてしまうことがあります。ましてや、他の人が書いたSQLを読み解くのはさらに難しいです。
つまり、SQLを書くときは、あとから自分で見直しても理解しやすいようにする、あるいは他の人が見たときも、どんなデータ集計をしているのかわかりやすくするという考え方が重要です。
WITH句を使って可読性を高める
SQLには可読性を高める手法として、クエリを分割して整理する構文があります。それがWITH句です。WITH句はSQLの中で一時的なテーブルを作成できる機能です。このWITH句を使って、一時テーブルを作成することで、SQLで複雑な処理を行うときでも可読性を高めることができます。特に、サブクエリを使ったSQLに関しては、WITH句を使って書き換えることで、可読性が高くなります。
まずは、WITH句の基本構文から確認しましょう。WITH句を使ったSQLは次のように書くことができます。SQLの最初にWITHを使って、そのあとにテーブル名を記述します。このテーブル名はどんな名前をつけてもよいので、SQLを見る人が解釈がしやすいような名前をつけるのがおすすめです。テーブル名のあとにはAS()を記述し、カッコの中にSQLを記述することができます。また、WITH句を使った一時テーブルは、SQLの中で何個も記述することができます。複数書く場合は、AS()のあとにカンマをつけて、そのあとにまた別のテーブル名を記述します。一番最後に記述した一時テーブルのあとにはカンマはつけないので注意しましょう。
WIHT句で一時テーブルを作成したあとは、通常のSQLを記述します。このときに、WITH句で作成した一時テーブルをSQLの中でも使うことができます。
・WITH句の基本構文
WITH テーブル名1 AS(
SELECT * FROM テーブル名
),
テーブル名2 AS(
SELECT * FROM テーブル名
)
SELECT
*
FROM
テーブル名1実際にWITH句を使った方法がSQL9-6になります。まずは、SQL9-6を実行して出力結果を確認してみましょう。SQL9-6はサブクエリを使ってヘビー、ミドル、ライトの人数を集計したSQL9-2を、WITH句を使って書き換えたSQLになります。サブクエリを使ったSQL9-2と比較すると、WITH句を使ったSQLのほうがどんな処理をしているかわかりやすいのではないでしょうか。
★ハンズオン〜SQLを実行してみよう〜
・SQL9-6
WITH hml_users AS(
SELECT
o.user_id,
SUM(p.price) AS total_price,
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) >= 200000 THEN 'ミドル'
WHEN SUM(p.price) < 200000 THEN 'ライト'
ELSE 'その他'
END AS hml
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
o.user_id IS NOT NULL
AND o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
AND o.is_canceled = 0
GROUP BY
o.user_id
)
SELECT
hml,
COUNT(DISTINCT user_id) AS uu
FROM
hml_users
GROUP BY
hml・表9-10：SQL9-6の実行結果
hml | uu |
|---|---|
ライト | 855 |
ミドル | 108 |
ヘビー | 22 |
WITH句が使われているSQLは、まず最後のクエリを確認すると、内容を把握しやすいです。今回の場合は、最終的に実行されているのが次のSQLになります。これだけ見ると最終的にどんな数値を集計しているのか非常にシンプルです。hml_usersというテーブルからhmlごとにグルーピングをして、ユーザー数を集計しています。なので、最終的にはヘビー、ミドル、ライトのユーザーが何人いるのかが、SQLの実行結果として出力されます。
・WITH句を使ったSQLで最終的に実行されているSQL
SELECT
hml,
COUNT(DISTINCT user_id) AS uu
FROM
hml_users
GROUP BY
hml次に確認するのがhml_usersというテーブルです。今回用意したテーブルにhml_usersというテーブルは存在しません。ここではWITH句を使って、hml_usersという一時テーブルを作成していることになります。
SQL9-6の最初に書かれているWITH句を確認しましょう。WITH hml_users AS()でhml_usersという一時テーブルを定義しています。このとき、hml_usersという名前は任意に設定できます。今回はユーザー情報を購入金額に応じてヘビー、ミドル、ライトのHMLに分類しているので、hml_usersという名前にしています。
WITH句の中身を確認すると、ユーザー情報をヘビー、ミドル、ライトに分類するSQL9-1とまったく同じになっているのがわかると思います。つまり、CASE式を使ってユーザーをヘビー、ミドル、ライトの3つに分類したSQLの実行結果をhml_usersという一時テーブルに保存し、最終的にはhml_usersというテーブルから、データを集計しているということです。
・図9-11：WITH句活用のイメージ
このように、WITH句を使うと一時テーブルを作成することができるため、SQLの可読性が高まります。最終的にどんなSQLを実行して結果が出力されるのか、その前処理としてどんなSQLが実行されているのか、順番に確認することができます。
データ分析で重要なWITH句
著者もデータ分析でSQLを活用しているさまざまな現場を見てきましたが、WITH句を活用しているところは意外と少ないように感じます。データ分析をしていると、複雑なSQLになることはよくあります。サブクエリを使って、SELECTの中に何個も何個もSELECTが含まれているようなSQLもたくさん見てきました。そういったSQLを最初に見ると、どんな処理をしているのか内容を確認するのが非常に難しいです。しかし、今回のようにWITH句を使い、一時テーブルを作ってSQLの処理を1つずつ順番に記述していくことで、可読性が高まり、どんな集計をしているのかも理解がしやすくなります。
WITH句は決して難しい構文ではありません。むしろWITH句を使いこなすとSQLが非常にシンプルになります。なので、データ分析においては、ぜひ積極的にWITH句を活用することをおすすめします。
データ分析における「サブクエリ」や「WITH句」の考え方
サブクエリやWITH句を使うと、複数のSQLを組み合わせることができます。データ分析では、さまざまな条件でデータを集計することがありますが、ほしいデータが常にテーブルに存在しているとは限りません。そんなときは、図9-12のように、サブクエリやWITH句を使って一度SQLでデータを集計して、その結果に対してさらに集計をするという考え方がとても重要です。これは、SQLを実行すると行と列で構成されたテーブルで結果が出力されるという特徴を応用した考え方になります。
SQLで出力した結果に対して、さらにSQLを実行できるという考え方を理解することで、データ分析でもより複雑な条件でデータを集計できるようになります。ぜひ、データ分析でSQLを使う場合は、サブクエリやWITH句の使い方を身につけておきましょう。
・図9-12：サブクエリ、WITH句の考え方
演習問題
演習問題9-1
次の条件に従い、2023年の年間のユーザーごとの購入に応じてHML（ヘビー、ミドル、ライト）に分類し、HMLごとのユーザー数を集計してください。また、このときユーザー情報が空のデータとキャンセルのある購入データは除外してください。
- ヘビー：年間の購入件数が500件以上
- ミドル：年間の購入件数が300件以上、500件未満
- ライト：年間の購入件数が300件未満
演習問題9-2
次の条件式でサブクエリが使われているSQLをWITH句を使ったSQLに書き換えてください。
SELECT
o.user_id,
p.product_id,
p.name,
p.price
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
o.user_id IN (
SELECT DISTINCT
o2.user_id
FROM
orders AS o2
LEFT JOIN products AS p2 ON o2.product_id = p2.product_id
WHERE
p2.name = 'トートバッグ'
AND o2.user_id IS NOT NULL
)
LIMIT 10