【SQL文法基礎6】条件に合わせて分類をする 〜 CASE 〜
データ分析では、複数のテーブルを結合してデータを取得することが多いです。しかし、必ずしもほしいデータがテーブルに存在しているとは限りません。その場合は、条件に合わせてデータを分類することで、自分がほしいデータを作る必要があります。 本章では、そんなデータの分類に必要な方法について学んでいきましょう。
データを分類するCASE式
HML分析でユーザーを分類するための定義
データ分析で購入履歴を分析することはよくあります。例えば、どんなユーザーが、いつ、何を購入したのか、どのくらいの金額を使ったのかなど、購入履歴を分析して、自社のECサイトの状況を把握することは、ビジネスにおいても非常に重要です。
本章では、購入履歴テーブルを使ってHML分析をする方法を考えてみましょう。HML分析とは、ユーザーをヘビー（Heavy）、ミドル（Middle）、ライト（Light）の3つに分類して購入者を分析する手法です。
HML分析をする前に、最初に必要なことは、どのような条件で分類するかを決めることです。データ分析では、データの集計の前に定義を確認することも重要なステップなので、ぜひ覚えておきましょう。今回は、ユーザーごとの年間の購入金額によって、次のように分類します。また、このときユーザー情報が空のデータと、キャンセルのある購入データは含めません。
- ヘビー（H）：年間の購入金額が1,000,000円以上
- ミドル（M）：年間の購入金額が200,000円以上、1,000,000円未満
- ライト（L）：年間の購入金額が200,000円未満
・図8-1：HML分析
分類の定義が決まったら、次はSQLでデータを取得します。最初に、ユーザーごとの年間の購入金額を求めるSQL8-1を実行して、出力結果を確認してみましょう。SQL8-1はこれまでの復習になるので、まずはSQLの中身を確認します。
HMLの分類をするためには、ユーザーごとの購入金額が必要です。そのため、SQL8-1では、JOINを使って購入履歴テーブル（orders）と商品情報テーブル（products）を結合し、商品の金額を取得できるようにしています。また、ユーザーごとの購入金額が必要なので、GROUP BY o.user_idでユーザーごとに集計します。購入金額はSUM(p.price)で求めることができます。
最後に条件として、o.user_id IS NOT NULLでユーザー情報が空でない条件と、o.order_date BETWEEN '2023-01-01' AND '2023-12-31'で年間の購入金額になるように期間の条件を設定します。また、キャンセルのある購入データは含めないため、AND o.is_canceled = 0の条件をつけます。
これで、ユーザーごとの年間の購入金額を求めることができました。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-1
SELECT
o.user_id,
SUM(p.price) AS total_price
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・表8-2：SQL8-1の実行結果
user_id | total_price |
|---|---|
A000001 | 25400 |
A000002 | 4200 |
A000003 | 6100 |
A000004 | 25600 |
A000005 | 155290 |
A000006 | 63300 |
A000008 | 40000 |
A000009 | 271200 |
A000010 | 30900 |
A000011 | 38700 |
・・・ | ・・・ |
CASE式を使ったデータの分類
HML分析ではユーザーごとの購入金額によって、ユーザーを分類する必要があります。このとき、既存のデータから新しいデータへ分類する方法として活用できるのがCASE式です。CASE式を活用することで、SQL8-1で求めたユーザーごとの年間の購入金額をもとに、ユーザーをヘビー、ミドル、ライトに分類できます。
まずは、CASE式の基本構文から学んでいきましょう。CASEで始まり、ENDで終わるのがCASE式です。WHENのあとには条件を設定して、その条件に一致した場合の値をTHENのあとに設定します。CASE式の中では、WHENからTHENは何個でも記述することができます。最後の条件として、ELSEにはWHENからTHENで設定した条件以外に該当する場合の値を設定できます。
・CASE式の基本構文
CASE
WHEN 条件1 THEN 結果1
WHEN 条件2 THEN 結果2
(ELSE 上記以外の条件に該当した場合の結果)
ENDでは、実際にCASE式を使ってHMLの分類をしたSQLを確認します。SQL8-2を実行して、SQL8-1との出力結果の違いも確認してみましょう。
基本的なSQLはSQL8-1もSQL8-2も同じですが、CASE式が追加されているのが大きな違いです。CASEからENDまでの記述で、ユーザーの購入金額からヘビー、ミドル、ライトの3つに分類した新しい値が追加されています。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-2
SELECT
o.user_id,
SUM(p.price) AS total_price,
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) < 1000000 AND 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・表8-3：SQL8-2の実行結果
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 | ライト |
・・・ | ・・・ | ・・・ |
SQL8-2のCASEからENDまでを確認すると、最初の条件として、WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'があります。これは購入金額であるSUM(p.price)が1,000,000円以上の場合を条件にして、そのときの結果をTHENのあとで「ヘビー」と設定しています。
同じくWHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ミドル'は、購入金額が1,000,000円未満で200,000円以上の場合は「ミドル」、WHEN SUM(p.price) < 200000 THEN 'ライト'は、購入金額が200,000円未満の場合は「ライト」と値を設定しています。
最後に、ELSE 'その他'としているのはヘビー、ミドル、ライトの条件のどれとも一致しなかった場合に「その他」と設定しています。ただし、今回の場合ではユーザーの購入金額は必ずヘビー、ミドル、ライトの3つのいずれかに分類されるため、ELSEの条件はなくても問題ありません。
また、CASE式で設定した出力結果にASでhmlという別名をつけています。これはSELECT文のときに解説したASを使って、出力結果テーブルのカラムに別名をつけるいう方法を活用しています。今回のようにCASE式を使うときも、ASを使って別名をつけることができます。ASを活用することで、CASE式でどんな処理をして何を求めているのかわかりやすくなります。
・SQL8-2で使われているCASE式
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ミドル'
WHEN SUM(p.price) < 200000 THEN 'ライト'
ELSE 'その他'
END AS hmlこのように、CASE式を使うことで条件に合わせて特定の値を設定できます。ユーザーごとの購入金額に応じてHMLに分類する場合など、データ分析では既存のデータから新しい値を設定する場合に、CASE式が活用されることが多いです。
CASE式の注意点
CASE式を使う場合には、条件を記述する順番に注意する必要があります。CASE式の条件は記述した上から順番に評価され、条件に一致した場合はそのあとの条件はスキップされます。
この考え方を使って、SQL8-2のCASE式は次のように書き換えることができます。SQL8-2との違いは、2つめの条件がWHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ミドル'からWHEN SUM(p.price) >= 200000 THEN 'ミドル'に変わっていることです。どちらの条件でも結果は同じになります。
・SQL8-2で使われているCASE式の書き換え
CASE
WHEN SUM(p.price) >= 1000000 THEN 'ヘビー'
WHEN SUM(p.price) >= 200000 THEN 'ミドル'
WHEN SUM(p.price) < 200000 THEN 'ライト'
ELSE 'その他'
END AS hmlCASE式は、複数条件が記載されている場合に上から順番に処理が実行され、条件に一致した場合、そのあとの条件はスキップされます。つまり、1つ目の条件であるSUM(p.price) >= 1000000に一致しない場合のみ、次の条件であるSUM(p.price) >= 200000の条件に続きます。したがって、もともと記述していたSUM(p.price) < 1000000の条件はあってもなくても結果は同じになります。
ここで注意が必要なのが、CASE式の順番を変えると結果も変わる可能性があるということです。HMLに分類をするSQLで、CASE式の条件の順番を変えるとどうなるか、SQL8-3を実行して出力結果を確認してみましょう。
SQL8-2とSQL8-3はどちらもHMLの分類をするSQLです。CASE式にある条件の順番を変えただけで、他の条件は変わっていません。違いはCASE式の条件であるミドルとヘビーの順番が変わっていることです。このようにCASE式で条件の順番を変えると結果が変わってしまいます。実際にSQL8-3を実行すると、SQL8-2と結果が異なり、出力結果にヘビーが出力されていないのがわかります。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-3
SELECT
o.user_id,
SUM(p.price) AS total_price,
CASE
WHEN SUM(p.price) >= 200000 THEN 'ミドル'
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・表8-4：SQL8-3の実行結果
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 | ライト |
・・・ | ・・・ | ・・・ |
CASE式の処理を確認すると、最初に書かれているSUM(p.price) >= 200000が処理されます。その条件に一致しない場合は、次に書かれているSUM(p.price) >= 1000000の条件に続きます。そうすると、例えば合計金額が120万円だったとき、最初の条件であるSUM(p.price) >= 200000にも一致します。つまり、この順番で条件を記述すると、合計金額が100万円以上のヘビーであっても、最初の条件であるミドルと判定されてしまいます。そのため、出力結果にヘビーが出力されず、ミドルかライトのみになってしまい、本来のHMLの値とは異なる結果になってしまいます。
このように、CASE式を使って複数の条件を使うときは、最初に記述した条件から順番に処理が実行されるということに注意しないと、意図した結果にならない場合があります。間違えないためには、厳しい条件から順番に記述して、徐々に緩い条件を記述するように意識しましょう。
集約関数とCASE式を組み合わせる
別々の条件でデータを取得する場合
CASE式は条件に一致した新しい値を設定することができるため、データ分析では集約関数と合わせて使うことがあります。例えば、購入履歴テーブルから商品情報のカテゴリが「食品」の購入件数と、「水」の購入件数を同時に求めたい場合に、どうすればよいか考えてみましょう。
複数の条件で同時に求めるのが難しい場合は、別々のデータとして集計できるか考えてみます。まずは正確なカテゴリ情報を把握するために、SQL8-4を実行して出力結果を確認してみましょう。SQL8-4では、重複を排除するDISTINCTを使って、カテゴリ情報にどんな値が入っているのか確認しています。そうすると、large_categoryに「食品」、small_categoryに「水」というデータが入っているのが確認できます。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-4
SELECT DISTINCT
large_category,
medium_category,
small_category
FROM
products・表8-5：SQL8-4の実行結果
large_category | medium_category | small_category |
|---|---|---|
インテリア | 家具 | ソファ |
インテリア | 家具 | 台 |
インテリア | 家具 | 棚 |
インテリア | 家具 | 椅子 |
ファッション | アクセサリー | 時計 |
ファッション | カバン | トートバッグ |
ファッション | シューズ | パンプス |
ファッション | 衣服 | トップス |
ファッション | 衣服 | ワンピース |
ファッション | 衣服 | 靴下 |
・・・ | ・・・ | ・・・ |
この情報をもとに、食品の購入件数と水の購入件数をそれぞれ取得してみます。SQL8-5、SQL8-6を実行して、出力結果を確認してみましょう。SQL8-5とSQL8-6はWHEREの条件が違うだけでそれ以外は同じSQLになっています。
まずは購入件数を確認したいので、購入履歴テーブル（orders）からデータを取得しています。また、カテゴリの情報は購入履歴テーブルには入っていないので、商品情報テーブル（products）と結合しています。結合には左外部結合を使っていますが、内部結合を使っても同じ結果になるため、今回はどちらでも問題ありません。
あとは求めたい条件をつけ、集約関数を使って購入件数を集計します。SQL8-5ではp.large_category = '食品'、SQL8-6ではp.small_category = '水'とそれぞれWHEREの中で条件を設定しています。そして、購入件数の集計には集約関数を使って、COUNT(DISTINCT o.order_id)で求めることができます。
ちなみに、本サイトのデータでは購入ID（order_id）は同じ注文で複数商品を購入した場合に同じ値が複数行に現れるため、COUNT(DISTINCT order_id)で正確な注文件数を集計できます。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-5
SELECT
COUNT(DISTINCT o.order_id) AS order_count
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
p.large_category = '食品'・表8-6：SQL8-5の実行結果
order_count |
|---|
2810 |
・SQL8-6
SELECT
COUNT(DISTINCT o.order_id) AS order_count
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
p.small_category = '水'・表8-7：SQL8-6の実行結果
order_count |
|---|
1807 |
このように、違う条件でデータを求める場合は、WHEREの条件を変更して2回SQLを実行することで集計ができます。
CASE式を活用した効率的なデータ取得方法
別々の条件でデータを取得する場合、条件を変えて2回SQLを実行するのは効率が悪いです。なぜなら、SQLを2回実行することで手間もかかりますし、前述したSQL8-5とSQL8-6の違いはWHEREの条件だけで、あとはまったく同じSQLだからです。このように、別々の条件でデータを取得する場合、効率的に集計する方法が集約関数とCASE式を組み合わせたデータ集計です。
SQL8-5、SQL8-6と同じようにカテゴリが「食品」の購入件数と、「水」の購入件数を同時に求めたい場合、CASE式を使って1つのSQLにまとめることができます。SQL8-7を実行して、出力結果を確認してみましょう。
SQL8-7を見ると複雑なSQLに感じるかもしれませんが少しずつ解説します。まず、基本的なSQLはSQL8-5やSQL8-6とほとんど変わっていません。購入履歴テーブルと商品情報テーブルを結合させて、購入IDを集計することで、購入件数を求めています。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-7
SELECT
COUNT(DISTINCT CASE WHEN p.large_category = '食品' THEN o.order_id ELSE NULL END) AS "食品",
COUNT(DISTINCT CASE WHEN p.small_category = '水' THEN o.order_id ELSE NULL END) AS "水"
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id・表8-8：SQL8-7の実行結果
食品 | 水 |
|---|---|
2810 | 1807 |
SQL8-7はSELECTのあとに記述されている集約関数とCASE式が複雑ですが、SQL8-7を次のようなSQLだと思うと理解しやすいかもしれません。
・SQL8-7のイメージ
SELECT
COUNT(DISTINCT CASE式) AS "食品",
COUNT(DISTINCT CASE式) AS "水"
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_idSQL8-7で今までと違うところは、集約関数のCOUNTの中にCASE式が入っていることです。CASE式は条件を設定して、条件に一致する値を設定できます。それを応用して、集約関数とCASE式を組み合わせることで、異なる条件をまとめて1回のSQLで集計できます。
SQL8-7の集約関数の中で使われているCASE式を確認しましょう。CASE式の中を見ると、WHEN p.large_category = '食品'となっているので、大カテゴリが食品という条件が設定されています。そのあとはTHEN order_id ELSE NULL ENDとなっているため、これは大カテゴリが食品の場合はorder_idが出力され、それ以外の場合はNULLが出力されるということです。つまり、このCASE式の中ではorder_idかNULLのどちらかが出力されるようになっています。そして、CASE式の前にDISTINCTがついてるため、order_idに重複があった場合は、重複が排除されます。また、NULLの場合は、集約関数の集計対象から外されます。そのため、今回の場合は大カテゴリが食品の場合だけ、購入件数が集計されることになります。
・SQL8-7で使われているCASE式①
CASE
WHEN p.large_category = '食品' THEN order_id
ELSE NULL
END同様に、カテゴリが水の購入件数も求めることができます。食品の購入件数を出力するSQLとの違いは、CASE式の条件がp.small_category = '水'となっている部分です。この場合は、小カテゴリが水という条件が設定されているため、小カテゴリが水の場合だけorder_idが出力され、それをCOUNTで集計し、購入件数を求めています。
・SQL8-7で使われているCASE式②
CASE
WHEN p.small_category = '水' THEN order_id
ELSE NULL
ENDまた、集約関数の最後にはASを使って、別名で集計結果を取得しています。SQL8-7では、カテゴリが食品の場合の購入件数と水の場合の購入件数をそれぞれ集計しているため、どちらがどんな集計結果になっているかわかりやすいように、ASを使って「食品」と「水」という別名をつけて、出力結果のカラムをわかりやすくしています。
このように、集約関数とCASE式を使うことで、複数の条件でも1つのSQLを実行するだけで集計結果を求めることができます。データ集計では、条件を一部変えてデータを取得することはよくあるため、今回のように集約関数とCASE式を組み合わせる手法を覚えておくと非常に便利です。
・図8-9：集約関数とCASE式を組み合わせたイメージ
UNIONを使って複数の条件をまとめて取得する場合との違い
SQL8-7の出力結果を確認すると、食品と水の購入件数が横に出力されています。これはSELECT文の中で集約関数とCASE式を使って2つの集計結果を出力しているため、2つのデータが横に出力されたということです。
横持ちのデータを変換し、データを縦に結合して取得する方法は、第7章の「複数のテーブルを「縦に」結合するUNION」で解説しました。例えば、食品の購入件数と水の購入件数を縦に出力したい場合は、UNIONを使って同じように1回のSQLで求めることができます。
・図8-10：UNIONとCASE式の違い
SQL8-8を実行して、集約関数とCASE式を合わせたときの出力結果の違いを確認してみましょう。SQL8-8は、SQL8-5とSQL8-6をUNION ALLで結合したSQLです。SQL8-8の出力結果には重複行がないため、UNION ALLを使ってもUNIONを使っても、どちらも同じ結果になります。このように、食品の購入件数と水の購入件数を同時に集計したいときは、UNION ALLを使った場合と、CASE式を使った場合、どちらでも求めることができます。
大きな違いは、結果が横持ちで出力されるか、縦持ちで出力されるかです。UNIONで結合する場合は、データが縦に結合されますが、CASE式を使う場合は、SELECTのあとにカラムとして出力するため、横持ちで結果が出力されます。したがって、縦持ちで結果を求めたい場合にはUNIONを使って、横持ちで結果を求めたい場合にはCASE式を使うのがよいでしょう。
ただし、UNIONを使う場合は、同じSQLを2回実行しているのに近く、SQLの重複がある分非効率であることに変わりありません。そのため、今回の場合はCASE式を使って集計できたほうがより効率的です。
★ハンズオン〜SQLを実行してみよう〜
・SQL8-8
SELECT
'食品' as data_key,
COUNT(DISTINCT o.order_id) as data_value
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
p.large_category = '食品'
UNION ALL
SELECT
'水' as data_key,
COUNT(DISTINCT o.order_id) as data_value
FROM
orders AS o
LEFT JOIN products AS p ON o.product_id = p.product_id
WHERE
p.small_category = '水'・表8-11：SQL8-8の実行結果
data_key | data_value |
|---|---|
食品 | 2810 |
水 | 1807 |
データ分析における「CASE式」の考え方
CASE式を使うと、複数の条件に合わせてデータを分類できます。CASE式を活用することで、もともとのテーブルにない新しい情報を追加したり、集約関数と合わせて、複数の条件をまとめて1回のSQLで取得できます。データ分析においては、図8-12のように、既存のテーブルからデータを「横」に追加して出力結果を求めることがCASE式で重要な考え方です。なので、新し値（カラム）を追加したい場合や、データを横に持ちたい場合などは、CASE式をうまく活用していきましょう。
・図8-12：CASE式の考え方
演習問題
演習問題8-1
次の条件に従い、2023年の年間の購入件数に応じてユーザーをHML（ヘビー、ミドル、ライト）に分類して、ユーザーID、購入件数、HMLの3つを出力してください。また、このときユーザー情報が空のデータとキャンセルのある購入データは除外してください。
- ヘビー：年間の購入件数が500件以上
- ミドル：年間の購入件数が300件以上、500件未満
- ライト：年間の購入件数が300件未満
演習問題8-2
集約関数とCASE式を使って購入履歴テーブル（orders）から男性の購入件数、女性の購入件数、全体の購入件数の3つを同時に出力してください。