2023年に知っておく必要のある2つの重要なSQL CASE WHENの例

'2 important SQL CASE WHEN examples to know in 2023'

データサイエンス

リアルなSQLのCASE WHEN問題の解決方法をマスターする

SQLでの本番面接問題のCASE WHEN使用例！

記事5つの高度なSQLコンセプトでは、CASE..WHEN文の基礎と同じくユースケースを探求することができます。ただし、これらの記事ではCASE WHENの実世界への適用については触れていません。

そのため、私はLinkedInでスポーツおよびeコマース企業のデータサイエンス専門家と連絡を取り、これらの2つのSQL CASE WHENの例を集めました。これらの例は、就職面接で最も頻繁に出題されるものです。

なぜCASE WHENが最も頻繁に問われるコンセプトの1つなのでしょうか？

• PDFからのエンティティ抽出をLLMsを使用して自動化する方法
• 合成データは、機械学習のパフォーマンスを向上させることができるのか？
• ローリング回帰による時間変動係数の推定

なぜなら、SQLのCASE WHEN文はデータのクエリ時にIf..Elseロジックを実装するのに役立つからです。

条件に基づいてデータを抽出または集計する必要があることがよくあります。もちろん、これらの条件を適用するためにWHERE句を使用することができますが、そのような条件に基づいて新しい列を作成したい場合には、CASE..WHENが便利で使用する必要があります。

この記事では、SQLのCASE WHENを使用してこれらの質問を解決するアプローチを学びます。さらに、複雑なクエリをシンプルで理解しやすいステップに分解する方法も学びます。

このクイックな読み物で探索する2つの質問を以下に簡単に紹介します。

・ 例1: スポーツトーナメントのポイント表を作成する ・ 例2: eコマースウェブサイトの新規およびリピート顧客を見つける

例データセットのCSVファイルは、この記事の最後にあります。

まずは、スポーツアナリストが提案した質問から始めましょう。彼らの分析チームは定期的に、異なるチーム間で行われた総試合数に基づいてポイント表を作成する必要があります。

そのため、彼らはこの質問をすべてのデータアナリストの求人面接で尋ねるのです。

例1: スポーツトーナメントのポイント表を作成する

これは、テーブルを長い形式（行数 > 列数）からワイド形式（列数 > 行数）に変換するクラシックなシナリオです。これはまた、SQLでのCASE WHENの重要なユースケースであるデータピボットとも呼ばれます。

このシナリオでは、試合を行ったチームの名前と勝者が記載されたテーブルがあります。各チームがいくつの試合を行い、いくつの試合に勝ち、負け、引き分けたかについての情報が得られるポイント表を作成する必要があります。

このような質問に対処する手順を見てみましょう。

以下は、各行が2つのチーム間の試合に属し、勝者の列がどのチームが勝ったかを示している入力テーブルです。勝者の列にNULLの値がある場合、試合は引き分けであり、どちらのチームも勝ちませんでした。

この質問を以下のサブタスクに分割しましょう。

1. 各チームが勝った試合の総数を見つける
2. 各チームが負けた試合の総数を見つける
3. どちらのチームも勝たなかった試合の総数を見つける
4. 各チームがプレイした試合の総数を見つける

チームが何回試合に勝ったかを理解するには、各試合でどのチームが勝ったかを理解する必要があります。これは、team_1列とteam_2列をwinner列と比較することで行うことができます。

したがって、特定の行において、team_1列の値とwinner列の値が等しい場合、team_1が勝者です。

以下に示すように、SQLのCASE..WHEN..THENステートメントを使用してまったく同じロジックを変換することができます。

SELECT team_1      , team_2      , winner      , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag      , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams

上記のクエリに示されているように、追加の列win_flagが作成されます。チームが勝利した場合、この列に値1が割り当てられます。同様に、winner列がNULLの場合、draw_flag列に値1が割り当てられます。

したがって、上記のクエリは、team_1列のすべてのチームに対して以下の出力を作成します。

同様に、team_2列とwinner列の値が等しい場合、team_2が勝者です。したがって、team_2のすべてのチームに対してまったく同じクエリを記述できます。

SELECT team_1      , team_2      , winner      , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag      , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams

ここで、team_2の値に対して以下の出力を得ることができます。

さて、上記の2つのクエリはあくまで理解のためのものです。実際には、team_1列とteam_2列の各チームのwin_flagおよびdraw_flagに対して単一のCTEを作成することができます。以下に示すように。

WITH win_draw_flag AS(SELECT team_1 as team      , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag      , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teamsUNION ALLSELECT team_2 as team      , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag      , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flagFROM analyticswithsuraj.teams)

これにより、以下のようなCTEが作成されます。これは理解のためにのみ表示しています。

まだ長い形式のテーブルにいることを覚えておいて、各チームが試合に勝ったかどうかの情報を持っています。

次に、列を単純に集計して、各チームの総試合数、勝利数、敗北数を取得する必要があります。次のクエリを使用して簡単に行うことができます。

SELECT team        , COUNT(*) AS matches_played        , SUM(win_flag) AS matches_won        , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS matches_lost        , SUM(draw_flag) AS matches_drawFROM win_draw_flagGROUP BY teamORDER BY team

COUNT(*)は、CTE win_draw_flagにおいて各チームが出現した総数を示し、それから勝利数と引き分け数を引くことで、各チームが試合に負けた総数を得ることができます。

CTEを別途作成せずに、以下のようにクエリを書くこともできます。そして、CASE..WHENクエリ全体をサブクエリとして渡すことができます。

SELECT team        , COUNT(*) AS matches_played        , SUM(win_flag) AS matches_won        , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS matches_lost        , SUM(draw_flag) AS matches_drawFROM   (  SELECT team_1 as team        , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag        , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag  FROM analyticswithsuraj.teams  UNION ALL  SELECT team_2 as team        , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag        , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag  FROM analyticswithsuraj.teams  ) AS win_draw_flagGROUP BY teamORDER BY team

これにより、先述の出力とまったく同じ結果が得られます。

さて、この問題を解決するための複数の方法がありますが、私はこのアプローチがより簡単だと考えました。もし他の解決策を見つけた場合は、コメントで教えてください。

例2：eコマースウェブサイトの新規顧客とリピート顧客を見つける

これは、日付の比較とIf..Elseロジックの実装にCASE..WHENステートメントを使用するクラシックな例の1つです。このタイプの問題は、顧客を取り扱う企業でどこでも遭遇する可能性があります。

シナリオは次のとおりです。毎日、顧客がウェブサイトを訪れて製品を購入します。あなたの仕事は、各日において新しい顧客がいくついたか、リピートした顧客がいくついたかを特定することです。

以下は、顧客がcustomer_id ABC101、BCD201、およびABD101でウェブサイトを複数の日に訪れ、異なる製品を購入した入力テーブルである注文です。

この問題を次のサブタスクに分解しましょう。

1. 最初の訪問日、つまり最初の日付を見つける
2. 最初の日付を注文日と比較して、顧客がリピートまたは初めて訪れたかどうかを判断する

以下のクエリを使用して、最初のサブタスクを簡単に解決できます。すべてのレコードをcustomer_idでグループ化し、order_dateの最小値を見つけることで解決できます。

SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id

これは簡単でした！

次に、first_order_dateを各order_dateと比較するために、まず両方の列を1つのテーブルに持ってくる必要があります。

上記のクエリを使用してcustomer_idでJOINすることで、簡単にそれを行うことができます。ここでは、上記のクエリを使用してCTEを作成し、入力テーブルと結合するための一時テーブルを取得できます。

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.*      , t2.first_order_dateFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id

今、両方の列を1つのテーブルに取り込んだので、order_dateとfirst_order_dateを比較し、以下のIf..Elseロジックを実装することができます。

1. first_order_dateとorder_dateが同じ場合、顧客は新規顧客です。
2. first_order_dateとorder_dateが異なる場合、顧客は繰り返し顧客です。

したがって、SQLのCASE WHENを使用して上記の2つのIf..Else文を実装するために、2つの列を作成する必要があります。

別のテーブルを作成する必要はありません。代わりに、2つのテーブルを結合したクエリに2つの列を追加することができます。以下はその方法です。

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.*      , t2.first_order_date      , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag      , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id

その結果、new_customer_flag列は、first_order_date列とorder_date列が等しい場合に1になります。同様に、repeat_customer_flag列は、first_order_date列とorder_date列が異なる場合に1になります。

最後のステップは、すべてのレコードをorder_dateでグループ化し、new_customer_flag列とrepeat_customer_flag列を合計することだけです。

これを行うには、上記のテーブルが必要です。以下に示すように、別のCTEを作成してこれを実現できます。

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id),customers AS(SELECT t1.*      , t2.first_order_date      , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag      , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id)SELECT order_date      , SUM(new_customer_flag) AS number_of_new_customers      , SUM(repeat_customer_flag) AS number_of_repeat_customersFROM customersGROUP BY order_dateORDER BY order_date

これが必要な出力を取得する方法です。入力テーブルと比較して結果を確認できます。

また、この問題を解決するための別のアプローチもありますが、これは私が見つけた最も簡単なアプローチです。以下のコメントでアプローチを記述することを忘れないでください。

以上、

この記事では、SQLのCASE WHENを使用した実世界のシナリオへのアプローチ方法と、複雑なSQLクエリをシンプルなものに分解する方法について学びました。この記事がお役に立てば幸いです。

ケーススタディやCASE WHEN、RANK()、ROW_NUMBER()、GROUP BYに基づく質問は、データサイエンスの求人面接で一般的です。問題を小さなサブタスクに分解することで、面接官に対してアプローチや問題解決の思考プロセスを示すことができます。

したがって、このトピックは、SQL CASE WHEN、GROUP BYのスキルを磨き、次の求人面接でも成功するために役立つでしょう。

VoAGIの他のストーリーを読みたいですか？

💡 VoAGIメンバーになって、VoAGIのストーリーや興味深いVoAGIニュースレターに無制限アクセスしましょう。料金の一部が私に入りますが、追加費用はかかりません。

💡 必ずサインアップし、他の200人以上に参加して、データサイエンスガイド、トリックとテクニック、SQLとPythonのベストプラクティスに関する別の記事をお見逃しなく。

読んでいただき、ありがとうございます！

データセット：これらは私がこれらの例のために作成したダミーデータセットです。私のGithubリポジトリから無料でダウンロードすることができます — 例1と例2。

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!