[MySQL] Bereken maximale bezetting in een opgegeven periode

[Wolf3D]

Enige tijd geleden ben ik helemaal vastgelopen op een bepaalde query, welke ik niet voor elkaar krijg.
Na gisteren een hele dag gestoeid en ge-Googled te hebben, ben ik inmiddels een paar stapjes dichterbij. Ik heb echter nog het laatste duwtje nodig....

Probleembeschrijving:
Ik heb een object dat kan worden gereserveerd. Een gebruiker heeft maximaal één object en dat object kan een X aantal (object.amount) keer worden gereserveerd. Ook kan de gebruiker vooraf opgeven op welke datums het object beschikbaar is (object.start / object.end). Beschikbaarheid en reserveringen worden gedaan in blokken van 30 minuten.
Nu wil ik uit de data kunnen halen welk object in een bepaalde periode nog gereserveerd kan worden en de beschikbaarheid (available in onderstaande query) groter is dan 0.

Een exportje van mijn test db:

SQL:

CREATE TABLE IF NOT EXISTS `object` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `amount` int(11) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `object` (`id`, `name`, `amount`, `start`, `end`) VALUES
(1, 'Voorwerp 1', 3, '2015-07-12 12:00:00', '2015-07-12 18:00:00'),
(2, 'Voorwerp 2', 4, '2015-07-12 10:00:00', '2015-07-12 15:00:00');

CREATE TABLE IF NOT EXISTS `reservation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `object_id` int(11) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `reservation` (`id`, `object_id`, `start`, `end`, `status`) VALUES
(1, 1, '2015-07-12 14:00:00', '2015-07-12 15:00:00', 1),
(2, 1, '2015-07-12 12:00:00', '2015-07-12 17:30:00', 1),
(3, 1, '2015-07-12 16:00:00', '2015-07-12 17:30:00', 1),
(4, 1, '2015-07-12 12:00:00', '2015-07-12 12:30:00', 1);

De query die ik nu nog gebruik maar niet werkt:

SQL:

SELECT GROUP_CONCAT(r.id) reservations_ids, o.amount total, SUM(CASE WHEN r.id IS NOT NULL THEN 1 ELSE 0 END) total_reservations, (o.amount - SUM(CASE WHEN r.id IS NOT NULL THEN 1 ELSE 0 END)) available
FROM object o
LEFT JOIN reservation r ON r.object_id = o.id
  /* Only join reservations that are within the object availability */
  AND (r.start >= o.start AND  r.end < o.end)
  /* Only join reservations that are made for the given datetimes */
  AND (
      /* where given start date is between start and end */
    ('2015-07-12 14:00' >= r.start AND '2015-07-12 14:00' < r.end) 
      OR 
      /* or where given end date is between start and end */
    ('2015-07-12 17:00' >= r.start AND '2015-07-12 17:00' < r.end)
      OR
      /* or where given start date is smaller than start and given end date is bigger than end. */
    ('2015-07-12 14:00' <= r.start AND '2015-07-12 17:00' > r.end)
  )
  AND r.status = 1
WHERE 1=1
  /* Check if object is available at all */
  AND o.start <= '2015-07-12 14:00' AND o.end >= '2015-07-12 17:00'

In bovenstaande query gebruik ik overigens geen BETWEEN omdat in mijn situatie de eind tijd eigenlijk loopt tot :59 of :29.

Als bovenstaande query wordt uitgevoerd dan krijg je terug: available = 0, voor de periode van 12:00 tot 18:00. Terwijl het object eigenlijk nog 1x beschikbaar is. Dit heeft te maken met de overlap. Op het moment dat twee boekingen elkaar niet overlappen telt deze eigenlijk maar voor één boeking.

Op http://stackoverflow.com/...nt-calls-in-a-time-period heb ik een gelijksoortig probleembeschrijving gevonden. Ook zijn ASCII-weergave van het probleem is misschien duidelijker. Echter de gegeven oplossingen daar krijg ik niet aan de praat in mijn situatie. Ook probeer ik de oplossingen gegeven op http://stackoverflow.com/...ative-sum-column-in-mysql erbij te betrekken. Maar het lukt me maar niet.

Ik heb inmiddels dit:

SQL:

SELECT date, x.t, @runtot := (@runtot + x.t) AS rt, @runtot := x.t AS ne
FROM (SELECT (@runtot := 0)) c, (
SELECT start AS date, 1 as t 
FROM reservation r
WHERE start BETWEEN '2015-07-12 12:00:00' AND '2015-07-12 18:00:00'
UNION ALL
SELECT end AS date, -1 as t
FROM reservation r
WHERE end BETWEEN '2015-07-12 12:00:00' AND '2015-07-12 18:00:00'
ORDER BY date
) x

In de kolom 'rt' wordt volgens mij alleen de vorige row waarde gebruikt. Wat nu de volgende waardes oplevert:

code:

date                     t  rt  ne
2015-07-12 12:00:00      1   1   1
2015-07-12 12:00:00      1   2   1
2015-07-12 12:30:00     -1   0  -1
2015-07-12 14:00:00      1   0   1
2015-07-12 15:00:00     -1   0  -1
2015-07-12 16:00:00      1   0   1
2015-07-12 17:30:00     -1   0  -1
2015-07-12 17:30:00     -1  -2  -1

De kolom 'rt' lijkt alleen te kijken naar de vorige rij maar ik wil dat hij doorrekent, zodat ik vervolgens via MAX(rt) kan zien wat de maximale bezetting tijdens een periode is.

Wat ik wil zien is dus eigenlijk:

code:

date                     t  rt  ne
2015-07-12 12:00:00      1   1   1
2015-07-12 12:00:00      1   2   1
2015-07-12 12:30:00     -1   1  -1
2015-07-12 14:00:00      1   2   1
2015-07-12 15:00:00     -1   1  -1
2015-07-12 16:00:00      1   2   1
2015-07-12 17:30:00     -1   1  -1
2015-07-12 17:30:00     -1   0  -1

Iemand die mij de laatste zet kan geven?

[Douweegbertje]

Wat is nu T, RT en NE?

Kun je iets duidelijker zijn over wat je nu precies aan data terug wilt hebben?

[Wolf3D]

Ik kan me voorstellen dat het niet helemaal duidelijk is/was.
t = Zodra een reservering start wordt dit de waarde 1. Zodra een reservering stop wordt het -1.
Om 12:00 starten twee reserveringen (dus twee keer 1). Vervolgens eindigt er een reservering om 12:30 (dus -1). Om 14:00 start weer een nieuwe reservering (dus 1) en om 15:00 eindigt er weer eentje (dus -1). Enz....

rt = Cumulatief opgetelde waardes uit t. Dus rt van de huidige rij is rt van de vorige rij + t uit de huidige rij.
Tenminste, dat is wat ik wil. Niet wat er nu gebeurt.
Wat ik wil is dat ik alle t waardes bij elkaar optel, stap voor stap en daar dan de hoogste waarde van terugkrijg.
Dus kijkend naar de t kolom zijn de waardes nu:
1, 1, -1, 1, - 1, 1, -1, -1
Als ik die domweg bij elkaar optel (SUM) dan is de uitkomst 0. Echter wil ik de MAX waarde weten die de SUM is geweest tijdens de berekening.
Als het een grafiek zou zijn waarbij elke waarde in t zou bepalen of de grafiek omhoog of omlaag gaat, dan wil ik aan het einde weten wat de hoogste stand van de grafiek is geweest.

Ik merk dat ik moeite heb om het uit te leggen..... excuses. Hopelijk is het nu wel duidelijk.

Waar mijn probleem op neerkomt is dat ik een methode zoek waarmee ik bepaal of een object nog gereserveerd kan worden binnen een bepaalde periode. En wat het maximaal aantal reserveringen is welke tegelijkertijd plaatsvinden.

Als ik het vergelijk met een kamer met bedden waarbij een kamer 2 bedden heeft en ik verhuur één van de bedden voor 12:00 tot 14:00 en nog eentje voor 15:00 tot 18:00. Dan heb ik dus over de periode 12:00 tot 18:00 constant een bed beschikbaar. Echter als ik via SQL zou zoeken op het aantal reserveringen tussen 12:00 en 18:00 dan krijg ik het resultaat 2 terug. Waarbij 2 bedden - 2 reserveringen = 0 bedden. Dit klopt dus niet. De query met rt is een poging om wel de juiste waarde te krijgen.

[DJMaze]

Je query is afhankelijk van datum EN tijd.
Je moet dus alles in een tijdeenheid verwerken.

Stel je grafiek is per half uur, dan moet je querien op:

SELECT WHERE start BETWEEN '2015-07-12 00:00:00' AND '2015-07-13 00:00:00'
En dan afrekken in:
00:00 = 2
00:30 = 2
01:00 = 2
01:30 = 2
02:00 = 2
02:30 = 2
etc....

In in die lijst dan de min en max zoeken.

Dit kan ook met procedures, maar niet in MySQL

Bijvoobeeld:
http://stackoverflow.com/...f-dates-between-two-dates
Maar dit is lastig in MySQL

code:

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
select * from time_intervals JOIN reserveringen ON

[ Voor 109% gewijzigd door DJMaze op 30-07-2015 02:11 ]

[Guillome]

SQL:

SET @date = '2015-07-12';
SET @timeStart = '14:00';
SET @timeEnd = '15:00';

select CONCAT(@date, ' ', @timeStart) start, CONCAT(@date, ' ', @timeEnd) end, obj.*
from object obj
left join reservation res on res.object_id = obj.id and res.start >= CONCAT(@date, ' ', @timeStart) and res.end < CONCAT(@date, ' ', @timeEnd)

where obj.start <= CONCAT(@date, ' ', @timeStart) and obj.end >= CONCAT(@date, ' ', @timeEnd)

group by obj.id
having count(res.Id) < obj.amount

Is dit niet ongeveer wat je zoekt?
Volgens mij doe je te moeilijk Of ik las niet goed.

[ Voor 203% gewijzigd door Guillome op 30-07-2015 10:53 ]

[NMe]

Guillome schreef op donderdag 30 juli 2015 @ 10:35:
Is dit niet ongeveer wat je zoekt?
Volgens mij doe je te moeilijk Of ik las niet goed.

Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

[Guillome]

Wat bedoel je daarmee NMe?

[radem205]

Ik denk dat NMe bedoelt dat je de GROUP BY niet correct toepast.

De kolommen die benoemd worden in de SELECT én niet in de GROUP BY voorkomen dienen voorzien te zijn van een aggregate-functie (MAX(), MIN(), SUM(), etc.). In jouw query wordt obj.* gebruikt, wat betekent dat voorgaande niet opgaat.

Zie: https://dev.mysql.com/doc...n/group-by-functions.html

[ Voor 15% gewijzigd door radem205 op 30-07-2015 14:51 ]

[Guillome]

Dat weet ik idd ook wel, ik vond het als voorbeeld voor het probleem duidelijk genoeg.

[NMe]

Guillome schreef op donderdag 30 juli 2015 @ 14:49:
Dat weet ik idd ook wel, ik vond het als voorbeeld voor het probleem duidelijk genoeg.

Voor iemand die een dergelijk probleem heeft en daarbij niet zelf al aan group by gedacht lijkt te hebben lijkt me dat niet duidelijk genoeg, nee.

[Guillome]

Oneens. Maar dat boeit niet. Hopelijk is TS wel blij met mijn inbreng

[Wolf3D]

Allereerst ben ik blij met alle inbreng. Dus alvast bedankt!

Ik zie echter niet in hoe een GROUP BY mij verder gaat helpen. Ik snap dat met GROUP BY + SUM() alle waardes uit (rt) bij elkaar opgeteld kunnen worden. Echter geeft me dat niet het juiste resultaat.
Of misschien snap ik jouw (Guillome) voorbeeld niet helemaal?
Als ik namelijk met jouw query startdatum op 12:00 zet en einddatum op 18:00, dan krijg ik geen resultaten terug. Echter zou er wel 1 resultaat moeten terugkomen, namelijk die van object 1.

[Guillome]

Nee hoor, object 1 mag 3 keer verhuurd worden, en wordt al 4 keer verhuurd tussen 12 uur en 6 uur.
De sum() vergelijk ik met de amount van het object

Overigens, als je het niet echt nodig hebt, is INNODB een trage engine. MyISAM is vaak sneller

[ Voor 32% gewijzigd door Guillome op 30-07-2015 15:11 ]

[Wolf3D]

Precies daar ligt het probleem.

Wolf3D schreef op woensdag 29 juli 2015 @ 16:10:
Als ik het vergelijk met een kamer met bedden waarbij een kamer 2 bedden heeft en ik verhuur één van de bedden voor 12:00 tot 14:00 en nog eentje voor 15:00 tot 18:00. Dan heb ik dus over de periode 12:00 tot 18:00 constant een bed beschikbaar. Echter als ik via SQL zou zoeken op het aantal reserveringen tussen 12:00 en 18:00 dan krijg ik het resultaat 2 terug. Waarbij 2 bedden - 2 reserveringen = 0 bedden. Dit klopt dus niet. De query met rt is een poging om wel de juiste waarde te krijgen.

Object 1 mag dus niet eenvoudig 3x maximaal worden verhuurd, maar mag een maximale bezetting hebben van 3x.

Om nog een keer terug te gaan naar de bedden van hierboven. Stel dat ik een kamer beschikbaar stel met 2 bedden van 12:00 tot 18:00, en er wordt een bed van 12:00 tot 13:00 gereserveerd en eentje van 13:00 tot 14:00. Dan heb ik van 12:00 tot 14:00 maximaal 1 bed in gebruik en na 14:00 zijn zelfs alle bedden vrij. Via GROUPBY + MAX() worden gewoon alle reserveringen op een hoop gegooid en krijg ik terug dat alle bedden van die kamer van 12:00 tot 18:00 bezet zijn.

[Guillome]

Ah ja je bedoelt zo`n visualisatie?



Een stuk lastiger. Heb zelf ook zoiets gebouwd.
Ik heb er een class van gemaakt in php.
Deze vul ik met de criteria, en dan daar de logica instoppen.
Anders krijg je onbeheersbare queries.

[ Voor 13% gewijzigd door Guillome op 30-07-2015 16:04 ]

[Wolf3D]

Ja, die visualisatie geeft het wel goed weer. Alleen die getalletjes binnen de zwarte balken snap ik niet helemaal. Je snapt het nu in ieder geval!

Zoals ik zei in mijn openingspost, zijn er wel meerdere gebruikers die het volgens mij hebben weten op te lossen via SQL (zie links naar stackoverflow). Echter lukt mij dat (net) niet, terwijl ik volgens mij een eind was. Of ... lijkt dat maar zo?

[Guillome]

Die getalletjes zijn je registratie-ids

En ik ga me er niet aan wagen om dat in SQL only op te lossen.
Er komt altijd logica bij, en dan ben je op een gegeven moment de draad kwijt met een mega query.
En moet je op een gegeven moment toch opsplitsen in kleinere blokken.

[ Voor 73% gewijzigd door Guillome op 30-07-2015 16:52 ]

[DJMaze]

In FirebirdSQL gebruik ik een procedure

code:

CREATE PROCEDURE DAYRANGE (
  SDATE Date, 
  EDATE Date)
 returns (
  ADAY Date)
AS
BEGIN
    ADAY = SDATE;
    WHILE (SDATE < EDATE) DO BEGIN
        SUSPEND;
        SDATE = SDATE+1;
        ADAY = SDATE;
    END
END

In FirebirdSQL kan ik die dan als VIEW/TABLE gebruiken.

code:

SELECT
    ADAY,
    RESERVERINGEN.*
FROM DAYRANGE('2015-01-01', '2015-07-01')
LEFT JOIN reservation ON (start <= ADAY AND end >= ADAY)

Maar zoals ik al zei, dit werkt niet in MySQL, hiervoor moet je even op zoek hoe je het resultaat in een temporary table kan stoppen die alleen voor die transaction beschikbaar is (het gaat dus om de interval).

[ Voor 10% gewijzigd door DJMaze op 30-07-2015 21:25 ]

[gekkie]

DJMaze schreef op donderdag 30 juli 2015 @ 21:17:
In FirebirdSQL gebruik ik een procedure

code:

CREATE PROCEDURE DAYRANGE (
  SDATE Date, 
  EDATE Date)
 returns (
  ADAY Date)
AS
BEGIN
    ADAY = SDATE;
    WHILE (SDATE < EDATE) DO BEGIN
        SUSPEND;
        SDATE = SDATE+1;
        ADAY = SDATE;
    END
END

In FirebirdSQL kan ik die dan als VIEW/TABLE gebruiken.

code:

SELECT
    ADAY,
    RESERVERINGEN.*
FROM DAYRANGE('2015-01-01', '2015-07-01')
LEFT JOIN reservation ON (start <= ADAY AND end >= ADAY)

Maar zoals ik al zei, dit werkt niet in MySQL, hiervoor moet je even op zoek...

Hebben andere database dan geen series generator zoals in postgresql a la:

generate_series('2014-01-01 00:00'::timestamptz, current_date::timestamptz, '1 day') as day

gebruik dat nogal vaak voor timeseries data met een coalesce(, default) voor als er op dat tijdstip geen waarde in de DB is.

En between zuigt apenootjes is mijn bescheiden mening aangezien *beide* grenzen inclusief zijn.
Waarbij je dus de kans loopt bij een query op de volgende timerange een waarde op de grens weer mee te nemen.

[ Voor 3% gewijzigd door gekkie op 30-07-2015 21:26 ]

[DJMaze]

gekkie schreef op donderdag 30 juli 2015 @ 21:25:
Hebben andere database dan geen series generator zoals in postgresql a la:

generate_series('2014-01-01 00:00'::timestamptz, current_date::timestamptz, '1 day') as day

gebruik dat nogal vaak voor timeseries data met een coalesce(, default) voor als er op dat tijdstip geen waarde in de DB is.

Helaas niet!
Neemt niet weg dat mensen het proberen met Oracle's "dual", SQL Server's recursive "with" of deze MySQL poging: http://stackoverflow.com/...eries-equivalent-in-mysql

Helaas is MySQL gewoon niet geschikt en moet je het in code oplossen.
En dat is best simpel met DateTime en DateTimeInterval in PHP

[ Voor 8% gewijzigd door DJMaze op 30-07-2015 21:32 ]

[gekkie]

DJMaze schreef op donderdag 30 juli 2015 @ 21:29:
[...]
Helaas niet!
Neemt niet weg dat mensen het proberen met Oracle's "dual", SQL Server's recursive "with" of deze MySQL poging: http://stackoverflow.com/...eries-equivalent-in-mysql

Hmm voor timeseries echt onhandig (wel handig als het timezone loos is je er in postgres fijn een index op kunt zetten)
Achja postgresql ontbeerd ook weer dingen die andere DB's wel hebben de UPSERT (nu nog komt in 9,5) ... en ik mis persoonlijk nog wat array functions.

[DJMaze]

offtopic:
[quote]gekkie schreef op donderdag 30 juli 2015 @ 21:32:
Achja postgresql ontbeerd ook weer dingen die andere DB's wel hebben de UPSERT (nu nog komt in 9,5) ... en ik mis persoonlijk nog wat array functions.
[/quote]
True, GROUP_CONCAT() in MySQL is ook handig.

[gekkie]

Voorbeeldje in Postgresql met generate_series:

SQL:

WITH x AS (
    WITH w AS (
        SELECT
            EXTRACT(EPOCH FROM DATE_TRUNC('milliseconds', timerange.timestamp_utc AT TIME ZONE 'UTC')) * 1000 AS timestamp_utc,
            timerange.query_type::TEXT AS query_type,
            COUNT(db_statistics.db_queries.query_type) AS nr_queries
        FROM (
            SELECT
                UNNEST(ENUM_RANGE(NULL::db_statistics.query_type)) AS query_type,
                GENERATE_SERIES(DATE_TRUNC('second', %(from_time)s), DATE_TRUNC('second', %(until_time)s), '1 second') AS timestamp_utc
            GROUP BY query_type
    ) AS timerange
        LEFT OUTER JOIN db_statistics.db_queries ON
            timerange.timestamp_utc = DATE_TRUNC('second', db_statistics.db_queries.query_timestamp_utc)
            AND
            timerange.query_type = db_statistics.db_queries.query_type
        WHERE
            DATE_TRUNC('second', timestamp_utc) > DATE_TRUNC('second', %(from_time)s)
            AND
            DATE_TRUNC('second', timestamp_utc) <= DATE_TRUNC('second', %(until_time)s)
        GROUP BY timerange.query_type, timerange.timestamp_utc
        ORDER BY timerange.timestamp_utc
    )
    SELECT *
    FROM w
    UNION ALL
        SELECT
            w.timestamp_utc AS timestamp_utc,
            'total' AS query_type,
            COALESCE(SUM(w.nr_queries)::BIGINT, 0) AS nr_queries
        FROM w
        GROUP BY w.timestamp_utc
        ORDER BY timestamp_utc
)
SELECT
    x.query_type AS serie_label,
    JSON_AGG(x) AS serie_data
FROM x
GROUP BY x.query_type

poept data uit die nagenoeg zo als json naar de client kan en de grafiek in.

[ Voor 15% gewijzigd door gekkie op 30-07-2015 21:42 ]

[Wolf3D]

Bedankt voor jullie reactie.
Ik ben inderdaad bang dat ik het gewoon via PHP moet gaan oplossen.
Hoewel ik dan goed moet kijken om alles zo efficiënt mogelijk te laten doen heb ik in ieder geval meer vrijheid en hou ik het iets overzichtelijker....

Thanks!

[BramV]

Wel mogelijk denk ik Bezig met eerste poging fietsverhuur lijkt er misschien op:
- totaal aantal fietsen beschikbaar per dag - periode (op basis van individuele beschikbare fietsen per dag-periode, individuele fiets kan weg zijn voor reparatie of ander filiaal
- hulptabel voor periode (in dit geval tabel per half uur )

SQL:

 
select 
    rent_date,period,Voorraad,count(fietsverhuur.naam) as Verhuurd,
    (Voorraad - count(fietsverhuur.naam)) as Beschikbaar,
    IFNULL(group_concat(fietsverhuur.naam), '-') as ResIds
from
    (select 
        available . *,
            IFNULL(sum(fietsen.available), 0) as Voorraad
    from
        (select distinct
        fietsen.rent_date, timeslot.period_start, timeslot.period
    from
        fietsen
    join timeslot
    group by rent_date , timeslot.period) as available
    left outer join fietsen ON fietsen.rent_date = available.rent_date
        and fietsen.start_time <= available.period_start
        and fietsen.end_time - 1 > available.period_start
    group by available.rent_date , period_start) as InStore
        left outer join
    fietsverhuur ON fietsverhuur.start_date = InStore.rent_date
        and fietsverhuur.rent_start <= Instore.period_start
        and fietsverhuur.rent_end - 1 > InStore.period_start
group by rent_date , period_start

 

code:

vr=voorraad
vh=verhuurd
bs=beschikbaar



 huurdatum   tijd   vr  vh  bs  resid 
2015-01-01  10:30   2   0   2   -
2015-01-01  11:00   2   0   2   -
2015-01-01  11:30   2   0   2   -
2015-01-01  12:00   2   1   1   res 1
2015-01-01  12:30   2   1   1   res 1
2015-01-01  13:00   2   2   0   res 1,res 3
2015-01-01  13:30   2   2   0   res 3,res 1
2015-01-01  14:00   2   1   1   res 3
2015-01-01  14:30   2   1   1   res 3
2015-01-01  15:00   2   2   0   res 3,res 2
2015-01-01  15:30   2   2   0   res 2,res 3

[ Voor 73% gewijzigd door BramV op 06-08-2015 22:04 ]