属性ごとの件数の一覧をFULLJOINで作る

公開:2021/01/12
更新:2021/01/14
5 min読了の目安(約5200字TECH技術記事

属性ごとのid数が欲しい時にどうするか

2つのキャンペーンの両方に応募した人と、それぞれ片方にしか応募していない人のid数、のようにいくつかの属性の組み合わせがどのような内訳なっているかを知りたい時にどうするか。

ここで言う属性というのは

  • キャンペーンに応募したかどうか
  • 特定の商品を買っているかどうか
  • ある期間中に購買があったかどうか
  • 居住地は関東かそれ以外か
  • お知らせの許諾があるかないか

といったフラグであったり

  • 性別や年代
  • 都道府県

のような区分のことで、ここではまとめて「属性」と表現しておくが何でもいい。

この記事で使うサンプルはこちら(動作確認はBigQueryのみ)。

WITH tbl1 AS (
SELECT 1 AS id , 1 AS flag	
UNION ALL SELECT 2 AS id , 1 AS flag	
UNION ALL SELECT 3 AS id , 0 AS flag	
UNION ALL SELECT 4 AS id , 0 AS flag	
UNION ALL SELECT 5 AS id , 1 AS flag	
UNION ALL SELECT 6 AS id , 0 AS flag	
UNION ALL SELECT 7 AS id , 0 AS flag	
UNION ALL SELECT 8 AS id , 1 AS flag	
UNION ALL SELECT 9 AS id , 1 AS flag	
UNION ALL SELECT 10 AS id , 1 AS flag	
)

,tbl2 AS (
SELECT 1 AS id , 0 AS flag	
UNION ALL SELECT 2 AS id , 1 AS flag	
UNION ALL SELECT 3 AS id , 1 AS flag	
UNION ALL SELECT 4 AS id , 1 AS flag	
UNION ALL SELECT 5 AS id , 0 AS flag	
UNION ALL SELECT 6 AS id , 1 AS flag	
UNION ALL SELECT 7 AS id , 0 AS flag	
UNION ALL SELECT 8 AS id , 0 AS flag	
UNION ALL SELECT 9 AS id , 0 AS flag	
UNION ALL SELECT 10 AS id , 1 AS flag	
)

,tbl3 AS (
SELECT 1 AS id , 1 AS flag	
UNION ALL SELECT 3 AS id , 1 AS flag	
UNION ALL SELECT 4 AS id , 1 AS flag	
UNION ALL SELECT 9 AS id , 0 AS flag
)
-- ここにクエリを追加する

属性が2つの場合を考える

まず、2つの属性AとBについて「属性Aと属性Bの両方を持つか、どちらか一方だけかそれぞれの人数」を考えてみる。

サンプルテーブルにはそれぞれflagのカラムがあるので

  • 属性A:tbl1のflagが1
  • 属性B:tbl2のflagが1

とすると、

  • 属性Aと属性Bの両方を持つid数
  • 属性Aしか持たないid数
  • 属性Bしか持たないid数

を調べればいい。ベン図で書くと、2つの円が交わる部分とそうでない部分。ここまででまずはクエリを書いてみて欲しい。

数えるだけなら簡単

属性Aのid数が6、属性Bのid数は5はCOUNTすればすぐわかる(テーブルを見て直接数えたりしないように。サンプルなので小さいからできるが通常は不可能)。あとは2つの属性に共通するidの数なのでINNER JOINを使おう、と考えるのは自然だ。

それぞれの属性を持っているidリストを作ってINNER JOINで共通するidを取り出して数を数えればいい。

-- 属性Aのidリストを抽出
,
id_a AS (
SELECT id FROM tbl1 WHERE flag=1
)

-- 属性Bのidリストを抽出
,
id_b AS (
SELECT id FROM tbl2 WHERE flag=1
)

--両方の属性を持つidを数える
SELECT 
COUNT(id_a.id) 
FROM id_a 
INNER JOIN id_b 
on id_a.id=id_b.id

#2

結果は2となるので

  • 属性Aと属性Bの両方を持つid数:2
  • 属性Aしか持たないid数:4(6-2)
  • 属性Bしか持たないid数:3(5-2)

と答えが出てくる。合計しても10にならないのはid7がどちらの属性にもないから。

と、入門書にはこんな感じで書いてあるしこれでも間違いではないのだが、この方法だけしか知らないと実務では大変だ。

INNER JOINではなくFULL JOINで全パターンの組み合わせを作る

上記の方法には

  1. クエリが1つにならない
  2. 3つ以上になると対応が大変
  3. 数だけしかわからないのでそのあとの抽出に使えない

という問題があるのでお勧めしない。ではどうするか。

結論から言うと、「FULL JOINを使って全パターンの組み合わせを作ってから集計すればいい」となる。

最後のSELECT分を次のように書き換えて実行してみよう。

SELECT 
tbl1.flag AS flag_a
,tbl2.flag AS flag_b
,COUNT(1) AS CNT
FROM tbl1 
FULL JOIN tbl2 
ON tbl1.id=tbl2.id
GROUP BY 1,2

このクエリでは属性ごとのidリストを作らずに最初の状態をidでFULL JOINし、フラグの組み合わせごとで集計している。結果は

flag_a flag_b cnt
1 0 4
1 1 2
0 1 3
0 0 1

となる。先ほどの結果と比べると

  • 両方のフラグが立っている=属性Aと属性Bの両方を持つid数:2
  • flag_aが1でflag_bが0=属性Aしか持たないid数:4
  • flag_aが0でflag_bが1=属性Bしか持たないid数:3

と同じ結果が出ているのが判るだろう。しかも

  • flag_aとflag_bが両方0=属性Aも属性Bも持たないid数:1

と抜け落ちていたどちらの属性でもないidも拾えている。

FULL JOINで書くメリット1・1つのクエリでかける

INNER JOINで書くことをお勧めしない理由として

  1. クエリが1つにならない
  2. 3つ以上になると対応が大変
  3. 数だけしかわからないのでそのあとの抽出に使えない

の3つを挙げたが、最初の2つはこれで解決できる。

まず1つ目についてはFULL JOINなら全パターンを1度に出せるので、属性それぞれと共通部分の数を個別に計算する必要がなくなり、1つのクエリになる。

複数のクエリを実行するとどのクエリがどの件数だったか忘れたり、数値をどこかに転記する際に違うところに書いたりとミスを誘発しやすいが、一覧で出てくればその心配はない。

いままで個別に集計していた人は是非試してみて欲しい。早く正確にしかも間違いが減る。

FULL JOINで書くメリット2・属性がいくつになっても簡単に対応できる。

次に、属性が3つになったらどうなるだろう。

  • 3つの属性に共通するid数
  • 各属性のid数(3つ)

に加えて

  • 3つのうち2つを持つid数(3つ)

と7つのSELECT文が必要だ。これが4つ、5つと増えて行くと大変なことになるのはすぐわかるだろう。

FULL JOINで書けば3つだろうが5つだろうがJOINするテーブルが増えるだけ。例えば3つなら

SELECT 
tbl1.flag AS flag_a
,tbl2.flag AS flag_b
,tbl3.flag AS flag_c
,COUNT(1) AS CNT
FROM tbl1 
FULL JOIN tbl2 
ON tbl1.id=tbl2.id
FULL JOIN tbl3
ON tbl1.id=tbl3.id
GROUP BY 1,2,3

と簡単に追加できるし、いくら組み合わせが増えても一度で完結する。

なお実行してみればわかるが(実行しないでこの文章だけ見てわかったつもりになるとあとで間違える)、この結果は少々おかしなことになっているので注意。詳しくは注意点に書いておく。

FULL JOINで書くメリット3・その次に使いまわせる

FULL JOINする際に合わせて集計しても答えはでるのだが、JOINと集計をわけよう。JOINしたらidごとのフラグの状況が付くようにして、最後にSELECTではそのテーブルから集計するようにする。クエリで書くと


-- JOINしてidごとのフラグが付いた状態にする
,
tbl4 AS (
SELECT 
COALESCE(tbl1.id,tbl2.id) AS id
,tbl1.flag AS flag_a
,tbl2.flag AS flag_b
FROM tbl1 
FULL JOIN tbl2 
ON tbl1.id=tbl2.id
)

-- 最後は集計だけ
SELECT 
flag_a 
,flag_b
,COUNT(1) AS CNT
FROM tbl4 
GROUP BY 1,2

となる。利点は後で属性を追加するのが簡単(この手前で別のidリストを作ってさらにJOIN)なのと、idごとパターンが残るのでこの後「〇〇の条件のIDが欲しい」と言った依頼にもすぐ対応ができること。

注意点

この方法を使うにあたり細かいがいくつか。

idの重複があるとずれる

JOINなのでidに重複があると値がおかしくなる。サンプルでは条件で絞り込みしただけでidリストが作れたが、実際にはこの段階でいろいろ複雑な処理をしてidリストを作ることも度々あるのでDISTINCTを入れた方が安全。

idが欠けている場合はNULLに注意

「属性がいくつになっても簡単に対応できる」のクエリを実行したらわかるが、tbl3のidが他に比べて少ないために0の場合とnullの場合が別に出てきてしまう。これで件数を出しても意図した値にはならない。

修正する方法は2つあって、1つは結合した後にNULLを0に変換する方法。つまりfalg_cを

,case when tbl3.flag is null then 0 else 1 end AS flag_c

とすればいい。

もう1つは、結合する前にflag=1でtbl3を絞り込む方法。これなら属性Cを持たないidは値がNULLになる。

どちらでもよいが、多分前者(NULLなら0にする方)がわかりやすい。

LEFT JOINとの使い分け

一方の対象が全idとか、キャンペーンの申し込みをしたり特定期間中に購買があったのような特定の条件を満たすidのうちさらに何か別の条件があるか無いかといった話であればLEFT JOINを使う。

サンプル属性Aと属性Bのように、リストが一致しない場合はFULL JOIN一択。この場合は結合した後に両方にないidのカラムにNULLが出てくるので、COALESCEやCASEでNULLにならないようにするのを忘れないようにしておくこと。

「データ分析と整備のためのSQL」について

「データ分析と整備のためのSQL」の説明や注意点はこちらをご覧ください

https://zenn.dev/shinu/articles/047dc015854338

この記事の想定レベル

  • 「INNER JOINではなくFULL JOINで全パターンの組み合わせを作る」まででレベル2
  • 全体を知っていて実務で使えたらレベル3

を想定している。