SQLでGROUP BYとORDER BYを使用する方法

チュートリアル
SQL

紹介

構造化クエリ言語（SQL）データベースは、多くのテーブルを通じて多くのデータを格納および管理できます。大規模なデータセットでは、データを整理する方法を理解することが重要です。特に、結果セットの分析やレポートや外部通信のデータを整理する場合に。

データを整理するのに役立つSQLの2つの一般的な文は、GROUP BYとORDER BYです。 GROUP BY 文は、クエリで指定した列に基づいてデータをグループ化して並べ替えるものであり、集計関数とともに使用されます。 ORDER BY は、結果セットをアルファベット順または数字順に昇順または降順に並べ替えることができます。

このチュートリアルでは、GROUP BY と ORDER BY 文を使用して SQL でクエリの結果を並べ替えます。また、クエリで集計関数と WHERE 句を実装して、さらに結果を並べ替える練習をします。

前提条件

このガイドに従うには、SQLを使用するある種の関係データベース管理システム（RDBMS）を実行しているコンピュータが必要です。このチュートリアルの手順と例は、次の環境を使用して検証されました：

  • A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
  • サーバーにインストールされてセキュアになっているMySQL。これを設定するには、当社のUbuntu 20.04にMySQLをインストールする方法ガイドに従ってください。このガイドでは、このガイドのステップ3で非ルートのMySQLユーザーも設定されていることを前提としています。

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

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

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

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

  1. ssh sammy@your_server_ip

次に、MySQL プロンプトを開き、sammy をあなたの MySQL ユーザーアカウント情報で置き換えます：

  1. mysql -u sammy -p

データベース名を movieDB として作成します：

  1. CREATE DATABASE movieDB;

データベースが正常に作成された場合、次の出力が表示されます：

Output
Query OK, 1 row affected (0.01 sec)

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

  1. USE movieDB;



Output
Database changed

データベースを選択した後、その内部にテーブルを作成します。このチュートリアルの例では、ローカル映画館の上映情報を格納するテーブルを作成します。このテーブルには以下の７つの列が含まれます：

  • theater_id: 各映画館の上映室の値を int データ型で格納し、テーブルの 主キー として機能します。つまり、この列の各値はそれぞれの行の一意の識別子として機能します。
  • date: 映画の上映日を年、月、日で特定するために DATE データ型を使用します。このデータ型は、年には４桁、月と日には最大２桁のパラメータに従います（YYYY-MM-DD）。
  • time: 映画の予定された上映時間を時、分、秒で表します。これには TIME データ型を使用し、（HH:MM:SS）となります。
  • movie_name: 最大40文字の varchar データ型を使用して、映画の名前を格納します。
  • movie_genre：各映画のジャンル情報を保持するために、最大30文字のvarcharデータ型を使用します。
  • guest_total：映画上映に参加したゲストの総数を示すintデータ型です。
  • ticket_cost：この列には、decimalデータ型が使用され、精度が4でスケールが1です。つまり、この列の値には4桁の数字と小数点以下の2桁が含まれます。この列は、特定の映画上映のチケット料金を表します。

次のCREATE TABLEコマンドを実行して、movie_theaterという名前のテーブルを作成します。

  1. CREATE TABLE movie_theater (

  2. theater_id int, 

  3. date DATE,

  4. time TIME, 

  5. movie_name varchar(40),

  6. movie_genre varchar(30),

  7. guest_total int,

  8. ticket_cost decimal(4,2),

  9. PRIMARY KEY (theater_id)

  10. );

次に、空のテーブルにサンプルデータを挿入します：

  1. INSERT INTO movie_theater

  2. (theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)

  3. VALUES

  4. (1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),

  5. (2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),

  6. (3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),

  7. (4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),

  8. (5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),

  9. (6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),

  10. (7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),

  11. (8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),

  12. (9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),

  13. (10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),

  14. (11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),

  15. (12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);



Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

データを挿入したら、SQLでクエリ結果をソートする準備が整います。

GROUP BYを使用する

GROUP BYステートメントの機能は、共有された値を持つレコードをグループ化することです。 GROUP BYステートメントは、クエリで常に集計関数と共に使用されます。集計関数は情報を要約して単一の結果を返します。たとえば、列の総数や合計をクエリで取得でき、これにより結果は単一の値になります。 GROUP BY句を使用すると、集計関数を実装して、望むグループごとに1つの結果値を取得できます。

GROUP BYは、1つの列だけでなく、指定したグループによってソートされた複数の希望する結果を返すのに役立ちます。さらに、GROUP BYは常にFROM文と（選択する場合は）WHERE句の後に配置する必要があります。以下は、GROUP BYと集計関数を使用したクエリの構造の例です:

GROUP BY syntax
SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

GROUP BYステートメントの使用方法を説明するために、複数の映画リリースのキャンペーンを率いており、マーケティングの成功を評価したいとします。地元の劇場に、金曜日と土曜日にゲストから収集したデータを共有するように依頼します。まず、movie_theaterテーブルから「すべての列」を選択するためにSELECTと*シンボルを実行してデータを確認します:

  1. SELECT * FROM movie_theater;



Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

このデータは役立ちますが、特定の列に結果を並べ替えて深く評価したいと考えています。

いくつかの異なるジャンルの映画に取り組んだので、映画観客がそれらをどのように受け入れたかが気になります。具体的には、各映画ジャンルごとの平均視聴者数を知りたいと考えています。movie_genre列からさまざまな種類の映画を取得するためにSELECTを使用し、guest_total列にAVG集計関数を適用し、averageという名前の列のエイリアスを作成し、movie_genreで結果をグループ化するためにGROUP BYステートメントを含めます。このようにグループ化することで、各映画ジャンルの平均結果が得られます:

  1. SELECT movie_genre, AVG(guest_total) AS average

  2. FROM movie_theater 

  3. GROUP BY movie_genre;



Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

この出力は、movie_genre グループ内の各ジャンルについての4つの平均を提供します。この情報に基づくと、Action ジャンルの映画が1回の上映あたり最も多くのゲストを引き付けました。

次に、劇場の収益を2日間にわたって測定したいとします。次のクエリは、date 列からの値と、SUM 集約関数によって返される値を返します。具体的には、集約関数 SUM は、数式を括弧で囲んで、合計ゲスト数にチケット代を掛ける（* 演算子を使用）ことで表されます: SUM(guest_total * ticket_cost)。このクエリは、集約関数によって返される列に対して total_revenue のエイリアスを提供するために AS 句を含んでいます。そして、GROUP BY 文を使用してクエリ結果を date 列でグループ化します:

  1. SELECT date, SUM(guest_total * ticket_cost) 

  2. AS total_revenue 

  3. FROM movie_theater 

  4. GROUP BY date;



Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

date 列をグループ化するために GROUP BY を使用したので、この出力は各日のチケット売上の合計収益を提供します。この場合、金曜日の5月27日の売上は7,272ドルで、土曜日の5月28日の売上は9,646ドルです。

今、1本の映画に焦点を当てて分析したいと想像してください：The Bad Guys。このシナリオでは、家族がアニメ映画を観る選択にどのようにタイミングと価格が影響するかを把握したいとします。このクエリでは、ticket_costの最大値を取得するために集約関数MAXを使用し、price_data列のエイリアスを作成するためにASを含めることを確認してください。その後、movie_nameを”The Bad Guys”に絞り込むためにWHERE句を使用し、guest_total数が100を超える映画の時間を決定するために>比較演算子を使用します。その後、GROUP BYステートメントを使用して、timeでグループ化します。

  1. SELECT time, MAX(ticket_cost) AS price_data 

  2. FROM movie_theater

  3. WHERE movie_name = "The Bad Guys" 

  4. AND guest_total > 100

  5. GROUP BY time;



Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

この出力によると、The Bad Guysの映画は、午前9:00の早いマチネの時間により多くのゲストが参加し、チケット1枚あたり$8.00というより手頃な価格設定でした。ただし、これらの結果はまた、5:00 pmに$13.00の高いチケット価格を支払ったことを示しており、昼間が遅すぎず、チケット代を少し多く支払うことを家族が好むことを示しています。これは、The Bad Guysの映画が83人しかいなかった午後10:00の時間と比較したときに公正な評価のようです。チケット1枚当たりの価格が$18.00でした。これは、希望する時間と価格ポイントに基づいて選択を行っている家族の出席を増やすために、マチネと早い夜の時間枠をもっと開けることができることを映画館のマネージャーに証拠として提供するのに役立つ情報です。

GROUP BYはほぼ常に集計関数と一緒に使用されますが、稀に例外があります。ただし、集計関数なしで結果をグループ化したい場合は、DISTINCTステートメントを使用して同じ結果を得ることができます。DISTINCT句は、結果セット内の重複を削除し、列内の一意の値を返すことで、SELECTステートメントとのみ使用できます。たとえば、すべての映画を名前でグループ化したい場合、次のクエリを使用できます:

  1. SELECT DISTINCT movie_name FROM movie_theater;



Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

テーブル内のすべてのデータを表示したときに、複数の上映があったため、映画の名前に重複があったことを思い出してください。したがって、DISTINCTはこれらの重複を削除し、ユニークな値を単一の列movie_nameの下に効果的にグループ化しました。これは、GROUP BYステートメントを含む次のクエリと実質的に同一です:

  1. SELECT movie_name FROM movie_theater GROUP BY movie_name;

集計関数を使用したGROUP BYの使用を練習したので、次にクエリ結果をORDER BYステートメントでソートする方法を学びます。

ORDER BYを使用

ORDER BYステートメントの機能は、クエリで指定した列に基づいて結果を昇順または降順に並べ替えることです。指定した列が保存しているデータ型に応じて、ORDER BYはアルファベット順または数値順に並べ替えます。デフォルトでは、ORDER BYは結果を昇順でソートします。しかし、降順でソートしたい場合は、クエリにキーワードDESCを含める必要があります。また、GROUP BYとORDER BYステートメントを併用することもできますが、ORDER BYは正しく機能するために後に配置する必要があります。 GROUP BYと同様に、ORDER BYもFROMステートメントとWHERE句の後に配置する必要があります。 ORDER BYを使用する一般的な構文は次の通りです：

ORDER BY syntax
SELECT column_1, column_2 FROM table ORDER BY column_1;

映画館のサンプルデータを使用して、ORDER BYを使った結果のソートを練習しましょう。まず、次のクエリでguest_total列から値を取得し、ORDER BYステートメントで数値の値を並べ替えます：

  1. SELECT guest_total FROM movie_theater 

  2. ORDER BY guest_total;



Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

クエリが数値の値を持つ列を指定したため、ORDER BYステートメントはguest_total列の下に25から始まる数値の昇順で結果を整理しました。

DESCキーワードをクエリの末尾に追加することで、カラムを降順に並べることができます。さらに、movie_nameの下の文字値でデータを並べ替えたい場合は、クエリで指定します。それを使用して、ORDER BYを使用して、文字値でmovie_nameカラムを降順に並べ替えるタイプのクエリを実行しましょう。結果をさらに並べ替えるために、timeカラムから午後10時に上映される映画のデータを取得するWHERE句を含めてください：

  1. SELECT movie_name FROM movie_theater

  2. WHERE time = '10:00:00' 

  3. ORDER BY movie_name DESC;



Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

この結果セットは、アルファベット順に降順で、トップガンマーヴェリックからダウンタウンアビー：新たなる時代まで、午後10時に4つの異なる映画が表示されています。

次のクエリでは、ORDER BYとGROUP BYステートメントを組み合わせ、各映画の総収益を生成するために集計関数SUMを使用します。ただし、映画館は総客数を誤って計算し、各上映に12人のグループの事前購入および予約チケットを含めるのを忘れたとします。

このクエリでは、SUMを使用して、各映画の上映ごとに追加の12人のゲストを含めるために、加算演算子+を実装し、guest_totalに12を加える必要があります。これをカッコで囲んでください。次に、この合計をticket_costで乗算します。演算子*を使用して、数式を完成させ、最後に新しい列total_revenueのエイリアスを作成するためにAS句を追加します。そして、GROUP BYを使用して、movie_name列から取得したデータに基づいて各映画のtotal_revenue結果をグループ化します。最後に、ORDER BYを使用して、新しい列total_revenueの結果を昇順で整理します：

  1. SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 

  2. AS total_revenue

  3. FROM movie_theater 

  4. GROUP BY movie_name 

  5. ORDER BY total_revenue;



Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

この結果セットには、追加の12人のチケット販売を含めた各映画の総収益が表示され、総チケット販売が最も少ないものから最も多いものへの昇順で整理されます。これにより、『Top Gun Maverick』が最も多くのチケット販売を受けたことがわかりますが、『Men』は最も少なかったことがわかります。一方、『The Bad Guys』と『Downton Abbey A New Era』の映画は、総チケット販売で非常に近い結果でした。

このセクションでは、ORDER BYステートメントを実装するさまざまな方法と、好みの順序を指定する方法、つまり、昇順と降順の両方を文字と数値のデータ値に対して学習しました。また、結果を絞り込むためにWHERE句を含める方法を学び、集約関数と数式を使用したGROUP BYおよびORDER BYステートメントを使用したクエリを実行しました。

結論

GROUP BY 文と ORDER BY 文の使い方を理解することは、結果とデータを整理するために重要です。複数の結果を1つのグループの下に整理したいか、列の1つをアルファベット順または降順に並べ替えたいか、あるいは両方を同時に行いたいかは、あなたとあなたの望む結果次第です。また、WHERE 句を使用して結果をさらに詳細に並べ替える他の方法についても学びました。さらに学びたい場合は、SQLでワイルドカードを使用する方法 のチュートリアルをチェックして、LIKE 句を使用して結果をフィルタリングする方法を練習してください。

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-groupby-and-orderby-in-sql