Aufgabe 16 - SQL Aggregation & Grouping (E-Sports Analytics)

Aufgabe 16 - SQL Aggregation & Grouping

Worum geht es?

In dieser Übung werden Rohdaten zu aussagekräftigen Berichten verdichtet: mit Aggregatfunktionen zusammenfassen, mit GROUP BY gruppieren und mit HAVING filtern (siehe Kapitel 7 - Aggregation und Gruppierung). Im Expertenteil kommen gefilterte Aggregate, Zwischensummen und der Spitzenreiter je Region dazu.

Was Sie dafür brauchen

• Kapitel 7 - Aggregation und Gruppierung.
• Ein PostgreSQL-Server und ein SQL-Editor.

Welche Kompetenzen Sie erwerben und zeigen

• Sie verdichten Daten mit COUNT, SUM, AVG, MIN, MAX.
• Sie gruppieren mit GROUP BY und filtern Gruppen mit HAVING.
• Sie grenzen WHERE von HAVING ab und verstehen den Umgang mit NULL.
• Sie nutzen gefilterte Aggregate und Zwischensummen und ermitteln Spitzenwerte je Gruppe.

Pädagogische Einordnung

• Reproduktion: einfache Aggregate berechnen (Teil A).
• Reorganisation und Transfer: gruppieren und filtern (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: fortgeschrittene Auswertungen entwickeln (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Schema und Testdaten

CREATE TABLE teams (
    team_id INT PRIMARY KEY, team_name VARCHAR(50), region VARCHAR(30)
);
CREATE TABLE arenas (
    arena_id INT PRIMARY KEY, arena_name VARCHAR(50), city VARCHAR(50), capacity INT
);
CREATE TABLE matches (
    match_id INT PRIMARY KEY, team_id INT, arena_id INT, game VARCHAR(50), kills INTEGER
);
CREATE TABLE sponsorships (
    sponsor_id INT PRIMARY KEY, sponsor_name VARCHAR(50), team_id INT
);

INSERT INTO teams VALUES (1,'Cyber Vipers','Europe'),(2,'Neon Samurais','Asia'),(3,'Arctic Foxes','North America'),(4,'Desert Rats','Africa');
INSERT INTO arenas VALUES (10,'Alpha Dome','Berlin',5000),(20,'Cyber Stadium','Tokyo',15000),(30,'Empty Hall','Unknown',500);
INSERT INTO matches VALUES (101,1,10,'Valorant',45),(102,1,20,'League of Legends',30),(103,2,20,'Counter-Strike',12),(104,1,10,'Valorant',55),(105,99,10,'Dota 2',55);
INSERT INTO sponsorships VALUES (501,'Red Bull',1),(502,'Logitech',1),(503,'Intel',3),(504,'Nvidia',NULL);

Teil A - Basis-Statistiken

1. Gesamtsumme aller Kills über alle Matches.
2. Anzahl der verschiedenen Spiele in matches.
3. Größte und kleinste Arena-Kapazität.

Teil B - Gruppierung

1. Anzahl der Teams je Region, sortiert nach Anzahl absteigend.
2. Durchschnittliche Kills je Spiel.
3. Je Teamname die Gesamtzahl der Sponsoren.

Teil C - Gruppen filtern (HAVING)

1. Teams mit durchschnittlich mehr als 40 Kills über ihre Matches.
2. Arenen mit mehr als einem Match.
3. Teams mit mehr als einem Sponsor und einem Match in einer Arena mit Kapazität über 10.000.

Teil D - Expertenteil: Gefilterte Aggregate, Zwischensummen, Spitzenreiter

1. Gefiltertes Aggregat (FILTER): In einer einzigen Abfrage je Team ausgeben: Gesamtzahl der Matches, davon Anzahl der Valorant-Matches, mit dem PostgreSQL-Konstrukt count(*) FILTER (WHERE game = 'Valorant'). Erklären, warum das eleganter ist als zwei getrennte Abfragen.
2. Zwischensummen (ROLLUP): Die Kills je Region und eine Gesamtsumme über alle Regionen in einer Abfrage ausgeben, mit GROUP BY ROLLUP (region). Beschreiben, was die zusätzliche Zeile mit NULL in der Region-Spalte bedeutet.
3. Spitzenreiter je Region: Für jede Region den Teamnamen mit den meisten Gesamt-Kills ermitteln. Da Fensterfunktionen erst in der 4. Klasse behandelt werden, mit einer korrelierten Subquery oder einem Vergleich gegen das Gruppenmaximum lösen.
4. NULL verstehen: Zeigen und erklären, wie AVG(kills) und COUNT(kills) mit fehlenden Werten umgehen und warum COUNT(*) und COUNT(spalte) unterschiedliche Ergebnisse liefern können. Nutzen Sie dazu den Sponsor 'Nvidia' mit team_id IS NULL.

Wissenscheck

1. Warum schlägt SELECT region, team_name, COUNT(*) FROM teams GROUP BY region fehl?
2. Warum kann man Aufgabe C.1 nicht mit WHERE AVG(kills) > 40 lösen?
3. Wie verhalten sich AVG() und COUNT(spalte) gegenüber NULL?
4. Was leistet count(*) FILTER (WHERE ...) gegenüber zwei getrennten Abfragen?
5. Was bedeutet die zusätzliche NULL-Zeile bei GROUP BY ROLLUP (region)?

Abgabe

• Die Datei aufgabe16_aggregation.sql mit allen Abfragen, fehlerfrei ausführbar, Schlüsselwörter konsistent groß, inklusive der Erklärungen aus Teil D als Kommentare.

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at