SQLite Fensterfunktionen-Analysen (Window Analytics)

Einführung

In diesem Lab werden Sie die Fensterfunktionen (Window Analytics) von SQLite erkunden, wobei der Schwerpunkt auf dem Rangordnen von Zeilen und der Berechnung von kumulierten Summen (Running Totals) liegt. Sie lernen, wie Sie Fensterfunktionen verwenden, um Berechnungen über Mengen von Zeilen durchzuführen, die sich auf die aktuelle Zeile beziehen.

Insbesondere verwenden Sie die Funktion ROW_NUMBER(), um jeder Zeile basierend auf dem Umsatzbetrag einen eindeutigen Rang zuzuweisen. Sie lernen auch, wie Sie kumulierte Summen berechnen und Daten für eine fortgeschrittenere Analyse partitionieren. Dieses Lab bietet eine praktische Einführung in Fensterfunktionen in SQLite.

Erstellen einer Sales-Datenbank und -Tabelle

In diesem ersten Schritt erstellen Sie eine SQLite-Datenbank namens sales.db und eine Tabelle namens sales, um Verkaufsdaten zu speichern. Diese Tabelle enthält Spalten für Produkt-ID, Produktname und Umsatzbetrag (Sales Amount).

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

Erstellen Sie zunächst die Datenbank sales.db und öffnen Sie das SQLite-Befehlszeilentool, indem Sie den folgenden Befehl ausführen:

sqlite3 sales.db

Dieser Befehl erstellt die Datenbankdatei und öffnet die SQLite-Shell, in der Sie SQL-Befehle ausführen können. Sie sehen eine Eingabeaufforderung wie diese:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Erstellen Sie als Nächstes die Tabelle sales mit den folgenden Spalten: product_id, product_name und sales_amount. Geben Sie den folgenden SQL-Befehl an der sqlite>-Eingabeaufforderung ein und drücken Sie die Eingabetaste:

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

Dieser Befehl richtet die Tabelle sales ein, wobei:

• product_id eine Ganzzahl ist, die die eindeutige Kennung für jedes Produkt darstellt.
• product_name ein Textfeld ist, das den Namen des Produkts speichert.
• sales_amount eine Ganzzahl ist, die den Umsatzbetrag für das Produkt darstellt.

Sie sehen keine Ausgabe, wenn der Befehl erfolgreich ausgeführt wird.

Beispieldaten in die Sales-Tabelle einfügen

Nachdem Sie die Tabelle sales erstellt haben, fügen wir einige Beispieldaten hinzu. Wir fügen sechs Datensätze ein, die verschiedene Produkte und ihre Umsatzbeträge darstellen.

Fügen Sie die folgenden Datensätze in die Tabelle sales ein, indem Sie diese Befehle einzeln an der sqlite>-Eingabeaufforderung ausführen:

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

Diese Befehle fügen der Tabelle sales sechs Zeilen hinzu. Jede Zeile stellt ein Produkt mit seiner ID, seinem Namen und seinem Umsatzbetrag dar.

• INSERT INTO sales (product_id, product_name, sales_amount) gibt an, dass Sie Daten in die Spalten product_id, product_name und sales_amount der Tabelle sales einfügen.
• VALUES (1, 'Laptop', 1200) stellt die Werte bereit, die für jeden Datensatz eingefügt werden sollen.

Um zu bestätigen, dass die Daten korrekt hinzugefügt wurden, führen Sie diesen Befehl aus, um alle Datensätze in der Tabelle anzuzeigen:

SELECT * FROM sales;

Erwartete Ausgabe:

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

Diese Ausgabe zeigt die product_id, product_name und sales_amount für jeden Datensatz. Der Befehl SELECT * ruft alle Spalten aus der angegebenen Tabelle ab.

Zeilen mit ROW_NUMBER() einordnen

In diesem Schritt lernen Sie, wie Sie die ROW_NUMBER()-Fensterfunktion (Window Function) verwenden, um jeder Zeile basierend auf dem sales_amount einen eindeutigen Rang zuzuweisen. Dies ist nützlich, um die meistverkauften Produkte zu identifizieren.

Die Funktion ROW_NUMBER() weist jeder Zeile innerhalb einer Partition eines Resultsets eine eindeutige Ganzzahl zu. Der Rang wird durch die in der ORDER BY-Klausel angegebene Reihenfolge bestimmt.

Führen Sie die folgende Abfrage an der sqlite>-Eingabeaufforderung aus:

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

Diese Abfrage berechnet den Rang jedes Produkts basierend auf seinem sales_amount in absteigender Reihenfolge.

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) weist jeder Zeile basierend auf dem sales_amount einen Rang zu, wobei der höchste Umsatzbetrag den Rang 1 erhält.
• ORDER BY sales_amount DESC gibt an, dass die Rangfolge auf dem sales_amount in absteigender Reihenfolge basieren soll.

Erwartete Ausgabe:

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

Wie Sie sehen, enthält die Spalte sales_rank nun den Rang jedes Produkts basierend auf seinem sales_amount, wobei der höchste Umsatzbetrag (Laptop) den Rang 1 erhält.

Laufende Summen berechnen

In diesem Schritt lernen Sie, wie Sie laufende Summen (kumulative Summen) mithilfe von Fensterfunktionen (Window Functions) berechnen. Laufende Summen sind nützlich, um die Summe der Werte über einen Zeitraum oder über eine Reihe von Zeilen zu verfolgen.

Um laufende Summen zu berechnen, verwenden Sie die Funktion SUM() mit der Klausel OVER() und einer ORDER BY-Klausel, um die Reihenfolge anzugeben, in der die Summe berechnet wird.

Fügen wir zunächst eine Spalte sale_date zur Tabelle sales hinzu und füllen wir sie mit einigen Beispieldaten. Führen Sie die folgenden Befehle an der sqlite>-Eingabeaufforderung aus:

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

Diese Befehle fügen der Tabelle sales eine Spalte sale_date hinzu und aktualisieren die Tabelle mit Beispieldaten für jedes Produkt.

Berechnen wir nun die laufende Summe von sales_amount im Zeitverlauf, geordnet nach sale_date. Führen Sie die folgende Abfrage aus:

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

Diese Abfrage berechnet die laufende Summe von sales_amount im Zeitverlauf, geordnet nach sale_date.

• SUM(sales_amount) OVER (ORDER BY sale_date) berechnet die kumulative Summe von sales_amount bis zu jedem sale_date.
• ORDER BY sale_date gibt an, dass die laufende Summe basierend auf dem sale_date in aufsteigender Reihenfolge berechnet werden soll.

Erwartete Ausgabe:

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

Die Spalte running_total zeigt die kumulative Summe von sales_amount bis zu jedem sale_date. Beispielsweise beträgt die laufende Summe am '2023-01-15' 1600, was der Summe der Umsätze vom '2023-01-01', '2023-01-05', '2023-01-10' und '2023-01-15' entspricht.

Daten für die Analyse partitionieren

In diesem Schritt lernen Sie, wie Sie Daten mithilfe der PARTITION BY-Klausel innerhalb von Fensterfunktionen (Window Functions) partitionieren. Die Partitionierung ermöglicht es Ihnen, Ihre Daten in logische Gruppen zu unterteilen und dann Berechnungen innerhalb jeder Gruppe unabhängig voneinander durchzuführen.

Fügen wir unserer Tabelle sales eine Spalte product_category hinzu. Führen Sie die folgenden Befehle an der sqlite>-Eingabeaufforderung aus:

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

Diese Befehle fügen der Tabelle sales eine Spalte product_category hinzu und aktualisieren die Tabelle mit Kategorien für jedes Produkt.

Verwenden wir nun PARTITION BY, um die laufende Summe von sales_amount innerhalb jeder product_category zu berechnen. Führen Sie die folgende Abfrage aus:

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

Diese Abfrage berechnet die laufende Summe von sales_amount innerhalb jeder product_category, geordnet nach sale_date.

• PARTITION BY product_category unterteilt die Daten in Partitionen basierend auf der product_category.
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) berechnet die kumulative Summe von sales_amount innerhalb jeder product_category, geordnet nach sale_date.

Erwartete Ausgabe:

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

Die Spalte running_total_by_category zeigt die kumulative Summe von sales_amount innerhalb jeder product_category, geordnet nach sale_date. Beachten Sie, dass die laufende Summe für jede Kategorie neu beginnt.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie Fensterfunktionen (Window Functions) in SQLite verwenden, um fortgeschrittene Datenanalysen durchzuführen. Sie begannen mit der Erstellung einer sales-Tabelle und dem Einfügen von Beispieldaten. Anschließend verwendeten Sie die Funktion ROW_NUMBER(), um Produkte basierend auf ihrem Umsatz (sales amount) zu bewerten (rank). Sie haben auch gelernt, wie Sie laufende Summen (running totals) mithilfe der Funktion SUM() mit der Klausel OVER() berechnen und wie Sie Daten mithilfe der Klausel PARTITION BY partitionieren, um Berechnungen innerhalb logischer Gruppen durchzuführen. Diese Fähigkeiten bilden eine Grundlage für komplexere Datenanalyseaufgaben in SQLite.