======[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