SQLでCASE式を使用する方法

投稿日: 2022-10-25 2022-10-25
タグ: sql

序章

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

if condition=true

	then action A

	else action B

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

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

このチュートリアルでは、の使用方法を学習します。 CASE を使用してデータに条件を設定する式 WHEN, THEN, ELSE、と END キーワード。

前提条件

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

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

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

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

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

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

ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、置き換えます sammy with your MySQL user account information:

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：を使用して各アーティストの名前を保存します varchar 最大30文字のデータ型。
album_name：を使用します varchar データ型。各アルバムの名前を保持するために最大30文字。
release_date：を使用して各アルバムのリリース日を追跡します DATE を使用するデータ型 YYYY-MM-DD 日付形式。
genre_type：を使用して各アルバムのジャンル分類を表示します varchar 最大25文字のデータ型。
copies_sold：を使用します decimal 数百万人が販売したアルバムコピーの総数を格納するデータ型。この列は、1のスケールで4の精度を指定します。つまり、この列の値は4桁で、そのうちの1桁は小数点の右側にあります。

名前の付いたテーブルを作成します top_albums 次のコマンドを実行することにより、これらの各列が含まれます CREATE TABLE 指図：

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

データを挿入すると、使用を開始する準備が整います CASE SQLの式。

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 典型的な if-then-else 声明。
THEN：このキーワードは、特定の値が基準を満たさない場合に設定した可能性のある各条件をフィルタリングします。
ELSE：データ値が、それぞれを通過した後に設定した条件のいずれも満たさない場合 WHEN と THEN ステートメントの場合、このキーワードを使用して、分類できる最終条件を指定できます。
END：正常に実行するには CASE 式と条件を設定するには、で終了する必要があります END キーワード。

この理解で CASE 式の構造と構文がわかれば、サンプルデータの練習を始める準備ができています。

CASE式の使用

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

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

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 値。ながら ELSE データ値がで設定したすべての条件を満たしている場合、キーワードは必要ない場合があります。 CASE 式、それは単一の条件の下で適切に分類できるように、任意の残差データに役立ちます。

この次のクエリでは、同じように記述します 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のアルバムです。したがって、この次のクエリでは、新しいクエリを作成します CASE からの数値データに基づいてスコアを設定する式 copies_sold これまでに販売されたアルバムの。

を使用します CASE 次の例のように、少なくとも3,500万枚のアルバムを「最高」、2,500万枚を「素晴らしい」、2,000万枚を「良い」、それよりも少ないものを「平凡」として販売するアルバムの条件を設定する式。

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関数と連結式に関するガイドを確認してください。

getdocs

13036