[MySQL] Complexe query voor bezetting berekenen

[CU2morO]

Update: Inmiddels al wat stappen verder. Scroll naar beneden voor de laatste updates / vragen

Situatieschets

In een planapplicatie die ik heb ontwikkeld kunnen gebruikers mensen koppelen aan groepen op bepaalde dagdelen op week basis (dus bijv. starten op dinsdag 17-7-2012 en herhaal iedere 2 weken).

Een dagdeel is in de ochtend, middag of beide (hele dag)

Een groep heeft een bepaalde max capaciteit

Een bezetting heeft een begin en einddatum, waarbij de weekdag van de begindatum bepaald om welke weekdag het gaat.

Nu wil ik een query schrijven waarbij een gebruiker de eerste vrije plek kan zoeken. Ik krijg het niet voor elkaar om dit met een eenvoudige query of zelfs een predef stored proc. op te lossen.

Technische gegevens:

code:

Dagdeel
-----------------
Code | IsOchtend | IsMiddag

code:

Groep
-----------------
Naam | Capaciteit

code:

Bezetting
-----------------------------------------
DagdeelID | GroepID | UserID | StartDatum | EindDatum | Herhaal

Ik wil nu dus een query schrijven die gaat kijken of er vrije plekken zijn in een specifieke groep, of de eerste beschikbare groep tussen een start en einddatum.

Bijvoorbeeld tussen dinsdag 17-7-2012 en dinsdag 15-7-2014, herhaal iedere 2 weken (dus alle 'even' weken tussen die 2 data).

Is er een manier om dit slim te query'en? Zat zelf al te denken aan een stored func die per gegeven datum en wel / niet ochtend of middag per groep aangeeft hoeveel plek er is, maar dat zou betekenen dat ik die query moet uitvoeren voor iedere datum in die range. In dit geval...~50 weken (iedere even week over periode van 2 jaar). Dat lijkt mij een erg dure operatie....

[ Voor 3% gewijzigd door CU2morO op 23-07-2012 13:38 ]

[Boss]

Het lastige zit hem dus in de 'herhaal'. Misschien in het handig om die gewoon uit te schrijven in een (tijdelijke) tabel en daar je queries op te doen.

[Rotterdammertje]

Ik ben een tijd geleden tegen een soortgelijk probleem aangelopen -- testzalen met een bepaalde capaciteit, waar reserveringen op geplaatst kunnen worden, waarbij een reservering een begin- en einddatum/tijd heeft, en een aantal personen; zoek de testzalen en periodes die nog een bepaalde capaciteit beschikbaar hebben. Ik heb het toen opgelost door allereerst de reserveringen te beschouwen als twee tijdstippen: aan het begin van de reservering neemt de capaciteit van een testzaal af, aan het eind van de reservering neemt de capaciteit weer toe. Hierna heb ik gebruik gemaakt van analytic functions (Oracle database) om de cumulatieve som van de bezetting per testzaal te bepalen, en daarmee de beschikbare tijdslots per testzaal. Zie ook hier!

De herhalende periodes kan je denk ik het beste oplossen door een tijdelijke tabel te vullen, of een table function de herhalingen te laten genereren.

[CU2morO]

Klinkt inderdaad ingewikkeld, Rotterdammer.

Blij dat jij het op hebt kunnen lossen met de tools die Oracle biedt. Ben inmiddels zelf al wat stapjes verder door inderdaad gebruik te maken van een tijdelijke tabel die ik vul met de datums dmv een repeat loop. Deze datums selecteer ik dan allemaal en mbv een stored function haal ik de vrije plaatsen (capaciteit - bezetting) op voor een groep + datum.
Geen schoonheidsprijs, maar het werkt en nog snel ook (< 50ms).

Loop nu tegen het volgende probleem aan:

code:

datum      group_id   vrije plaatsen
2011-01-01    1         0
2011-01-01    2         0
2011-01-08    1         1
2011-01-08    2         0
2011-01-15    1         1
2011-01-15    2         1
2011-01-22    1         2
2011-01-22    2         2
2011-01-29    1         1
2011-01-29    2         0
2011-02-05    1         0
2011-02-05    2         1
2011-02-12    1         0
2011-02-12    2         1
2011-02-19    1         0
2011-02-19    2         0

Hier wil ik nu de begin en einddatum van een periode met vrije plekken uithalen per groep. Ter illustratie, bij de gegeven dataset hierboven, wil ik de volgende resultaten:

code:

group_id  begin       eind
1        2011-01-08  2011-01-29
2        2011-01-15  2011-01-22
2        2011-02-05  2011-02-12

Enig idee hoe ik dat een beetje efficient voor elkaar krijg in MySQL? Maximaal zullen er denk 100-200 datums gegenereerd worden in de praktijk voor een query. Dit heeft misschien invloed op wat wel en niet acceptabel is.

[Wolfboy]

Als je begint met deze query als startpunt, dan zou je de rest erbij kunnen joinen volgens mij

Het is nogal een hack... maar het werkt prima

SQL:

select 
    date_format(
        adddate('2012-1-1', @week:=@week+7), 
        '%Y-%m-%d'
    ) date
from
    any_table_with_more_than_52_rows,
    (select @week:=-7) week
limit 52

[CU2morO]

Beetje erge hack ja

Ook is het aantal '52' verre van vast. Ik heb een arbitraire begindatum, einddatum én spacing tussen de datums (meestal 7 of 14 dagen). De complete range hoeft niet per se 1 jaar te zijn (in de praktijk, vaak om en nabij 4 jaar)

Maar zoals je in mijn vorige post kan lezen heb ik het probleem van datums genereren al enigszins acceptabel opgelost en ben nu bij het volgende vraagstuk aanbeland.

Toch bedankt voor je input! Alle perspectieven helpen.

[ Voor 10% gewijzigd door CU2morO op 23-07-2012 14:08 ]

[pedorus]

Als je die tabel hebt, en je haalt de datums zonder beschikbaarheid (simpele WHERE) weg, dan heb je:

• een begindatum als de tijdsperiode ervoor (datum-spacing) de zaal niet beschikbaar is (NOT EXISTS)
• een bijbehorende einddatum, namelijk de eerstvolgende datum+x*spacing waarbij de keer daarop de zaal niet beschikbaar is. De test op spacing kan hier op basis van de daadwerkelijke datums ((a-b)%interval=0). De eerstvolgende datum is de datum waarvoor er geen eerdere match bestaat. Je joined hierbij een keer of 4 ofzo op dezelfde tabel. Overigens valt dit te versimpelen als je zeker weet dat er een datum met beschikbaarheid 0 zal volgen, waarvoor je dan die week ervoor kan gebruiken.

Dat kun je in principe in een query omzetten, performance zou weleens kunnen tegenvallen... Waarom moet dit perse met SQL worden opgelost?

[CU2morO]

Hoeft niet per se in SQL, maar de ervaring leert dat de DB(MS) beter is in dit soort berekening dan de applicatielaag er achter (Rails).

Misschien kan ik inderdaad beter wat ik nu heb, datum + vrije spots op die dag accepteren van de DB (dit is al redelijk complex samengesteld) en dan applicatie niveau de ranges er uithalen.

Had alleen liever gehad dat ik óf alles in MySQL óf alles in Rails zou doen. Nu moet ik combineren waardoor het slechter te (her)gebruiken valt in soortgelijke situaties in de toekomst.

Wat betreft je antwoord inhoudelijk:
Had inderdaad zoiets in mijn hoofd:
- Pak alle records met >0 capaciteit en een record met NULL of <= 0 capaciteit er voor
- Pak het eerste record date na dit record komt met NULL of <= 0 capaciteit

EDIT: Dan heb ik inderdaad nog even de groep genegeerd. Ga er van uit dat het om 1 groep gaat nu. Dit is wel op te vangen met een group by of wat slimme subqueries.

Probleem is dat ik hiervoor dezelfde dataset (datums, groepen, vrije spots) moet hergebruiken en dat kan niet makkelijk in MySQL. Zelfs de results tijdelijk dumpen in een temp table gaat niet werken, aangezien je die maar 1x kan gebruiken per query.

[ Voor 39% gewijzigd door CU2morO op 24-07-2012 00:28 ]

[CU2morO]

Inmiddels opgelost door de tijdelijke tabel met de vrije spots informatie 2x te clonen. Hier heb ik een query omheen geschreven die de eerste en laatste records van ranges vind en deze selecteert.

[Wolfboy]

CU2morO schreef op maandag 23 juli 2012 @ 13:48:
Beetje erge hack ja

Ook is het aantal '52' verre van vast. Ik heb een arbitraire begindatum, einddatum én spacing tussen de datums (meestal 7 of 14 dagen). De complete range hoeft niet per se 1 jaar te zijn (in de praktijk, vaak om en nabij 4 jaar)

Dat kan je natuurlijk prima dynamisch houden, zolang je maar een tabel hebt met meer rijen dan waar je op moet querien zit je goed

In dat geval is de beschikbaarheid simpelweg een "left join" met een "is null" om alle beschikbare data te krijgen.

Het moeilijke gedeelte zit in het samenvoegen van de aaneengesloten delen, maar ook dat is goed te doen met een left join op zichzelf met een filter op alle rijen waar huidige periode + 1 niet bestaat.