SQLite Datengruppierung

Einführung

In diesem Lab lernen Sie, wie Sie Daten in SQLite mithilfe von Aggregatfunktionen und Gruppierungs-Klauseln zusammenfassen und analysieren. Sie werden COUNT und SUM für Berechnungen untersuchen, Daten nach einzelnen Spalten gruppieren, Gruppen mit HAVING filtern und gruppierte Ausgaben sortieren. Diese praktische Erfahrung vermittelt Ihnen grundlegende Fähigkeiten zur Datenmanipulation in SQLite.

Skills Graph

Erstellen der Tabelle "Orders" und Einfügen von Daten

In diesem Schritt erstellen Sie eine Datenbank namens sales.db und eine Tabelle orders darin. Anschließend fügen Sie Beispieldaten in die Tabelle ein. Diese Tabelle wird im gesamten Lab verwendet, um Datengruppierungstechniken zu üben.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM. Ihr Standardpfad ist /home/labex/project.

Erstellen Sie zunächst die Datenbank sales.db und verbinden Sie sich mit ihr, indem Sie den folgenden Befehl verwenden:

sqlite3 sales.db

Dieser Befehl öffnet die SQLite-Shell, und Sie sehen eine Eingabeaufforderung wie sqlite>.

Erstellen Sie als Nächstes die Tabelle orders mit Spalten für order_id (Bestellungs-ID), customer_id (Kunden-ID), product_name (Produktname), quantity (Menge) und price (Preis). Führen Sie den folgenden SQL-Befehl aus:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

Dieser Befehl erstellt die Tabelle orders mit den angegebenen Spalten und Datentypen. Die Spalte order_id wird als Primärschlüssel (primary key) festgelegt.

Fügen Sie nun Beispieldaten in die Tabelle orders ein. Führen Sie die folgenden INSERT-Anweisungen einzeln aus:

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

Diese Befehle fügen sieben Datenzeilen in die Tabelle orders ein, die verschiedene Kundenbestellungen darstellen.

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie eine einfache SELECT-Abfrage (query) ausführen:

SELECT * FROM orders;

Dieser Befehl zeigt alle Zeilen und Spalten in der Tabelle orders an.

Aggregieren mit COUNT und SUM

In diesem Schritt verwenden Sie die Aggregatfunktionen COUNT und SUM, um Berechnungen mit den Daten in der Tabelle orders durchzuführen. Aggregatfunktionen ermöglichen es Ihnen, Daten aus mehreren Zeilen in einem einzigen Ergebnis zusammenzufassen.

Sie sollten noch mit der Datenbank sales.db aus dem vorherigen Schritt verbunden sein. Wenn nicht, stellen Sie die Verbindung mit folgendem Befehl wieder her:

sqlite3 sales.db

Verwenden wir zunächst die Funktion COUNT, um die Gesamtzahl der Bestellungen in der Tabelle zu ermitteln. Führen Sie den folgenden SQL-Befehl aus:

SELECT COUNT(*) FROM orders;

Diese Abfrage (query) gibt die Gesamtzahl der Zeilen in der Tabelle orders zurück, die die Gesamtzahl der Bestellungen darstellt.

Die Funktion COUNT(*) zählt alle Zeilen in der Tabelle, unabhängig davon, ob Spalten NULL-Werte enthalten.

Als Nächstes verwenden wir die Funktion SUM, um die Gesamtmenge aller bestellten Produkte zu berechnen. Führen Sie den folgenden SQL-Befehl aus:

SELECT SUM(quantity) FROM orders;

Diese Abfrage gibt die Summe der Spalte quantity für alle Zeilen in der Tabelle orders zurück.

Die Funktion SUM addiert die Werte in der angegebenen Spalte.

Berechnen wir abschließend den Gesamtumsatz, der mit allen Bestellungen erzielt wurde. Führen Sie den folgenden SQL-Befehl aus:

SELECT SUM(quantity * price) FROM orders;

Diese Abfrage multipliziert die Spalten quantity und price für jede Zeile und summiert dann die Ergebnisse, wodurch Sie den Gesamtumsatz erhalten.

Gruppieren nach einzelnen Spalten

In diesem Schritt lernen Sie, wie Sie die GROUP BY-Klausel verwenden, um Zeilen basierend auf den Werten in einer oder mehreren Spalten zu gruppieren. Dies wird häufig in Verbindung mit Aggregatfunktionen verwendet, um zusammenfassende Statistiken für jede Gruppe zu berechnen.

Sie sollten noch mit der Datenbank sales.db aus dem vorherigen Schritt verbunden sein. Wenn nicht, stellen Sie die Verbindung mit folgendem Befehl wieder her:

sqlite3 sales.db

Gruppieren wir die Tabelle orders nach customer_id und zählen die Anzahl der Bestellungen für jeden Kunden. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

Diese Abfrage (query) gibt die customer_id (Kunden-ID) und die Anzahl der Bestellungen (order_count) für jeden eindeutigen Kunden zurück. Die GROUP BY customer_id-Klausel weist SQLite an, die Zeilen basierend auf den Werten in der Spalte customer_id zu gruppieren. Die Funktion COUNT(*) zählt dann die Anzahl der Zeilen in jeder Gruppe.

Als Nächstes gruppieren wir die Tabelle orders nach product_name und berechnen die für jedes Produkt bestellte Gesamtmenge. Führen Sie den folgenden SQL-Befehl aus:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

Diese Abfrage gibt den product_name (Produktnamen) und die bestellte Gesamtmenge (total_quantity) für jedes eindeutige Produkt zurück. Die GROUP BY product_name-Klausel weist SQLite an, die Zeilen basierend auf den Werten in der Spalte product_name zu gruppieren. Die Funktion SUM(quantity) berechnet dann die Summe der Spalte quantity für jede Gruppe.

Gruppieren wir abschließend die Tabelle orders nach customer_id und berechnen den von jedem Kunden generierten Gesamtumsatz. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

Diese Abfrage gibt die customer_id (Kunden-ID) und den von jedem Kunden generierten Gesamtumsatz (total_revenue) zurück.

Anwenden von HAVING auf Gruppen

In diesem Schritt lernen Sie, wie Sie die HAVING-Klausel verwenden, um Gruppen zu filtern, nachdem sie durch die GROUP BY-Klausel erstellt wurden. Die HAVING-Klausel ähnelt der WHERE-Klausel, arbeitet jedoch mit Gruppen anstelle von einzelnen Zeilen.

Sie sollten noch mit der Datenbank sales.db aus dem vorherigen Schritt verbunden sein. Wenn nicht, stellen Sie die Verbindung mit folgendem Befehl wieder her:

sqlite3 sales.db

Gruppieren wir die Tabelle orders nach customer_id und zählen die Anzahl der Bestellungen für jeden Kunden. Anschließend verwenden wir die HAVING-Klausel, um die Ergebnisse so zu filtern, dass nur Kunden berücksichtigt werden, die mehr als eine Bestellung aufgegeben haben. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

Diese Abfrage (query) gibt die customer_id (Kunden-ID) und die Anzahl der Bestellungen (order_count) für jeden Kunden zurück, der mehr als eine Bestellung aufgegeben hat. Die GROUP BY customer_id-Klausel gruppiert die Zeilen nach customer_id, und die HAVING COUNT(*) > 1-Klausel filtert die Gruppen so, dass nur diejenigen berücksichtigt werden, bei denen die Anzahl der Bestellungen größer als 1 ist.

Als Nächstes gruppieren wir die Tabelle orders nach product_name und berechnen die für jedes Produkt bestellte Gesamtmenge. Anschließend verwenden wir die HAVING-Klausel, um die Ergebnisse so zu filtern, dass nur Produkte berücksichtigt werden, bei denen die bestellte Gesamtmenge größer als 1 ist. Führen Sie den folgenden SQL-Befehl aus:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

Diese Abfrage gibt den product_name (Produktnamen) und die bestellte Gesamtmenge (total_quantity) für jedes Produkt zurück, bei dem die bestellte Gesamtmenge größer als 1 ist.

Gruppieren wir abschließend die Tabelle orders nach customer_id und berechnen den von jedem Kunden generierten Gesamtumsatz. Anschließend verwenden wir die HAVING-Klausel, um die Ergebnisse so zu filtern, dass nur Kunden berücksichtigt werden, die mehr als 1000 US-Dollar Umsatz generiert haben. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

Diese Abfrage gibt die customer_id (Kunden-ID) und den von jedem Kunden generierten Gesamtumsatz (total_revenue) zurück, der mehr als 1000 US-Dollar Umsatz generiert hat.

Gruppierte Ausgabe sortieren

In diesem Schritt lernen Sie, wie Sie die ORDER BY-Klausel verwenden, um die Ausgabe einer Abfrage (query) zu sortieren, die die GROUP BY-Klausel enthält. Das Sortieren der gruppierten Ausgabe kann die Analyse und das Verständnis der Daten erleichtern.

Sie sollten noch mit der Datenbank sales.db aus dem vorherigen Schritt verbunden sein. Wenn nicht, stellen Sie die Verbindung mit folgendem Befehl wieder her:

sqlite3 sales.db

Gruppieren wir die Tabelle orders nach customer_id und zählen die Anzahl der Bestellungen für jeden Kunden. Anschließend verwenden wir die ORDER BY-Klausel, um die Ergebnisse in absteigender Reihenfolge basierend auf der Anzahl der Bestellungen zu sortieren. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;

Diese Abfrage gibt die customer_id (Kunden-ID) und die Anzahl der Bestellungen (order_count) für jeden Kunden zurück, sortiert in absteigender Reihenfolge basierend auf der order_count. Die GROUP BY customer_id-Klausel gruppiert die Zeilen nach customer_id, und die ORDER BY order_count DESC-Klausel sortiert die Ergebnisse in absteigender Reihenfolge basierend auf dem Alias order_count.

Als Nächstes gruppieren wir die Tabelle orders nach product_name und berechnen die für jedes Produkt bestellte Gesamtmenge. Anschließend verwenden wir die ORDER BY-Klausel, um die Ergebnisse in aufsteigender Reihenfolge basierend auf dem Produktnamen zu sortieren. Führen Sie den folgenden SQL-Befehl aus:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

Diese Abfrage gibt den product_name (Produktnamen) und die bestellte Gesamtmenge (total_quantity) für jedes Produkt zurück, sortiert in aufsteigender Reihenfolge basierend auf dem product_name.

Gruppieren wir abschließend die Tabelle orders nach customer_id und berechnen den von jedem Kunden generierten Gesamtumsatz. Anschließend verwenden wir die ORDER BY-Klausel, um die Ergebnisse in absteigender Reihenfolge basierend auf dem Gesamtumsatz zu sortieren. Führen Sie den folgenden SQL-Befehl aus:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

Diese Abfrage gibt die customer_id (Kunden-ID) und den von jedem Kunden generierten Gesamtumsatz (total_revenue) zurück, sortiert in absteigender Reihenfolge basierend auf dem total_revenue.

Um die SQLite-Shell zu verlassen, führen Sie Folgendes aus:

.exit

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Aggregatfunktionen (aggregate functions) wie COUNT und SUM verwenden, um Daten in SQLite zusammenzufassen. Sie haben eine sales.db-Datenbank mit einer orders-Tabelle erstellt und Beispieldaten eingefügt. Anschließend haben Sie COUNT(*) verwendet, um die Gesamtzahl der Bestellungen zu ermitteln, und SUM(quantity * price), um den Gesamtumsatz zu berechnen. Sie haben auch gelernt, wie Sie Daten mithilfe der GROUP BY-Klausel gruppieren, Gruppen mithilfe der HAVING-Klausel filtern und die Ausgabe mithilfe der ORDER BY-Klausel sortieren. Diese Fähigkeiten bilden eine solide Grundlage für die Datenanalyse in SQLite.