[MySQL] Totaal van overlappende tijd

[Verwijderd]

Ik ben met een agenda bezig, en zit met een erg vervelend probleem.
Ik wil het totaal van de uren die een persoon met een agenda werkt berekenen. Hierbij moeten dubbel geplande afspraken in een uur dus als een uur tellen. even een voorbeeld

10:00 tot 11:00
10:10 tot 11:30
12:30 tot 12:50

is 1:50 uur gewerkt.

omdat ik een zeer intensief gebruikte agenda heb wil ik dit om snelheids reden eigenlijk in een query oplossen, zonder php checks te gebruiken. Heeft iemand een idee hoe / of ik dat op kan lossen?

bvd

[Verwijderd]

Wat maakt een query sneller dan een php calculatie? Ik vind dit duidelijk een scenario waar je juist php voor zou gebruiken. Een dergelijke calculatie heeft immers niets met je database te maken.

[Varienaja]

Je kunt een array maken, waarbij de index de minuut aangeeft, en de inhoud 0 of 1 is.

10:00 tot 11:00
10:10 tot 11:30
12:30 tot 12:50

Je gaat de afspraken langs, en vult daarbij het array, je array zal er dan als volgt uit gaan zien

code:

...10:00     11:00     12:00     13:00...
...00111111111111111000000000011111000...

Daarna tel je het aantal 1tjes, en dan weet je precies hoeveel minuten die persoon 'gewerkt' heeft.

[ritsjoena]

Hoewel het misschien sneller kan.

Vraag ze allen op. Sorteer ze eventueel op begintijd.

Begin bij begintijd 1 (de eerste) te op tot eindtijd van 1.
Als begintijd 2 < eindtijd 1 neem dan het interval eindtijd 1 - eindtijd 2 voor het totaal.
Anders gewoon eindtijd 2 - begintijd 2.

Dus
10:00 - 11:00 + 10:30 - 12:00 wordt dan

11:00-10:00 + 12:00 -11:00

p.s. let wel even op het geval eindtijd 2 < eindtijd 1.

[ Voor 9% gewijzigd door ritsjoena op 14-09-2005 11:09 ]

[Verwijderd]

Verwijderd schreef op woensdag 14 september 2005 @ 11:04:
Wat maakt een query sneller dan een php calculatie? Ik vind dit duidelijk een scenario waar je juist php voor zou gebruiken. Een dergelijke calculatie heeft immers niets met je database te maken.

als ik duizenden rijen moet gaan fetchen wordt de snelheid behoorlijk minder. Daarom zou ik LIEVER via mysql rekenen. wanneer dat onmogelijk zou blijken, zal ik het wel met php oplossen

[Verwijderd]

ritsjoena schreef op woensdag 14 september 2005 @ 11:08:
Hoewel het misschien sneller kan.

Vraag ze allen op. Sorteer ze eventueel op begintijd.

Begin bij begintijd 1 (de eerste) te op tot eindtijd van 1.
Als begintijd 2 < eindtijd 1 neem dan het interval eindtijd 1 - eindtijd 2 voor het totaal.
Anders gewoon eindtijd 2 - begintijd 2.

Dus
10:00 - 11:00 + 10:30 - 12:00 wordt dan

11:00-10:00 + 12:00 -11:00

p.s. let wel even op het geval eindtijd 2 < eindtijd 1.

Zo zou ik het in php moeten oplossen. maar mijn vraag is of er voor mysql iets te bedenken is wat de berekening doet?

bedankt voor het meedenken

[Verwijderd]

ach die 'database' (sorry, zo mag je mysql eigelijk niet noemen) moet toch echt ook al die velden selecteren, dus zolang je die 'duizende rijen' niet over internet moet gooien is er niets aan de hand.

Ik zou zelf objecten maken met een begin en eindtijd en daar een methode 'overlaps' en 'merge' oid op definieren. Zodoende is het een kwestie van 'if a overlaps b then merge a and b'

[Verwijderd]

Dit is onmogelijk om in alleen SQL te doen. (Edit 2: Nou ja, wellicht wel, maar blij word je er niet van)

Als het werkelijk om ontzettend veel gegevens gaat, zal je de performance moet zoeken in het opslaan van procesgegevens. Naast de afspraken sla je dan ook op hoeveel uur er per dag gewerkt is. Zo is deze info altijd snel beschikbaar. Natuurlijk moet je wel bij iedere nieuwe afspraak op die dag de gewerkte uren opnieuw berekenen en opslaan, maar dit levert wel de beste performance op, mocht dit nodig blijken te zijn.

Edit:

Verwijderd schreef op woensdag 14 september 2005 @ 11:14:
ach die 'database' (sorry, zo mag je mysql eigelijk niet noemen) ...

-1 Flame bait, nergens voor nodig ...

Edit 3:
C'est la ton qui fait la musique ... dus een "MySQL is beperkt RDBMS wat dit onmogelijk zal maken" was veel constructiever geweest dan de bovenstaande steek onder water. Inhoudelijk heb je gelijk, maar de vorm is niet constructief ...
/me houdt nu echt op met off topic reageren voor vandaag

[ Voor 51% gewijzigd door Verwijderd op 14-09-2005 11:35 ]

[jvdmeer]

Zomaar een ideetje:

Maak een tabel met alle tijden erin per minuut, dus 24*60 records.
Voer nu een join uit van deze tabel op de tabel met de afspraken, en daarop een count uitvoeren.

[Varienaja]

Ik ben nog even aan het puzzelen geweest om te kijken of het ook met SQL kan, en ik denk dat dat ook wel lukt:

code:

select t1.begintijd, t2.eindtijd, t2.eindtijd-t1.begintijd as lengte, t.persoon
from tijden t1, tijden t2
where t1.persoon = t2.persoon 
  and t1.begintijd<=t2.begintijd
  and t1.eindtijd<t1.eindtijd
  and t1.id<>t2.id
union
select t.begintijd, t.eindtijd, t.eindtijd-t.begintijd as lengte, t.persoon
from tijden t
where not exists (select t3.* 
                  from tijden t3 
                  where t.id<>t3.id
                    and t.persoon=t3.persoon
                    and t.begintijd<=t3.begintijd
                    and t.eindtijd<t3.eindtijd)

Kortom: selecteer het totaal uit de overlappende tijden en daarna het totaal van de niet-overlappende tijden.

[ Voor 3% gewijzigd door Varienaja op 14-09-2005 11:20 ]

[Verwijderd]

jvdmeer schreef op woensdag 14 september 2005 @ 11:16:
Zomaar een ideetje:

Maak een tabel met alle tijden erin per minuut, dus 24*60 records.
Voer nu een join uit van deze tabel op de tabel met de afspraken, en daarop een count uitvoeren.

HA, klinkt goed. Hoe kom je erop Ik zal het eens proberen. Ik twijfel er alleen wel aan of dat de performance wel ten goede komt.

[Verwijderd]

Varienaja schreef op woensdag 14 september 2005 @ 11:19:
Ik ben nog even aan het puzzelen geweest om te kijken of het ook met SQL kan, en ik denk dat dat ook wel lukt:

code:

select t1.begintijd, t2.eindtijd, t2.eindtijd-t1.begintijd as lengte, t.persoon
from tijden t1, tijden t2
where t1.persoon = t2.persoon 
  and t1.begintijd<=t2.begintijd
  and t1.eindtijd<t1.eindtijd
  and t1.id<>t2.id
union
select t.begintijd, t.eindtijd, t.eindtijd-t.begintijd as lengte, t.persoon
from tijden t
where not exists (select t3.* 
                  from tijden t3 
                  where t.id<>t3.id
                    and t.persoon=t3.persoon
                    and t.begintijd<=t3.begintijd
                    and t.eindtijd<t3.eindtijd)

Kortom: selecteer het totaal uit de overlappende tijden en daarna het totaal van de niet-overlappende tijden.

jammer genoeg zit ik vast aan een mysql versie die geen subquery's ondersteunt

[Verwijderd]

Verwijderd schreef op woensdag 14 september 2005 @ 11:24:jammer genoeg zit ik vast aan een mysql versie die geen subquery's ondersteunt

Verwijderd schreef op woensdag 14 september 2005 @ 11:14:-1 Flame bait, nergens voor nodig ...

Catch my drift MrX?

[ACM]

Nou, volgens mij kan het prima met simpelere queries

2 stappen zijn er nodig:
1) Neem de totale tijden.
2) Neem de tijden "die je te veel" gerekent hebt.

1) is triviaal:

SQL:

SELECT userid, SUM(eindtijd - starttijd) FROM agenda GROUP BY userid

2) is wat lastiger:

SQL:

SELECT a.userid, 
    SUM(
      CASE WHEN b.eindtijd < a.eindtijd THEN b.eindtijd ELSE a.eindtijd END 
         - b.starttijd
   ) 
FROM agenda a
  JOIN agenda b ON b.starttijd BETWEEN a.starttijd AND a.eindtijd
                       AND a.userid = b.userid
                       AND a.id <> b.id 
GROUP BY a.userid

Combineren is wat lastiger zeker in MySQL. Als je het van 1 specifieke gebruiker wil moet het zo wel lukken:

SQL:

SELECT 
  (SELECT SUM(eindtijd - starttijd) FROM agenda WHERE userid = X)
 -
  IFNULL((SELECT a.userid, 
    SUM(
        CASE WHEN b.eindtijd < a.eindtijd THEN b.eindtijd ELSE a.eindtijd END
            - b.starttijd
   ) 
  FROM agenda a
    JOIN agenda b ON b.starttijd BETWEEN a.starttijd AND a.eindtijd
                       AND a.userid = b.userid
                       AND a.id <> b.id 
  WHERE userid = X),
 0)

Of voor allemaal zoiets:

SQL:

SELECT x.userid, x.tijd - IFNULL(y.tijd, 0)
FROM
  (SELECT userid, SUM(eindtijd - starttijd) as tijd FROM agenda GROUP BY userid) x
  LEFT JOIN
  (SELECT a.userid, 
    SUM(
         CASE WHEN b.eindtijd < a.eindtijd THEN b.eindtijd ELSE a.eindtijd END
            - b.starttijd
   ) as tijd
FROM agenda a
  JOIN agenda b ON b.starttijd BETWEEN a.starttijd AND a.eindtijd
                       AND a.userid = b.userid
                       AND a.id <> b.id 
GROUP BY a.userid) y ON x.userid = y.userid

Voor < 4.1, dus zonder subqueries zou je de resultaten in een temporary table kunnen stoppen en dan combineren.

[ Voor 3% gewijzigd door ACM op 14-09-2005 11:48 ]

[jochemd]

ACM schreef op woensdag 14 september 2005 @ 11:39:
2) Neem de tijden "die je te veel" gerekent hebt.

2) is wat lastiger:

SQL:

SELECT a.userid, 
    SUM(
      CASE WHEN b.eindtijd < a.eindtijd THEN b.eindtijd ELSE a.eindtijd END 
         - b.starttijd
   ) 
FROM agenda a
  JOIN agenda b ON b.starttijd BETWEEN a.starttijd AND a.eindtijd
                       AND a.userid = b.userid
                       AND a.id <> b.id 
GROUP BY a.userid

Werkt dit ook voor het volgende scenario:
afspraak 1: 10:00 - 11:00
afspraak 2: 10:30 - 12:30
afspraak 3: 12:00 - 13:00

[ACM]

Volgens mij wel.

Je telt eerst 1 + 2 + 1 uur op.

En daarna trek je er 0.5 en 0.5 uur af. 't Maakt voor mijn code tenslotte niet uit dat afspraak 3 na 2 eindigt, maar 2 er midden in stopt.

[Verwijderd]

Is het niet zo dat nu de verschillen twee keer mee geteld worden? Want ik krijg nu bij een overlapping van 35 minuten de waarde 01:10 terug!

Bij de join vergelijkt hij volgens mijn eerst AgendaPuntID 1 <> AgendaPunt 2 en dan bij de volgende record AgendaPunt 2 <> AgendaPunt 1?

code:

SELECT a.UserID,  
    SEC_TO_TIME(SUM( 
      CASE WHEN b.EindTijd <a.EindTijd THEN TIME_TO_SEC(b.EindTijd) ELSE TIME_TO_SEC(a.EindTijd) END  
         - TIME_TO_SEC(b.StartTijd) 
   )) , a.Datum
FROM AgendaPunt a 
  JOIN AgendaPunt b ON b.StartTijd BETWEEN a.StartTijd AND a.EindTijd 
                       AND a.UserID = b.UserID 
                       AND a.AgendaPuntID <> b.AgendaPuntID  
                       AND a.Datum = b.Datum
WHERE
    a.Datum LIKE '2005-08%'
GROUP BY a.UserID, a.Datum

Als ik de controle er in zet dat de starttijd oplopend moet zijn (zodat de punten niet omgedraaid kunnen worden) dan kan ik niet twee agendapunten hebben die om dezelfde tijd starten.

[ Voor 13% gewijzigd door Verwijderd op 14-09-2005 15:30 ]

[ACM]

Verwijderd schreef op woensdag 14 september 2005 @ 15:11:
Is het niet zo dat nu de verschillen twee keer mee geteld worden? Want ik krijg nu bij een overlapping van 35 minuten de waarde 01:10 terug!

Vreemd, met wat voor tijden?
Want hier in PostgreSQL kreeg ik met jouw en met jochem's voorbeeld de verwachte waarden terug.

Bij de join vergelijkt hij volgens mijn eerst AgendaPuntID 1 <> AgendaPunt 2 en dan bij de volgende record AgendaPunt 2 <> AgendaPunt 1?

Dat zou het niet mogen zijn, want de begintijden verschillen en dus valt het omgekeerde geval niet in de BETWEEN van de join.

Wat is het resultaat van:

SQL:

SELECT *
FROM AgendaPunt a 
  JOIN AgendaPunt b ON b.StartTijd BETWEEN a.StartTijd AND a.EindTijd 
                       AND a.UserID = b.UserID 
                       AND a.AgendaPuntID <> b.AgendaPuntID  
                       AND a.Datum = b.Datum
WHERE
    a.Datum LIKE '2005-08%'

Als ik de controle er in zet dat de starttijd oplopend moet zijn (zodat de punten niet omgedraaid kunnen worden) dan kan ik niet twee agendapunten hebben die om dezelfde tijd starten.

Wat imho ook niet nodig zou moeten zijn?
Maar let er op dat bovenstaande query niet de netto, maar de bruto overlap weergeeft. Dus als je 2 punten met dezelfde begintijd hebt en een derde punt dat een half uur later dan dat gezamelijke beginpunt eindigt, dan hoort daar dus 2x een half uur uit te komen. Omdat je dan met de eerste SUM ook twee keer dat halve uur te veel hebt geteld.
Hmm... Dat is niet waar, je moet dus dat nog zien te corrigeren. Dan wordt ie weer wat lastiger

[ Voor 29% gewijzigd door ACM op 14-09-2005 15:43 ]

[ACM]

Nou, ik moest-en-zou het oplossen

De aanpak heb ik afgeleid van die iemand hierboven met zijn bitjes aanvinken:
stap 1 is selecteer eerst alle afspraken waar er geen overlap meer is met een agendapunt dat nog eerder begint:

SQL:

CREATE TEMPORARY TABLE agendasum as SELECT DISTINCT a.userid, 
    a.starttijd, a.eindtijd
FROM agenda a
  LEFT JOIN agenda b ON b.starttijd < a.starttijd AND b.eindtijd > a.starttijd
                       AND a.userid = b.userid
                       AND a.id <> b.id 
WHERE
b.id IS NULL;

Deze query is al mysql-compatible, anderen zouden een NOT EXISTS in de where clause kunnen overwegen ipv de join.

Stap 2 is een herhalende stap:

SQL:

UPDATE agendasum SET 
eindtijd = (SELECT MAX(a.eindtijd) FROM agenda a WHERE 
            agendasum.eindtijd > a.starttijd AND agendasum.eindtijd < a.eindtijd 
                    and a.userid = agendasum.userid)
WHERE
 eindtijd < (SELECT MAX(a.eindtijd) FROM agenda a WHERE 
                 agendasum.eindtijd > a.starttijd AND agendasum.eindtijd < a.eindtijd 
                      and a.userid = agendasum.userid)

En deze query dan herhalen tot er niks meer wijzigt in die temporary table. Vervolgens nog de verschillen in die tabel met mijn originele eerste query bij elkaar optellen en je zou een heel eind moeten komen.

Dit ziet er eng uit, maar doordat Postgres geen multi-table-update kent (of ik de syntax ervan niet ken), heb ik dezelfde subselect moeten hergebruiken. Vziw kan bovenstaande met alle mysql's > 4.0 als je gebruik maakt van handige joins in een multi-table-update. Iets ala:
UPDATE agenda a JOIN agenda b ON ... SET a.eindtijd = MAX(b.eindtijd) GROUP BY a.id
werkt dat niet, dan moet je nieuwe temporary tables maken, tot er niks meer verschilt tov de vorige.

[Verwijderd]

Heel erg bedankt ACM, hier heb ik super veel aan
Ik heb het nu voor elkaar gekregen om alle dubbel geplande tijd, van de totale tijd af te trekken.

[ACM]

Kan je nog uit de doeken doen hoe je het precies op wist te lossen?

Mijn eerste weg werd nogal lastig gevallen door stukjes dubbele overlap die te vaak werden afgetrokken van het totaal. De tweede ga ik juist vanaf de andere kant, gewoon door domweg de stukken samen te vatten, tot er niks meer samen te vatten is.

[Verwijderd]

Er was al een systeem wat een totaal aantal uren berekende, maar daar moest ik nu dus die dubbele uren uit filteren. Daarom heb ik er voor gekozen om het systeem te laten wat het was, en later een slag te doen om de dubbel gerekende uren er af te halen. Omdat ik php arrays werk was dat vrij simpel bij te passen.

De vergelijking a.AgendaPuntID < b.AgendaPuntID is voor wat ik al eerder aangaf in dit topic, dat dubbele afspraken met een zelfde begin en eind tijd dubbel er af gehaald werden. Omdat agendapuntid een auto increment column is werkt dat prima voor mij.

code:

SELECT a.UserID,  
    SEC_TO_TIME(SUM( 
      CASE WHEN b.EindTijd < a.EindTijd THEN TIME_TO_SEC(b.EindTijd) ELSE TIME_TO_SEC(a.EindTijd) END  
         - TIME_TO_SEC(b.StartTijd) 
   ))
FROM AgendaPunt a 
  JOIN AgendaPunt b ON b.StartTijd BETWEEN a.StartTijd AND a.EindTijd 
                       AND a.UserID = b.UserID 
                       AND a.AgendaPuntID <> b.AgendaPuntID  
                       AND a.Datum = b.Datum
                       AND 
                       (
                        a.StartTijd < b.StartTijd
                        OR
                        a.AgendaPuntID < b.AgendaPuntID
                       )
WHERE
    a.Datum LIKE '2005-08%'
GROUP BY a.UserID