Wie man GROUP BY und ORDER BY in SQL verwendet

Einführung

SQL-Datenbanken (Structured Query Language) können große Datenmengen über zahlreiche Tabellen speichern und verwalten. Bei großen Datensätzen ist es wichtig zu verstehen, wie man Daten sortiert, insbesondere um Ergebnismengen zu analysieren oder Daten für Berichte oder externe Kommunikation zu organisieren.

Zwei häufig verwendete Befehle in SQL, die beim Sortieren von Daten helfen, sind GROUP BY und ORDER BY. Eine GROUP BY-Anweisung gruppiert Daten basierend auf Spalten, die Sie in der Abfrage angeben, und wird mit Aggregatfunktionen verwendet. Eine ORDER BY-Anweisung ermöglicht es Ihnen, Ergebnismengen alphabetisch oder numerisch sowie in aufsteigender oder absteigender Reihenfolge zu organisieren.

In diesem Tutorial sortieren Sie Abfrageergebnisse in SQL mit den Anweisungen GROUP BY und ORDER BY. Sie werden auch üben, Aggregatfunktionen und die WHERE-Klausel in Ihren Abfragen zu verwenden, um die Ergebnisse noch weiter zu sortieren.

Voraussetzungen für GROUP BY und ORDER BY in SQL

Um dieser Anleitung zu folgen, benötigen Sie einen Computer mit einem relationalen Datenbankmanagementsystem (RDBMS), das SQL verwendet. Die Anweisungen und Beispiele in diesem Tutorial wurden in der folgenden Umgebung validiert:

• Ein Server mit Ubuntu 20.04, einem nicht-root Benutzer mit sudo-Administratorrechten und aktivierter Firewall. Folgen Sie unserer Anleitung zur ersten Servereinrichtung mit Ubuntu 20.04, um zu beginnen.
• MySQL auf dem Server installiert und gesichert. Folgen Sie unserer Anleitung How To Install MySQL on Ubuntu 20.04, um dies einzurichten. Diese Anleitung geht davon aus, dass Sie auch einen nicht-root MySQL-Benutzer eingerichtet haben, wie in Schritt 3 dieser Anleitung beschrieben.

Hinweis: Bitte beachten Sie, dass viele relationale Datenbankmanagementsysteme ihre eigenen einzigartigen Implementierungen von SQL verwenden. Obwohl die in diesem Tutorial beschriebenen Befehle auf den meisten RDBMSs funktionieren, kann die genaue Syntax oder Ausgabe abweichen, wenn Sie sie auf einem anderen System als MySQL testen.

Um das Sortieren von Datenergebnissen in diesem Tutorial zu üben, benötigen Sie eine Datenbank und eine Tabelle mit Beispieldaten. Falls Sie keine solche Datenbank haben, können Sie den Abschnitt „Verbindung zu MySQL und Einrichtung einer Beispieldatenbank“ lesen, um zu erfahren, wie Sie eine Datenbank und eine Tabelle erstellen. Diese Anleitung verweist auf diese Beispieldatenbank und Tabelle im gesamten Tutorial.

Verbindung zu MySQL und Einrichtung einer Beispieldatenbank

Wenn Ihr SQL-Datenbanksystem auf einem Remote-Server läuft, verbinden Sie sich per SSH mit Ihrem Server von Ihrer lokalen Maschine aus:

ssh sammy@your_server_ip

Öffnen Sie als Nächstes die MySQL-Eingabeaufforderung und ersetzen Sie sammy durch die Informationen zu Ihrem MySQL-Benutzerkonto:

mysql -u sammy -p

Erstellen Sie eine Datenbank mit dem Namen movieDB:

CREATE DATABASE movieDB;

Wenn die Datenbank erfolgreich erstellt wurde, erhalten Sie die folgende Ausgabe:

Query OK, 1 row affected (0.01 sec)

Um die Datenbank movieDB auszuwählen, führen Sie die folgende Anweisung USE aus:

USE movieDB;

Ausgabe:

Database changed

Nachdem Sie die Datenbank ausgewählt haben, erstellen Sie eine Tabelle darin. In diesem Tutorial erstellen wir eine Tabelle, die Informationen über die Vorführungen eines lokalen Kinos speichert. Diese Tabelle enthält die folgenden sieben Spalten:

• theater_id: Speichert Werte des int-Datentyps für die Vorführräume jedes Kinos und dient als Primärschlüssel der Tabelle, was bedeutet, dass jeder Wert in dieser Spalte als eindeutiger Bezeichner für die jeweilige Zeile fungiert.
• date: Verwendet den DATE-Datentyp, um das genaue Datum zu speichern, an dem ein Film gezeigt wurde, im Format Jahr, Monat und Tag (YYYY-MM-DD).
• time: Stellt die geplante Vorführzeit des Films mit dem TIME-Datentyp in Stunden, Minuten und Sekunden (HH:MM:SS) dar.
• movie_name: Speichert den Namen des Films mit dem varchar-Datentyp mit einer maximalen Länge von 40 Zeichen.
• movie_genre: Verwendet den varchar-Datentyp mit einer maximalen Länge von 30 Zeichen, um Informationen über das Genre jedes Films zu speichern.
• guest_total: Zeigt die Gesamtzahl der Gäste an, die eine Filmvorführung besucht haben, und verwendet den int-Datentyp.
• ticket_cost: Verwendet den decimal-Datentyp mit einer Präzision von vier und einer Skala von zwei, was bedeutet, dass die Werte in dieser Spalte bis zu vier Ziffern und zwei Dezimalstellen haben können. Diese Spalte gibt die Ticketkosten für die jeweilige Filmvorführung an.

Erstellen Sie eine Tabelle namens movie_theater, die jede dieser Spalten enthält, indem Sie den folgenden Befehl CREATE TABLE ausführen:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
);

Fügen Sie als Nächstes einige Beispieldaten in die leere Tabelle ein:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(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);

Ausgabe:

Query OK, 12 rows affected (0.00 sec)

Nachdem Sie die Daten eingefügt haben, können Sie mit dem Sortieren der Abfrageergebnisse in SQL beginnen.

Verwendung von GROUP BY

Die Funktion einer GROUP BY-Anweisung besteht darin, Datensätze mit gemeinsamen Werten zu gruppieren. Eine GROUP BY-Anweisung wird immer mit einer Aggregatfunktion in einer Abfrage verwendet. Wie Sie sich erinnern, fasst eine Aggregatfunktion Informationen zusammen und gibt ein einzelnes Ergebnis zurück. Zum Beispiel können Sie nach der Gesamtzahl oder der Summe einer Spalte abfragen, und dies wird einen einzigen Wert in Ihrem Ergebnis zurückgeben. Mit einer GROUP BY-Klausel können Sie die Aggregatfunktion verwenden, um ein Ergebnis pro gewünschter Gruppe zu erhalten.

GROUP BY ist nützlich, um mehrere gewünschte Ergebnisse zu erhalten, die nach den angegebenen Gruppen sortiert sind, anstatt nur eine einzelne Spalte. Zudem muss GROUP BY immer nach der FROM-Anweisung und der WHERE-Klausel kommen, wenn Sie eine verwenden. Hier ist ein Beispiel, wie eine Abfrage mit GROUP BY und einer Aggregatfunktion strukturiert ist:

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

Um zu veranschaulichen, wie man GROUP BY-Anweisungen verwenden kann, stellen Sie sich vor, Sie führen die Kampagne für mehrere Filmstarts und möchten den Erfolg Ihrer Marketingmaßnahmen bewerten. Sie bitten ein lokales Kino, die Daten zu den Gästen zu teilen, die an Freitag und Samstag die Filme besucht haben. Beginnen Sie, indem Sie mit SELECT und dem *-Symbol alle Spalten aus der movie_theater-Tabelle auswählen:

SELECT * FROM movie_theater;

Ausgabe:

+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| 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)

Während diese Daten hilfreich sind, möchten Sie eine tiefere Bewertung vornehmen und die Ergebnisse für einige spezifische Spalten sortieren.

Da Sie mit Filmen aus verschiedenen Genres gearbeitet haben, möchten Sie wissen, wie gut die Filme bei den Kino-Besuchern angekommen sind. Insbesondere möchten Sie den durchschnittlichen Zuschauerkreis für jedes Filmgenre erfahren. Verwenden Sie SELECT, um die verschiedenen Filmtypen aus der Spalte movie_genre abzurufen. Wenden Sie dann die Aggregatfunktion AVG auf die Spalte guest_total an, verwenden Sie AS, um ein Alias für eine Spalte namens average zu erstellen, und fügen Sie die GROUP BY-Anweisung hinzu, um die Ergebnisse nach movie_genre zu gruppieren. Auf diese Weise erhalten Sie den Durchschnitt für jedes Filmgenre:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Ausgabe

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

Diese Ausgabe zeigt die vier Durchschnittswerte für jedes Genre innerhalb der Gruppe movie_genre. Basierend auf diesen Informationen lockten Action-Filme die höchste durchschnittliche Anzahl von Gästen pro Vorführung an.

Nun nehmen wir an, Sie möchten die Einnahmen des Kinos an zwei verschiedenen Tagen messen. Die folgende Abfrage gibt Werte aus der Spalte date sowie Werte zurück, die von der Aggregatfunktion SUM ermittelt wurden. Die Aggregatfunktion SUM wird eine mathematische Gleichung in Klammern einschließen, um (mit dem Operator *) die Gesamtzahl der Gäste mit den Ticketkosten zu multiplizieren, dargestellt als: SUM(guest_total * ticket_cost). Diese Abfrage enthält die AS-Klausel, um der Spalte, die von der Aggregatfunktion zurückgegeben wird, ein Alias namens total_revenue zuzuweisen. Anschließend schließen Sie die Abfrage mit der GROUP BY-Anweisung ab, um die Abfrageergebnisse nach der Spalte date zu gruppieren:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Ausgabe

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

Da Sie GROUP BY verwendet haben, um die Spalte date zu gruppieren, gibt Ihre Ausgabe die Ergebnisse für die gesamten Ticketverkäufe an jedem Tag zurück: $7.272 für Freitag, den 27. Mai und $9.646 für Samstag, den 28. Mai.

Stellen Sie sich nun vor, Sie möchten sich auf und die Analyse eines Films konzentrieren: The Bad Guys. In diesem Szenario möchten Sie herausfinden, wie Timing und Preisgestaltung die Wahl einer Familie beeinflussen, einen Animationsfilm zu sehen. Verwenden Sie für diese Abfrage die Aggregatfunktion MAX, um die maximalen Ticketkosten abzurufen und stellen Sie sicher, dass Sie AS verwenden, um ein Alias für die Spalte price_data zu erstellen. Verwenden Sie dann die WHERE-Klausel, um die Ergebnisse auf den Film The Bad Guys zu beschränken und verwenden Sie AND, um auch die beliebtesten Vorführzeiten basierend auf einer Gästezahl von mehr als 100 mit dem Vergleichsoperator > zu bestimmen. Fügen Sie schließlich die GROUP BY-Anweisung hinzu und gruppieren Sie nach time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Ausgabe

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

Diese Ausgabe zeigt, dass mehr Gäste die Filmvorführung von The Bad Guys zu der frühen Matinee-Zeit um 9:00 Uhr besuchten, bei der der Ticketpreis von $8.00 günstiger war. Diese Ergebnisse zeigen jedoch auch, dass Kinobesucher den höheren Ticketpreis von $13.00 um 17:00 Uhr zahlten, was darauf hindeutet, dass Familien Vorführungen bevorzugen, die nicht zu spät am Tag sind und bereit sind, für ein Ticket etwas mehr zu zahlen. Dies scheint eine faire Einschätzung zu sein, wenn man es mit der 22:00 Uhr Zeit vergleicht, bei der der Film nur 83 Gäste hatte und der Ticketpreis bei $18.00 lag. Diese Informationen können dem Kino-Manager helfen, dass mehr Matinee- und frühere Abendvorführungen die Teilnahme von Familien erhöhen können, die auf bevorzugte Zeiten und Preisgestaltungen angewiesen sind.

Bitte beachten Sie, dass GROUP BY zwar fast immer mit einer Aggregatfunktion verwendet wird, es aber Ausnahmen geben kann, auch wenn diese selten sind. Wenn Sie jedoch Ihre Ergebnisse ohne eine Aggregatfunktion gruppieren möchten, können Sie die DISTINCT-Anweisung verwenden, um dasselbe Ergebnis zu erzielen. Eine DISTINCT-Klausel entfernt Duplikate in einer Ergebnismenge, indem nur die einzigartigen Werte in der Spalte zurückgegeben werden, und kann nur mit einer SELECT-Anweisung verwendet werden. Wenn Sie zum Beispiel alle Filme nach Name gruppieren möchten, können Sie dies mit der folgenden Abfrage tun:

SELECT DISTINCT movie_name FROM movie_theater;

Ausgabe

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

Wie Sie sich aus der Ansicht aller Daten in der Tabelle erinnern werden, gab es Duplikate der Filmtitel, da es mehrere Vorführungen gab. Daher entfernte DISTINCT diese Duplikate und gruppierte effektiv die einzigartigen Werte unter der einzigen Spalte movie_name. Dies ist nahezu identisch mit der folgenden Abfrage, die eine GROUP BY-Anweisung enthält:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Verwendung von ORDER BY

Die Funktion der ORDER BY-Anweisung besteht darin, Ergebnisse in aufsteigender oder absteigender Reihenfolge basierend auf den Spalten, die Sie in der Abfrage angeben, zu sortieren. Abhängig vom Datentyp, der von der Spalte gespeichert wird, die Sie angeben, wird ORDER BY die Ergebnisse alphabetisch oder numerisch organisieren. Standardmäßig wird ORDER BY die Ergebnisse in aufsteigender Reihenfolge sortieren; wenn Sie eine absteigende Reihenfolge bevorzugen, müssen Sie das Schlüsselwort DESC in Ihrer Abfrage angeben. Sie können auch die ORDER BY-Anweisung zusammen mit GROUP BY verwenden, aber sie muss danach kommen, damit sie ordnungsgemäß funktioniert. Ähnlich wie bei GROUP BY muss ORDER BY auch nach der FROM-Anweisung und der WHERE-Klausel kommen. Die allgemeine Syntax für die Verwendung von ORDER BY lautet:

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

Fahren wir mit den Beispieldaten für das Kino fort und üben, wie man Ergebnisse mit ORDER BY sortiert. Beginnen Sie mit der folgenden Abfrage, die Werte aus der Spalte guest_total abruft und diese numerischen Werte mit einer ORDER BY-Anweisung organisiert:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Ausgabe

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

Da Sie in Ihrer Abfrage eine Spalte mit numerischen Werten angegeben haben, hat ORDER BY die Ergebnisse numerisch in aufsteigender Reihenfolge organisiert, beginnend mit 25 in der Spalte guest_total.

Wenn Sie die Spalte in absteigender Reihenfolge ordnen möchten, fügen Sie das Schlüsselwort DESC am Ende der Abfrage hinzu. Wenn Sie die Daten außerdem nach den Zeichenwerten in der Spalte movie_name sortieren möchten, geben Sie dies in Ihrer Abfrage an. Lassen Sie uns diese Art der Abfrage durchführen, indem wir ORDER BY verwenden, um die Spalte movie_name mit Zeichenwerten in absteigender Reihenfolge zu ordnen. Sortieren Sie die Ergebnisse weiter, indem Sie eine WHERE-Klausel hinzufügen, um die Daten zu den Filmen abzurufen, die um 22:00 Uhr gezeigt wurden:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Ausgabe

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

Dieses Ergebnisset listet die vier verschiedenen Filmvorführungen um 22:00 Uhr in absteigender alphabetischer Reihenfolge, beginnend mit Top Gun Maverick bis hin zu Downton Abbey A New Era.

Für die nächste Abfrage kombinieren Sie die ORDER BY- und GROUP BY-Anweisungen mit der Aggregatfunktion SUM, um Ergebnisse zu den Gesamteinnahmen für jeden Film zu generieren. Angenommen, das Kino hat die Gesamtzahl der Gäste falsch gezählt und vergessen, spezielle Gruppen zu berücksichtigen, die für eine Gruppe von 12 Personen an jeder Vorführung Tickets im Voraus gekauft und reserviert haben.

Verwenden Sie in dieser Abfrage SUM und fügen Sie die zusätzlichen 12 Gäste bei jeder Filmvorführung hinzu, indem Sie den Operator für Addition (+) verwenden und dann 12 zu guest_total hinzufügen. Stellen Sie sicher, dass Sie dies in Klammern einschließen. Multiplizieren Sie dann diese Gesamtzahl mit ticket_cost unter Verwendung des Operators * und schließen Sie die mathematische Gleichung am Ende mit einer Klammer ab. Fügen Sie dann die AS-Klausel hinzu, um das Alias für die neue Spalte namens total_revenue zu erstellen. Verwenden Sie dann GROUP BY, um die Ergebnisse der total_revenue-Spalte nach dem movie_name zu gruppieren. Zum Schluss verwenden Sie ORDER BY, um die Ergebnisse in aufsteigender Reihenfolge zu organisieren:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Ausgabe

+-------------------------+---------------+
| 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)

Dieses Ergebnisset zeigt uns die Gesamteinnahmen für jeden Film mit den zusätzlichen 12 Gastticketverkäufen und organisiert die Gesamteinnahmen in aufsteigender Reihenfolge von niedrig bis hoch. Daraus erfahren wir, dass Top Gun Maverick die meisten Ticketverkäufe erhielt, während Men die wenigsten erzielte. Inzwischen lagen die Filme The Bad Guys und Downton Abbey A New Era mit ihren Gesamteinnahmen sehr nah beieinander.

In diesem Abschnitt haben Sie verschiedene Möglichkeiten geübt, wie man die ORDER BY-Anweisung anwendet. Sie haben auch gelernt, wie man die WHERE-Klausel einbezieht, um die Ergebnisse weiter einzugrenzen. Sie haben eine Abfrage mit GROUP BY, ORDER BY, einer Aggregatfunktion und einer mathematischen Gleichung ausgeführt.

Fazit

Das Verständnis, wie man GROUP BY und ORDER BY in SQL verwendet, ist wichtig, um Ergebnisse und Daten zu sortieren. Ob Sie nun mehrere Ergebnisse unter einer Gruppe organisieren möchten, eine Ihrer Spalten in alphabetischer und absteigender Reihenfolge anordnen möchten oder beides gleichzeitig tun wollen; es liegt an Ihnen und dem gewünschten Ergebnis. Sie haben auch gelernt, wie man die Ergebnisse noch weiter mit der WHERE-Klausel sortieren kann.