Factor over tijden berekenen met mysql
Ik heb de volgende database:
Database gegevens
Tabel a:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `a` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`tijd_in` TIME NULL,
`tijd_uit` TIME NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC));
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('18:00:00', '20:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('17:00:00', '21:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('17:00:00', '19:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('19:00:00', '21:00:00');
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`tijd_in` TIME NULL,
`tijd_uit` TIME NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC));
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('18:00:00', '20:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('17:00:00', '21:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('17:00:00', '19:00:00');
INSERT INTO `a` (`tijd_in`, `tijd_uit`) VALUES ('19:00:00', '21:00:00');
Tabel b:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
CREATE TABLE `b` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`factor` DECIMAL(3,2) NOT NULL,
`tijd_in` TIME NULL,
`tijd_uit` TIME NULL,
PRIMARY KEY (`id`, `factor`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC));
INSERT INTO `b` (`factor`, `tijd_in`, `tijd_uit`) VALUES ('1.50', '00:00:00', '04:00:00');
INSERT INTO `b` (`factor`, `tijd_in`, `tijd_uit`) VALUES ('1.25', '18:00:00', '20:00:00');
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`factor` DECIMAL(3,2) NOT NULL,
`tijd_in` TIME NULL,
`tijd_uit` TIME NULL,
PRIMARY KEY (`id`, `factor`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC));
INSERT INTO `b` (`factor`, `tijd_in`, `tijd_uit`) VALUES ('1.50', '00:00:00', '04:00:00');
INSERT INTO `b` (`factor`, `tijd_in`, `tijd_uit`) VALUES ('1.25', '18:00:00', '20:00:00');
In tabel a staan diverse tijden waarover conform tabel b een bepaalde factor overheen moet.
Voorbeeld:
Regel 1 van tabel a:
18:00:00 valt onder de factor van tabel b met id = 2.
Over de tijd 20:00:00 - 18:00:00 = 2 uur moet dus een factor van 1.25.
2 * 1.25 = 2.5 als eindresultaat.
Regel 2 van tabel a:
17:00:00 valt ook onder de factor van tabel b met id = 2
* Over de tijd van 17:00:00 t/m 18:00:00 wordt geen factor berekend
* Over de tijd van 18:00:00 t/m 20:00:00 wordt wel een factor berekend
* Over de tijd van 20:00:00 t/m 21:00:00 wordt weer geen factor berekend
Zo zijn er dus diverse opties mogelijk.
In tabel b staat dus de tijd wanneer zo'n factor berekend moet worden over tabel a heen.
Huidige situatie:
Voor elke factor wordt er nu handmatig vergeleken in de query. Dit moet geautomatiseerd worden.
Dit is een voorbeeld van hoe een deel wordt gedaan nu. Dit gebeurd voor alle mogelijke opties en toeslagen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
CASE
WHEN
(
tijd_in <= '00:00:00' AND tijd_uit > '00:00:00' AND tijd_uit < '6:00:00'
)
THEN
(
ROUND((TIME_TO_SEC(TIMEDIFF(TIME('06:00:00'), tijd_uit))/3600),2)
)
ELSE
(.........
WHEN
(
tijd_in <= '00:00:00' AND tijd_uit > '00:00:00' AND tijd_uit < '6:00:00'
)
THEN
(
ROUND((TIME_TO_SEC(TIMEDIFF(TIME('06:00:00'), tijd_uit))/3600),2)
)
ELSE
(.........
Conclusie:
Bovenstaande situatie zou ik graag willen realiseren. Er is al een bestaande situatie, maar wil deze vernieuwen om performance winst te boeken. Het kan totaal om duizenden/tienduizenden regels gaan waarover dit berekend moet worden.
Hoe kan ik deze situatie het beste aanpakken? Alvast bedankt voor je hulp.
Gewijzigd op 18/11/2014 08:38:14 door Peter K
Dan voor die performance eerst even vier andere dingen:
1. Op de primaire sleutels hoef je geen UNIQUE INDEX te zetten.
2. In de tweede tabel b met de factoren per tijdvak mag niets nullable zijn. Je hebt anders namelijk een onbruikbaar en dus ongeldig tijdvak.
3. In de tweede tabel kun je de primaire sleutel verkleinen van een INT (4 bytes) naar een SMALLINT (2 bytes) of zelfs een TINYINT (1 byte). Zo veel tijdvakken zijn er namelijk niet.
4. Sla het berekende resultaat op in een extra tabel c met een primaire sleutel die refereert naar de primaire sleutel van tabel a. Met andere woorden: bouw een cache, zodat je alleen nog nieuwe tijdvakken hoeft door te rekenen.
Gewijzigd op 18/11/2014 08:57:28 door Ward van der Put
Bedankt voor je snelle reactie. Bovenstaand was een voorbeeld snel aangemaakt.
Punten 1, 2 en 3 zijn in de huidige situatie wel in orde. Excuses hiervoor.
Punt 4:
Ik wil werken zonder een extra tabel waar hard data in staat. Zodra tabel a namelijk veranderd moet alles realtime kloppen. Na een update in tabel a wil ik dus niet tabel c weer moeten herberekenen.
Wat je al hebt berekend, hoef je niet steeds realtime opnieuw te berekenen: dáár zit de performancewinst als je tienduizenden registraties hebt en er dagelijks maar enkele honderden bijkomen.
Wat ik opmaak uit de OP is dat een tijdsregistratie meerdere factoren kan bevatten, dus dan moet er een lookup table komen voor minimaal ieder uur van de dag.
En heb je de query al wel eens zonder de berekening uitgevoerd?
Het idee van het systeem is dat er tijden ingevuld worden in tabel a door de gebruiker. Deze kunnen ten alle tijden gewijzigd worden. (ook in het verleden nog aanpasbaar) Tijdens het aanpassen wordt er een nieuwe regel aangemaakt met de gewijzigde tijden en de huidige regel wordt op niet actief gezet.
Uiteindelijk wil ik een eindtotaal kunnen opmaken van de uren + eventuele toeslagen. Dit over bijvoorbeeld een bereik van bijvoorbeeld 10 dagen t/m 1 jaar.
De nauwkeurigheid maakt niet zo heel veel uit. Laat ons uitgaan van 2 decimalen.
Het gaat nu nog niet perse om de snelheid, maar om het verhaal hoe de query tot stand kan komen.
Zelf heb ik dit nu gevonden als een optie:
Overlappende uren vinden
Code (php)
1
TIMEDIFF((LEAST(a.tijd_uit,b.tijd_uit)),(GREATEST(a.tijd_in,b.tijd_in))) as overlappende_uren
Vermenigvuldigd met factor
Code (php)
1
SEC_TO_TIME((TIME_TO_SEC(TIMEDIFF((LEAST(a.tijd_uit,b.tijd_uit)),(GREATEST(a.tijd_in,b.tijd_in))))*b.factor) - TIME_TO_SEC(TIMEDIFF((LEAST(a.tijd_uit,b.tijd_uit)),(GREATEST(a.tijd_in,b.tijd_in))))) as toeslag_uren
Kan dit wellicht simpeler of beter/effecitiever?
Een datum is niet bekend in dit geval.
Maar in openingspost stel je dat de performance wilt verbeteren.
Denk je nu echt dat het introduceren van een extra tabel, en het vervangen van CASE door greater/least daar aan toe bijdraagt?
Ik wil inderdaad performance verbeteren, maar daarnaast ook uitbreiden.
Het gaat niet om het vervangen van 1 case in dit geval maar om het vervangen van meerdere cases. Gezien het aantal cases ook variabel moet worden zal ik dus gaan moeten werken met een extra tabel dacht ik? De gebruiker moet namelijk zelf kunnen kiezen welke toeslag en wanneer deze berekend wordt. Vandaar dat ik dus met het idee van de extra tabel gekomen ben.
De case die er nu in staat is ook nog vele malen groter en onlogisch opgebouwd. Als ik deze netjes opsplits als in de beginpost bij huidige situatie, loopt deze op tot 50 regels +/-. Dit kan ik nu vervangen met een LEFT JOIN van de toeslag en vervolgens dit als stuk in de query:
Code (php)
1
SEC_TO_TIME((TIME_TO_SEC(TIMEDIFF((LEAST(a.tijd_uit,b.tijd_uit)),(GREATEST(a.tijd_in,b.tijd_in))))*b.factor) - TIME_TO_SEC(TIMEDIFF((LEAST(a.tijd_uit,b.tijd_uit)),(GREATEST(a.tijd_in,b.tijd_in))))) as toeslag_uren
Daarnaast is het nu ook nog zo dat het stuk script in PHP geloopt wordt. Dit wil ik nu ook door mysql gaan doen door het gebruik van 'SUM' en 'WITH ROLLUP'.
Maar wat je aangeeft, is een extra tabel erg slecht voor de performance dan vergeleken met een heleboel cases? Is het niet sneller om gebruik te maken van kleine tabellen met kleine queries?
Alvast bedankt voor jullie input,
Bijvoorbeeld:
>> De gebruiker moet namelijk zelf kunnen kiezen welke toeslag en wanneer deze berekend wordt.
Dat kun je op meerdere manieren lezen. De meeste complexe: elke gebruiker kan voor zijn eigen urenregistratie én vervolgens per klant/project andere toeslagen/kortingen rekenen. Dan krijg je er één, twee of drie sleutels bij (gebruiker, klant en/of project).
Verder denk ik dat je best hier en daar wat keuzen mag maken. Bijvoorbeeld: die factoren per tijdvak worden in de regel gebruikt om overuren te factureren/vergoeden met een toeslag. Op een gegeven moment komt daaraan echter een eind: je gaat niet facturen van 3 jaar geleden of loonstroken van 5 jaar geleden herzien. Die data hoef je dus ook niet meer realtime door te rekenen.
Alle mogelijkheden openlaten en niets uitsluiten is soms lastig te automatiseren.