SQLが苦手な方にレクチャーしている時のことです。「SQLの英語がわかりにくい、構文が何をしているかイメージしづらいのでマッチングアプリで例えてほしい」と言われまして、例えてみたら思ったよりもしっくりきたのでブログに残そうと思います。

題して、初心者でも簡単にわかるマッチングアプリを使ってクエリを学ぶです。

※あくまでわかり易さを意識するためマッチングアプリを題材にしただけになります。

※実際のマッチングアプリにどんなデータが格納されているかなど分かりませんし、イメージで書いています。

※最低限SELECT文などは書ける・意味がわかるプロダクトマネージャーを対象としています。

この記事で学べること

集計関数とGroup by を学ぶ (Case）
where句で複数条件をつける
条件に一致したデータを抜き出す（Having）
他のデータソースとくっつける（join）

題材（架空マッチングアプリ）

Aさんはとあるマッチングアプリを利用しています。マッチした人が1000名を超えたため効率よくデートをしていきたいと考えています。運良くマッチした人のDBにアクセスができたのでクエリを使って条件にマッチする人を抜き出したいと思っています。

今回はマッチしたユーザーから、平均年収450万以上の都道府県に在住の、年齢25歳未満・25~30歳・30歳以上でかつ職業別に何件あるかを知りたいとします。

まず名前と年齢と出身地、年収が入ったuser_infoデータベースを見つけたのでこれを使って絞り込みたいとします。

name	age	prefecture	income
山田太郎	25	東京都	400万円
田中亮太	35	大阪府	500万円
鈴木一郎	28	北海道	450万円
佐藤次郎	31	福岡県	550万円
高橋花道	29	愛知県	420万円

1. 年齢別で何人いるかをカウントしたい

あなたは1,000人のなかから年齢別にどれくらいいるのか知りたくなったとします。

そんなときに使えるのがGroup By集計関数です。

SELECT age, COUNT(*) as total_persons
FROM user_info GROUP BY age;

上記のクエリを使用することで、年齢ごとに人数をカウントすることができます。

GROUP BYは、SQLの集計関数の1つであり、データをグループ化して集計するために使用されます。GROUP BYを使用すると、クエリの結果を特定の列に基づいてグループ化することができます。今回であれば、出身地でもグループできますし、年収でもグループできます。

範囲を絞って絞り込みしたい。

今のは実数を使っていますが、例えば25歳未満、25歳~30歳などのように範囲を決めたいときもありますよね。そんな時はCase文を使います。

SELECT
  CASE
    WHEN age < 25 THEN '25歳未満'
    WHEN age >= 25 AND age <= 30 THEN '25歳から30歳'
    ELSE '30歳以上'
  END AS age_group,
  COUNT(*) as total_persons
FROM
  user_info
GROUP BY
  age_group;

CASE文は、SQLの条件付き処理の1つであり、条件に応じて異なる値を返すために使用されます。CASE文は、IF/ELSE文と似たような構文を持つことが多く、複雑な条件式を評価することができます。CASE文を使用するには、まずSELECTステートメントを使用して、クエリの結果を取得します。次に、CASE文を使用して、条件式を評価し、異なる値を返します。

2.出身地ごとの平均年収をみたい、そして絞り込みたい。

ここで脱線して、居住地ごとの平均年収を知りたくなりました。また平均年収450万円よりも高い居住地を知りたいとします。

SELECT
  prefecture,
  AVG(income) as avg_income
FROM
  user_info
GROUP BY
  prefecutre
HAVING
  avg_income > 450万円;

このクエリは、user_infoテーブルから居住地ごとに平均年収を計算し、450万円以上の平均年収を持つ居住地を抽出します。HAVING句を使用して、集計結果をフィルタリングしています。HAVING句は、WHERE句と同様に、条件に合致するレコードのみを抽出しますが、WHERE句とは異なり、HAVING句は集計関数に対して使用されます。

結果東京都と大阪府の平均年収が450万円より高いことを知りました！（事実は知らないです。すみません）

3.東京都と大阪府出身の人を抜き出したい。

年齢別に集計したクエリと、HAVINGを使って得た情報をうまく活用したいところです。

SELECT name, age, income
FROM user_info
WHERE prefecture IN ('東京都', '大阪府');

このクエリで、user_infoテーブルから、東京都または大阪府の人の名前、年齢、および年収を抽出できます。
これを1.でやった年齢の集計と組み合わせると、簡単に東京都・大阪府の人を年齢の範囲別にカウントできます。

SELECT
  CASE
    WHEN age < 25 THEN '25歳未満'
    WHEN age >= 25 AND age <= 30 THEN '25歳から30歳'
    ELSE '30歳以上'
  END AS age_group,
  COUNT(*) as total_persons
FROM
  user_info
WHERE prefecture IN ('東京都', '大阪府');
 GROUP BY
  age_group;

注意点としては、WHERE句は、GROUP BYよりも前にすることです。集計する前に条件を絞り込むとします。

ここまででを通じて東京都・大阪府の年齢範囲別にどんな人がいるかを知ることができるようになりました！

最後に職業も掛け合わせてみたいところですが、ここで問題がありました。今までは一つのテーブルでやっていましたが、職業は別の以下のようなテーブルにありました。

4.職業と組み合わせて知りたい情報を抜き出す。

別のテーブルに職業と名前が入ったテーブルを見つけました。ここで使えるのがjoinです。（SQLで一番つまづきやすい構文ですね！）

name	job
山田太郎	商社
田中亮太	IT
鈴木一郎	公務員
佐藤次郎	メーカー
高橋花道	IT

JOINは、SQLで最もよく使用されるクエリの1つであり、複数のテーブルからデータを抽出するために使用されます。JOINを使用すると、複数のテーブルからデータを結合して、1つのテーブルのように表示することができます。

例えば、ユーザー情報と職業情報を持つ2つのテーブルがある場合、JOINを使用して次のようにクエリを記述することができます。（※実際は名前で結合することはおすすめしませんが、今回は特別）

SELECT user_info.name, user_info.age, job_info.job
FROM user_info
INNER JOIN job_info ON user_info.name = job_info.name;

このクエリは、user_infoテーブルとjob_infoテーブルを結合し、名前が一致するレコードのユーザー名、年齢、および職業を抽出します。

INNER JOINは、2つのテーブルの内部結合を表します。JOINを使用すると、テーブルを結合する際に使用されるカラムを指定する必要があります。この例では、nameカラムが使用されています。

JOINには、他にもLEFT JOINやRIGHT JOINなどの種類があります。LEFT JOINは、左側のテーブルの全てのレコードを返し、右側のテーブルの該当するレコードがない場合はNULL値を返します。RIGHT JOINは、右側のテーブルの全てのレコードを返し、左側のテーブルの該当するレコードがない場合はNULL値を返します。

簡単に言うと、user_infoとjob_infoでLEFT JOINした場合は、user_infoに登録されている名前はすべて表示されます。ただ職業がない場合はNULLになります。RIGHTはその逆ですね。

これらを使うことで、以下のようなクエリを書くことができます。

SELECT
  CASE
    WHEN age < 25 THEN '25歳未満'
    WHEN age >= 25 AND age <= 30 THEN '25歳から30歳'
    ELSE '30歳以上'
  END AS age_group,
  job,
  COUNT(*) as total_persons
FROM
  user_info
INNER JOIN job_info ON user_info.name = job_info.name
WHERE prefecture IN ('東京都', '大阪府');
 GROUP BY
  age_group,job;

東京都・大阪府に在住のマッチしたユーザーから、年齢を25歳未満・25~30歳・30歳以上でかつ職業別に何件あるかを知ることができます！

SQLや実際に書くクエリは難しそうな印象もありますが、イメージしやすいデータを使いながら自分がやりたい手順に合わせると覚えやすいですよね。この記事がクエリに苦戦しているプロダクトマネージャーの役に立つことを祈ります！