この記事は「データベーススペシャリスト資格に興味はあるが、どのようなものか？どう学ぶのか？」という方向けに、具体的な内容と私自身の挑戦ログをお伝えする。学び中の方や、これから学ぼうとされる方の参考になれば幸いだ。
今回はSQLによるテーブルの結合（3/3）について書く。
※注意点として、SQLはDBMSによって作法が異なる。この記事はGoogle Big Queryに準拠するものであることをお含みおき願いたい。
同一テーブルの結合
前回振り返り
- 今までは単一テーブルの操作を学習。これから複数テーブルの操作を学ぶ。これは、実務では「扱いやすさ」の観点から複数テーブルに分かれているが、分析においては「幅広く見る」ためにテーブルをつなげる必要があるから。
- SQLにおいては「異なる性質（列）で分けたデータ」を、“横”につなげる操作を「結合（join）」と言う。
- また、「一定期間ごとに集計されたレコード（行）」を、“縦”につなげる操作を「集合演算（union）」と言う。
- 前回までで結合（join）について最も基本の「inner join」と、別パターン（left outer join、right outer join、full outer join）および応用パターン（3つ以上のテーブル結合）を学んだ。
- 今回は結合（join）の発展形を学ぶ。5月7日～5月9日を通じて「結合（join）の基本・応用・発展」の学習が完了するかたち。
同一テーブルの結合
- 前回までは「２つ以上の異なるテーブルを結合」した。
- ただ、実務では「同じテーブルをずらして結合」したい場合がある。
- 例示は以下。年間の販売個数のテーブル（small_year）を元に、前年比成長を分析したい場合。
- SQLとしては以下のようになる。一番最後のon句で、フィールドの年数をひとつずらして結合するのがポイント。これにより左側が前年度、右側が翌年度のような結果が取得できる。
同じテーブルを総当たりで結合
- 続いて、「同じテーブルを総当たりで結合」する場合を学ぶ。
- 構文としては「cross_join（交差結合）」を使用する。
- 例示は以下。年間の販売個数のテーブル（small_year）を元に、「ある年度をベースに、他の年度との比較を分析したい場合」。
- SQLとしては以下のようになる。cross_joinで総当たりの結合パターンを作ったうえで、base_yearが2017年の行を抽出している。
テーブル内のnullを別の値に置換
nullを別の値に置換（基本）
- さて、テーブル結合においてはnull（空白）の扱いに慎重になる必要がある。
- なぜなら、例えば結合したい列の一部の値がnullだったとき、うまく結合・集計できないからだ。
- そこで、「テーブルを結合するさい、列の一部の値がnullだったときに、別の値に置換したい」場合を学ぶ。
- 例示は以下。「ユーザーごとのライフタイムバリュー（small_ltv）」と、「ユーザーごとの会員登録および初回購入年（small_reg_pur_year）」を管理したテーブルを用意する。
- まず、単純にinner joinで結合すると、以下のようになる。
- もし、「会員登録年（registration_year）ごとのライフタイムバリューを求めたい」場合、会員登録年にnull値が含まれるので、このままでは計算できない。
- そこで、「会員登録年（registration_year）が空白（null）の場合は、（会員登録を始めた年である）2017年に置き換える」と仮置きすることを考える。
- このように、テーブル結合の際に一部のnull値を置き換える場合、「ifnull」関数を用いる。
- 例示は以下。置き換えしたうえで「会員登録年ごとのライフタイムバリュー合計」を計算している。
- このSQLはテーブルの結合（join）とグループ化（group by）の混合だが、いずれも基本なので難しい内容ではない。
nullを別の値に置換（応用）
- 続けて、「テーブルを結合するさい、列の一部の値がnullだったときに、別の値に置換したい」場合の応用例を学ぶ。
- ifnull関数より高度なnullの置換をしたい場合、「coalesce関数」を使う。（発音は”コウアレス”。融合などの意味）
- 例示として、「会員登録年（registration_year）ごとのライフタイムバリューを求めたい」かつ「会員登録年（registration_year）が空白（null）の場合は、初回購入年（first_purchase_year）に置き換える」としたい場合。
- 以下のように④部分をcoalesce関数に置き換えることで対応できる。
- なお、参考だがこの例文くらいシンプルな条件ならifnull関数でも正常に起動する。
それ以外の発展形と記述順の整理
非等値結合を利用したバスケット分析
- ここまで学んだjoinは、いずれもon句やusing句で「結合に使うフィールドの値が等しい」方法。
- 同じテーブルを「一つずらす場合」は、「ある列と、ある列からマイナス１した値」が等しい方法。
- しかし場合によって「異なる値」で結合することがある。そのような結合を非等値結合と言う。
- 代表的な活用事例はマーケットバスケット分析。同一の注文であわせ買いされる商品の組み合わせを可視化する手法。
- 例えば以下の例。１回あたりの注文（order_id）ごとに、どのような商品が購入されたか（product_name）を整理したテーブル。
- 上のテーブルを元に、非等値で自己結合し、「１回の注文における商品の組み合わせ表」を作成するためのSQLは以下のとおり。
- ポイントは④のとおり「異なる商品名の組み合わせ」を取得するため、あえて非等値を使用していること。
- なお、「＜」でなく「＜＞」を用いると「アジ・サバ」にくわえて「サバ・アジ」も抽出される。今回は組み合わせの網羅なので「＜」が適切。
記述順の整理について
- from、whereのあとにgroup by、havingと続き、最後にorder by、limitと記述する順番に注意。（SQLの内部処理もおおむねこの順番）
- 今まで学んだテーブルの結合は、「fromの一部」と覚えると順番がわかりやすい。
考察
- SQLについて。今回は「異なる性質（列）で分けたデータ」を、”横”につなげる操作「結合（join）」の発展を学んだ。5月7日～5月9日を通じて「結合（join）の基本・応用・発展」の学習が完了した。
- これ以降のSQL学習は「集合演算（union）⇒仮想テーブル⇒よく使う関数」までは必須。その後「分析に使う」観点ではウィンドウ関数の学習と分析演習、「試験対策」観点では参照以外の命令（insert,update,delete）や権限系の学習と過去問演習を行う形になる。
- 考察。今までの高度IT資格学習では「限られた時間での効率的なやり方」を模索・実践してきた。データベーススペシャリスト資格は午後２が論文でないなど特徴は異なる。しかし、学習法は可能な限り流用し「私に合った学習の仕組み作り」を工夫していきたい。
参考書籍
- 集中演習 SQL入門/木田和廣/株式会社インプレス
- データベーススペシャリスト2022年版/三好康之/翔泳社
- おうちで学べるデータベースのきほん/ミック,木村明治/翔泳社
コメント