[mysql] Laatste 30 items per groepering.

[ZpAz]

Ik heb de volgende tabel met de volgende inhoud

code:

measurements
===========================================
= stn | date                  | temp   | =
= 1   | 01-12-2001 14:30:03   | 2.0    | =
= 1   | 01-12-2001 14:31:45   | 2.1    | = 
= 1   | 03-12-2001 14:30:03   | 1.9    | =
= 2   | 01-12-2001  21:34:31  | 4.5    | =
= 2   | 01-12-2001 12:48:42   | 4.7    | =
= 2   | 03-12-2001 12:49:45   | 4.9    | =
===========================================

maar dan veel meer rijen.

Deze tabel bevat allemaal weer resultaten van verschillende stations (stn). Elke seconde geeft elk station een nieuwe waarde.

Nu wil ik graag de laatste 30 waardes van elk station opvragen, maar enkel als deze daadwerkelijk ook 30 waardes momenteel in de tabel heeft.

Ik heb zitten prutsen met group by en subquerys. Maar ik kom er niet uit.

Ik hoop dat iemand me verder kan helpen.

Vraag geherformuleerd en aangepast, zie dit bericht: ZpAz in "[mysql] Laatste 30 items per groepering."

[ Voor 16% gewijzigd door ZpAz op 25-09-2011 12:54 ]

[Gomez12]

Onder welke situatie kan een station geen 30 waardes meer hebben?

En waarom geen datetime veld?

Maar opzicht zou ik waarschijnlijk (in mysql) een query per station doen. Of simpelweg aan de applicatiekant de resultaten filteren op 30 per station.

Die 30 waardes per station die nekt je. Dat wordt of een megalomane query schrijven die allerlei gefilterde tussenresultaten aan elkaar unioned of gewoon in code het deels oplossen.
Hiervoor is afaik sql gewoon niet zo geschikt. Je kan 1 resultset terugkrijgen en daar allerlei parameters en condities aan verbinden, niet een onbeperkt aantal resultsets met allemaal losse parameters en condities...

[ZpAz]

Je hebt gelijkt het is een datetime veld.

Ik heb twee identieke tabellen. De ene is een Memory tabel (cache) en de andere een InnoDB tabel. Ik krijg elke seconde heel veel data binnen. En soms moet ik bewerken op een kleine selectie van de data doen (maximaal de laatste 30 items per station).

Ik wil dus eigenlijk alle data van de cache tabel verplaatsen naar de uiteindelijke tabel, behalve de laatste 30 rijen per station. Zodat ik hier dingen mee kan doen indien nodig.

Deze verplaatsing wordt periodiek (elke x seconden gedaan), alles behalve de laatste 30 punten per station moeten dan worden verplaatst.

[ Voor 12% gewijzigd door ZpAz op 24-09-2011 18:15 ]

[Gomez12]

Ik zie het waarschijnlijk niet helemaal, maar kan je niet beter in je uiteindelijke tabel een lastupdate time oid bijhouden? Zodat je enkel maar hoeft te kopiëren alles wat >lastupdatetime?

sowieso zou ik niet iets gaan verplaatsen uit een cache-tabel en daar dan weer condities aan gaan hangen (zoals laatste 30 items niet) dat maakt het hopeloos ingewikkeld en foutgevoelig en traag.

waarom hanteer je eigenlijk nog een cache als je daarna megalomane sql-expressies wilt hebben om ermee te gaan werken?

Dit klinkt me in de oren als over-optimalisation die uiteindelijk gaat backfiren omdat je query te ingewikkeld gaat worden.
Wil je een cache hebben, best. Maar houd het dan wel simpel zodat die ook snel blijft, straks ga je een situatie creëren waarin je cache langzamer gaat presteren dan je dbase...

Copieer alles uit je cache en werk die met lifo (of een extreem simpel mechanisme) bij zodat die snel blijft.

[ZpAz]

De laatste 30 items per station (wat er 2500 zijn) is nog best veel. (Welke elke sec wordt upgedated). Vandaar dat ik dacht, in een database kan je veel sneller bij de juiste informatie komen dan met arrays oid te gaan werken. Want dat is er immers voor gemaakt.

Vandaar. Maar als iemand me een duw in de richting kan geven voor de juiste query kom ik er wel uit denk ik.

Volgens mij moet de query helemaal niet zo lastig zijn. Maar een zetje in de juiste richting kan geen kwaad.

[ Voor 20% gewijzigd door ZpAz op 24-09-2011 18:57 ]

[GlowMouse]

http://www.xaprb.com/blog...max-row-per-group-in-sql/

[alex3305]

Waarom niet gewoonweg een simpele query doen waarbij je 'alles' selecteert, van nu naar verleden en dan een limiet van 30 instellen? Of zeg ik dan iets 'doms' ?

[ZpAz]

alex3305 schreef op zaterdag 24 september 2011 @ 21:38:
Waarom niet gewoonweg een simpele query doen waarbij je 'alles' selecteert, van nu naar verleden en dan een limiet van 30 instellen? Of zeg ik dan iets 'doms' ?

Nee dat werkt niet, ik krijg namelijk 'om en om' een resultaat van verschillende stations. Ik wil niet de laatste 30 items. Maar de laatste 30 items per station.

Had een query met een subquery. Maar dat is _veel_ te langzaam op de +- 100.000 rijen.

Denk dat ik het maar inderdaad anders aan moet pakken.

[alex3305]

Kun je het anders niet in een view gooien en dan de views om de zoveel tijd ophalen? Dat kan namelijk helpen in de performance, echter ben ik er bang voor dat cachen niet snel genoeg kan gaan als er om de paar seconden geüpdatet wordt.

[Gomez12]

alex3305 schreef op zaterdag 24 september 2011 @ 22:19:
Kun je het anders niet in een view gooien en dan de views om de zoveel tijd ophalen? Dat kan namelijk helpen in de performance, echter ben ik er bang voor dat cachen niet snel genoeg kan gaan als er om de paar seconden geüpdatet wordt.

Sinds wanneer is een view bij mysql iets anders dan een server-side query?

[BlackHawkDesign]

Zoals gomez al eerder zei, lijkt mij het ook slimmer om dit aan de applicatie kant af te handelen.

Misschien dat je waar meer info kan geven over: Hoe komen die resultaten binnen?

Wellicht is het makkelijker dat als de applicatie de resultaten al binnen krijgt, hij zijn eigen cache bijwerkt en vervolgens de nieuwe resultaten door pusht naar de db voor storage.

[Nactive]

Wat je zou kunnen doen (in een Oracle DB weet niet of het bestaat in MySQL) is gebruik maken van Analytische functies. Zij laten toe om op een rank toe te passen op een deelverzameling van uw resultaat (in dit geval deel je dan op in verzamelingen per station).
Vervolgens zou je dit dan met een CTE (Commen table expressie of een vieuw / subquery indien CTE niet wordt ondersteund) where rank < 30 kunnen weg filteren.

(Zo zou ik het doen in Oracle maar vrees dat MySQL dit niet allemaal ondersteund).

Edit

Wat misschien een andere oplossing is en die eerder zal werken in mysql.

MySQL:

select t1.stn, t1.date, t1.tmp
from tabel1 as t1
join tabel1 as t2 on t1.stn = t2.stn and t1.date <= t2.date
group by t1.stn, t1.date, t1.tmp
having count(1) <= 30

Geen idee of dit helemaal MySQL correcte syntax is maar iets in deze aard zou normaal wel het gewenste resultaat moeten geven.

[ Voor 31% gewijzigd door Nactive op 25-09-2011 10:55 ]

[NomoDigger]

Kan je niet een query maken die voor ieder station de query doet en die dan allemaal unioned

dus

MySQL:

(SELECT data1.tabel FROM tabel WHERE (eisen AND station=1) ORDER BY datum LIMIT 30)
UNION
(SELECT data1.tabel FROM tabel WHERE (eisen AND station=2) ORDER BY datum LIMIT 30)
[...]

Dan hou je overzicht, en krijg je wat je wilt toch?

[ Voor 7% gewijzigd door NomoDigger op 25-09-2011 11:22 ]

[nescafe]

MySQL moet dit wel kunnen, zie ook mijn eerdere post in: nescafe in "[SQL] Maximum aantal rows per unieke FK"

MySQL:

SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn
FROM data
GROUP BY stn, date, temp
HAVING countPerStn <= 30
ORDER BY stn, date desc;

Heb dit niet getest maar volgens mij maak je hier gebruik van het feit dat HAVING pas na de table scan wordt uitgevoerd en dat je daarin ook computed columns mee kan nemen.

[ Voor 21% gewijzigd door nescafe op 25-09-2011 11:34 ]

[ZpAz]

nescafe schreef op zondag 25 september 2011 @ 11:33:
MySQL moet dit wel kunnen, zie ook mijn eerdere post in: nescafe in "[SQL] Maximum aantal rows per unieke FK"

MySQL:

SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn
FROM data
GROUP BY stn, date, temp
HAVING countPerStn <= 30
ORDER BY stn, date desc;

Heb dit niet getest maar volgens mij maak je hier gebruik van het feit dat HAVING pas na de table scan wordt uitgevoerd en dat je daarin ook computed columns mee kan nemen.

Met of zonder de GROUP BY krijg ik trouwens exact dezelfde resultaten terug. Deze lijkt me dan overbodig?

edit:
Die eerste regel lijkt me een ternary operator, correct? En := is toewijzen in Mysql.

Hoezo is de HAVING countPerStn <= 30. Wat ik daar lees lijkt me dat je de rijen wil hebben waar de waarde countPerStn kleiner of gelijk is aan 30 is. Terwijl ik ook resultaten terug krijg waar countPerStn 31 is. Plaatje

[ Voor 47% gewijzigd door ZpAz op 25-09-2011 12:20 ]

[nescafe]

Misschien zijn je meetwaarden niet uniek, hoogt hij wel @a op maar worden de dubbele regels in de GROUP BY samengevoegd. In dat geval moet je even een unieke column toevoegen aan je SELECT en GROUP BY.

In principe is die GROUP BY verplicht, maar MySQL slikt hem ook zonder.

[ZpAz]

Goed, de vraag moet ik anders formuleren. Ik wil de resultaten welke groter zijn dan 30. Dus nummer 31, 32 enz.

Met de volgende query

SQL:

SET @countPerStn = 0;
SET @lastStn = 0;
SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn 
FROM cache 
GROUP BY stn, date
ORDER BY stn, date asc;

(Gebasseerd op bovenstaande minus HAVING)

Krijg ik alle resultaten en heeft elke rij een mooi nummertje gekregen per stationsgroepering. (resultaat).

Maar nu wil ik enkel de rijen waar countPerStn > 30. Is dit mogelijk?

Dus uit deze hele set resultaten enkel de resultaten overhouden waar countPerStn > 30.

Zeg maar zoiets:

SQL:

SET @countPerStn = 0;
SET @lastStn = 0;

SELECT stn, date, temp FROM (
    SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn 
    FROM cache 
    GROUP BY stn, date
    ORDER BY stn, date asc;
) WHERE countPerStn > 30

Is dit mogelijk?

[ Voor 113% gewijzigd door ZpAz op 25-09-2011 13:05 ]

[nescafe]

Dan zul je toch twee dingen apart moeten regelen. Bovenstaande query geeft altijd de eerste (want je hebt DESC in je ORDER BY vervangen door ASC) 30 meetwaarden, ongeacht hoeveel meetwaarden er per station zijn.

Om na te gaan of er daadwerkelijk 30 meetwaarden zijn, zul je denk ik toch een subquery moeten gebruiken, ik weet niet hoe MySQL daarmee om gaat maar in MSSQL is het bijv. mogelijk om een subselect aan je FROM-clause mee te geven zodat je vrij zeker weet dat hij maar 1-malig wordt uitgevoerd.

SQL:

SET @countPerStn = 0; 
SET @lastStn = 0; 
SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn
FROM cache  
  INNER JOIN (SELECT stn FROM cache GROUP BY stn HAVING COUNT(*) >= 30) x
    ON cache.stn = x.stn
GROUP BY stn, date 
ORDER BY stn, date DESC -- Je wilde toch de laatste 30 waardes?

Als dit niet werkt zou je het ook in je WHERE-clause kunnen doen met een subquery.

[ZpAz]

Bedankt.

En je hebt gelijk, ik heb DESC nodig.

Met de volgende query is het uiteindelijk gelukt.

MySQL:

SET @countPerStn = 0;
SET @lastStn = 0;

SELECT stn, date, temp, countPerStn FROM
(
    SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn 
    FROM cache 
    GROUP BY stn, date
    ORDER BY stn, date DESC
) as tempTable 
WHERE countPerStn > 30

Nu eerst maar weer een grote set data genereren en kijken hoe snel de query is.

Op 421k rijen doet hij er hier 300ms over. Dat is prima te doen. Thanks.

edit: Vervolgvraag:

Je kan in Mysql het volgende doen:

MySQL:

Insert into mainTable (SELECT stn, date, temp FROM cache) 

Dat kopieert de geselecteerde inhoud van de cache tabel naar de mainTable. Maar nu wil ik de rijen ook uit de cache tabel verwijderen. Is het mogelijk om de 'geretourneerde rijen' te verwijderen in een statement?

[ Voor 40% gewijzigd door ZpAz op 25-09-2011 14:14 ]

[nescafe]

Nu heb ik toch je vraag niet goed begrepen. Wat als er nu 40 meetwaarden zijn? Wil je dan de laatste 10? Of de laatste 30?

[ZpAz]

nescafe schreef op zondag 25 september 2011 @ 14:16:
Nu heb ik toch je vraag niet goed begrepen. Wat als er nu 40 meetwaarden zijn? Wil je dan de laatste 10? Of de laatste 30?

Mijn vraagstelling was verkeerd, er moeten per station 30 in de cache tabel achterblijven (welke het laatste zijn toegevoegd). Wat de eerder genoemde query doet. De oudere resultaten (als er meer zijn dan 30) moeten verplaatst worden naar de hoofdtabel en uit de cache tabel verwijderd worden.

[nescafe]

Als je een unieke referentie hebt (of kunt maken) kun je die gebruiken om records die in je mainTable zitten, uit de cache verwijderen:

SQL:

DELETE cache
FROM cache
  INNER JOIN mainTable ON cache.stn = mainTable.stn AND cache.date = mainTable.date

Of het mogelijk is om te verwijderen op basis van 'affected rows'... weet ik niet.

[ZpAz]

nescafe schreef op zondag 25 september 2011 @ 14:33:
Als je een unieke referentie hebt (of kunt maken) kun je die gebruiken om records die in je mainTable zitten, uit de cache verwijderen:

SQL:

DELETE cache
FROM cache
  INNER JOIN mainTable ON cache.stn = mainTable.stn AND cache.date = mainTable.date

Of het mogelijk is om te verwijderen op basis van 'affected rows'... weet ik niet.

De main table groeit na elke insert. Als je dan die gaat matchen tegen de cache tabel. Dan gaat dat matchen steeds langer duren lijkt mij?

[nescafe]

Dat klopt, maar een beetje database schrikt niet van. Je kunt je wel afvragen of dit de goede oplossing is.

Misschien een goed moment om een stap terug te nemen: wat wil je nu bereiken? Ben je een oplossing aan het maken voor een probleem dat niet bestaat?

MySQL kan prima grote aantallen data opslaan en weer gefilterd opvragen. Hiervoor hoef je niet zelf met data te gaan slepen. Je kunt wat dat betreft alles in dezelfde tabel laten.

Wil je toch gaan opsplitsen t.b.v. storage en analyse, dan lijkt het mij logischer om de inserts direct op de mainTable uit te voeren en die gestaag te laten groeien (keep it simple).

Vervolgens kun je voorgedefiniëerde query's maken en hun output in aparte analyse-tabellen/databases opslaan.

Zo weet je in ieder geval dat je data in mainTable altijd up-to-date is en dat je niet hoeft te wachten op het verwerken van de cache-tabel.

[ZpAz]

De mainTable is puur 'data opslag' hier wordt niet direct iets mee gedaan.

Het punt is als volgt.

Elke seconde krijg ik van 800 (gesimuleerde) weerstations gegevens doorgestuurd. Hier kunnen fouten in optreden. Bijvoorbeeld een waarde wijkt te veel af oid. Dit moet gecontroleerd worden door de laatste 30 waardes van het station te vergelijken met de nieuwe waarde.

Wanneer alles goed is moet het in de uiteindelijke tabel worden gestopt zodat dit later bekeken kan worden.

Mijn idee was dus om alle gegevens in een Memory table te stoppen, zodat ik bijvoorbeeld makkelijk de gemiddelde temperatuur van de laatste 30 items per station kan berekenen. In plaats van elke keer door een array te loopen waar de laatste 30 waarde's van een station in zit en daar het gemiddelde van uit te rekenen.

De oudste informatie (waar we dus nu een query voor hebben) wordt dan elke x seconden verplaatst uit de memory tabel naar de uiteindelijke tabel. Zodat door de memory tabel sneller gezocht kan worden en de data bij een reboot gewoon aanwezig blijft. (Op hooguit een paar sec verlies na.).

edit:

De query zoals hij momenteel staat samen met jou join delete query is tot nu toe wel snel genoeg. Er blijven ongeveer 80 - 90k rijen in de cache tabel. En er worden elke 3 seconden ongeveer 6000 verplaatst naar de hoofd tabel.

[ Voor 9% gewijzigd door ZpAz op 25-09-2011 15:14 ]

[nescafe]

Het berekenen van het gemiddelde over de laatste 30 waarden van 1 station is iets wat MySQL makkelijk voor je kan doen, ook op zo'n monstertabel. Daar heb je geen arrays en loops voor nodig.

Waarom zou je die foute waarde ook niet gewoon opslaan zodat je op den duur ziet of een weerstation betrouwbare meetresultaten doorgeeft? (Deze 'foute' waarde zou je dan wel kunnen uitsluiten voor reguliere analyses).

Mijn tip: stort je even in een tutorial SQL want ik heb het idee dat je de meest basale functies (bijv. AVG & GROUP BY) nog niet kent.

[ZpAz]

AVG, COUNT, MAX, MIN e.d zijn functies die ik wel ken. En GROUP BY is ook niet onbekend voor mij. Nu ben ik geen SQL Guru die de meest complexe queries in elkaar kan zetten, dat zal ik toegeven.

Ik denk dat ik te gecompliceerd dacht en daardoor een probleem creëerde die er in eerste instantie niet was.

De 'loop' was dan dat de cache ipv in een geheugen tabel zit gewoon in arrays in Java. En dat die dan om de x tijd wordt geleegd in de database. Vandaar loop.

Het is een tabel die flink doorgroeit. Na een paar minuten draaien is het al een tabel van 50MB. Welke constant op hetzelfde tempo doorgroeit. Dus vandaar dat ik verwachtte dat het wel eens langzamer kon gaan worden.

[ Voor 64% gewijzigd door ZpAz op 25-09-2011 15:35 ]

[C0rnelis]

Klopt het dat dit 'huiswerk' is ? (al dan niet met meerdere personen in een groep)?

Een vraag die je je ook kunt afvragen: moet je alle gegevens ook daadwerkelijk opslaan? Kan je niet bijvoorbeeld 30 opslaan in jouw cachelaag en vervolgens eens per 5 seconden of 10 seconden een waarde wegschrijven? Dat scheelt enorm in de hoeveelheid data en om dat nu direct onbetrouwbaar te noemen.. (of is dat een keiharde eis)

[ZpAz]

Het is inderdaad een opdracht voor school. De opdracht was om de data op te slaan en te zorgen dat de data klopt. Maar als je ergens mee vast loopt mag je altijd vragen toch.

De opdracht is alle data opslaan, en de data die incorrect is correct maken.