[MySQL] Max 200 waarden opvragen, tijdstip gelijk verdeeld

[DRvDijk]

Hoi, ik heb een tabel:

code:

id - int
timestamp - datetime
value - varchar

Deze tabel zal vele vele records gaan bevatten, en ik wil er nu een grafiekje van maken. Dat gaat wel lukken, maar nu moet ik een query op de database doen zodat er niet zoveel-duizend result-rows uitkomen, maar slechts 200 (bijvoorbeeld).

Dit doe je natuurlijk met een LIMIT 200. Maarrrr, in dezelfde query is ook een start en eind-datetime opgegeven, en nu wil ik niet de eerste 200 hebben of de laatste 200, maar 200 over de tijd (ongeveer) gelijkmatig verdeelde rijen.

Vraag: hoe doe ik dit?

Mijn idee was in eerste instantie: de tijd die ertussen zit (zeg 3600 seconden) te delen door 200 (=18), en dus een conditie op te nemen dat de timestamp-verschillen wel groter zijn dan 18 (ook dit weet ik niet verder uit te werken). Nadeel hiervan zou dan weer zijn dat je misschien niet precies 200 results hebt.

Edit: Tweede idee (samen met mensen op MSN bedacht): gewoon alle rijen selecteren die aan tijdstip voldoen (begin/eind) gesorteerd op dat tijdstip, en dan alleen de i-de rij lezen (dus 3600 rijen, dan alleen rij 0+a*18, voor a=0 tm 200).. Dit zal wel gaan werken, maar net is anders..

Ideeën? Dank alvast!

[ Voor 14% gewijzigd door DRvDijk op 09-10-2005 14:52 ]

[Boss]

Volgens mij is dit in SQL niet te doen. Misschien kan je eerst met een randomizer in een extra kolom een aantal willekeurige waarden zetten. Daar dan op sorteren en dan de eerst x nemen?

[NoFearWizz]

ik denk dat je het met je php of asp code moet oplossen want in sql kan dat idd niet zonder extra velden..

[DRvDijk]

Hmm, dank alvast.

Een andere oplossing in mijn geval zou kunnen zijn dat ik niet per ce het aantal records beperk, maar de tijd die tussen de records zelf zit, dus bijvoorbeeld timestamp van rij x moet minimaal 5 verschillen van timestamp van rij y.

Dit is bij mij ook een probleem.. ?

[RobIII]

NoFearWizz schreef op zondag 09 oktober 2005 @ 15:01:
ik denk dat je het met je php of asp code moet oplossen want in sql kan dat idd niet zonder extra velden..

Kan prima hoor, kwestie van een beetje creatief groeperen etc.

SQL:

select datediff(s,@dtStart,timestamp)/datediff(s,@dtStart,@dtEnd)/200, count(id)
from mytable
where timestamp between @dtStart and @dtEnd
group by datediff(s,@dtStart,timestamp)/datediff(s,@dtStart,@dtEnd)/200

Of, een beetje duidelijker:

SQL:

DECLARE @dtStart as datetime
DECLARE @dtEnd as datetime
DECLARE @interval as int

SET @dtStart = '20051001'
SET @dtEnd = '20051009'
SET @interval = datediff(s,@dtStart,@dtEnd)/200

select datediff(s,@dtStart,timestamp)/@interval, count(id)
from mytable
where timestamp between @dtStart and @dtEnd
group by datediff(s,@dtStart,timestamp)/@interval

Dit geeft hoe dan ook altijd 200 records terug (mits interval niet te klein en voldoende records etc) met een gelijke spreiding.

Ik zal 'm proberen uit te leggen:
Je selecteert (m.b.v. de where) sowieso alleen maar de 'range' die je wil hebben. De interval bepaal je door de 2 datums van elkaar af te trekken en het resultaat daarvan in secondes uitgedrukt deel je door het aantal records dat je wil hebben (in dit geval dus 200). Vervolgens doe je een integer deling, waardoor alle records (uit de range) vallen in 1 van de interval waardes, en daarop groepeer je.
Je zult wel effe de intervalwaardes (+startdatum) moeten terugrekenen naar een datum/tijd om uit te kienen welk record wat is, maar met een simpele datadd van de (interval*colommetintervalwaardes)+dtSart heb je het dan al weer teruggerekend.

SQL:

DECLARE @dtStart as datetime
DECLARE @dtEnd as datetime
DECLARE @interval as int

SET @dtStart = '20050801'
SET @dtEnd = '20050829'
SET @interval = datediff(s,@dtStart,@dtEnd)/200

select DateAdd(s,t.i*@interval, @dtStart) as mydate, t.hits
from (
    select (datediff(s,@dtStart,timestamp)/@interval) as i, count(id) as hits
    from myTable
    where timestemp between @dtStart and @dtEnd
    group by datediff(s,@dtStart,timestamp)/@interval
) as t
order by mydate

Schuif je dat weer in elkaar (minder leesbaar) dan krijg je:

SQL:

select DateAdd(s,t.i*(datediff(s,'20050801','20050829')/200), '20050801') as mydate, t.hits
from (
    select (datediff(s,'20050801',timestamp)/(datediff(s,'20050801','20050829')/200)) as i, count(id) as hits
    from tbl_avatarlog
    where timestamp between '20050801' and '20050829'
    group by datediff(s,'20050801',timestamp)/(datediff(s,'20050801','20050829')/200)
) as t
order by mydate

Voila

/edit: Crap. MySQL.... Ik zie dan altijd MSSQL staan Whishfull thinking waarschijnlijk
Anyway, ik neem aan dat dit ook met MySQL kan. Zo niet, dan heb je nu een goede reden om naar MSSQL over te stappen

[ Voor 157% gewijzigd door RobIII op 09-10-2005 15:48 ]

[DRvDijk]

Eeh, misschien rare vraag, maar datediff heeft toch maar 2 parameters?
Ik kan datediff sowiezo niet gebruiken (te oude MySQL op de site)...

[RobIII]

DRvDijk schreef op zondag 09 oktober 2005 @ 15:46:
Eeh, misschien rare vraag, maar datediff heeft toch maar 2 parameters?
Ik kan datediff sowiezo niet gebruiken (te oude MySQL op de site)...

Zie edit. Maar met mijn uitleg moet je er in MySQL ook uit kunnen komen lijkt me. Zo ingewikkeld is het ook weer niet It ain't exactly rocket science

[DRvDijk]

basic idea: groeperen op (verschil van start-date met een bepaald interval).
Da's wel een idee inderdaad. Nu moet ik nog ff gaan nadenken, want de data die erdoorheen gaat kan ook weer uit verschillende bronnen komen (apart veld sourceid die ik hier niet genoemd heb). Elke bron moet dan max 200 resultaten hebben, dus dan is het weer een andere group by..

Eens zien

[RobIII]

DRvDijk schreef op zondag 09 oktober 2005 @ 15:54:
basic idea: groeperen op (verschil van start-date met een bepaald interval).
Da's wel een idee inderdaad. Nu moet ik nog ff gaan nadenken, want de data die erdoorheen gaat kan ook weer uit verschillende bronnen komen (apart veld sourceid die ik hier niet genoemd heb). Elke bron moet dan max 200 resultaten hebben, dus dan is het weer een andere group by..

Eens zien

Ik heb het hier getest op een tabel met een bult records met timestamps (datetime velden) en het werkt als een zonnetje. Als je andere "eisen" stelt is het dus een kwestie van aanpassen. Het werkt in ieder geval.

[ Voor 3% gewijzigd door RobIII op 09-10-2005 16:18 ]

[DRvDijk]

Yes, het werkt inderdaad

SQL:

SELECT eenhoop FROM tabellen
WHERE voorwaarden
GROUP BY ROUND((timestamp - starttime)/((starttime - endtime)/maxresults - 1)), sourceid
ORDER BY timestamp

MySQL versie zoveel.zoveel doet ook aan integer division, mijne nog niet, vandaar de ROUND

[ Voor 19% gewijzigd door DRvDijk op 09-10-2005 17:28 ]

[cheazers]

DRvDijk schreef op zondag 09 oktober 2005 @ 17:28:
Yes, het werkt inderdaad

SQL:

SELECT eenhoop FROM tabellen
WHERE voorwaarden
GROUP BY ROUND((timestamp - starttime)/((starttime - endtime)/maxresults - 1)), sourceid
ORDER BY timestamp

Zijn starttime en endtime datetime velden? Volgens mij moet je dan datediff gebruiken. Ik krijg dit niet werkend in mysql namelijk. (ook niet met datediff).

[RobIII]

cheazers schreef op woensdag 21 november 2007 @ 13:11:
[...]


Zijn starttime en endtime datetime velden? Volgens mij moet je dan datediff gebruiken. Ik krijg dit niet werkend in mysql namelijk. (ook niet met datediff).

Euh, je hebt gezien dat je een behoorlijk bejaard topic schopt?
Ik neem aan dat TS inmiddels een oplossing heeft gevonden (en misschien wel een nieuwe opdracht )

Als je een soortgelijk probleem hebt, open dan gerust een nieuw topic (maar lees voordat je dat doet wel even onze Programming Beleid Quickstart)

[ Voor 20% gewijzigd door RobIII op 21-11-2007 13:18 ]