[MySQL] Enorm lange UNION query moet toch simpeler kunnen?

[TromboneFreakus]

Het is lang geleden dat ik op dit forum actief geweest ben. Hopelijk kan iemand me helpen met het volgende.

Inleiding
Voor mijn muziekvereniging heb ik software ontwikkeld waarin we nagenoeg alle relevante zaken registreren. Daar hoort ook een administratie van instrumenten bij.

Er wordt bijgehouden welke instrumenten er worden onderhouden en welk type onderhoud het betreft: periodiek onderhoud of incidenteel onderhoud. Het idee van periodiek onderhoud is dat - zeker bepaalde typen instrumenten - hoe dan ook om de zoveel tijd (preventief) onderhoud nodig hebben.

Voor het periodieke onderhoud is een (eenvoudig) plan opgevat. Dit komt er in de kern op neer dat voor instrumenten een onderhoudsfrequentie is bedacht en dat vervolgens het aantal instrumenten over die onderhoudsperiode wordt verdeeld. Dus stel dat we 18 klarinetten hebben en de standaard onderhoudsfrequentie is eens per 3 jaar, dan zijn ieder jaar dus 6 klarinetten aan de beurt.

Voor de helderheid: het is geen optie om alle instrumenten tegelijkertijd te laten onderhouden, omdat je dan alle muzikanten wekenlang hun instrument ontneemt. :-). Juist het roulatiesysteem maakt dat er waar nodig tijdelijk een leeninstrument of een instrument op voorraad kan worden gebruikt.

Op dit moment staat nog op papier wanneer welk instrument aan de beurt is. Dat is natuurlijk heel bewerkelijk. En zonde; de administratie is immers in de basis al digitaal.

Het doel
Dat moet slimmer kunnen, dacht ik. Het doel is simpel: de computer moet in feite 'met een druk op de knop' een lijst instrumenten presenteren die nu aan de beurt zijn voor periodiek onderhoud. En ik kom een heel eind, maar ik loop toch enigszins vast. Mijn voorgevoel zegt dat een frisse blik van jullie wonderen gaat doen.

Huidige tabelstructuur
De onderliggende tabelstructuur is - voor zover relevant - op dit moment als volgt. Ik doe het maar even in de vorm van query's, mochten jullie het liever anders zien dan hoor ik graag.

Een tabel instrumentenlijst.

Deze tabel bevat de kerngegevens van alle instrumenten.

SQL:

-- Table structure for table `instrumentenlijst`
--

CREATE TABLE IF NOT EXISTS `instrumentenlijst` (
  `InstrumentID` int(11) NOT NULL AUTO_INCREMENT,
  `InstrumentTypeID` int(11) DEFAULT NULL,
  `Serienummer` varchar(50) DEFAULT NULL,
  `MerkID` int(11) DEFAULT NULL,
  `Aanschafdatum` date NOT NULL,
  `Aanschafprijs` double DEFAULT NULL,
  `Verzekerde_waarde` decimal(6,2) DEFAULT NULL,
  `Aantal jaren afschrijving` int(11) DEFAULT NULL,
  `OnderhoudsID` int(11) DEFAULT NULL,
  `Priveeigendom` enum('ja','nee') NOT NULL DEFAULT 'nee',
  `Opmerkingen` text,
  `Gewijzigd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`InstrumentID`),
  KEY `Gewijzigd` (`Gewijzigd`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 CHECKSUM=1 AUTO_INCREMENT=335 ;

Een tabel instrumenttypes

SQL:

-- Table structure for table `instrumenttypes`
--

CREATE TABLE IF NOT EXISTS `instrumenttypes` (
  `InstrumentTypeID` int(11) NOT NULL AUTO_INCREMENT,
  `Instrumenttype` varchar(50) DEFAULT NULL,
  `Gewijzigd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`InstrumentTypeID`),
  KEY `Gewijzigd` (`Gewijzigd`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 CHECKSUM=1 AUTO_INCREMENT=79 ;

Een tabel instrumenten_onderhoudshistorie.

Deze tabel bevat op regelniveau details over uitgevoerde reparaties, hetzij periodiek, hetzij incidenteel.

SQL:

-- Table structure for table `instrumenten_onderhoudshistorie`
--

CREATE TABLE IF NOT EXISTS `instrumenten_onderhoudshistorie` (
  `InstrumentonderhoudshistorieID` int(11) NOT NULL AUTO_INCREMENT,
  `InstrumentID` int(11) NOT NULL,
  `Datum` date NOT NULL,
  `Bedrag` decimal(6,2) NOT NULL,
  `ReparateurID` int(11) NOT NULL,
  `type` enum('periodiek','incidenteel') NOT NULL DEFAULT 'incidenteel',
  `Omschrijving` text NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`InstrumentonderhoudshistorieID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=183 ;

En de tabel instrumenten_onderhoud_standaardfrequentie.

In deze tabel wordt per instrumenttype de standaard onderhoudsfrequentie ingevoerd (bijv. klarinetten per 36 maanden, trombones per 96 maanden). Ook wordt de het aantal rondes per frequentie ingevoerd. Dus bij een termijn van 36 maanden en 3 rondes per keer, vindt er effectief jaarlijks onderhoud plaats op 1/3 van de instrumenten van dat type.

SQL:

-- Table structure for table `instrumenten_onderhoud_standaardfrequentie`
--

CREATE TABLE IF NOT EXISTS `instrumenten_onderhoud_standaardfrequentie` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `InstrumentTypeID` int(11) NOT NULL,
  `Frequentie_maanden` int(11) NOT NULL DEFAULT '36',
  `Rondes_per_frequentie` int(11) NOT NULL DEFAULT '3',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Mijn huidige oplossing
Mijn huidige oplossing komt er op neer dat ik een query heb opgezet waarbij

• alle instrumenten van een bepaalde type
• die niet binnen nu en de voor dat type geldende standaardtermijn al zijn onderhouden
• en beperkt tot een aantal dat gelijk staat aan 1/x deel van het aantal instrumenten van dat type (waarbij x de rondefrequentie is)

Dit doe ik dan door een combinatie van PHP en MySQL-code. De query in MySQL die in de basis werkt staat hieronder.

Het idee is dat de PHP-code

1. in een where loop de tabel instrumenttypes uitleest
2. voor ieder instrumenttype het juiste getal invult waar nu in onderstaande query A1.InstrumentTypeID=3 staat
3. voor ieder instrumenttype het getal 6 waar nu in onderstaande query LIMIT 0,6 staat vervangt door het voor dat type juiste maximum (dus bij 18 klarinetten in drie sub-groepjes 6)
4. alles wordt samengevoegd met UNION ALL tot een hele grote query

code:

SELECT *,
(SELECT Datum FROM `instrumenten_onderhoudshistorie` WHERE type='incidenteel' AND `instrumenten_onderhoudshistorie`.InstrumentID = A1.InstrumentID ORDER BY `instrumenten_onderhoudshistorie`.`Datum` DESC
LIMIT 0 , 1) as laatste_inc_onh,
(SELECT Datum FROM `instrumenten_onderhoudshistorie` WHERE type='periodiek' AND `instrumenten_onderhoudshistorie`.InstrumentID = A1.InstrumentID ORDER BY `instrumenten_onderhoudshistorie`.`Datum` DESC
LIMIT 0 , 1) as laatste_per_onh,
(select (count(A1.InstrumentID) / B1.Rondes_per_frequentie) from instrumentenlijst as A1
JOIN `instrumenten_onderhoud_standaardfrequentie` B1 ON ( A1.InstrumentTypeID = B1.InstrumentTypeID ))    as max_aantal 


FROM `instrumentenlijst` A1
JOIN `instrumenten_onderhoud_standaardfrequentie` B1 ON ( A1.InstrumentTypeID = B1.InstrumentTypeID ) 

WHERE 
A1.InstrumentID NOT IN (SELECT InstrumentID FROM `instrumenten_onderhoudshistorie` WHERE `type`= 'periodiek' AND (TIMESTAMPDIFF(MONTH,`Datum`,NOW()) < B1.Frequentie_maanden))
AND
A1.InstrumentTypeID=3

ORDER BY laatste_per_onh DESC

LIMIT 0,6

Vraag 1: kan dit niet eenvoudiger?
Deze oplossing levert een gigantisch lange query op. Mijn voorgevoel zegt dat dit ongetwijfeld korter en slimmer moet kunnen. Heeft iemand daarvoor wellicht de gouden tip?

De UNION heb ik nodig omdat ik in de WHERE filter op instrumenttype. Dat heb ik echter nodig, omdat ik anders ofwel

• met de LIMIT op het slot: een te korte lijst met allerlei verschillende instrumenttypes krijg;
• zonder de LIMIT op het slot: de totale lijst van alle nog te onderhouden instrumenten krijg, zonder rekening te houden met de verdeling in sub-termijnen;

Ik heb zelf al van alles geprobeerd, waaronder de uitleg met het werken met subquery's die ik vond op https://www.xaprb.com/blo...max-row-per-group-in-sql/. Mijn voorgevoel zegt dat in die richting toch ergens de oplossingsrichting zit. Tot op heden geeft dit echter geen resultaat. Ik doe toch ergens iets niet goed (ik presteer het om ofwel 0 resultaten, ofwel ongewijzigde resultaten te krijgen).

Vraag 2: hoe ga ik slimmer om met dynamisch wijzigende gegevens?
Een ander punt is dat deze query - naast de omvang - een ander groot nadeel heeft: het dynamische effect ervan. Daarmee doel ik op het effect dat de query niet corrigeert voor al in deze periode uitgevoerd periodiek onderhoud.

Dus als ik vandaag een lijst opvraag van instrumenten die "aan de beurt" zijn, deze instrumenten laat onderhouden en dat onderhoud invoeren, dan zijn na die invoer volgens het systeem ogenblikkelijk alweer de volgende instrumenten aan de beurt. Terwijl ik daarvoor nu juist die standaard verdeling van onderhoudsfrequenties voor had bedacht.

Ik heb dit laatste proberen te corrigeren met een WHERE-voorwaarde die detecteert of er in de periode tussen nu en de onderhoudsperiode per subgroep (dus 12 maanden in het voorbeeld) periodiek onderhoud is geweest. Dan wordt echter geen enkel instrument meer getoond zodra er 1 instrument voor periodiek onderhoud is ingevoerd. Dat is niet handig; het moet niet zo zijn dat als de instrumentenbeheerder een dag later nog even wil dubbelchecken welke instrumenten ook alweer aan de beurt zijn dat dan de lijst alweer op 0 staat. Of geforceerd wordt van lijstjes printjes te maken omdat hij weet dat zodra er 1 onderhoud wordt ingevoerd de lijst weg is. Ik wilde juist van het papier af. :-)

Per instrument "hard" aan de database toevoegen tot welk onderhoudsgroepje ze behoren wil ik eigenlijk voorkomen. Dan ga ik namelijk eerder meer dan minder handwerk voor onze instrumentenbeheerder creeren. Bovendien is dat een hele bewerkelijke oplossing, omdat het aantal subgroepjes niet vast staat. Het ene instrument gaart eens in de drie jaar op onderhoud, het andere eens in de vijf en het aantal instrumenten per type verschilt sterk (20 klarinetten vs. 1 set pauken bijvoorbeeld).

Ook andere oplossingen zijn in mijn gedachten al de revue gepasseerd, maar steeds loop ik vast.

Ik denk ongetwijfeld ergens veel te moeilijk. Heeft een van jullie een of meer handige tips?

Alvast heel erg veel dank!

[NMe]

Ik zie geen enkele union in je query?

Daarnaast:

Deze oplossing levert een gigantisch lange query op. Mijn voorgevoel zegt dat dit ongetwijfeld korter en slimmer moet kunnen. Heeft iemand daarvoor wellicht de gouden tip?

Waarom? Heb je daadwerkelijk problemen geconstateerd of vind je gewoon dat de query te lang is? Er is niks mis met een bliksemsnelle lange query. Andersom is er wel iets mis met een korte query die niet heel optimaal kun runnen. Dus: wat is het probleem dat je op wil lossen?

[ACM]

TromboneFreakus schreef op zaterdag 28 juli 2018 @ 18:30:

Vraag 1: kan dit niet eenvoudiger?
Deze oplossing levert een gigantisch lange query op. Mijn voorgevoel zegt dat dit ongetwijfeld korter en slimmer moet kunnen. Heeft iemand daarvoor wellicht de gouden tip?

Aangezien je toch al php gebruikt, waarom wil je die deel-queries uberhapt in een union stoppen? Ik ben zelf een groot fan van het complete resultaat uit SQL krijgen, maar soms is dat gewoon niet realistisch.

Je kan dan beter wat meer in php houden en per type instrument een query uitvoeren. Scheelt je mogelijk nog wat gedoe met het zoeken naar een complexere query

Vraag 2: hoe ga ik slimmer om met dynamisch wijzigende gegevens?
Een ander punt is dat deze query - naast de omvang - een ander groot nadeel heeft: het dynamische effect ervan. Daarmee doel ik op het effect dat de query niet corrigeert voor al in deze periode uitgevoerd periodiek onderhoud.

Maar ik vermoed dat je hiermee effectief een oplossing voor de eerdere kunt maken.

Nu probeer je een "ik wil het onderhoudsschema weten" voor elkaar te krijgen. Maar waarom redeneer je niet gewoon zo: "ik wil weten welke instrumenten bijna aan onderhoud toe zijn"?
Is dat niet veel nuttiger en handiger?

De instrumenten zijn, neem ik aan, toch al volgens het juiste schema in onderhoud geweest toen jouw software nog niet gebruikt werd? Dan heb je in principe al helemaal het juiste schema erin verwerkt en hoef je alleen nog maar te weten welke als eerstvolgende aan de beurt zijn voor onderhoud.

Dit is een ongeteste query die (als het goed is ) voor ieder instrument teruggeeft waarbij de laatste onderhoudsbeurt langer geleden is dan de voorgeschreven interval:

SQL:

SELECT
        *
FROM instrumentenlijst i
    JOIN instrumenten_onderhoud_standaardfrequentie iof ON i.InstrumentTypeID = iof.InstrumentTypeID
    JOIN instrumenten_onderhoudshistorie io 
                 ON io.InstrumentID = i.InstrumentID 
                        AND io.Timestamp < NOW() - INTERVAL iof.Frequentie_maanden MONTH
                        AND NOT EXISTS(SELECT *
                                                        FROM instrumenten_onderhoudshistorie ionewer
                                                        WHERE ionewer.InstrumentID = io.InstrumentID
                                                            AND ionewer.Timestamp > io.Timestamp
                                    )
        )

Je kan daar uiteraard ook bij de INTERVAL nog een stukje vanaf trekken zodat ze 'bijna aan de beurt' geven, ipv 'net voorbij de termijn', met zoiets:
INTERVAL (iof.Frequentie_maanden - 6) MONTH

Een andere variant op bovenstaande query is domweg alle instrumenten tonen met hun meest recente onderhoudsbeurt en verwachte eerstvolgende beurt; gesorteerd op waneer ze weer aan de beurt zijn.
Dat kan je bereiken door de "AND io.Timestamp"-clausule te verwijderen en het geheel te sorteren op "io.Timestamp + INTERVAL iof.Frequentie_maanden MONTH".

Dit heeft in ieder geval als voordeel dat het helemaal dynamisch per instrument kijkt en het in de query zelf niet meer boeit hoeveel andere instrumenten al aan de beurt zijn geweest. Als het goed is, is tenslotte de meest recente onderhoudsbeurt al zo gedaan dat het voldoet aan de beoogde onderhoudsfrequentie op het juiste moment. En als er een tussentijdse onderhoudsbeurt was, dan wil je natuurlijk niet in potentie de dag erna het instrument aanbieden voor z'n periodieke onderhoud, maar pas weer over de beoogde X tijd.

Dat proberen het allemaal goed te 'load balancen' is iets dat het sowieso ingewikkeld maakt. Aangezien je met fysieke objecten en afhankelijkheden van mensen zit, is dat iets dat je wellicht beter gewoon aan die mensen over kunt laten. Ze moeten toch al alles rond dat onderhoud regelen, zoals levering, afspraken, vervangende instrumenten regelen (of niet als iemand toch op vakantie gaat rond die tijd), etc.

Nieuwe instrumenten gooien natuurlijk wat roet in het eten en komen ook niet uit bovenstaande query. Daar zou je nog wel je originele query op los kunnen laten, maar je zou ook dat handmatig kunnen doen door alvast een onderhoudsbeurt vooruit te plannen (of, erg hacky, een fictieve in het verleden maken).

[pedorus]

Ik ben het eens met ACM, dat je dit beter in php of anders kan doen. SQL is hier eigenlijk niet erg geschikt voor, en in het bijzonder mysql al helemaal niet.

Wat je namelijk hebt is dat je een soort dubbel groupwise-maximum probleem hebt. Daardoor heb je al snel views nodig, of wat hulpqueries, of krijg je dezelfde queries vaker dan eens. Maar laat ik toch een poging laten zien:

Wat handig zou zijn is te weten wanneer er voor het laatst onderhoud was, afgezien van dit jaar, en met de aanschafdatum als dit niet bestaat. Hiervoor zou je een hulp-view kunnen maken:

SQL:

create view laatste_periodiek_onderhoud as 
select  i.InstrumentID, i.InstrumentTypeID, ifnull(a.datum, i.Aanschafdatum) as onderhoudsdatum 
from instrumentenlijst i 
left join instrumenten_onderhoudshistorie a 
 on a.InstrumentID=i.InstrumentID and a.datum < MAKEDATE(year(now()),1) and a.type="periodiek"
left join instrumenten_onderhoudshistorie b 
 on a.InstrumentID=b.InstrumentID and b.datum > a.datum 
  and b.datum < MAKEDATE(year(now()),1) and b.`type`="periodiek" 
where b.datum is null;

Hier zit 2x vrij hardcoded een dynamische datum in, namelijk MAKEDATE(year(now()),1) om te zorgen dat we onderhoud van het huidige jaar niet meenemen. Wat je terugkrijgt scheelt dus aan de hand van het jaar, je kunt dus niet zomaar alvast naar volgend jaar kijken. (Wat ook onhandig is, is dat mysql geen variabelen ondersteund in views of WITH kent in veelvoorkomende versies, waardoor je dit niet kan generaliseren.) Ik ga er ook van uit dat incidenteel onderhoud periodiek onderhoud niet vervangt.

Wat ook handig is als view, is om te weten hoeveel apparaten per groepje maximaal onderhoud per jaar krijgen. Dan krijg je zoiets:

SQL:

create view instrumentypes_onderhoud_per_jaar as
select a.InstrumentTypeID, a.Frequentie_maanden, a.Rondes_per_frequentie, 
  ceil(count(*)/Rondes_per_frequentie) as MaxAantalPerJaar 
from instrumenten_onderhoud_standaardfrequentie a inner join instrumentenlijst b 
 on a.InstrumentTypeID=b.InstrumentTypeID
group by a.InstrumentTypeID, a.Frequentie_maanden, a.Rondes_per_frequentie;

(Ik ga uit van groepen per jaar, afronden naar boven, en een gelijk maximum per jaar, en niet een al te uitgebreid schema hier. Het is me niet helemaal duidelijk wat de bedoeling exact is.)

Vervolgens is er wel een query te maken voor het onderhoud van dit jaar:

SQL:

select a.InstrumentID,a.onderhoudsdatum, count(*) as volgnr 
from laatste_periodiek_onderhoud a inner join laatste_periodiek_onderhoud b 
 on a.InstrumentTypeID=b.InstrumentTypeID and ((a.onderhoudsdatum > b.onderhoudsdatum) 
  or (a.onderhoudsdatum=b.onderhoudsdatum and a.InstrumentID>=b.InstrumentID))
inner join instrumentypes_onderhoud_per_jaar c on a.InstrumentTypeID=c.InstrumentTypeID
group by a.InstrumentID,a.onderhoudsdatum, c.Frequentie_maanden, c.MaxAantalPerJaar
having count(*)<=c.MaxAantalPerJaar 
 and TIMESTAMPDIFF(MONTH, a.onderhoudsdatum, MAKEDATE(year(now())+1,1)) >= c.Frequentie_maanden;

We onderhouden bij deze query maximaal x apparaten per groepje en alleen als er al Frequentie_maanden zijn verstreken aan het einde van dit jaar ten opzichte van het vorige onderhoud. volgnr is het volgnummer binnen een groepje.

Stel nu dat je vervolgens alvast wil weten welke apparaten volgend jaar onderhoud gaan krijgen, dan wordt het al weer lastig natuurlijk. Het is mogelijk, de vraag is of je deze kant op wil.

NMe schreef op zondag 29 juli 2018 @ 00:25:
Ik zie geen enkele union in je query?

Het lijkt me dat er origineel per InstrumentTypeID een query wordt gedaan die dan met UNION ALL gemerged worden.

TromboneFreakus schreef op zaterdag 28 juli 2018 @ 18:30:
Ik heb zelf al van alles geprobeerd, waaronder de uitleg met het werken met subquery's die ik vond op https://www.xaprb.com/blo...max-row-per-group-in-sql/. Mijn voorgevoel zegt dat in die richting toch ergens de oplossingsrichting zit. Tot op heden geeft dit echter geen resultaat. Ik doe toch ergens iets niet goed (ik presteer het om ofwel 0 resultaten, ofwel ongewijzigde resultaten te krijgen).

Deze truc is -hoewel sneller- helaas undefined behavior, en dat het niet werkt verbaasd me niets. Net als een truc met order by + geen full group by voor het oplossen van een groupwise-maximum probleem. Vandaar dat ik hierboven bijvoorbeeld met count(*) werk en met left join. Als je goed op let dan zie je dat ik "dependent subquery" in de explain probeer te vermijden en niet gebruik, dit komt omdat die veelal supertraag worden.

[incaz]

Ik zou in dit geval een extra tabel aanmaken waar je de geplande onderhoudsdiensten laat invullend door php. Dat zijn wel wat meer losse queries, maar in principe is het wijzigen van iets dan gewoon een actie waarbij je alle geplande onderhoud voor de toekomst uit de tabel gooit voor dat instrumenttype, en een nieuwe planning maakt.

Het voordeel is dat je daarmee je planning ook makkelijker in losse functies kunt opsplitsen, die je ook per stuk kunt testen en in de toekomst vervangen als de behoefte verandert. Je neemt nu namelijk wat aan over hoe je het onderhoud verdeelt (per jaar een aantal rondes) en die aannames voldoen nu, maar... dan komt er een moment dat de triangels alleen in januari en februari onderhouden moeten worden ofzo, en dan klopt je aanname van een gelijkmatige verdeling niet meer en dan moet je de hele query in z'n geheel aanpassen.
(En implementeer die tests gelijk, daar doe je je toekomstige zelf een groot plezier mee.)

Door het naar code te halen en op te splitsen kun je, als je dat nodig hebt, speciale gevallen makkelijker in code uitwerken, makkelijker testen, en weten dat je case voor de triangels geen invloed heeft op het rooster van de trombones.

Extra voordeel is dat je met een roostertabel ook makkelijker iets zou kunnen implementeren waarbij je handmatig veranderingen aanbrengt, of dat nou via code is met een hippe drag-en-drop-interface of je gewoon handmatig een datum van 1 instrument verandert omdat het zo uitkomt.
Je leesquery is dan simpel en geeft altijd gewoon het onderhoudsrooster.