Adventskalender 1. Dezember 2024 - Ent(ität)en

[paedubucher]

Ent(ität)en

Hinter dem ersten Türchen verbirgt sich die Technologie DuckDB, wobei es sich um eine moderne OLAP-Datenbank handelt. Hiermit können Entitäten mit einer Ente verwaltet werden, womit der Name des ersten Türchens auch erklärt wäre.

Die Inspiration für dieses Türchen ist eine Scripting-Challenge aus dem Jahr 2022, wo es darum ging Fussballergebnisse in Ligatabellen umzuwandeln. Die Daten wurden erweitert und aktualisiert; sie stehen unter leagues.zip zum Download zur Verfügung.

Das Ziel der Übung ist es, die Ligatabelle anhand der Beispieldaten mit SQL zu berechnen.

Ausgangslage und Setup

Wir beginnen mit einer jungfräulichen Installation von Debian 12 “Bookworm”, die für unsere Zwecke gut ohne GUI auskommt. Wir arbeiten mit dem Benutzer user, der über sudo-Rechte verfügt.

Von der Download-Seite können wir DuckDB direkt herunterladen und entpacken:

Code: Alles auswählen

wget https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip

Zum Entpacken wird das Paket unzip benötigt:

Code: Alles auswählen

sudo apt install -y unzip

Womit das heruntergeladene Archiv auch sogleich entpackt wird:

Code: Alles auswählen

 unzip duckdb_cli-linux-amd64.zip

Das Ergebnis ist die Binärdatei duckdb, welche ca. 48 Megabyte wiegt. Wir verschieben diese an einen passenden Ort:

Code: Alles auswählen

sudo mv duckdb /usr/local/bin/

Beispieldaten

Als nächstes sollen die Spieldaten heruntergeladen werden:

Code: Alles auswählen

wget https://m346.frickelbude.ch/files/leagues.zip

Auch dieses Archiv soll entpackt werden:

Code: Alles auswählen

unzip leagues.zip

Wodurch ein Verzeichnis namens leagues erstellt worden ist. Darüber lässt sich mit tree ein erster Überblick erhalten:

Code: Alles auswählen

sudo apt install -y tree
tree leagues

Was dann ungefähr folgendermassen aussieht:

Code: Alles auswählen

leagues
├── bundesliga
│   ├── day01.json
│   ├── day02.json
│   ├── day03.json
│   ├── day04.json
│   ├── day05.json
│   ├── day06.json
│   ├── day07.json
│   ├── day08.json
…

Wir haben mehrere Unterordner mit verschiedenen Ligen, und pro Liga gibt es mehrere JSON-Dateien, welche ungefähr folgendermassen aussehen (Beispiel: leagues/bundesliga/day01.json):

Code: Alles auswählen

[
  {
    "homeTeam": "Bayern München",
    "awayTeam": "FSV Mainz 05",
    "homeGoals": 2,
    "awayGoals": 3
  },
  {
    "homeTeam": "RB Leipzig",
    "awayTeam": "Werder Bremen",
    "homeGoals": 1,
    "awayGoals": 2
  },
  {
    "homeTeam": "Eintracht Frankfurt",
    "awayTeam": "VfL Wolfsburg",
    "homeGoals": 0,
    "awayGoals": 2
  },
  …

Die Dateien wurden zufällig mit einigermassen realistischen Stärkeverhältnissen generiert; die Struktur sollte selbsterklärend sein.

Ziel der Übung ist es, eine Ligatabelle mit den folgenden Spalten zu generieren:

1. Rang
2. Mannschaftsname
3. Anzahl absolvierter Spiele
4. Erreichte Punkte (Sieg: 3, Unentschieden: 1, Niederlage: 0)
5. Anzahl Siege
6. Anzahl Unentschieden
7. Anzahl Niederlagen
8. Erzielte Tore
9. Kassierte Tore
10. Tordifferenz (erzielte minus kassierte Tore)

Die Tabelle soll absteigend nach Punkten und Tordifferenz sortiert werden.

Datenimport

Wir starten DuckDB auf einem noch nicht existierenden Verzeichnis namens leagues-db, wodurch man sogleich Persistenz erhält:

Code: Alles auswählen

duckdb leagues-db

Es sollte der Prompt D erscheinen:

Code: Alles auswählen

D

DuckDB kann das Schema von JSON-Dateien automatisch ermitteln, was wir mit describe from versuchen wollen (auf Prompts und Ausgaben wird ab hier grösstenteils verzichtet):

Code: Alles auswählen

describe from "leagues/bundesliga/day*.json";

Wir stellen fest, dass DuckDB sowohl Feldnamen als auch Datentypen korrekt erkannt hat.

Zunächst sollen die Daten von den JSON-Dateien in eine Tabelle übertragen werden. Hierzu erstellen wir einerseits eine Sequenz für die automatische ID-Nummerierung und andererseits eine Tabelle namens bundesliga_games, welche von dieser Sequenz Gebrauch macht:

Code: Alles auswählen

create sequence bundesliga_game_id;
create table bundesliga_games (
    id integer primary key default(nextval('bundesliga_game_id')),
    homeTeam varchar(100),
    awayTeam varchar(100),
    homeGoals integer,
    awayGoals integer
);

Quizfrage: Warum ist die Vergabe einer künstlichen ID hier sinnvoll?

Als nächstes werden die Daten aus den JSON-Dateien in die erstellte Tabelle eingelesen:

Code: Alles auswählen

insert into bundesliga_games
(homeTeam, awayTeam, homeGoals, awayGoals)
select * from "leagues/bundesliga/day*.json";

Es wird das insert/select-Muster verwendet, wobei die Ergebnismenge aus der JSON-Datei die Grundlage für das Einfügen der Daten bildet.

Nun folgt der erste interessante Kniff: Aus jedem Spielergebnis sollen zwei Einträge geschrieben werden; je einer für die Heim- und für die Auswärtsmannschaft. Dies lässt sich mit einer View auf die gerade erstellte Tabelle bewerkstelligen:

Code: Alles auswählen

create view bundesliga_per_team as (
    select id as gameId, homeTeam as team, homeGoals as goals_scored,
    awayGoals as goals_conceded from bundesliga_games
    union
    select id as gameId, awayTeam as team, awayGoals as goals_scored,
    homeGoals as goals_conceded from bundesliga_games
);

Mithilfe der union werden die Ergebnisse aus den beiden Perspektiven (Heim- und Auswärtsmannschaft) aneinandergehängt.

Das Ergebnis kann nun folgendermassen bestaunt werden:

Code: Alles auswählen

select * from bundesliga_per_team;

Ergebnisberechnung

Anhand der Spielergebnisse sollen nun sogenannte Minitabellen erstellt werden: Aus jedem Spielergebnis aus der Perspektive jeder Mannschaft soll eine “Tabelle” (technisch gesehen: eine Tabellenzeile) für den jeweiligen Spieltag berechnet werden. Hierzu kommt neben einfacher Arithmetik das case/when/then/else-Konstrukt zum Einsatz, wozu wiederum eine neue View erstellt wird:

Code: Alles auswählen

create view bundesliga_results_day as (
    select team, goals_scored, goals_conceded, (goals_scored - goals_conceded) as goals_diff,
    case
        when goals_diff > 0
        then 3
        else case when goals_diff = 0 then 1 else 0 end
    end as points,
    case
        when goals_diff > 0
        then 1
        else 0
    end as wins,
    case
        when goals_diff = 0
        then 1
        else 0
    end as ties,
    case
        when goals_diff < 0
        then 1
        else 0
    end as defeats,
    from bundesliga_per_team
);

Damit wäre der erste Teil der Berechnung auch schon erledigt.

Übung: Die Tabelle erstellen

Nun seid ihr gefragt: Wie erhält man aus den ganzen Minitabellen nun eine spieltagsübergreifende Ligatabelle? Hierzu einige Tipps:

1. Die Aggregatsfunktionen count und sum im Zusammenspiel mit der group by-Klausel können hilfreich sein.
2. Die Sortierung ist in SQL denkbar einfach mit order by zu bewerkstelligen.
3. Am besten erstellt man eine weitere View für die Tabellenansicht, etwa mit dem Namen bundesliga_table.
4. Die offizielle Dokumentation kann auch sehr hilfreich sein.

Die Nummerierung der Einträge lässt sich auf Basis der Tabellen-View folgendermassen bewerkstelligen:

Code: Alles auswählen

select row_number() over() as '#', * from bundesliga_table;

Ich bin auf eure Lösungen gespannt!

PS: Wer noch ein Weihnachtsgeschenk braucht, dem kann ich das Buch DuckDB in Action vom Manning-Verlag empfehlen!

[TuxPeter]

Eine interessante Geschichte!
Ich habe mal nach weiteren grundlegenden Infos gesucht und dieses gefunden.: https://www.heise.de/blog/Ente-gut-alle ... 53854.html

[Liffi]

Super Sache, duckdb stand schon länger auf der Liste, aber diese Liste ist leider auch ganz schön lang .
Ohne hier zu viel spoilern zu wollen: Steht ein badischer Verein mit 62 Punkten am Ende ganz oben und einer aus dem Ruhrgebiet mit 27 Punkten und -25 Tordifferenz ganz unten?

Ich musste nicht mal in die Dokumentation schauen, weil sich die Abfragen so natürlich angefühlt haben. Sicher auch, weil ich sonst schon viel "normales" SQL von mir geben muss .

EDIT:: und natürlich ein großartiger Titel

[Meillo]

Nun komme ich endlich mal dazu, mir dieses Tuerchen anzuschauen.

DuckDB kann das Schema von JSON-Dateien automatisch ermitteln, was wir mit describe from versuchen wollen (auf Prompts und Ausgaben wird ab hier grösstenteils verzichtet):

Code: Alles auswählen

describe from "leagues/bundesliga/day*.json";

Wir stellen fest, dass DuckDB sowohl Feldnamen als auch Datentypen korrekt erkannt hat.

Zunächst sollen die Daten von den JSON-Dateien in eine Tabelle übertragen werden. Hierzu erstellen wir einerseits eine Sequenz für die automatische ID-Nummerierung und andererseits eine Tabelle namens bundesliga_games, welche von dieser Sequenz Gebrauch macht:

Code: Alles auswählen

create sequence bundesliga_game_id;
create table bundesliga_games (
    id integer primary key default(nextval('bundesliga_game_id')),
    homeTeam varchar(100),
    awayTeam varchar(100),
    homeGoals integer,
    awayGoals integer
);

Quizfrage: Wenn DuckDB das Schema der Json-Dateien selbst erkennen kann, warum kann es damit dann nicht auch gleich die Tabelle anlegen?

Antwortversuch: Das wird wohl an den Datentypen liegen, aber es koennte ja auch raten und flexible Typen waehlen.

Quizfrage: Warum ist die Vergabe einer künstlichen ID hier sinnvoll?

Antwortversuch: Keine Ahnung. *Ist* sie denn sinnvoll? Wenn die Tabelle nur eine Saison aufnehmen soll (was ohne Saisonspalte zwangslaeufig der Fall sein muss, da man sonst keine sinnvolle Tabelle erzeugen kann), dann kommt jede Kombination aus Heim- und Auswaertsteam nur genau einmal vor (beim Rueckspiel sind die Teams ja getauscht). Wozu also die ID? Ich weiss es nicht. Was das Datenmodell an sich angeht, scheint sie mir unnoetig zu sein. Man wuerde sie mehr aus Bequemlichkeit reinmachen, weil sie wenig kostet und durchaus praktisch sein kann.

Ich bin auf eure Lösungen gespannt!

Leider kann ich keine liefern, da das zu viel meiner Gehirnkapazitaet in Anspruch nehmen wuerde. Aber ich finde es clever, wie du das Problem dieses Mal so ganz in SQL loest. Nicht dass man IMO zu viel Logik in SQL stecken sollte, aber Views beispielsweise werden gemeinhin zu selten genutzt, finde ich. Darum danke fuer diesen Ansatz.

[Meillo]

Ich habe nun selbst etwas recherchiert.

Quizfrage: Wenn DuckDB das Schema der Json-Dateien selbst erkennen kann, warum kann es damit dann nicht auch gleich die Tabelle anlegen?

So sollte es moeglich sein, die Json-Datei automatisch in eine Tabelle zu bringen (mit create und insert in einem):

Code: Alles auswählen

create table bundesliga_games
as select * from "leagues/bundesliga/day*.json";

Das waere doch deutlich bequemer als die Schritte von Hand zu machen.

In diesem Fall haben wir ja keine besonderen Anforderungen an die Tabelle. Die Automatik wird schon alles sinnvoll machen.

[paedubucher]

Quizfrage: Warum ist die Vergabe einer künstlichen ID hier sinnvoll?

Antwortversuch: Keine Ahnung. *Ist* sie denn sinnvoll? Wenn die Tabelle nur eine Saison aufnehmen soll (was ohne Saisonspalte zwangslaeufig der Fall sein muss, da man sonst keine sinnvolle Tabelle erzeugen kann), dann kommt jede Kombination aus Heim- und Auswaertsteam nur genau einmal vor (beim Rueckspiel sind die Teams ja getauscht). Wozu also die ID? Ich weiss es nicht. Was das Datenmodell an sich angeht, scheint sie mir unnoetig zu sein. Man wuerde sie mehr aus Bequemlichkeit reinmachen, weil sie wenig kostet und durchaus praktisch sein kann.

Tipp: Ein Verein kann pro Saison mehrmals das gleiche Ergebnis gegen einen anderen Verein erreichen. In weiteren Verarbeitungsschritten dürfte sich das Vorhandensein einer ID positiv auswirken.

Nachtrag: Fast zwei Wochen später gebe ich einen Lösungsvorschlag als YouTube-Video ab. Die Übung findet man auf meiner Modulwebseite.

[Meillo]

Quizfrage: Warum ist die Vergabe einer künstlichen ID hier sinnvoll?

Antwortversuch: Keine Ahnung. *Ist* sie denn sinnvoll? Wenn die Tabelle nur eine Saison aufnehmen soll (was ohne Saisonspalte zwangslaeufig der Fall sein muss, da man sonst keine sinnvolle Tabelle erzeugen kann), dann kommt jede Kombination aus Heim- und Auswaertsteam nur genau einmal vor (beim Rueckspiel sind die Teams ja getauscht). Wozu also die ID? Ich weiss es nicht. Was das Datenmodell an sich angeht, scheint sie mir unnoetig zu sein. Man wuerde sie mehr aus Bequemlichkeit reinmachen, weil sie wenig kostet und durchaus praktisch sein kann.

Tipp: Ein Verein kann pro Saison mehrmals das gleiche Ergebnis gegen einen anderen Verein erreichen.

Fuer die Tabelle `bundesliga_games' ist das irrelevant. In `bundesliga_per_team' koennte es ohne `id' zu mehrfachen identischen Eintragen kommen ... aber ist das schlimm? Bislang habe ich noch keine technische Notwendigkeit fuer die ID gefunden.

In weiteren Verarbeitungsschritten dürfte sich das Vorhandensein einer ID positiv auswirken.

Ist das noch Teil des Tipps oder Teil der Antwort?

Nun gut, die letzten SQL-Befehle fehlen ja noch, also kann ich mir die nicht anschauen ... und selbst bin ich auch nicht so ein grosser SQL-Experte, dass ich sie mir mal schnell ausdenken und alle Implikationen analysieren kann.

Hast du es denn mal ohne ID versucht? Geht es dann immer noch? Du schreibst ja nur von ``sinnvoll'' und nicht von ``noetig''.

So ID-Spalten ist halt ein neumodischer Stil ... das machen die heutigen Entwickler, die keine so genaue Ahnung haben und sich denken, dass eine ID immer praktisch ist, weil man sich dann mehrspaltige PKs und doppelte Eintraege ersparen kann. Das sind dann oft keine Notwendigkeiten fuer das Datenmodell, sondern nur Bequemlichkeiten. Wenn es aber nur Bequemlichkeit ist, dann ist die Antwort auf die Quizfrage genau diese Bequemlichkeit. Das deutet das Wort ``sollte'' eigentlich schon an. Denn eine Notwendigkeit kann es bei dieser Fragestellung eigentlich nicht geben.

Vermutlich geht es dir also darum, dass sich die Eintraege der Tabelle `bundesliga_per_team' mittels einer solchen ID noch den Begegnungen in `bundesliga_games` zuordnen lassen.

Frage: Muss man sie noch zuordnen koennen? Die Tabelle ist ja nur ein Hilfskonstrukt fuer die folgende Tabelle, die die Ligatabelle erzeugt. In dieser gibt es keine Verknuepfung zu einzelnen Spielen mehr. Das Hilfskonstrukt hat folglich keine bleibende Bedeutung und koennte aus meiner Sicht bereits gleichermassen reduziert sein wie die Ligatabelle.

Das sind natuerlich theoretische Ueberlegungen. Waehrend der Entwicklung (insbesondere bei noch nicht so erfahrenen Entwicklern) ist es natuerlich ungemein hilfreich, die IDs zu haben, um nachvollziehen zu koennen, was passiert. Einen triftigen Grund darueber hinaus habe ich fuer die ID-Spalte bisher noch nicht erkennen koennen, aber das muss ja nichts heissen.

(Sorry fuer meine harte Analyse. An so einer harten Position kann man sich halt gut abarbeiten. Man sieht dann manchmal klarer ... oder muss klarer sehen, wenn man sich gegen eine so harte Position verteidigen muss. ... Was ich gegen mich selbst ebenso tue. Gut moeglich, dass ich hier viel zu verbissen ran gehe. Aber ein bisschen Gehirnjogging kann ja auch Spass machen. )

[paedubucher]

Antwortversuch: Keine Ahnung. *Ist* sie denn sinnvoll? Wenn die Tabelle nur eine Saison aufnehmen soll (was ohne Saisonspalte zwangslaeufig der Fall sein muss, da man sonst keine sinnvolle Tabelle erzeugen kann), dann kommt jede Kombination aus Heim- und Auswaertsteam nur genau einmal vor (beim Rueckspiel sind die Teams ja getauscht). Wozu also die ID? Ich weiss es nicht. Was das Datenmodell an sich angeht, scheint sie mir unnoetig zu sein. Man wuerde sie mehr aus Bequemlichkeit reinmachen, weil sie wenig kostet und durchaus praktisch sein kann.

Tipp: Ein Verein kann pro Saison mehrmals das gleiche Ergebnis gegen einen anderen Verein erreichen.

Fuer die Tabelle `bundesliga_games' ist das irrelevant. In `bundesliga_per_team' koennte es ohne `id' zu mehrfachen identischen Eintragen kommen ... aber ist das schlimm? Bislang habe ich noch keine technische Notwendigkeit fuer die ID gefunden.

Tabellen und Views sind Mengen. Wenn ich bei der Weiterverarbeitung nur noch die Sicht der Heimmannschaft habe, könnte es aus Sicht von dieser folgende Einträge geben:

1. FC Bayern München | 3 | 0
2. FC Bayern München | 2 | 1
3. FC Bayern München | 1 | 1
4. FC Bayern München | 5 | 0
5. FC Bayern München | 3 | 0

Der erste und der fünfte Eintrag haben den gleichen Wert, also wird das Ergebnis nur noch einmal aufgelistet. Anders sieht es aus, wenn ich das künstliche ID-Attribut mitberücksichtige:

1. 23 | FC Bayern München | 3 | 0
2. 34 | FC Bayern München | 2 | 1
3. 55 | FC Bayern München | 1 | 1
4. 76 | FC Bayern München | 5 | 0
5. 89 | FC Bayern München | 3 | 0

Jetzt sind das erste und das fünfte Element zwei verschiedene Werte und bleiben in der View erhalten.

Hast du es denn mal ohne ID versucht? Geht es dann immer noch? Du schreibst ja nur von ``sinnvoll'' und nicht von ``noetig''.

Ohne ID ging es damals eben nicht: es sind Ergebnisse verschwunden. Die ID zu verwenden ist eine sinnvolle Lösung, da sie das Problem zuverlässig löst. Über "nötig" könnte man jetzt diskutieren, da auch andere Lösungen denkbar wären, z.B. ein zusätzliches Aufführen des Gegners mit der Information, ob es sich um ein Heim- oder um ein Auswärtsspiel handeln würde. (Das würde jedoch nur für Ligen funktionieren, wo die Mannschaften je einmal zu Hause und einmal auswärts auf den gleichen Gegner treffen. In der Schweizer Super League, die wesentlich kleiner ist, gibt es mehrere Aufeinandertreffen.) Eine Alternative wäre ein Spieldatum, das aber in den Ausgangsdaten nicht vorhanden ist.

Von daher ist die ID eine sehr sinnvolle Lösung und wohl die pragmatischste.

[Meillo]

Tabellen und Views sind Mengen.

Ich glaube nicht, dass diese Aussage zutrifft. Gegenbeispiel:

Code: Alles auswählen

mysql> create table foo ( bar int );                                                                                   
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1);                                                                                     
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;                                                                                              
+------+
| bar  |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

Zur Menge wird die Sache erst dadurch, dass du beim Erzeugen der View ein `UNION' verwendest, was ein Mengenoperator ist. Du musst stattdessen `UNION ALL' verwenden, dann bleiben alle Werte erhalten. Die Dokumentation erklaert das gut: https://duckdb.org/docs/sql/query_synta ... -semantics

Von daher ist die ID eine sehr sinnvolle Lösung und wohl die pragmatischste.

Pragmatisch ja, aber sinnvoll nicht, da sie den eigentlichen Sachverhalt zugedeckt hat.

[paedubucher]

Tabellen und Views sind Mengen.

Ich glaube nicht, dass diese Aussage zutrifft. Gegenbeispiel:

Code: Alles auswählen

mysql> create table foo ( bar int );                                                                                   
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1);                                                                                     
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;                                                                                              
+------+
| bar  |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

Zur Menge wird die Sache erst dadurch, dass du beim Erzeugen der View ein `UNION' verwendest, was ein Mengenoperator ist. Du musst stattdessen `UNION ALL' verwenden, dann bleiben alle Werte erhalten. Die Dokumentation erklaert das gut: https://duckdb.org/docs/sql/query_synta ... -semantics

Von daher ist die ID eine sehr sinnvolle Lösung und wohl die pragmatischste.

Pragmatisch ja, aber sinnvoll nicht, da sie den eigentlichen Sachverhalt zugedeckt hat.

Danke für die Erläuterung! UNION ALL kannte ich noch nicht. Dann war es UNION als Mengenoperation, die zum Problem geführt hat, und der eindeutige ID-Wert, durch den die identischen Ergebnisse die UNION haben "überleben" lassen.