Einleitung

Seit Jahren tanze ich auf dem LAMP-Stack, jedoch meistens nur mit einfachen Queries. Heute möchte ich einen Schritt weiter gehen - mit dem Abbilden einer Sport-Tabelle in MySQL.

Die Regeln

Zur Verdeutlichung: Jedes Team einer Liga hat einen Platz in einer Tabelle/einem Ranking. Diese ist absteigend geordnet: Zunächst nach Punkten, dann nach Tor-Differenz. Wer ein Spiel gewonnen hat, bekommt 3 Punkte, Unentschieden ergibt 1 Punkt und Verlierer eines Matches bekommen 0 Punkte.

Hier einmal die aktuelle Bundesliga-Tabelle:

Bundesliga-Tabelle (Stand: 22.11.2015 12:25PM)

© Google (Stand: 22.11.2015 12:25PM)

Die Datenbank und die Daten

Als Datenbank verwende ich MySQL. Es gibt eigentlich nur zwei Tabellen - teams und matches.

Teams

Zunächst setze ich die teams-Tabelle auf:

CREATE TABLE teams (
	id integer PRIMARY KEY AUTO_INCREMENT,
	name varchar(100)
);

Diese wird dann mit den aktuellen Teilnehmern der 1. Bundesliga befüllt. Dazu nutze ich die openligaDB mit deren IDs - das hilft später beim Übernehmen der Spiele:

<?php

$data = json_decode(file_get_contents("http://www.openligadb.de/api/getmatchdata/bl1/2015"));

echo "INSERT INTO teams (id, name) VALUES \n";

$teams = [];

foreach ($data as $team) {
	
	$Team1 = $match->Team1;
	$Team2 = $match->Team2;

	$teams[$Team1->TeamId] = $Team1->TeamName;
	$teams[$Team2->TeamId] = $Team2->TeamName;
}

foreach ($teams as $id => $name) {
	echo '(' . $id . ', "' . $name . '"), ';
}

Das ausgegebene SQL habe ich dann manuell in die Konsole geworfen - ging mir im Endeffekt schneller, bevor ich den Voodoo mit ext/mysqli aufmache.

Matches

Die Matches-Tabelle enthält die wirklich relevanten Spieldaten: WelcheS Team hat wie viele Tore geschossen und kassiert in jeweils einem Spiel. Das Datenbank-Schema ist denkbar einfach:

CREATE TABLE matches (
	id integer PRIMARY KEY AUTO_INCREMENT,
	Team1_id integer,
	Team1_goals integer,

	Team2_goals integer
);

Die Spiele kommen aus der selben API wie oben, jedoch habe ich aus Einfachheit die Daten zweimal weggespeichert: Erst ist Team1 die Heimmannschaft, dann die Gäste. Dafür das PHP-Skript:

<?php

$data = json_decode(file_get_contents("http://www.openligadb.de/api/getmatchdata/bl1/2015"));

echo "INSERT INTO matches (team1_id, team1_goals, teams2_goals) VALUES \n";

foreach ($data as $match) {
	if(!empty($match->MatchResults)) { //Gibt es schon Ergebnisse für dieses Spiel, oder liegt es bspw. in der Zukunft?

		$Team1 = $match->Team1->TeamId;
		$Team2 = $match->Team2->TeamId;

		$Team1Goals = $match->MatchResults[1]->PointsTeam1;
		$Team2Goals = $match->MatchResults[1]->PointsTeam2;
		
		echo "($Team1, $Team1Goals, $Team2Goals), \n";
		echo "($Team2, $Team2Goals, $Team1Goals), \n";
	}
}

Aufbau der Monster-Query.

Ich habe mich entschieden, diese Query modulweise aufzubauen. So wird ersichtlich, wo welcher Code relevant ist.

Schritt 1: Wir brauchen die Teams und die Anzahl der Spiele, die sie bestritten haben. Dazu erstelle ich eine innere Tabelle im FROM-Befehl. Diese Tabelle nenne ich Game, weil sie die Mathematik hinter jedem einzelnen Spiel verbirgt. Im oberen SELECT-Befehl addiere ich die einzelnen Punkte nur noch auf. Sie enthält für jedes Spiel eine 1 - das Spiel wurde gespielt - und die id des Teams. Anschließend verknüpfe ich diese konstruierte Tabelle mit der teams.id, um das einzelne Game einer Mannschaft zuordnen zu können:

SELECT
	teams.name,
	Sum(GamesPlayed) AS GamesPlayed
FROM (
	SELECT
		Team1_id Team,
		1 GamesPlayed
	FROM matches
) AS Game

JOIN teams ON teams.id = Game.Team
GROUP BY teams.id
;

Schritt 2: Nun wäre es gut zu wissen, wie oft die Mannschaft gewonnen hat (also Team1_Goals > Team2_Goals). Dazu nutze ich die If-Funktion, weil die Werte relativ “einfach” sind:

SELECT
	teams.name,
	Sum(GamesWon) AS GamesWon
FROM (
	SELECT
		Team1_id Team,
		IF(Team1_Goals > Team2_Goals, 1, 0) GamesWon
	FROM matches
) AS Game

JOIN teams ON teams.id = Game.Team
GROUP BY teams.id
;

Schritt 2.1: Nach diesem Schema kann ich nun die anderen beiden Spielausgänge (Gleichstand/Draw mit Team1_Goals = Team2_Goals und Niederlage/Lost mit Team1_Goals < Team2_Goals) in die Abfrage schreiben:

SELECT
	teams.name,
	Sum(GamesWon) AS GamesWon,
	Sum(GamesDraw) AS GamesDraw,
	Sum(GamesLost) AS GamesLost
	
FROM (
	SELECT
		Team1_id Team,
		IF(Team1_Goals > Team2_Goals, 1, 0) GamesWon,
		IF(Team1_Goals = Team2_Goals, 1, 0) GamesDraw,
		IF(Team1_Goals < Team2_Goals, 1, 0) GamesLost
	FROM matches
) AS Game

JOIN teams ON teams.id = Game.Team
GROUP BY teams.id
;

Schritt 3: Die Tordifferenz (Team1_Goals - Team2_Goals GoalDiff) hinzuzufügen, ist in diesem Zusammenhang dann auch kein Problem mehr:

SELECT
	teams.name,
	Sum(GoalDiff) AS GoalDiff
	
FROM (
	SELECT
		Team1_id Team,
		Team1_Goals - Team2_Goals GoalDiff
	FROM matches
) AS Game

JOIN teams ON teams.id = Game.Team
GROUP BY teams.id
;

Schritt 4: Das Letzte - und Entscheidene ist der Punktestand. Hier nutze ich die Case-Syntax, weil es drei Zustände mit drei unterschiedlichen Ausgängen gibt.

SELECT
	teams.name,
	Sum(Points) AS Points
	
FROM (
	SELECT
		Team1_id Team,
		CASE
			WHEN Team1_Goals > Team2_Goals THEN 3
			WHEN Team1_Goals = Team2_Goals THEN 1
			ELSE 0
	     END Points
	FROM matches
) AS Game

JOIN teams ON teams.id = Game.Team
GROUP BY teams.id
;

Schritt 5: Die komplette Query mit allen relevanten Daten sieht dann also am Ende so aus. Die Tabelle wird zunächst nach den Punkten und dann nach der Tordifferenz sortiert:

SELECT
	teams.name,
	Sum(GamesPlayed) AS GamesPlayed,
	Sum(GamesWon) AS GamesWon,
	Sum(GamesDraw) AS GamesDraw,
	Sum(GamesLost) AS GamesLost,
	Sum(GoalDiff) AS GoalDiff,
	Sum(Points) AS Points
FROM (
	SELECT
		Team1_id Team,
		1 GamesPlayed,

		IF(Team1_Goals > Team2_Goals, 1, 0) GamesWon,
		IF(Team1_Goals = Team2_Goals, 1, 0) GamesDraw,
		IF(Team1_Goals < Team2_Goals, 1, 0) GamesLost,

		Team1_Goals - Team2_Goals GoalDiff,

		CASE
			WHEN Team1_Goals > Team2_Goals THEN 3
			WHEN Team1_Goals = Team2_Goals THEN 1
			ELSE 0
		 END Points
	FROM matches
) AS Game
JOIN teams
	ON teams.id = Game.Team
GROUP BY
	teams.id
ORDER BY
	Points DESC,
	GoalDiff DESC
;

Das Ergebnis

Am Ende bekomme ich also eine Tabelle, die man so quasi ins Sportmagazin abdrucken könnte:

name GamesPlayed GamesWon GamesDraw GamesLost GoalDiff Points
Bayern München 13 12 1 0 35 37
Borussia Dortmund 13 9 2 2 18 29
VfL Wolfsburg 13 7 3 3 7 24
Borussia Mönchengladbach 13 7 1 5 6 22
Hertha BSC 12 6 2 4 1 20
Bayer 04 Leverkusen 13 6 2 5 -2 20
Hamburger SV 13 6 2 5 -2 20
FC Schalke 04 13 6 2 5 -2 20
1. FC Köln 13 5 4 4 -3 19
1. FSV Mainz 05 13 5 2 6 -1 17
FC Ingolstadt 04 12 4 4 4 -2 16
Eintracht Frankfurt 13 3 5 5 -3 14
SV Darmstadt 98 12 3 4 5 -4 13
Werder Bremen 13 4 1 8 -10 13
Hannover 96 13 3 2 8 -10 11
TSG 1899 Hoffenheim 12 2 4 6 -5 10
FC Augsburg 13 2 3 8 -8 9
VfB Stuttgart 13 3 0 10 -15 9

Am Ende bleibt es mir nur noch, den Ursprung der Abfrage zu verlinken.

Avatar

Veröffentlicht am von Jannik

open source Technik

19 | der blasse, dünne Junge aus der Nachbarschaft | Macht Web Design, Theatertechnik und Pfadfinder | Ist #Wö‑Leiter | Studiert was mit Medien