[SQL] Maximum aantal resultaten per seconde

[Verwijderd]

Graag wil ik uit een tabel met gesprekken, weten hoeveel gesprekken er maximaal gelijktijdig zijn gevoerd.

De tabel met gesprekken heeft de volgende indeling:

- id (INT)
- anummer (INT)
- bnummer (INT)
- van (DATETIME)
- tot (DATETIME)

Ik hoop dat dit met een enkele of een paar query's mogelijk is.

Heeft iemand een idee hoe ik dit kan inrichten?

Het alternatief is per seconde kijken of een gesprek wordt gevoerd (totaal 84.400 per dag met een cronjob)

Ik gebruik PHP 5.2.5 en MySQL 5, alvast bedankt voor jullie medewerking!

[RobIII]

Wat heeft dit met SEA te maken?
Waar hoort mijn topic?
SEA >> PRG

En verder: wat heb je zelf al geprobeerd?

[.oisyn]

Heb je al bedacht hoe je dat met de hand op zou moeten lossen? Dat idee heb ik namelijk niet, en als de een query moet bedenken zonder dat je dat al weet dan loop je idd vast .

Overigens denk ik niet dat het met een query te doen is. Je hebt op z'n minst een stored procedure nodig als je het in de db zelf al op wilt lossen.

[justmental]

Het maximum aantal gesprekken kan alleen bereikt worden op de begintijd van een gesprek.
Gelijktijdige gesprekken van dit gesprek hebben deze begintijd tussen hun begintijd en eindtijd in zitten.
Een bijzonder geval hierbij is het piekgesprek zelf, dus de telling moet altijd eentje minder zijn.

[Noork]

mmh beetje vaag topic. Je kan toch gewoon een select uitvoeren, waarbij je dan alle records selecteert waarbij de eindtijd nog leeg is? Ik neem aan dat die leeg is als het gesprek nog bezig is. Hoe je dit verder uitvoert, met cronjob of niet, is afhankelijk van je wensen natuurlijk.

edit:
oeps, ik had de vraag niet helemaal door.

[ Voor 9% gewijzigd door Noork op 13-05-2008 12:59 ]

[.oisyn]

justmental schreef op dinsdag 13 mei 2008 @ 12:55:
Het maximum aantal gesprekken kan alleen bereikt worden op de begintijd van een gesprek.

Nou ja eigenlijk tussen de begintijd van een gesprek en de eindtijd van het eerstvolgende eindigende gesprek, maar als je de standaard haakjes-tellen-methode aanhoudt dan bereik je dat maximum idd bij de start van een gesprek (je zou ook andersom kunnen tellen namelijk ).

[dajappie]

Alleen in SQL lijkt me erg lastig. Efficientst zou waarschijnlijk zijn om een array te bouwen van 1440 of 86400 regels (elke minuut of seconde van een dag en een teller), dan eenmalig alle records van een dag te selecteren en per record in de array aan te geven in welke periodes het gesprek bezig was. Vervolgens orden je de array op aantal en daar heb je je maximum. Iets dergelijks kan je misschien met een tijdelijke tabel en een subselect ook doen, maar als je dan niet indexeert op tijd wordt het vast niet heel snel aangezien je veel meer queries moet doen.

[RobIII]

dajappie schreef op dinsdag 13 mei 2008 @ 13:20:
Vervolgens orden je de array op aantal en daar heb je je maximum.

Waarom nog ordenen? Je kunt tijdens het vullen toch al prima de max bijhouden? En desnoods race je 1 keer door je array om de max te vinden; nog altijd sneller dan een sort

[Verwijderd]

Bedankt voor de snelle reacties. Het probleem wat ik had was het grote aantal query's wat nodig was (script reageerde veel te langzaam). Ik zal het nu proberen om de manier zoals dajappie die omschreef. Een array maken met php en dan alle records ophalen en de (seconden)array vullen.

[.oisyn]

dajappie schreef op dinsdag 13 mei 2008 @ 13:20:
Alleen in SQL lijkt me erg lastig. Efficientst zou waarschijnlijk zijn om een array te bouwen van 1440 of 86400 regels (elke minuut of seconde van een dag en een teller)

En dan zit je dus per seconde of per minuut te werken, wat niet per se hoeft te kloppen. Het handigst is waar ik al naar hintte: zet de tabel om in events - een begin-event en een eind-event. Gooi alle events op een hoop en sorteer die op tijd. Loop er dan in volgorde overheen, waarbij je een tellertje bijhoudt die bij 0 begint en +1 doet bij een nieuw gesprek en -1 bij een eindigend gesprek. Hou verder nog de maximale waarde bij.

Als je over alle gegevens bent gelopen is je maximale waarde het maximum aantal gesprekken op een bepaald moment.

[Bosmonster]

Andere manier dan achteraf opzoeken is misschien in je applicatie bijhouden hoeveel actieve gesprekken er zijn (kwestie van +1 bij start en -1 bij einde gesprek). Hierbij kun je ook even kijken of het huidige aantal hoger is dan het maximum van die dag (of andere eenheid) en zo ja dat maximum vervangen.

Zo zou je uiteindelijk een tabel overhouden met per eenheid (uur, dag, wat je wilt) het maximum aantal gesprekken tegelijk. Zo kun je ook zonder hele zware bewerkingen achteraf hier statistieken aan verbinden over langere tijd.

Edit: lees nu .oisyns reactie pas Zegt ongeveer hetzelfde, maar dan alsnog achteraf. Keuze is aan TS natuurlijk. Ik geef zelf vaak voorkeur aan eenvoudige gegevens continu bijhouden ipv zware bewerkingen achteraf.

[ Voor 17% gewijzigd door Bosmonster op 13-05-2008 14:22 ]

[pkuppens]

.oisyn schreef op dinsdag 13 mei 2008 @ 13:53:
[...]

En dan zit je dus per seconde of per minuut te werken, wat niet per se hoeft te kloppen. Het handigst is waar ik al naar hintte: zet de tabel om in events - een begin-event en een eind-event. Gooi alle events op een hoop en sorteer die op tijd. Loop er dan in volgorde overheen, waarbij je een tellertje bijhoudt die bij 0 begint en +1 doet bij een nieuw gesprek en -1 bij een eindigend gesprek. Hou verder nog de maximale waarde bij.

Als je over alle gegevens bent gelopen is je maximale waarde het maximum aantal gesprekken op een bepaald moment.

What he says

Iets andere smaak daarvan is een tellertje bijhouden voor ieder tegengekomen tijdstip van het aantal gesprekken dat ooit gestart is en ooit ge-eindigd is.
Iets SQL-esquer?! Allemaal wel makkelijk in extra tabellen te updaten.
Mag je alsnog wel maximaliseren over het verschil, sorteren e.d..

[.oisyn]

pkuppens schreef op dinsdag 13 mei 2008 @ 14:32:
What he says

Iets andere smaak daarvan is een tellertje bijhouden voor ieder tegengekomen tijdstip van het aantal gesprekken dat ooit gestart is en ooit ge-eindigd is.

Da's idd ook wel een goede. Die data is met een simpele query wel te krijgen. Ook updates zijn goed te doen - een insert betekent een extra startcount voor alle rijen met een starttijd groter dan de starttijd van de nieuwe entry en een extra eindcount voor alle rijen met een eindtijd groter dan de eindtijd van de nieuwe entry. Bij deletes een vergelijkbare update (aftrekken ipv optellen). Sterker nog, je hebt niet eens aparte counters nodig - je kunt ook gewoon direct het verschil in de kolom opslaan. En dan moet je dus ophogen/verlagen bij alle gesprekken die begonnen zijn tussen starttijd en eindtijd bij resp. een insert en een delete.

[pkuppens]

Verwijderd schreef op dinsdag 13 mei 2008 @ 13:41:
Bedankt voor de snelle reacties. Het probleem wat ik had was het grote aantal query's wat nodig was (script reageerde veel te langzaam). Ik zal het nu proberen om de manier zoals dajappie die omschreef. Een array maken met php en dan alle records ophalen en de (seconden)array vullen.

Begin alsjeblieft niet met dat idee. Je hele bouwwerk valt om zodra er een keer iemand besluit om 23:59 voor 2 minuten te gaan bellen.
Of iemand vindt ineens de microsecondes ook belangrijk.

[dusty]

Er ontbreekt nogal wat aan je vraag wat je wilt, op een bepaalde dag, bepaald uur, bepaald gesprekID...

Dus hier een pseudo query uit de losse pols (zonder te testen) op bepaald gesprek om te kijken hoeveel gesprekken aan de gang was bij het gesprekid 48747.

code:

select
  a.id,
  a.van,
  a.tot,
  count(b.id) as TotalConcurrentCalls
from
  calllog a
  left join calllog b on (
    ( a.van between b.van and b.tot )
    or
    ( a.tot between b.van and b.tot )
    or
    ( b.van between a.van and a.tot )
    or
    ( b.tot between a.van and a.tot )
  )
where
  a.id=48747
group by
  a.id,
  a.van,
  a.tot
order by
  count(b.id)
limit 0,1

[.oisyn]

Dat was de vraag niet. De vraag was wat het maximaal aantal gesprekken tegelijk was. Als gesprek A start, en daarop volgen B, C en D allemaal na elkaar, en dan einigt A weer, dan tel jij er 4, maar het maximum was maar 2. Bovendien kan het ook korter dan die 4 betweens, namelijk: b.van < a.tot AND a.van < b.tot

[dusty]

Zodra hij de query gaat aanpassen aan wat hij exact wilt ( calls/sec ) zal hij toch "between" gaan gebruiken.

[.oisyn]

dusty schreef op dinsdag 13 mei 2008 @ 16:10:
Zodra hij de query gaat aanpassen aan wat hij exact wilt ( calls/sec )

Grappig dat jij weet wat hij exact wilt terwijl hij toch echt iets anders zegt in z'n topic . En ik zei niet dat de between in z'n algemeenheid overbodig was, ik zei dat dat wat jij deed korter opgeschreven kon worden.

Wat in z'n algemeenheid dus wel zo is is dat als je twee ranges op overlap wilt testen, dat een (range1.min <= range2.max && range2.min <= range1.max) volstaat, en je dus niet hoeft te testen of elk van de 4 punten in de andere range voorkomt. Vergelijkbaar (en wellicht wat intuitiever) is de test om te kijken of ze juist niet overlappen: range1.min > range2.max || range2.min > range1.max. Immers, als het minimum van de ene range na het maximum van de andere komt kan er nooit een overlap zijn, en vice versa.

[ Voor 41% gewijzigd door .oisyn op 13-05-2008 16:58 ]

[Voutloos]

Jep, de overlappende periodes vraag komt zo'n beetje maandelijks terug, zie oa Voutloos in "\[PHP/MySQL]Event selecteren binnen tijdg..." .

[.oisyn]

M'n occlusion culling code staat ook vol met dat soort tests

[ACM]

dajappie schreef op dinsdag 13 mei 2008 @ 13:20:
Alleen in SQL lijkt me erg lastig.

Het is bijzonder lastig - zo niet onmogelijk - in SQL. Je moet namelijk conditioneel relaties gaan leggen tussen de records van een set waarbij de condities veranderen tijdens de constructie van de set en dat hoort helemaal niet in SQL.

Efficientst zou waarschijnlijk zijn om een array te bouwen van 1440 of 86400 regels (elke minuut of seconde van een dag en een teller), dan eenmalig alle records van een dag te selecteren en per record in de array aan te geven in welke periodes het gesprek bezig was. Vervolgens orden je de array op aantal en daar heb je je maximum. Iets dergelijks kan je misschien met een tijdelijke tabel en een subselect ook doen, maar als je dan niet indexeert op tijd wordt het vast niet heel snel aangezien je veel meer queries moet doen.

Zelfs als je wel indexeert op tijd is dat erg vervelend om te doen. Bij 10.000 records met willekeurige starttijden over de dag verdeed en willekeurige eindtijden daar maximaal een uur na, kostte het mijn postgresql installatie ruim andehalve minuut om te vinden welke van de 86400 seconden binnen de meeste gesprekken viel.

De procedure die .oisyn beschrijft is echter ook prima in een procedurele SQL-taal te implementeren (zoals plpgsql in PostgreSQL) en daarmee kostte het minder dan 90ms.

Je hoeft daar overigens niet per se losse events van je tabel van te maken, UNION ALL is natuurlijk een prima vriend op zo'n moment:

SQL:

-- tabel met 10.000 willekeurige begintijden op vandaag
-- en eindtijd daar willekeurig binnen 3600 seconden na
CREATE TABLE dateranges AS
SELECT id, starttime,
  starttime + (INTERVAL '1 SECOND' * RANDOM() * 3600) AS endtime
FROM
  (SELECT generate_series as id,
       CURRENT_DATE::TIMESTAMP + (INTERVAL '1 SECOND' * RANDOM() * 86400)
              AS starttime
    FROM generate_series(1,10000)) AS dateseries;

CREATE INDEX dateranges_start_end_idx ON dateranges (starttime, endtime);

-- Deze select duurt zo'n anderhalve minuut bij mij
SELECT timemoment, count(*) as cnt
FROM
  (SELECT CURRENT_DATE::timestamp
                     + (INTERVAL '1 SECOND' * generate_series)
                 as timemoment
     FROM generate_series(0, 86399)) as t
JOIN dateranges d ON timemoment BETWEEN d.starttime AND d.endtime
GROUP BY timemoment
ORDER BY cnt DESC;
-- Time: 91044.799 ms

-- En deze procedure is zo klaar:
CREATE OR REPLACE FUNCTION count_max_usage () 
    RETURNS integer AS $$
DECLARE 
    counter INTEGER;
    maxcount INTEGER;
    temp RECORD;
BEGIN
    maxcount := 0;
    counter := 0;
    
    FOR temp IN 
        (SELECT 1 as incr, starttime as tm FROM dateranges
         UNION ALL
         SELECT -1 as incr, endtime as tm FROM dateranges) ORDER BY tm
    LOOP
        counter := counter + temp.incr;
        IF counter > maxcount THEN 
            maxcount := counter;
        END IF;
    END LOOP;
    
    RETURN maxcount;
END 
$$ LANGUAGE plpgsql;

SELECT count_max_usage();
-- Time: 89.051 ms

Ik weet niet hoe goed MySQL's functionele taal is in 5.1, maar die PostgreSQL-functie zou je op zich vrij eenvoudig moeten kunnen vertalen naar een functie in MySQL.

[OnTracK]

Ik had een tijdje terug ongeveer hetzelfde nodig, het maximale aantal events op dezelfde dag. Volgens mij moet dat prima toepasbaar zijn op jouw geval.

SQL:

SELECT
    COUNT(DISTINCT b.id) AS nr_conversations
FROM
    call_log a
    INNER JOIN call_log b ON a.van BETWEEN b.van AND COALESCE(b.tot, NOW())
WHERE
    1
GROUP BY
    a.van
ORDER BY
    nr_conversations DESC
LIMIT 1

Mijn grootste probleem was dat je een soort dubbele group by nodig hebt. Eentje die een count doet van conversaties op dezelfde momenten, en eentje die het maximale nummer van die counts pakt. Dat heb ik opgelost door het maximale nummer te bepalen door een order by + limit 1.

Alleen gezien het aantal ervaren mensen dat hier aangeeft dat het zo goed als onmogelijk is dit met SQL te doen, twijfel ik wel. Iemand die kan bevestigen dat dit werkt?

[ Voor 5% gewijzigd door OnTracK op 13-05-2008 20:06 ]

[ACM]

Als je het probleem omdraait - en dus netjes in Sets gaat denken - kan het wel in SQL.

Het moment met de meeste calls tegelijk, is per definitie als de meest recente call van die specifieke subset begonnen is, totdat de eerstvolgende call daarna eindigt. En dat laatste kan die laatst gestartte call zijn, maar ook een van de anderen waar ie na begon.

Oftewel, voor elke call kijk je binnen hoeveel andere calls zijn begintijd valt, telt er een bij op, sorteerd het omgekeerd en je weet het drukste moment.

Oftewel, in SQL:

SQL:

SELECT 
d.starttime, d.endtime,
(SELECT COUNT(*) 
    FROM dateranges ds
    WHERE d.starttime
            BETWEEN ds.starttime AND ds.endtime AND ds.id <> d.id) + 1 as cnt
FROM dateranges d
GROUP BY d.id, d.starttime, d.endtime
ORDER BY cnt DESC
LIMIT 1

Duurt helaas nog 6 seconden bij mij, maar dat is al een stuk beter dan de 90 voor de eerste aanpak.

OnTracK schreef op dinsdag 13 mei 2008 @ 19:59:
[code]

Afgezien van de (imho) overbodige DISTINCT en de WHERE-clause, doet die van jou inderdaad wat hier gevraagd wordt. Hij is semantisch min of meer hetzelfde als de mijne.

Mijn grootste probleem was dat je een soort dubbele group by nodig hebt. Eentje die een count doet van conversaties op dezelfde momenten, en eentje die het maximale nummer van die counts pakt. Dat heb ik opgelost door het maximale nummer te bepalen door een order by + limit 1.

Sja, je kan de boel ook in een nieuwe SELECT verpakken en er een MAX overheen halen... de order by + limit werkt ook prima in MySQL.

Alleen gezien het aantal ervaren mensen dat hier aangeeft dat het zo goed als onmogelijk is dit met SQL te doen, twijfel ik wel. Iemand die kan bevestigen dat dit werkt?

Het grootste manco dat ik aan de jouwe zie is dat het heel erg impliciet werkt als er maar 1 record is op een dag en dat je het record dat je aan het bekijken bent ook heel impliciet meetelt. Maar afgezien daarvan heb je voor zover ik kan zien inderdaad een oplossing voor het gestelde probleem gepost. Dat manco zou je nog op kunnen lossen door de INNER join naar een LEFT te veranderen, maar je kan het ook goed proberen te onthouden zodat je bij een wijziging van je ON-clause niet ineens een hele rustige dag (of een korter bereik) verpest

[ Voor 45% gewijzigd door ACM op 13-05-2008 20:17 ]

[OnTracK]

[b][message=30062813,noline]

[...]

Afgezien van de (imho) overbodige DISTINCT en de WHERE-clause, doet die van jou inderdaad wat hier gevraagd wordt. Hij is semantisch min of meer hetzelfde als de mijne.

Komt omdat ik in mijn oorspronkelijke query wel wat meer join'de en selecteerde, ik had even geen zin om uit te pluizen wat er nou ook alweer precies nodig was. En of een WHERE in MySQL nou verplicht was...

Mijn oorspronkelijk query had natuurlijk een iets ander doel. Daarom was ik ook niet zeker of deze toepasbaar was.

[...]

Sja, je kan de boel ook in een nieuwe SELECT verpakken en er een MAX overheen halen... de order by + limit werkt ook prima in MySQL.

Dat werkt (voor zover ik weet) niet in MySQL (misschien alleen MySQL 4?)

[ Voor 8% gewijzigd door OnTracK op 13-05-2008 20:19 ]

[ACM]

OnTracK schreef op dinsdag 13 mei 2008 @ 20:17:
Dat werkt (voor zover ik weet) niet in MySQL (misschien alleen MySQL 4?)

Subqueries werken vanaf 4.1, maar zelfs nu in 5.0 nog niet helemaal soepeltjes. Dus jouw variant is waarschijnlijk sneller in MySQL. Maar 'SELECT MAX(foo.cnt) FROM (SELECT count(b.id) as cnt FROM ...) as foo' zou wel al sinds 4.1.0 ofzo moeten werken.

Als je nu nog aan 3.x of 4.0 vast zou je bij je provider/sysadmin aan de bel moeten trekken. De performance van 4.1 en 5.0 zijn aardig beter en de veiligheid van die twee deprecated versies zal ook wel wat te wensen overlaten.

En ik heb nog nooit gehoord van een verplichte WHERE in MySQL. Zelfs de FROM is dat niet.

[ Voor 6% gewijzigd door ACM op 13-05-2008 20:29 ]

[OnTracK]

En ik heb nog nooit gehoord van een verplichte WHERE in MySQL. Zelfs de FROM is dat niet.

Komt natuurlijk door dat PHPmyadmin, die altijd een WHERE 1 erin gooit als je niets specificeert. Ik leer verder ook maar on-the-fly.

We (roeivereniging Gyas) zitten overigens op MySQL 4.1.nogwat, maar dat is ook niet mijn verantwoordelijkheid.

[ Voor 50% gewijzigd door OnTracK op 13-05-2008 23:24 ]