SQL サブクエリとWITH

サブクエリ

例えば会員情報テーブルAで30歳以上の人のidに絞り、そのidの購買履歴テーブルBを見るなど、あるクエリ結果を使ってさらにデータを抽出することができる。

この「クエリの中に別のクエリを書く」ことを「サブクエリを使う」という。上の例をサブクエリを使って書くと

SELECT * FROM B WHERE id IN (SELECT id FROM A WHERE age>=30)

となる。まずサブクエリの中にあるクエリが評価され、テーブルAにあるageが30以上のレコードのidが抽出される。次にテーブルBからそのidのレコードが抽出される。

サンプルデータ

kiso1_pos,kiso1_customer,kiso1_master,kiso1_heavyの4つを使う。使い方はSQL学習用サンプルデータを参照。

他のテーブルにあるIDを使って抽出する

posのうちヘビー会員のレコードだけが欲しい、という場合を考えてみよう。kiso1_heavyからidを抽出しどこかにメモでもしておいてkiso1_posからWHERE INで抽出するのも1つや2つならできなくはないが、数が多い場合や日々データが更新される中では非現実的だ。

そうなると、あるテーブルから必要なデータを抜き出してそのまま別の抽出の条件に使いたいと考えることになる。それを実現するのがサブクエリだ。

SELECT * FROM kiso1_pos
WHERE customer_id IN (SELECT customer_id FROM kiso1_heavy )

kiso1_heavyはヘビー会員フラグが1の会員IDが入っているので、まずサブクエリの中が「ヘビー会員のIDリストになる」。つまりkiso1_heavyのデータを見ればわかるように

SELECT * FROM kiso1_pos
WHERE customer_id IN (1,2,5,9・・・)

ということだ。あとはkiso1_posからWHERE INで抽出している。

カラム名が違っても使える

次にkiso1_customerにある特定のID(例では男性)のレコードをサブクエリを使って抽出してみよう。 kiso1_customerにはcustomer_idではなくidしかないが、それでもサブクエリに使うのは問題ない。

SELECT * FROM kiso1_pos
WHERE customer_id IN (SELECT id FROM kiso1_customer WHERE sex=1)

先ほどと同じで先にサブクエリの中、つまりSELECT id FROM kiso1_customer WHERE sex=1が評価され、idリストが数値で返ってくるだけなので、

SELECT * FROM kiso1_pos
WHERE customer_id IN (1,3,5,6,9・・・)

と同じ。

集計したテーブルをFROMの中に入れるサブクエリ

もう1つ、テーブルを集計してそれをさらに集計したいという状況がある。例えば「レコード数ごとの人数」で、まずIDごとにレコード数を集計し、今度はレコード数ごとの人数を取る。

SELECT recode ,count(1) as count
FROM(
SELECT customer_id,count(1) as recode FROM kiso1_pos GROUP BY customer_id
)
GROUP BY recode
ORDER BY recode

これも同様にまずFROMの中が評価される。サブクエリの中だけ抜き出すと

SELECT customer_id,count(1) as recode FROM kiso1_pos GROUP BY customer_id

これはcustomer_idごとレコード数を集計しているのはすぐわかるはずだ。

次にこの結果のテーブルを使ってrecodeごとの行数=人数を数えている。数が大きいor小さい順に見ることも多いので合わせて並び替えもしている。

サブクエリが複雑な場合

サブクエリを多用すると複雑になっていく。サブクエリの中でいくつもの条件式を使っていたり、サブクエリの中にさらにサブクエリ(入れ子とかネストとか言う)が入っていたりと特に整備のSQLは長くなりがちだ 。

判らなくなったら一番内側のサブクエリから順番に評価されていくのでその部分だけを切り出して実行してみるといいだろう。

それでもやはりクエリが複雑だと混乱しやすい。そこで中身を別に切り出して書く方法が用意されている。

WITH

WITHを使うとサブクエリの中身を外に書き出すことでクエリを見やすくできる。

WITHの使い方

WITHの使い方は以下の通り。

  • 最初はWITHで始める(コメントは除く)
  • 「テーブル名 as ()」 を1つのかたまりとして、()の中にクエリを書く
  • 複数書く場合は「, テーブル名 as ()」 でつなげる
  • 書いた順に処理されていく
  • 書いたクエリは最後のSELECT文以外でも何度でも使える
  • 最後にSELECT文を書く

サブクエリとWITHの比較

「ヘビーかつ男性の会員の来店日数別の人数」を考えてみよう。大きく分けて次の3つのステップになる。

  1. ヘビー会員かつ男性のIDを特定する
  2. 1の会員ごとの来店日数を数える
  3. 来店日数ごとの人数を数える

ではこれをサブクエリだけで書いた場合とWITHを使った場合でそれぞれ見てみよう。

サブクエリだけで書いた場合

SELECT
date_count
, COUNT(1)
FROM (
SELECT
customer_id
, COUNT(DISTINCT date) AS date_count
FROM kiso1_pos
WHERE customer_id IN (
SELECT customer_id FROM kiso1_heavy heavy
INNER JOIN (
SELECT * FROM kiso1_customer WHERE sex=1
) customer
ON heavy.customer_id=customer.id
)
GROUP BY 1
)
GROUP BY 1
ORDER BY 1

WITHを使った場合

— コメントは先にあってもよい
WITH
heavy_customer_id as (
SELECT customer_id FROM kiso1_heavy heavy
INNER JOIN (
SELECT * FROM kiso1_customer WHERE sex=1
) customer
ON heavy.customer_id=customer.id
)
,
customer_id_date_count as (
SELECT
customer_id
, COUNT(DISTINCT date) AS date_count
FROM kiso1_pos
WHERE customer_id IN (
SELECT * FROM heavy_customer_id
)
GROUP BY 1
)

SELECT
date_count
, COUNT(1)
FROM customer_id_date_count
GROUP BY 1
ORDER BY 1

サブクエリとWITHのどちらを使うか

上の例ぐらいであればどちらで書いても大きな差はないし、人によってはWITHの方がむしろわかりづらい人もいるだろう。しかしステップがもっと増え、条件での絞り込みが複雑になってくるとやはりWITHを使った方が見通しはよくなる。

なのでどちらも使えるようにして状況に合わせて使い分けられるようになっておくのが一番だ。

オフィシャルドキュメント

サブクエリ
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#subqueries

WITH
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#with-clause

2020年2月6日基礎1

Posted by shinu