SQLでCASE式を使用する方法

序章

プログラミング言語は通常、条件付きステートメントを備えています。これは、特定の条件が満たされるまで指定されたアクションを実行するコマンドです。 一般的な条件ステートメントはif, then, elseステートメントであり、これは通常、次のロジックに従います。

if condition=true

    then action A

    else action B

このステートメントのロジックは、次の言語に変換されます。「 condition がtrueの場合、 actionAを実行します。 それ以外の場合（それ以外の場合）は、アクションBを実行します。」

CASE式は、構造化照会言語（SQL）の機能であり、データベース照会に同様のロジックを適用し、結果セットの値を返す方法または表示する方法に条件を設定できます。

このチュートリアルでは、CASE式を使用して、WHEN、THEN、ELSE、およびENDキーワード。

前提条件

このチュートリアルを完了するには、次のものが必要です。

• sudo管理者権限とファイアウォールが有効になっているroot以外のユーザーがいるUbuntu20.04を実行しているサーバー。 Ubuntu20.04を使用したサーバーの初期設定に従って開始します。
• MySQLがサーバーにインストールされ、保護されています。 これを設定するには、 Ubuntu20.04ガイドにMySQLをインストールする方法に従ってください。 このガイドは、このガイドのステップ3 で概説されているように、root以外のMySQLユーザーも設定していることを前提としています。

注：多くのリレーショナルデータベース管理システムは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。

このチュートリアルでCASE式の使用を練習するには、サンプルデータがロードされたデータベースとテーブルが必要です。 挿入する準備ができていない場合は、次の MySQLへの接続とサンプルデータベースの設定セクションを読んで、データベースとテーブルを作成する方法を学ぶことができます。 このチュートリアルでは、このサンプルデータベースとテーブル全体を参照します。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

MySQLへの接続とサンプルデータベースの設定

SQLデータベースがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、sammyをMySQLユーザーアカウント情報に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

caseDBという名前のデータベースを作成します。

CREATE DATABASE caseDB;

データベースが正常に作成されると、次の出力が表示されます。

OutputQuery OK, 1 row affected (0.01 sec)

caseDBデータベースを選択するには、次のUSEステートメントを実行します。

USE caseDB;

OutputDatabase changed

データベースを選択したら、その中にテーブルを作成します。 このチュートリアルの例では、これまでで最も売れた10枚のアルバムのデータを保持するテーブルを作成します。 このテーブルには、次の6つの列が含まれます。

• music_id：intデータ型の値を表示し、テーブルの主キーとして機能します。つまり、この列の各値は、それぞれの一意の識別子として機能します。行。
• artist_name：最大30文字のvarcharデータ型を使用して各アーティスト名を保存します。
• album_name：varcharデータ型を使用します。これも、各アルバムの名前を保持するために最大30文字です。
• release_date：YYYY-MM-DD日付形式を使用する、DATEデータ型を使用して、各アルバムのリリース日を追跡します。
• genre_type：最大25文字のvarcharデータ型を使用して、各アルバムのジャンル分類を表示します。
• copies_sold：decimalデータ型を使用して、数百万枚のアルバムコピーの総数を保存します。 この列は、1のスケールで4の精度を指定します。つまり、この列の値は4桁で、そのうちの1桁は小数点の右側にあります。

次のCREATE TABLEコマンドを実行して、これらの各列を含むtop_albumsという名前のテーブルを作成します。

CREATE TABLE top_albums (
music_id int, 
artist_name varchar(30),
album_name varchar(30), 
release_date DATE,
genre_type varchar(25),
copies_sold decimal(4,1),
PRIMARY KEY (music_id)
); 

次に、いくつかのサンプルデータを空のテーブルに挿入します。

INSERT INTO top_albums
(music_id, artist_name, album_name, release_date, genre_type, copies_sold)
VALUES
(1, 'Michael Jackson', 'Thriller', '1982-11-30', 'Pop', 49.2),
(2, 'Eagles', 'Hotel California', '1976-12-08', 'Soft Rock', 31.5),
(3, 'Pink Floyd', 'The Dark Side of the Moon', '1973-03-01', 'Progressive Rock', 21.7),
(4, 'Shania Twain', 'Come On Over', '1997-11-04', 'Country', 29.6),
(5, 'AC/DC', 'Back in Black', '1980-07-25', 'Hard Rock', 29.5),
(6, 'Whitney Houston', 'The Bodyguard', '1992-11-25', 'R&B', 32.4),
(7, 'Fleetwood Mac', 'Rumours', '1977-02-04', 'Soft Rock', 27.9),
(8, 'Meat Loaf', 'Bat Out of Hell', '1977-10-11', 'Hard Rock', 21.7),
(9, 'Eagles', 'Their Greatest Hits 1971-1975', '1976-02-17', 'Country Rock', 41.2),
(10, 'Bee Gees', 'Saturday Night Fever', '1977-11-15', 'Disco', 21.6);

OutputQuery OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

データを挿入すると、SQLでCASE式を使用できるようになります。

CASE式の構文を理解する

CASE式を使用すると、データの条件を設定し、if-thenステートメントと同様のロジックを使用してデータを検索し、値を比較して、設定した条件に「真」として一致するかどうかを評価できます。 。 CASE式の一般的な構文の例を次に示します。

CASE式の構文

. . .
CASE 
    WHEN condition_1 THEN outcome_1
    WHEN condition_2 THEN outcome_2
    WHEN condition_3 THEN outcome_3
    ELSE else_outcome
END 
. . .

データに設定する条件の数に応じて、CASE式に次のキーワードも含めます。

• WHEN：このキーワードは、テーブルにあるデータ値を評価し、設定した条件または基準と比較します。 WHENは、一般的なif-then-elseステートメントのifに相当します。
• THEN：このキーワードは、特定の値が基準を満たさない場合に設定した可能性のある各条件をフィルタリングします。
• ELSE：WHENおよびTHENの各ステートメントを実行した後、データ値が設定した条件のいずれも満たさない場合、このキーワードを使用して、最終的な状態は、次のように分類できます。
• END：CASE式を正常に実行して条件を設定するには、ENDキーワードで終了する必要があります。

CASE式の構造と構文をこのように理解すると、サンプルデータの練習を開始する準備が整います。

CASE式の使用

あなたがエキセントリックな叔母キャロルの65歳の誕生日のお祝いのセットリストを準備しているDJだと想像してみてください。 彼女の好みを特定するのは難しいことを知っているので、あなたはあなたの音楽的な決定のいくつかを知らせるために、これまでのトップ10の販売アルバムについていくつかの調査を行うことにしました。

まず、SELECTと*記号を実行して、top_albumsテーブルにコンパイルしたリストを確認し、各列のすべてのデータを表示します。

SELECT * FROM top_albums;

Output+----------+-----------------+-------------------------------+--------------+------------------+-------------+
| music_id | artist_name     | album_name                    | release_date | genre_type       | copies_sold |
+----------+-----------------+-------------------------------+--------------+------------------+-------------+
|        1 | Michael Jackson | Thriller                      | 1982-11-30   | Pop              |        49.2 |
|        2 | Eagles          | Hotel California              | 1976-12-08   | Soft Rock        |        31.5 |
|        3 | Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Progressive Rock |        21.7 |
|        4 | Shania Twain    | Come On Over                  | 1997-11-04   | Country          |        29.6 |
|        5 | AC/DC           | Back in Black                 | 1980-07-25   | Hard Rock        |        29.5 |
|        6 | Whitney Houston | The Bodyguard                 | 1992-11-25   | R&B              |        32.4 |
|        7 | Fleetwood Mac   | Rumours                       | 1977-02-04   | Soft Rock        |        27.9 |
|        8 | Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Hard Rock        |        21.7 |
|        9 | Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Country Rock     |        41.2 |
|       10 | Bee Gees        | Saturday Night Fever          | 1977-11-15   | Disco            |        21.6 |
+----------+-----------------+-------------------------------+--------------+------------------+-------------+
10 rows in set (0.00 sec)

キャロルおばさんは1957年に生まれて以来、70年代から80年代にかけて多くのヒット曲を楽しんでいました。 彼女はポップ、ソフトロック、ディスコの大ファンなので、セットリストの最優先事項としてランク付けしたいと思います。

これを行うには、CASE式を使用して、genre_type列でこれらのデータ値を照会することにより、特定のジャンルの「高優先度」の条件を設定します。 次のクエリはこれを実行し、CASE式によって作成された結果の列のエイリアスを作成し、priorityという名前を付けます。 このクエリには、artist_name、album_name、およびrelease_dateも含まれています。 ENDキーワードを使用して、完全なCASE式を完成させることを忘れないでください。

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Pop' THEN 'High Priority' 
WHEN genre_type = 'Soft Rock' THEN 'High Priority'
WHEN genre_type = 'Disco' THEN 'High Priority'
END AS priority
FROM top_albums;

Output+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | priority      |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | NULL          |
| Shania Twain    | Come On Over                  | 1997-11-04   | NULL          |
| AC/DC           | Back in Black                 | 1980-07-25   | NULL          |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | NULL          |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | NULL          |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | NULL          |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

この出力は、これらのHigh Priorityジャンルタイプに設定した条件を反映していますが、ELSEキーワードを省略したため、NULL値と呼ばれるデータ値が不明または欠落しています。 。 データ値がCASE式で設定したすべての条件を満たしている場合、ELSEキーワードは必要ない場合がありますが、残りのデータに役立つため、単一のデータに適切に分類できます。調子。

この次のクエリでは、同じCASE式を記述しますが、今回はELSEキーワードで条件を設定します。 次の例では、ELSE引数は、genre_typeの優先度の高いデータ値を「たぶん」とラベル付けします。

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Pop' THEN 'High Priority' 
WHEN genre_type = 'Soft Rock' THEN 'High Priority'
WHEN genre_type = 'Disco' THEN 'High Priority'
ELSE 'Maybe'
END AS priority
FROM top_albums;

[sceondary_label Output]
+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | priority      |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Maybe         |
| Shania Twain    | Come On Over                  | 1997-11-04   | Maybe         |
| AC/DC           | Back in Black                 | 1980-07-25   | Maybe         |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | Maybe         |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Maybe         |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Maybe         |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

この出力は、優先度が最も高いアルバムとそうでないアルバムに設定した条件をよりよく表しています。 これは上位4枚のアルバム（Thriller、Hotel California、Rumours、Saturday Night Fever）の優先順位付けに役立ちますが、このセットリスト。 しかし、あなたはこれについてもキャロル叔母を説得しなければならないでしょう。

あなたは小さな実験を行い、キャロル叔母に彼女の音楽パレットを広げて残りのアルバムを聴くように頼むことにしました。 アルバムについてのコンテキストを提供せず、「メロウ」、「楽しい」、または「退屈」として正直にスコアを付けるように彼女に指示します。 彼女が終わったら、彼女はあなたに彼女のスコアで手書きのリストを渡します。 これで、クエリの条件を次のように設定するために必要な情報が得られました。

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Hard Rock' THEN 'Boring' 
WHEN genre_type = 'Country Rock' THEN 'Mellow'
WHEN genre_type = 'Progressive Rock' THEN 'Fun'
WHEN genre_type = 'Country' THEN 'Fun'
WHEN genre_type = 'R&B' THEN 'Boring'
ELSE 'High Priority' 
END AS score
FROM top_albums;

Output
+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | score         |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Fun           |
| Shania Twain    | Come On Over                  | 1997-11-04   | Fun           |
| AC/DC           | Back in Black                 | 1980-07-25   | Boring        |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | Boring        |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Boring        |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Mellow        |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

この出力に基づいて、キャロルおばさんは新しい音にオープンであるように見えます、そしてあなたはピンクフロイドのための彼女のスコアにうれしく驚いています。 しかし、AC / DC、ミートローフ、ホイットニー・ヒューストンの素晴らしい曲に彼女が興味を持っていないことに少しがっかりしています。

キャロルおばさんは、いくつかのアルバムが他のアルバムよりも客観的に人気があることを彼女に示すことができれば、より柔軟になる可能性があります。そのため、決定を左右するためにいくつかの数字を取り込むことにします。 事実、これらは何十年にもわたってファンに何百万枚ものコピーを販売してきたため、トップ10のアルバムです。 したがって、この次のクエリでは、これまでに販売されたアルバムのcopies_soldの数値データに基づいてスコアを設定する新しいCASE式を作成します。

CASE式を使用して、3500万枚以上を「最高」、2500万枚を「素晴らしい」、2000万枚を「良い」、およびそれ以下のアルバムの販売条件を設定します。次の例のように「平凡」です。

SELECT artist_name, album_name, release_date, CASE WHEN copies_sold >35.0 THEN 'best'
WHEN copies_sold >25.0 THEN 'great'
WHEN copies_sold >20.0 THEN 'good'
ELSE 'mediocre' END AS score FROM top_albums;

Output+-----------------+-------------------------------+--------------+-------+
| artist_name     | album_name                    | release_date | score |
+-----------------+-------------------------------+--------------+-------+
| Michael Jackson | Thriller                      | 1982-11-30   | best  |
| Eagles          | Hotel California              | 1976-12-08   | great |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | good  |
| Shania Twain    | Come On Over                  | 1997-11-04   | great |
| AC/DC           | Back in Black                 | 1980-07-25   | great |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | great |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | great |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | good  |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | best  |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | good  |
+-----------------+-------------------------------+--------------+-------+
10 rows in set (0.00 sec)

この出力に基づいて、それぞれが2,000万枚以上を販売したため、「平凡」と評価されたアルバムはありませんでした。 ただし、スコアに基づいて他のアルバムの中で際立っているアルバムがいくつかあります。 キャロルおばさんにAC/DCまたはホイットニーヒューストンを演奏する確かな証拠を提供できるようになりました。彼らのアルバムは2500万枚以上売れ、2つの最高の音楽作品になっています。

これで、CASE式を使用して、さまざまな目的の条件を文字および数値データ値で設定する方法を理解できました。 また、CASEがif-thenロジックを使用してこれらの値を比較し、目的の条件に基づいて応答を生成する方法。

結論

CASE式の使用方法を理解すると、設定した条件にデータを絞り込むのに役立ちます。 特定の値に異なる優先順位を設定する場合でも、一般的な意見や数値からの基準に基づいてスコアを付ける場合でも、ニーズに柔軟に対応できます。 結果セットのデータ値を操作する他の方法について知りたい場合は、CAST関数と連結式に関するガイドを確認してください。