[SQL/MySQL] Eerst en laatste waarden op een dag

[Xerohumoris]

SAMENVATTING:
Hoe selecteer ik de eerste en laatste waarde op een dag.

UITLEG:
Ik zou graag uit een tabel met de prijzen willen uitlezen. Het gaat dan om prijzen van aandelen en die schommelen graag. Nu wil ik graag van elke dag de maximale, minimale, gemiddelde en open en sluit waarde willen hebben.
De maximale, minimale en gemiddelde prijs zijn niet ingewikkeld. Dat doe ik als volgt:

SQL:

SELECT MAX( prijs ) , MIN( prijs ) , AVG( prijs ) , moment
FROM `prijshistorie`
WHERE comid =1
GROUP BY year( moment ) , month( moment ) , day( moment )

Nu wil ik daar ook graag de eerste en laatste prijs van de dag hebben. Nu heb ik het volgende geprobeerd. Dit is voor de maximale waarde maar minimaal zou dan op de zelfde manier moeten:

SQL:

SELECT MAX( prijs ) , MIN( prijs ) , AVG( prijs ) , (SELECT prijs FROM prijshistorie WHERE moment = MAX(moment)) AS last, moment
FROM `prijshistorie`
WHERE comid =1
GROUP BY year( moment ) , month( moment ) , day( moment )

Nu krijg ik een fout van mysql die ik niet helemaal kan thuis brengen:
#1111 - Invalid use of group function

Ik dit dan ook niet als oplossing. Maar is het mogelijk zowel de eerste als laatste waarde van de dag ook mee te krijgen. Probleem is alleen wel dat er geen vast moment op de dag is dat de eerste en laatste prijs binnen komen. Van de oudere gegevens staan de id's ook niet op volgorde. 2010 komt voor het jaar 2000 maar na 2001.

[Verwijderd]

Je zou in die subquery een order by moment kunnen toevoegen en een limit 1?

SELECT MAX( prijs ) , MIN( prijs ) , AVG( prijs ) , (SELECT prijs FROM prijshistorie WHERE comid = a.comid ORDER BY moment LIMIT 1) AS last, moment
FROM `prijshistorie` a
WHERE comid =1
GROUP BY year( moment ) , month( moment ) , day( moment )

[ Voor 62% gewijzigd door Verwijderd op 20-11-2013 17:59 ]

[Gomez12]

Het probleem zit hem vermoedelijk in je group by functie (alhoewel mysql hier bijna nooit over zeurt maartoch) Daarin geef je aan dat je per dag 1 record wilt ontvangen, maar je specificeert niet wat er moet gebeuren met je kolom last... Dus specificeer expliciet in je outer query welk record van last je terug wilt hebben ( er kunnen meerdere prijzen zijn met hetzelfde MAX(Moment) in theorie )
En anders is het simpelweg omdat je niets gealiased hebt. WHERE moment=MAX(moment) is een beetje rare aanroep want je hebt 2 tabellen prijshistorie openstaan en alletwee hebben ze de kolom moment, dus wat wil je nu waarvandaan hebben? Gebruik aliassen.


P.s. IPV group by year() , month(),day () kan je niet beter date() scheelt je 2 functieaanroepen?

[pedorus]

Dit is een klassiek probleem:

http://gathering.tweakers...keyword=groupwise+maximum
[google=groupwise maximum]

[The Eagle]

Ik werk nog net niet dagelijks met dit soort zaken
Zie http://stackoverflow.com/...ed-date-for-each-grouping
Je moet de max moment in je subquery plempen en die over al zijn keys inner joinen met je hoofdquery. Idem voor het min moment.

[Xerohumoris]

Verwijderd schreef op woensdag 20 november 2013 @ 17:59:
Je zou in die subquery een order by moment kunnen toevoegen en een limit 1?

SELECT MAX( prijs ) , MIN( prijs ) , AVG( prijs ) , (SELECT prijs FROM prijshistorie WHERE comid = a.comid ORDER BY moment LIMIT 1) AS last, moment
FROM `prijshistorie` a
WHERE comid =1
GROUP BY year( moment ) , month( moment ) , day( moment )

Is helaas geen optie. Het is een tabel met 2 miljoen records en na 10 minuten heb ik het wachten opgegeven. Mijn query duurde ook al 10 seconden maar dit gaat echt te ver. En nee snelheid speelt niet echt een rol aangezien dit op basis van een cronjob gaat gebeuren.
Met een kleine aanpassing werkte die wel. Maar gaf hij altijd de eerste record terug over de gehele database en dus niet alleen van die ene dag.

Date lijkt ook goed te werken bedankt voor de tip.

Maar wat is nu de beste oplossing om zowel de eerste als de laatste van de dag te selecteren... De workaround voor het "missen" van FIRST() en LAST() werken niet in dit geval.

[Douweegbertje]

Nja, weet je wanneer de eerste erin komt? Selecteer het dan gewoon 'hard' op die datum/tijd?

[Gomez12]

Qua performance zou ik gewoon zeggen : Maak een gedenormaliseerde extra tabel die per dag een min/max waarde bevat dat is 2 records per dag toevoegen (alle historische data blijft vermoedelijk gelijk) en je query wordt een heel stuk leesbaarder (ik vermoed dat hij iets groter is als wat hier gegeven) en je performance wordt ook gelijk een heel stuk beter.

[Xerohumoris]

Ik weet helaas niet wanneer de eerste of de laatste in de tabel komt. Dat is namelijk afhankelijk van het aandeel en de markt(en) waar in het aandeel gehandeld wordt.

@Gomez12:
Ik ben het met je eens. Dat is wat ik uiteindelijk ook ga doen. Ik denk persoonlijk alleen dat ik het in een plat tekst bestand zet omdat ik namelijk dan de volledige tabel nodig heb en geen selectie nodig heb.

Helaas los dit niet het probleem op van eerste en laatste over de dag van de gegevens die ik al heb.

[The Eagle]

Stukje pseudo-code:

SQL:

SELECT a.prijs, a.moment
  FROM prijshistorie a
 WHERE (a.moment =
           (SELECT MIN (ad.moment)
              FROM prijshistorie ad
             WHERE ad.key1 = a.key1
               AND ad.key2 = a.key2
               AND DAY (ad.moment) = SYSDATE)
       )
    OR (a.moment =
           (SELECT MAX (ac.moment)
              FROM prijshistorie ad
             WHERE ac.key1 = a.key1
               AND ac.key2 = a.key2
               AND DAY (ac.moment) = SYSDATE)
       )

Zoals al gezegd, inner join op de keys met als voorwaarde de date van vandaag. Hoe je die laatste in jouw SQL doet weet ik niet. Maar het principe zul je ongetwijfeld snappen.
Je zou het ook met een UNION op kunnen lossen, maar da's best een ranzige oplossing voor iets dergelijks.
Als de boel niet performant is: zet eens een paar indexen op je data, met name op moment als je daar veel op moet zoeken

[Xerohumoris]

Misschien even handig om aan te geven. Het gaat om een tabel die is opgezet als archive. Dit om hem "klein" te houden. Snelheid was nooit een doel. Daarom is het ook niet mogelijk om meer dan 1 index (nu op een id) te hebben.
Volgens mij het het ook zo dat naam referentie niet werkt in dat geval. Maar daar ben ik niet zeker van.

Ik ben bezig geweest met een JOIN. Maar krijg het niet goed voor elkaar. Het lijkt zelfs wel of de server volledig crasht als ik het probeer. De eerst volgende 10 minuten is hij volledig onbenaderbaar.

[pedorus]

Tsja, zonder nuttige index wordt het erg lastig.. Of zijn de id's te gebruiken als index voor een dag? Dan zou het met een hulptabelletje kunnen (met dag, min-id, max-id).

[The Eagle]

ALs het een archieftabel is om de productietabel klein te houden, dan snap ik dat. Maar als hettoch een archieftabel is, dan groeit de hoeveelheid opslag ook niet echt. Dan kun je er dus rustig een index opzetten

Dat je server crasht als je die join probeert is ws ook te wijten aan de manier waarop ie door de data moet spitten. Als je je DB server geen makkeijke toegangspaden tot zijn data geeft, doet ie keer op keer een full table scan. En dat duurt even ja

Check het explain plan van je query eens zou ik zeggen, en check zeker ook even welke indexen erop je data zitten. En zo niet: zetten!

[Gomez12]

pedorus schreef op donderdag 21 november 2013 @ 18:21:
Tsja, zonder nuttige index wordt het erg lastig.. Of zijn de id's te gebruiken als index voor een dag? Dan zou het met een hulptabelletje kunnen (met dag, min-id, max-id).

Als je zelf de id-uitgifte doet (en exact weet wat er gaat gebeuren als de id's op zijn) dan valt het te overwegen. Als het een db-auto-incremental id is dan hier nog niet eens over nadenken.

Het kan namelijk nu tijdelijk leuk goedgaan (zolang er vrije id's aan de bovenkant zijn) maar als iemand een actie doet met de id's (of als de id's op zijn) dan ga je onvoorspelbaar gedrag krijgen omdat je db gewoon een lager id kan uitgeven (als die vrij is) voor je max dan voor je min.
In principe (en praktijk) is een db-generated id gewoon een uniek getal en niets meer dan dat, deze kan tijdelijk keurig oplopen maar afaik garandeert geen enkel db dat (ik ken nog wel een aantal db's die het hoogst uitgegeven id enkel in het geheugen bewaren en bij elke reset van de server worden alle lege plaatsen vanaf 1 opgevuld met nieuwe records)

[Xerohumoris]

De uitgifte van een id gaat via de auto inc. idd. Daarnaast is de data van voor 2013 beetje ad random in de db terecht gekomen. Dit door problemen die er waren met de bestanden includeren. Deze bestanden liepen namelijk op tot 12o mb. Waar een aantal dubbelen in zaten. Niet iets wat via een php script makkelijk in een database kan worden gezet.

Maar terug naar het probleem: Ik zit er nu sterk over te denken om eerst de maxima, minima en gemiddelde op te halen en dan per resultaat nog twee queries als volgt te doen, een test laat zien dat het werkt:

SQL:

SELECT prijs FROM `prijshistorie` WHERE moment LIKE '2013-11-21%' ORDER BY moment DESC LIMIT 1

En dan dus met en zonder de DESC

Dit vraagt echter om 8000 extra queries. Niet echt gewenst maar is helaas niet anders. Krijg dit namelijk niet als subquery aan de praat. De DB staat op een ssd en 100% van de tijd gaat in het sorteren zitten. Het selecteren van de gegevens kost geen tijd. Van de 3 uur dat hij net bezig is geweest, zonder resultaat, was totaal 2 seconden voor het selecteren van de gegevens.

[pedorus]

In 3 uur tijd kun je met gemak die 2 miljoen records overpompen naar een (tijdelijke) tabel met indexen en een query daarover heen draaien. Een tabel met kolommen (datum, tijd, waarde) met index op (datum, tijd). Een dedicated scriptje over die 2 miljoen records zou al helemaal vele malen sneller zijn.

[Gomez12]

Waarom zou je dit zo krom doen?

Probeer eens een memory table an te maken van het volgende (pseudosql)

SQL:

select max(a.moment) as maxmoment,
min(a.moment) as minmoment,
date(a.moment) as datemoment
from prijshistorie as a
group by date(a.moment)

En dan kan je 2x joinen met die memory table op date(moment) en where maxmoment=moment dat is je laatste prijs en minmoment=moment is je eerste prijs.

Hoef je niets te orderen

[Xerohumoris]

Als een memory table zit ik op 200 MB aan geheugen. Op mijn eigen machine kan dat wel maar bij een webhost is dat geen optie. Maar ik ben het nu wel lokaal aan het testen.

Ik gebruik nu de volgende tabel en query maar daar loopt die ook op vast:

SQL:

CREATE TABLE IF NOT EXISTS `temp` (
  `id` int(255) NOT NULL,
  `comid` int(5) NOT NULL,
  `prijs` double NOT NULL,
  `moment` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `moment` (`moment`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

SELECT comid, MAX( prijs ) , MIN( prijs ) , AVG( prijs ),
(SELECT prijs FROM `temp` WHERE date(moment) = date(a.moment) ORDER BY moment LIMIT 1) AS eerste,
(SELECT prijs FROM `temp` WHERE date(moment) = date(a.moment) ORDER BY moment DESC LIMIT 1) AS laatste , date(moment)
FROM `temp` a
GROUP BY comid, date( moment );

Ik zie niet wat ik beter zou kunnen doen. Zonder de twee subqueries werkt hij prima en is hij in een 10de seconde klaar. Graag even wat advies dus.

Hoeveelheid geheugen is geen probleem is namelijk nog 7 GB beschikbaar. En CPU komt niet boven de 15% uit. De computer is niet de belemmering.

[pedorus]

Een query met date() kan in mysql daarop geen indexen gebruiken dacht ik, lijkt me vrij traag, vandaar het idee van de aparte datum-kolom. Een subquery is ook meestal zeer traag, je wilt joins hebben (bijv. 2 maal left join where ... is null met self-join).

[Xerohumoris]

Wordt het probleem niet mee opgelost. Net de halve dag lopen stoeien maar lost niets op. Ik heb nu namelijk ook een kolom met alleen de datum. En nog is het ontzettend traag of is de eerste waarde oudste datum in de tabel.

Het selecteren van maxima en minima zijn niet het probleem. Die mag jij van mij wel vergeten. Dus de vraag her geformuleerd:

Hoe selecteer ik de eerste en de laatste record per datum uit een mysql database? In MS Access zou ik dit doen, is volgens mij namelijk de enige die FIRST() en LAST() kent:

SQL:

SELECT FIRST(prijs), LAST(prijs) FROM prijshistorie GROUP BY DATE(moment)

Ik hoop dat het nu wat duidelijker is.

[pedorus]

mysql heeft stiekem ook een first(), namelijk door geen functie te gebruiken waar dit wel zou moeten, enkel dit levert ongedocumenteerd gedrag op.

Maar zoiets bijvoorbeeld:

SQL:

SELECT pmin.datum, pmin.prijs, pmax.prijs FROM prijshistorie pmin 
inner join prijshistorie pmax on pmin.datum=pmax.datum 
left join prijshistorie hmin on pmin.datum=hmax.datum and pmin.moment > hmin.moment 
left join prijshistorie hmax on pmax.datum=hmax.datum and pmax.moment < hmax.moment 
where hmin.datum is null and hmax.datum is null;

Aanname: (datum,moment) is uniek voor eerste en laatste moment op een dag (anders moet je ook iets met ids doen)

Andere databases hebben soms iets handigere functies hiervoor, maar die heeft mysql helaas niet..

[Xerohumoris]

Ik heb de oplossing gevonden. Duurt wel nog 10 seconden om te runnen maar loopt niet vast. Ik weet ook niet waar eerdere pogingen zijn gestrand. Ik heb namelijk het vermoeden dat ik deze al eerder heb geprobeerd:

SQL:

SELECT comid , MIN(prijs) AS mini, MAX(prijs) AS maxi, AVG(prijs) AS gem,
(SELECT prijs FROM temp WHERE a.datum = temp.datum ORDER BY a.moment LIMIT 1) AS first, 
(SELECT prijs FROM temp WHERE a.datum = temp.datum ORDER BY a.moment DESC LIMIT 1) AS last,
datum
FROM temp a GROUP BY comid, datum ORDER BY datum DESC

Ik wil jullie allemaal bedanken voor de hulp.