[SQL] Max Count in verzameling

[Verwijderd]

Pfff, zit er al even mee te stoeien totdat ik bedacht: GOT!

Ik wil het volgende weten: het maximum aantal autos wat zich tegelijk op de weg heeft bevonden in een bepaald tijdvak, bijv van 11u tot 19 u.

Ik heb het volgende uitgedacht:
- ik selecteer alle tijdstippen binnen dat tijdvak waarop iets is gebeurt (auto vertrok of kwam terug) (=gewone select)
- ik ga voor elk van die tijdstippen bepalen hoeveel auto's er TOEN op de weg zat (=select count())
- en dan selecteer ik daaruit de maximale waarde (= max)

Ik heb dus wel voor ogen hoe ik het gevraagde kan vinden, maar weet dit niet in SQL om te zetten. Kan hier iemand mij op weg helpen? Kan het in 1 SQL-statement? En hoe dan (ongeveer)?

Alvast herzlichen dank!

[ Voor 4% gewijzigd door Verwijderd op 27-01-2005 13:29 ]

[Swa-baldie]

2 dingen
-group by
-count

suc6

[Verwijderd]

Tsja, dat is toch iets te summier om mij verder te helpen. Zo ver kwam ik ook ongeveer. Het gaat mij erom hoe die verschillende statements in elkaar zijn te passen..

[P_de_B]

Je topicstart is te summier om je een veel gedetailleerder antwoord te geven. Als je nu eens je tabellen post, en welke database je gebruikt is het al wat eenvoudiger om je te antwoorden.

[Brakkie]

Verwijderd schreef op donderdag 27 januari 2005 @ 13:34:
Tsja, dat is toch iets te summier om mij verder te helpen. Zo ver kwam ik ook ongeveer. Het gaat mij erom hoe die verschillende statements in elkaar zijn te passen..

Het lijkt me handig om te vermelden welke database software je gebruikt. Hoe je tabellen er uitzien en wat je zelf al geprobeerd hebt. Dat laatste is namelijk een vereiste om hier een topic te kunnen beginnen.

Misschien dat dit in de richting is:

code:

SELECT 
              max(aantal) as max_aantal 
FROM 
             (SELECT 
                           count(id) as aantal 
              FROM 
                           gebeurtenissen 
              WHERE 
                           tijdstip
              BETWEEN 
                           tijd1 
              AND 
                           tijd2 
              GROUP BY 
                           tijdstip
              )
AS
             temp_table

edit:
Niet echt de meest efficiente oplossing, maar ach......

[ Voor 43% gewijzigd door Brakkie op 27-01-2005 14:00 ]

[Verwijderd]

OK, wat meer info.
Ik ben in VBA bezig een SQLstring samen te stellen om die, via passthrough, door te geven aan Oracle. De 'tijdstippen' waarop iets gebeurt zitten in de kolommen UITRIJTIJD en INRIJTIJD. De verzameling tijdstippen is dan:

code:

SELECT UITRIJTIJD, INRIJTIJD
FROM DIENSTAUTO
WHERE UITRIJTIJD BETWEEN begintijdvak AND eindtijdvak
AND INRIJTIJD BETWEEN begintijdvak AND eindtijdvak

De count (hoeveel autos zijn er NU op de weg) is iets als:

code:

SELECT COUNT(*)
FROM DIENSTAUTO
WHERE UITRIJTIJD < nu
AND INRIJTIJD > nu

Dat moet dan voor elk van de tijdstippen worden bepaald. En de maximale waarde daarin, dat is degene die ik zoek. En dan moet daar weer een 'max' overheen...

Ik zit al flink wat te stoeien maar ik kan het gewoon niet in een logische volgorde zetten. Sorry hoor...

[ Voor 10% gewijzigd door Verwijderd op 27-01-2005 13:52 ]

[Swa-baldie]

code:

select count(*), tijdvak
from tabel
group by tijdvak

summier genoeg? Als wij je datamodel niet kennen dan kunnen we ook niet helpen

[ Voor 45% gewijzigd door Swa-baldie op 27-01-2005 14:36 . Reden: tekstueel ]

[Dido]

Om te beginnen denk ik niet dat dit klopt:

De verzameling tijdstippen is dan:

code:

SELECT UITRIJTIJD, INRIJTIJD
FROM DIENSTAUTO
WHERE UITRIJTIJD BETWEEN begintijdvak AND eindtijdvak
AND INRIJTIJD BETWEEN begintijdvak AND eindtijdvak

Aangezien je dan alleen de inrij/uitrijtijd regels terugkrijgt die volledig binnen je tijdvak vallen. Als een auto uitrijdt binnen je tijdvak, maar niet binnen het tijdvak terugkomt, wordt ie niet geslecteerd.

Verder vraag ik me af wat je doet met auto's die voor je tijdvak uit, en na je tijdvak inrijden. Die negeer je, terwijl ze je hele tijdvak op de weg hebben gezeten.

[Verwijderd]

[quote]Swa-baldie schreef op donderdag 27 januari 2005 @ 13:52:

code:

select count(*), tijdvak
from tabel
group by tijdvak

Dit kan niet omdat tijdvak niet bestaat als veld in de database. Dat zou dan UITRIJTIJD en INRIJTIJD moeten zijn..?

Dido schreef op donderdag 27 januari 2005 @ 14:02:
Om te beginnen denk ik niet dat dit klopt:

[...]

Aangezien je dan alleen de inrij/uitrijtijd regels terugkrijgt die volledig binnen je tijdvak vallen. Als een auto uitrijdt binnen je tijdvak, maar niet binnen het tijdvak terugkomt, wordt ie niet geslecteerd.

Verder vraag ik me af wat je doet met auto's die voor je tijdvak uit, en na je tijdvak inrijden. Die negeer je, terwijl ze je hele tijdvak op de weg hebben gezeten.

Scherp! Inderdaad, dat klopt niet! Bedankt! Correctie:
Oeps, klopt toch wel! Dit statement geeft alleen de verzamelingen tijdstippen waarop iets verandert (auto komt binnen of vertrekt). En dat statement geeft alle uitrijtijden binnen de periode, en alle inrijtijden binnen dezelfde periode als resultaat. Het statement is niet bedoeld om de aantal bussen op een tijdstip te tellen he.

code:

SELECT UITRIJTIJD, INRIJTIJD
FROM DIENSTAUTO
WHERE (UITRIJTIJD BETWEEN begintijdvak AND eindtijdvak 
AND INRIJTIJD BETWEEN begintijdvak AND eindtijdvak)

Ik weet btw dat er voor het begin van het tijdvak geen autos op de weg zijn.

[ Voor 19% gewijzigd door Verwijderd op 27-01-2005 14:38 ]

[Verwijderd]

Verwijderd schreef op donderdag 27 januari 2005 @ 13:28:
Ik wil het volgende weten: het maximum aantal autos wat zich tegelijk op de weg heeft bevonden in een bepaald tijdvak, bijv van 11u tot 19 u.

Tenzij je een record per dienstauto per kwartier of zo bijhoudt, gaat je dat niet in 1 query lukken.
Je kunt wel het totaal aantal auto's in dat tijdsvak bepalen:

code:

select COUNT(*) from DIENSTAUTO
where UITRIJTIJD <= eindtijdvak
and INRIJTIJD > begintijdvak

Maar om een max te bepalen zul je zo'n query voor bv. elk kwartier in het tijdvak moeten uitvoeren. Of je moet alle records langslopen in chronologische volgorde, en dan 1 bij 't totaal optellen wanneer je een geldige uitrijtijd tegenkomt, en 1 ervan aftrekken wanneer je weer een inrijtijd ziet (en dan de max waarde bijhouden).
Dat kun je clientside of in een stored proc doen, maar in 1 "gewone" query gaat 't je niet lukken.

[Verwijderd]

Verwijderd schreef op donderdag 27 januari 2005 @ 18:41:
[...]

Tenzij je een record per dienstauto per kwartier of zo bijhoudt, gaat je dat niet in 1 query lukken.
Je kunt wel het totaal aantal auto's in dat tijdsvak bepalen:

code:

select COUNT(*) from DIENSTAUTO
where UITRIJTIJD <= eindtijdvak
and INRIJTIJD > begintijdvak

Maar om een max te bepalen zul je zo'n query voor bv. elk kwartier in het tijdvak moeten uitvoeren. Of je moet alle records langslopen in chronologische volgorde, en dan 1 bij 't totaal optellen wanneer je een geldige uitrijtijd tegenkomt, en 1 ervan aftrekken wanneer je weer een inrijtijd ziet (en dan de max waarde bijhouden).
Dat kun je clientside of in een stored proc doen, maar in 1 "gewone" query gaat 't je niet lukken.

Jou query geeft het aantal autos wat binnen het tijdvak rijdt. Maar stel nou dat er in de eerste helft van het tijdvak 10 autos rijden, en in de 2e helft weer 10: dan is de piek 10. Maar het resultaat van je query is 20.

Ik wil de count-query net elk kwartier ofzo uitvoeren, maar voor elke tijd die in mijn tijdenverzameling zit (=alle uitrijtijden en alle inrijtijden binnen het tijdvak). En dan het maximum.

Heel erg bedankt voor het meedenken! Serieus, ERG bedankt! Tis kei-pittig man, kword er gek van. Tgaat gewoon boven mijn pet, (iig voor nu)

[Vuurvlieg]

4 queries benodigd en stukje VBA code
q1 geeft het aantal ingereden auto's voor het begin van het tijdvak.
q1 = select count(inrijtijd) from autotabel where inrijtijd < begintijdvak

q2 geeft het aantal uitgereden auto's voor het einde van het tijdvak.
q2 = select count(uitrijtijd) from autotabel where uitrijtijd < begintijdvak;

q3 geeft alle inrijtijden voor het einde van het tijdvak.
q3 = select inrijtijd from autotabel where inrijtijd > begintijdvak AND inrijtijd < eindtijdvak order by inrijtijd;

q4 geeft alle uittijden voor het einde van het tijdvak.
q4 = select uitrijtijd from autotabel where uitrijtijd > begintijdvak AND uitrijtijd < eindtijdvak order by uitrijtijd;

4 resultsets r1, r2, r3, r4
counter geeft huidige stand van aantal auto's
max geeft max auto's tijdvak
counter en max begint op r1 - r2 (= het aantal auto's dat bij begintijdvak op het stuk weg is)

dan kijken naar eerste tijd na tijdvak
--> inrijtijd eerder dan counter++ en if(counter > max) max = counter
--> uitrijtijd eerder dan counter--
volgende record hetzelfde tot je door beide resultsets heen bent

wat je overhoud is het max in de periode.

[Wally-S]

ok dit denk ik ervan

nieuwe tabel (tijd_tabel)maken met alle mogelijke tijdstippen, dus van 00:00 t/m 23:59

select tijdstip, aantal from
(SELECT ttl.tijdstip , count(*) as aantal
from tijd_tabel ttl, dienstauto
where ttl.tijdstip between begintijd and eindtijd
group by ttl.tijdstip)
order by aantal desc

je krijgt nu een tabel met de tijdstippen en aantal auto's dat zich er op dat moment bevonden gesorteerd op aantal auto's.

effe in MS-Acces geprobeerd en het geeft het goede resultaat.

Succes ermee.

[Verwijderd]

Wally-S schreef op donderdag 27 januari 2005 @ 21:10:
ok dit denk ik ervan

nieuwe tabel (tijd_tabel)maken met alle mogelijke tijdstippen, dus van 00:00 t/m 23:59

select tijdstip, aantal from
(SELECT ttl.tijdstip , count(*) as aantal
from tijd_tabel ttl, dienstauto
where ttl.tijdstip between begintijd and eindtijd
group by ttl.tijdstip)
order by aantal desc

je krijgt nu een tabel met de tijdstippen en aantal auto's dat zich er op dat moment bevonden gesorteerd op aantal auto's.

effe in MS-Acces geprobeerd en het geeft het goede resultaat.

Succes ermee.

Thanks, dit klinkt mij goed in de oren! Alleen probeer ik dan het tabelletje tijd_tabel niet van 00:00 t/m 23:59 te laten beslaan maar telkens te laten vullen met de gewenste tijdstippen.
Dus eerst ttl vullen, dan jou query draaien.

Ik ga ermee aan de slag...

[Wally-S]

Verwijderd schreef op vrijdag 28 januari 2005 @ 07:28:
[...]

Thanks, dit klinkt mij goed in de oren! Alleen probeer ik dan het tabelletje tijd_tabel niet van 00:00 t/m 23:59 te laten beslaan maar telkens te laten vullen met de gewenste tijdstippen.
Dus eerst ttl vullen, dan jou query draaien.

Ik ga ermee aan de slag...

Daar heb ik ook aan gedacht, maar dat gaat denk ik niet lukken.
Niet alle mogelijke tijdstippen zijn namelijk aanwezig.

Tijdstip 10:20 kan bijvoorbeeld de drukst bezette zijn, maar het kan zijn dat dit nooit een begin of eindtijd is. Dus helemaal waterdicht is het niet.

[Verwijderd]

Ik heb het werkend gekregen maar ben er niet tevreden over.

Ik heb mijn toevlucht gezocht in SQL en VBA.
Ik doe een select om alle uitrijtijden te bepalen die binnen de opgegeven periode vallen.
Dan doe ik voor elke uitrijtijd een count hoeveel autos er op dat moment op de weg zijn.
En ik bewaar de max.

Gevolg: ik heb de juiste waarde. Maar.... de snelheid is afhankelijk van het aantal uitrijtijden binnen die periode. En dat kan flink oplopen waardoor dit eigenlijk een onwerkbare oplossing is.

Het moet toch kunnen, denk ik. Ach, ik werk er nog wel aan, dit weekend...

[flipm0]

Als je toch VBA er bij wil gebruiken, kan je volgens mij met 1 query en een lusje toe. Je vraagt gewoon alle in- en uitrijtijden binnen het tijdvak op, de resultaatset ga je vervolgens record voor record doorlopen. Per cyclus doe je dit:

- Wanneer het huidige record een uitrijtijd betreft, tel je bij de waarde in een (tijdelijke) variabele (bv v_current_cars, initieel 0), waarin het huidige aantal auto's dat zich op de weg bevindt staat, 1 op, wanneer het huidige record een inrijtijd bevat, trek je van deze waarde 1 af.

- Vervolgens vergelijk je de waarde in v_current_cars met een andere variabele (bijvoorbeeld v_max_cars), waarin het maximum aantal auto's dat tegelijkertijd op de weg is geweest staat (initeel 0). Wanneer de waarde in v_current_cars hoger is dan de waarde in v_max_cars, vervang je de waarde in v_max_cars door de waarde in v_current_cars.

Wanneer je alle records hebt doorlopen, moet in de tweede variabele (v_max_cars) volgens mij het maximum aantal auto's dat zich tegelijkertijd op de weg heeft bevonden staan.

Suc6!

[Killemov]

@Henk-Wim: Ik denk toch dat je originele uitgangspunt het beste is. Neem daar dan ook de begin- en eindtijden van het te meten tijdvak bij. Op deze manier ben je in ieder geval niet discreet bezig. (elke minuut, kwartier o.i.d.)

tijdstippen = unique [begintijd] union [alle inrijtijden] union [alle uitrijtijden] union [eindtijd]

En dan voor elk tijdstip berekenen hoeveel auto's er zijn. Let met name op de grensgevallen en die gevallen die meer dan het hele tijdvak beslaan.

Oh, ja: dit probleem moet toch zeker met alleen SQL op te lossen zijn, vandaar mijn antwoord. Ik dank dat de oplossing van flipm0 overigens ook zal werken.

[ Voor 16% gewijzigd door Killemov op 29-01-2005 13:34 ]