MySQL-Datenaggregation und -Gruppierung

Einführung

In diesem Lab werden wir die Datenaggregation und -gruppierung in MySQL untersuchen, essentielle Fähigkeiten für die Analyse und Zusammenfassung von Daten in Datenbanken. Sie werden lernen, wie Sie Aggregatfunktionen verwenden, um Berechnungen über Zeilen hinweg durchzuführen, Daten basierend auf Spaltenwerten zu gruppieren und gruppierte Ergebnisse zu filtern. Diese Techniken sind grundlegend für die Erstellung von Berichten, die Analyse von Trends und die Gewinnung sinnvoller Erkenntnisse aus Ihren Daten. Durch praktische Übungen werden Sie praktische Erfahrung mit diesen wichtigen Datenbankoperationen sammeln und verstehen, wie Sie Daten in MySQL effektiv analysieren können.

Grundlegende Aggregatfunktionen

In diesem Schritt werden wir die grundlegenden Aggregatfunktionen in MySQL untersuchen. Diese Funktionen führen Berechnungen über mehrere Zeilen durch und geben einen einzigen Wert zurück, was sie für die Datenanalyse unerlässlich macht.

Zunächst verbinden wir uns mit MySQL und wählen unsere Datenbank aus:

sudo mysql -u root

Sobald verbunden:

USE sales_db;

COUNT-Funktion

Beginnen wir mit der COUNT-Funktion, die die Anzahl der Zeilen in einem Ergebnis-Set zählt:

-- Zähle die Gesamtanzahl der Verkäufe
SELECT COUNT(*) as total_sales
FROM sales;

Sie sollten eine Ausgabe wie diese sehen:

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

COUNT kann auf verschiedene Weise verwendet werden:

-- Zähle die eindeutigen verkauften Produkte
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- Zähle die Verkäufe nach Kategorie
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

Erklärung:

• COUNT(*) zählt alle Zeilen in der Tabelle, einschließlich NULL-Werte
• COUNT(DISTINCT column) zählt nur die eindeutigen Werte in der angegebenen Spalte
• Wenn COUNT mit GROUP BY verwendet wird, berechnet es die Summen für jede Gruppe separat
• Das Schlüsselwort as erstellt Aliase für die Ergebnis-Spalten, wodurch die Ausgabe lesbarer wird

SUM-Funktion

Die SUM-Funktion berechnet die Summe numerischer Spalten:

-- Berechne die Gesamtmenge der verkauften Artikel
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- Berechne den Gesamtumsatz
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

Erklärung:

• SUM kann nur mit numerischen Spalten verwendet werden
• Berechnungen wie quantity * unit_price werden vor der Aggregation durchgeführt
• ROUND(x, 2) rundet Zahlen auf 2 Dezimalstellen
• Bei der Umsatzberechnung erfolgt die Multiplikation vor der Summierung, um die Genauigkeit zu gewährleisten

AVG-Funktion

Die AVG-Funktion berechnet den Mittelwert:

-- Berechne den durchschnittlichen Einzelpreis
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- Berechne die durchschnittliche Menge pro Verkauf
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

Erklärung:

• AVG ignoriert automatisch NULL-Werte bei seinen Berechnungen
• Der zweite Parameter in ROUND gibt die Anzahl der Dezimalstellen an
• Die Ergebnisse werden automatisch in den DECIMAL-Typ umgewandelt, um die Genauigkeit zu gewährleisten
• AVG wird häufig verwendet, um typische Werte in Ihren Daten zu finden

MAX- und MIN-Funktionen

Diese Funktionen finden die höchsten und niedrigsten Werte:

-- Finde den Preisbereich der Produkte
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- Finde die Daten des ersten und letzten Verkaufs
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

Erklärung:

• MIN/MAX funktionieren mit Zahlen, Strings und Datumsangaben
• Bei Datumsangaben findet MIN das früheste Datum, während MAX das späteste Datum findet
• Mehrere Aggregatfunktionen können in einer einzigen SELECT-Anweisung kombiniert werden
• Wie andere Aggregatfunktionen ignorieren sie automatisch NULL-Werte
• Diese Funktionen sind nützlich, um Wertebereiche und Grenzen in Ihren Daten zu finden

Gruppieren von Daten mit GROUP BY

In diesem Schritt werden wir lernen, wie wir Daten mit der GROUP BY-Klausel gruppieren können. Das Gruppieren ermöglicht es uns, Aggregatberechnungen auf Teilmengen unserer Daten basierend auf bestimmten Spaltenwerten durchzuführen.

Grundlegendes Gruppieren

Beginnen wir mit einfachen Gruppierungsoperationen:

-- Verkaufszahl und Gesamtmenge nach Kategorie
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

Diese Abfrage zeigt, wie viele Verkäufe und welche Gesamtmenge in jeder Kategorie verkauft wurden. Sie sollten eine Ausgabe wie diese sehen:

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

Erklärung:

• GROUP BY fasst Zeilen mit derselben Kategorie zu einer einzigen Zeile zusammen
• Die ORDER BY total_quantity DESC-Anweisung sortiert die Ergebnisse absteigend nach der Menge
• Jede Aggregatfunktion (COUNT, SUM) arbeitet unabhängig innerhalb jeder Gruppe
• Nicht aggregierte Spalten in der SELECT-Anweisung müssen in der GROUP BY-Klausel erscheinen

Gruppieren nach mehreren Spalten

Wir können nach mehreren Spalten gruppieren, um detailliertere Einblicke zu erhalten:

-- Verkaufsanalyse nach Kategorie und Region
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

Erklärung:

• Das Gruppieren nach mehreren Spalten erstellt Untergruppen für jede eindeutige Kombination
• Die Reihenfolge der Spalten in der GROUP BY-Klausel beeinflusst, wie die Daten gruppiert werden
• Die Ergebnisse werden zunächst nach Kategorie und dann nach Gesamtumsatz innerhalb jeder Kategorie sortiert
• Dieser Ansatz hilft, die besten Regionen innerhalb jeder Kategorie zu identifizieren

Datumsbasierte Gruppierung

MySQL bietet Funktionen, um Teile von Datumsangaben zu extrahieren, was für eine zeitbasierte Gruppierung nützlich ist:

-- Tagesverkaufszusammenfassung
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Erklärung:

• Gruppiert alle Transaktionen, die am gleichen Datum stattfanden
• Die ORDER BY sale_date-Anweisung ordnet die Ergebnisse chronologisch an
• Zählt die Transaktionen pro Tag und berechnet die täglichen Summen
• Nützlich für die Identifizierung von täglichen Verkaufsmustern und Trends
• Kann mithilfe von DATE-Funktionen so modifiziert werden, dass nach Monat oder Jahr gruppiert wird

Filtern von gruppierten Daten mit HAVING

In diesem Schritt werden wir uns mit der HAVING-Klausel befassen, die es uns ermöglicht, Ergebnisse nach der Gruppierung zu filtern. Während die WHERE-Klausel einzelne Zeilen vor der Gruppierung filtert, filtert die HAVING-Klausel die Gruppen selbst.

Grundlegende Verwendung von HAVING

Finden wir Kategorien, in denen mehr als 15 Artikel insgesamt verkauft wurden:

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

Dies zeigt nur die Kategorien, in denen die Gesamtmenge über 15 Artikel liegt:

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

Erklärung:

• Die HAVING-Klausel filtert die Gruppen, nachdem die GROUP BY-Klausel angewendet wurde.
• Sie können in der HAVING-Klausel auf die Ergebnisse von Aggregatfunktionen verweisen.
• Die Filterbedingung verwendet den Alias 'total_quantity'.
• Kategorien mit 15 oder weniger Artikeln werden aus den Ergebnissen ausgeschlossen.

Kombinieren von WHERE und HAVING

Wir können WHERE und HAVING gemeinsam verwenden. WHERE filtert Zeilen vor der Gruppierung, während HAVING nach der Gruppierung filtert:

-- Finde Kategorien mit hohem Umsatz in der Region Norden
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

Erklärung:

• WHERE filtert einzelne Zeilen (region = 'North') vor der Gruppierung.
• Dann werden die Daten nach Kategorie gruppiert.
• Schließlich filtert HAVING die gruppierten Ergebnisse (total_quantity > 5).
• Die Reihenfolge ist wichtig: FROM → WHERE → GROUP BY → HAVING → SELECT

Komplexe HAVING-Bedingungen

Wir können mehrere Bedingungen in der HAVING-Klausel verwenden:

-- Finde Kategorien mit hohem Verkaufsvolumen und Umsatz
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

Erklärung:

• Mehrere Bedingungen können mit AND/OR kombiniert werden.
• Alle Aggregatberechnungen stehen für HAVING-Bedingungen zur Verfügung.
• Sie können in der HAVING-Klausel arithmetische Operationen verwenden.
• Die ORDER BY-Klausel wird angewendet, nachdem die HAVING-Filter ausgewertet wurden.
• Diese Art von Abfrage ist nützlich, um die besten Kategorien zu identifizieren.

Fortgeschrittene Aggregationstechniken

In diesem letzten Schritt werden wir fortgeschrittene Aggregationstechniken erkunden, die alles zusammenfassen, was wir gelernt haben. Wir werden umfassende Verkaufsberichte erstellen, die die Stärke dieser Tools in Kombination demonstrieren.

Verkaufsleistungsdashboard

Erstellen wir eine umfassende Verkaufsanalyse:

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Erklärung:

• Erstellt einen vollständigen Überblick über die Leistung der Kategorien
• Verwendet DISTINCT, um die eindeutigen Produkte innerhalb jeder Kategorie zu zählen
• Kombiniert mehrere Aggregatfunktionen für eine umfassende Analyse
• Zeigt die Preisbereiche mit MIN und MAX
• Berechnet die durchschnittlichen Mengen und den Gesamtumsatz
• Die Ergebnisse werden nach Umsatz sortiert, um die besten Performers hervorzuheben

Regionale Leistungsanalyse

Analysieren wir die Verkaufsleistung nach Region:

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

Erklärung:

• Gruppiert die Verkaufsdaten nach geografischen Regionen
• Zeigt die Produktdiversität mit DISTINCT-Zählungen
• Berechnet die Gesamtumsatz- und Mengenmetriken
• avg_price_per_unit wird berechnet, indem der Umsatz durch die Menge geteilt wird
• HAVING filtert Regionen mit geringerem Umsatz aus
• Hilft bei der Identifizierung der am besten und am schlechtesten abschneidenden Regionen

Analyse der täglichen Trends

Erstellen wir einen Bericht über die täglichen Verkaufstrends:

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Erklärung:

• Verfolgt die täglichen Verkaufsleistungsmetriken
• Zeigt die Produktdiversität und Kategoriediversität pro Tag
• Zählt die täglichen Transaktionen und Mengen
• Berechnet den täglichen Umsatz und den durchschnittlichen Transaktionswert
• Die chronologische Sortierung hilft bei der Identifizierung von Trends
• Nützlich für die Erkennung von Mustern und saisonalen Effekten
• Kann bei der Lagerplanung und bei Entscheidungen zur Personalbeschaffung helfen

Zusammenfassung

In diesem Lab haben wir die wesentlichen Aspekte der Datenaggregation und -gruppierung in MySQL behandelt:

1. Die Verwendung grundlegender Aggregatfunktionen (COUNT, SUM, AVG, MAX, MIN) zur Zusammenfassung von Daten
2. Das Gruppieren von Daten mit GROUP BY zur Analyse von Mustern und Trends
3. Das Filtern von gruppierten Ergebnissen mit der HAVING-Klausel
4. Die Kombination mehrerer Techniken zur Erstellung umfassender Datenanalysen

Diese Fähigkeiten sind für die Datenanalyse und -berichterstattung in MySQL von grundlegender Bedeutung. Das Verständnis, wie man Daten effektiv aggregiert und gruppiert, ermöglicht es Ihnen, sinnvolle Erkenntnisse aus Ihren Datenbanken zu gewinnen und wertvolle Geschäftsberichte zu erstellen.