======[MySQL] Belegungsplan====== {{include MySQLInfo}} Ein häufiger Fall. Wir haben verschiedene Objekte. Und wir haben ein Stundenplan. Zudem haben wir die Reservationen. Nun wollen wir eine schöne Ansicht, wie die Objekte im Moment Belegt sind. Ein typisches Beispiel sind Spielfelder in einer Tennis-/Squash-Plätze. =====Tabellen===== Als erste habe ich mal 3 Tabellen: objekte: Alle Plätze perioden: Alle perioden die es pro Tag gibt. Diese definieren sich über die Startzeit belegung: Belegung der Plätze. [objekte] id | desc ---|-------- 1 | Platz 1 2 | Platz 2 [perioden] startzeit (TIMESTAMP) --------- 08:00:00 08:30:00 09:00:00 09:30:00 10:00:00 10:30:00 11:00:00 11:30:00 12:00:00 12:30:00 13:00:00 13:30:00 14:00:00 14:30:00 15:00:00 15:30:00 16:00:00 16:30:00 17:00:00 [belegung] id | object_id | user_id | anfangszeit (TIMESTAMP) | endzeit (TIMESTAMP) ---|-----------|---------|-------------------------|-------------------- 1 | 1 | 12 | 2011-03-30 09:30:00 | 2011-03-30 10:30:00 2 | 1 | 11 | 2011-03-31 09:30:00 | 2011-03-31 11:00:00 3 | 1 | 11 | 2011-03-29 16:30:00 | 2011-03-30 08:30:00 4 | 1 | 13 | 2011-03-28 14:00:00 | 2011-03-28 14:00:00 Was ich jetzt haben will ist die Belegung aller Objekte auf einen Stichtag. =====Gewünschtes Resultat===== Als Beispiel: Heute ist der 30. März 2011. Dann sieht mit den obigen Daten die Ausgabe wie folgt aus datum | object_id | perioden_start | gebucht | user_id 2011-03-30 | 1 | 08:00:00 | belegt | 11 2011-03-30 | 1 | 08:30:00 | belegt | 11 2011-03-30 | 1 | 09:00:00 | frei | 2011-03-30 | 1 | 09:30:00 | belegt | 12 2011-03-30 | 1 | 10:00:00 | belegt | 12 2011-03-30 | 1 | 10:30:00 | belegt | 12 2011-03-30 | 1 | 11:00:00 | frei | 2011-03-30 | 1 | 11:30:00 | frei | 2011-03-30 | 1 | 12:00:00 | frei | 2011-03-30 | 1 | 12:30:00 | frei | 2011-03-30 | 1 | 13:00:00 | frei | 2011-03-30 | 1 | 13:30:00 | frei | 2011-03-30 | 1 | 14:00:00 | frei | 2011-03-30 | 1 | 14:30:00 | frei | 2011-03-30 | 1 | 15:00:00 | frei | 2011-03-30 | 1 | 15:30:00 | frei | 2011-03-30 | 1 | 16:00:00 | frei | 2011-03-30 | 1 | 16:30:00 | frei | 2011-03-30 | 1 | 17:00:00 | frei | 2011-03-30 | 2 | 08:00:00 | frei | 2011-03-30 | 2 | 08:30:00 | frei | 2011-03-30 | 2 | 09:00:00 | frei | 2011-03-30 | 2 | 09:30:00 | frei | 2011-03-30 | 2 | 10:00:00 | frei | 2011-03-30 | 2 | 10:30:00 | frei | 2011-03-30 | 2 | 11:00:00 | frei | 2011-03-30 | 2 | 11:30:00 | frei | 2011-03-30 | 2 | 12:00:00 | frei | 2011-03-30 | 2 | 12:30:00 | frei | 2011-03-30 | 2 | 13:00:00 | frei | 2011-03-30 | 2 | 13:30:00 | frei | 2011-03-30 | 2 | 14:00:00 | frei | 2011-03-30 | 2 | 14:30:00 | frei | 2011-03-30 | 2 | 15:00:00 | frei | 2011-03-30 | 2 | 15:30:00 | frei | 2011-03-30 | 2 | 16:00:00 | frei | 2011-03-30 | 2 | 16:30:00 | frei | 2011-03-30 | 2 | 17:00:00 | frei | =====Lösung===== Und so sieht eine mögliche Lösung aus: SELECT datum, object_id, startzeit AS perioden_start, IF(SUM(state), 'belegt', 'frei') AS gebucht, MAX(user_id) AS user_id FROM ( SELECT var.datum, per.startzeit, per.object_id, @state := TIMESTAMP(var.datum, per.startzeit) BETWEEN bel.anfangszeit AND bel.endzeit AS state, IF(@state, bel.user_id, NULL) AS user_id FROM -- hier kann man auch ein beliebiges anderes Datum (ohne Zeitstempfel) setzen (SELECT CURDATE() AS datum) AS var, ( --Alle Perioden mit allen Objekten kombiniern SELECT startzeit, objekte.id AS object_id FROM perioden, objekte ) AS per --Diese Kombination mit den Belegungen erweitern LEFT JOIN belegung AS bel ON bel.object_id = per.object_id ) AS rohdaten GROUP BY object_id, startzeit, datum =====Erklärbär ===== Das ganze ist in drei Schichten aufgebaut Im inneren beginne wir mit einer Kombination aller Perioden mit allen Objekten [Perioden pro Objekt] SELECT startzeit, objekte.id AS object_id FROM perioden, objekte Diese werden nun mittels eines LEFT-JOINS über die object_id mit den Belegungen kombiniert. Hier kommt auch unser Stich-Tag zum Einsatz. Im Subquery [var] wird der Stichtag im Format DATE ausgegeben. Dies hat den Vorteil, dass wir beim auswählen des Stichtages nur an einem einzigen Ort im SQ dies anpassen müssen. Zugleich prüfen wir, ob Periode innerhalb der Belegung ist und geben das als [state] aus. Falls das Objekt belegt ist, geben wir auch die user_id aus, damit wir gleich sehen durch wen. [Rohdaten] SELECT var.datum, per.startzeit, per.object_id, -- TIMESTAMP(var.datum, per.startzeit): erstellt aus der startzeit und dem Datum ein TIMESTAMP @state := TIMESTAMP(var.datum, per.startzeit) BETWEEN bel.anfangszeit AND bel.endzeit AS state, IF(@state, bel.user_id, NULL) AS user_id FROM --hier kann man auch ein beliebiges anderes Datum (Format DATE) setzen (SELECT CURDATE() AS datum) AS var, ([Perioden pro Objekt]) AS per --Diese Kombination mit den Belegungen erweitern LEFT JOIN belegung AS bel ON bel.object_id = per.object_id Dies erzeugt uns aber noch zu viele Datensätze. Und zwar ist das [Anzahl Perioden pro Objekt]*[Anzahl user_id pro Objekt] Also müssen wir das ganze noch zusammenfassen SELECT datum, object_id, startzeit AS perioden_start, IF(SUM(state), 'belegt', 'frei') AS gebucht, MAX(user_id) AS user_id FROM ([Rohdaten]) AS rohdaten GROUP BY object_id, startzeit, datum =====Weitere Möglichkeiten===== Wenn man ganz genau weiss, wieviele Objekte man hat und sich diese nicht verändern, kann man auch eine Kreuztabelle erstellen, welche dann pro Periode eine Zeile hat in der jedes Objekt aufgelistet wird. Dazu müssen wir nur das äusserste SQL umschreiben datum, startzeit AS perioden_start, IF(SUM(state), 'belegt', 'frei') AS gebucht, IF(SUM(IF(object_id = 1, state, false)), 'belegt', 'frei') AS gebucht_1, IF(SUM(IF(object_id = 2, state, false)), 'belegt', 'frei') AS gebucht_2, IF(SUM(IF(object_id = 3, state, false)), 'belegt', 'frei') AS gebucht_3 FROM ([Rohdaten]) AS rohdaten GROUP BY startzeit, datum; Das Resultat sieht dann folgendermassen aus datum | perioden_start | gebucht_1 | gebucht_2 | gebucht_3 -----------|----------------|-----------|-----------|---------- 2011-03-30 | 08:00:00 | belegt | frei | frei 2011-03-30 | 08:30:00 | belegt | frei | frei 2011-03-30 | 09:00:00 | frei | frei | frei 2011-03-30 | 09:30:00 | belegt | frei | frei 2011-03-30 | 10:00:00 | belegt | frei | frei 2011-03-30 | 10:30:00 | belegt | frei | frei 2011-03-30 | 11:00:00 | frei | frei | frei 2011-03-30 | 11:30:00 | frei | frei | frei 2011-03-30 | 12:00:00 | frei | frei | frei 2011-03-30 | 12:30:00 | frei | frei | frei 2011-03-30 | 13:00:00 | frei | frei | frei 2011-03-30 | 13:30:00 | frei | frei | frei 2011-03-30 | 14:00:00 | frei | frei | frei 2011-03-30 | 14:30:00 | frei | frei | frei 2011-03-30 | 15:00:00 | frei | frei | frei 2011-03-30 | 15:30:00 | frei | frei | frei 2011-03-30 | 16:00:00 | frei | frei | frei 2011-03-30 | 16:30:00 | frei | frei | frei 2011-03-30 | 17:00:00 | frei | frei | frei