[MySQL] totale uurwaarde berekenen, performance issues

[bartbh]

Naar aanleiding van het oude topic van mij (MySQL totaal berekenen over tijdspanne van 1 uur, mei 2013) open ik nu een nieuw topic. De query werkt namelijk, alleen de performance is bedroevend.

Het opwekken van energie via zonnepanelen wordt gelogd in de database. Ruwweg elke 10 minuten wordt er de totale opwekking van een locatie (sinds het plaatsen van de omvormer, dus cumulatief) en nog een paar waardes weggeschreven.

Nu wil ik graag via MySQL bepalen hoeveel er totaal, per uur, opgewekt wordt door alle locaties bij elkaar.

Als voorbeeld gebruik ik de volgende dataset:

code:

| ID | ID_LOCATIE |                          TIME | ENERGY_TOTAL |
|----|------------|-------------------------------|--------------|
|  1 |          1 | August, 04 2014 07:08:00+0000 |          500 |
|  2 |          2 | August, 04 2014 07:09:00+0000 |         1000 |
|  3 |          1 | August, 04 2014 07:58:00+0000 |         1000 |
|  4 |          2 | August, 04 2014 07:54:00+0000 |         1200 |
|  5 |          1 | August, 04 2014 08:04:00+0000 |         1300 |
|  6 |          2 | August, 04 2014 08:06:00+0000 |         1600 |
|  7 |          1 | August, 04 2014 08:56:00+0000 |         1500 |
|  8 |          2 | August, 04 2014 08:59:00+0000 |         1700 |
|  9 |          1 | August, 04 2014 09:01:00+0000 |         1750 |
| 10 |          2 | August, 04 2014 09:02:00+0000 |         1900 |
| 11 |          1 | August, 04 2014 09:57:00+0000 |         2100 |
| 12 |          2 | August, 04 2014 09:58:00+0000 |         2400 |
| 13 |          1 | August, 04 2014 10:08:00+0000 |         2500 |
| 14 |          2 | August, 04 2014 10:04:00+0000 |         2700 |
| 15 |          1 | August, 04 2014 10:56:00+0000 |         3000 |
| 16 |          2 | August, 04 2014 10:57:00+0000 |         3250 |

En met de volgende query

MySQL:

select
    hour(time),
    SUM(hourly_energy) as e
FROM
    (  select
        time,
        id_locatie,
        round(max(energy_total)) - (
            select round(max(energy_total))  
            from
                data_running_summed s2  
            where
                date(time) = date('2014-08-04')  
                and hour(s2.time) = hour(s1.time)-1  
                and s2.id_locatie = s1.id_locatie  
            group by
                hour(time)  
        ) as hourly_energy  
    from
        data_running_summed s1  
    where
        date(time) = date('2014-08-04')  
    group by
        s1.id_locatie,
        hour(time)  
    order by
        time  
    ) as totalen  
GROUP BY
    hour(time)

Krijg ik netjes de volgende output.

code:

| HOUR(TIME) |      E |
|------------|--------|
|          7 | (null) |
|          8 |   1000 |
|          9 |   1300 |
|         10 |   1750 |

(zie ook het voorbeeld op sqlfiddle)

het klopt dat het 1e uur van de dag nu nog op 0/null komt te staan, dat is voorlopig een kleine bug die ik accepteer.

Bij een kleine dataset gaat dat prima. Echter bij de volledige dataset van ruim 1 miljoen records neemt deze query zo 3 - 5 minuten in beslag. De volledige set bestaat uit nog 4 kolommen meer, maar deze worden niet geselecteerd dus dat lijkt me niet het probleem.

Kortom, hoe kan ik deze query optimaliseren dan wel herschrijven? De huidige performance is bedroevend en ik ben nu bijna geneigd om via PHP i.c.m. foreach handmatig elk uur te gaan berekenen.

Aangezien er ongeveer elke minuut wel weer nieuwe waardes gepost worden heeft query caching ook geen nut.

Wie o wie kan me (in de goede richting) helpen?!

[NMe]

Waarom heb je überhaupt een subquery nodig? Met deze heb je toch hetzelfde resultaat?

SQL:

SELECT HOUR(time) AS Hour, SUM(energy_total) AS Energy
FROM tabel
WHERE DATE(time) = '2014-08-04'
GROUP BY HOUR(time)
ORDER BY Hour

edit:

Of zijn die totalen cumulatief? If so, waarom sla je niet in plaats daarvan je mutaties op?

[ Voor 18% gewijzigd door NMe op 05-08-2014 21:24 ]

[Gomez12]

Maak je pagina zo dat de resultaten die uitgerekend zijn (minus het laatste uur) in een tijdelijke tabel verdwijnen.

Dan je aanroep omzetten naar 2 query's :
1 bepaalt de max tijd die in je tijdelijke tabel staat
2 : Haal alles uit de tijdelijke tabel en union dat met je huidige query (die dan enkel over de tijd vanaf max tijd uit je cache-tabel moet gaan)

Maar in wezen is de truc veelal met dit soort dingen (veel onveranderbare data) : Groupeer het naar temp-tabellen omdat de oude waardes toch nooit veranderen. Bewaar de originele waardes wel zodat je zo de temp/cache tabellen kan leegmieteren en het automatisch weer opgebouwd wordt.

----------

@NMe : Hij zit wat te goochelen met zijn uurtotalen, volgens mij (naar zijn vorige topic kijkende) kan hij niet overweg met sum(energy_total) qua resultaat.
Hij kan volgens mij wel gewoon een sum pakken en dan in scripting gewoon de vorige waarde eraf trekken voor de hoeveelheid geproduceerd in dat uur. Maar dat mag hij zelf bepalen.

[bartbh]

@NMe; de waardes zijn inderdaad cumulatief. Dat had ik er niet duidelijk bij staan en was ook niet duidelijk uit de voorbeeld data. Dit is een waarde (samen met nog een paar andere waarde) die rechtstreeks gelogd worden door de omvormers. Dus dat is 'ruwe' data bij elkaar opgeteld.

Sowieso geeft jouw opzet in ieder geval niet dezelfde resultaten, ook al niet op de test dataset.

@Gomez12;
Correct me if I'm wrong, maar dan bedoel je dat ik dan toch mijn voorstel moet volgen? Met php/foreach voor elk (geweest) uur de waarde ophalen uit de tijdelijke tabel, of als het nog niet bestaat berekenen en dan in de tijdelijke tabel opslaan. En voor het lopende uur dan de gegevens uit de huidige tabel gebruiken.

Dan krijg ik dus een geneste foreach loop, voor elk uur van de dag + voor elke locatie.

code:

foreach (hour < hour_now)
   foreach (locatie)
       //haal de uurwaarde op voor deze locatie voor dit uur

[The Eagle]

Kun je niet toevallig gewoon een function based index op de time kolom zetten? Weet niet of mysql dat ondersteunt, ik ken het alleen van Oracle namelijk. En dan bij voorkeur FB indexen die hour en date er uit haalt. Dat zijn namelijk je select criteria, en aangezien mysql niet van te voren weet of ie wel of geen relevante waarde uit de time kolom moet halen gaat ie vermoedelijk full table scans doen. Dat is idd niet leuk voor je performance

Alternatief: bij het wegschrijven van de data meteen laten splitsen in extra kolommen voor datum en tijd, en daar je select op los laten.

[Morrar]

Persoonlijk zou ik ook een oplossing met PHP doen en daar de data in bewerken. Dus een query zoals NMe voorstelt, maar dan per locatie:

SQL:

SELECT id_locatie, HOUR(time) AS Hour, MAX(energy_total) AS Yield
FROM data_running_summed
WHERE DATE(time) = '2014-08-04'
GROUP BY id_locatie, Hour
ORDER BY id_locatie, Hour

En dan in PHP over de rijen heenlopen en per locatie verwerken. Voorbeeld:

PHP:

//uitgaande dat de data in $data staat als [[location, hour, yield], ..., n]

//locatie data
$locdata = array();

foreach ($data as $rec) {
    //ff vars voor leesbaarheid
    $loc = $rec[0];
    $time = $rec[1];    
    $yield = $rec[2];

    //eventueel array aanmaken voor de locatie
    if (!$locdata[$loc]) {
        $locdata[$loc] = array();
   
        //vorige waarde resetten voor deze locatie
        $prev = 0;
    }

    //vorige meting eraf trekken en opslaan
    $locdata[$loc][$time] = $yield - $prev;

    //vorige waarde bijwerken
    $prev = $yield;
}

Even uit het hoofd, dus garantie tot aan de deur Data komt eruit als [loc1, ..., locN] => [hourX, ..., hourN] => yield. Uiteraard kun je ook locatie => uur doen, maar dan moet je ff wat omgooien.

[ Voor 107% gewijzigd door Morrar op 06-08-2014 00:31 ]

[Gomez12]

bartbh schreef op dinsdag 05 augustus 2014 @ 22:33:
Sowieso geeft jouw opzet in ieder geval niet dezelfde resultaten, ook al niet op de test dataset.

Dan zou ik nog maar eens goed overdenken waar nu de fout zit, want in wezen heb je een hele uitgebreide query gemaakt, maar als die niet hetzelfde resultaat geeft als de versimpelde versie van NMe dan ben ik bang dat er in je initiele query iets fout gaat.

Je moet altijd een simpele voorbeeld query hebben waarmee je je ingewikkelde query kan controleren, ik ben bang dat je nooit je ingewikkelde query hebt gecontroleerd en dat daar dan ergens iets fout gaat...

@Gomez12;
Correct me if I'm wrong, maar dan bedoel je dat ik dan toch mijn voorstel moet volgen? Met php/foreach voor elk (geweest) uur de waarde ophalen uit de tijdelijke tabel, of als het nog niet bestaat berekenen en dan in de tijdelijke tabel opslaan. En voor het lopende uur dan de gegevens uit de huidige tabel gebruiken.

Waarom php/foreach?

Pak gewoon je resultset op in php, dump voordat je ze toont die resultset ook in een temp-tabel (geen moeilijke constructies verzinnen, gewoon dumpen die hap).
En de volgende keer doe je een union-query van die temp-tabel en je echte tabel.

Geen foreach voor nodig voor zover ik kan zien...

Alternatief trouwens wat ik net nog zag in een ander pakket (ik heb geen idee hoe vaak je ze uitleest maar als dit vaak is dan helpt het wel) gooi in je query een where-instructie die het resultaat limiteert tot alles tot het begin van het huidige uur.
Als je die where instructie dan berekend in php (dus niet in je query gaan rekenen om het begin van het uur te bepalen) dan kan je database die query in zijn query-cache kwijt omdat die voor 60 minuten gelijk blijft.
Het ging hier over een pakketje wat elke 5 seconden ververst (vanwege andere dingen op het dashboard) en wat dan een interval heeft van 5 minuten, dat zijn toch mooi 60 query's die dan gecached worden.

[Morrar]

Gomez12 schreef op dinsdag 05 augustus 2014 @ 23:09:
[...]

Dan zou ik nog maar eens goed overdenken waar nu de fout zit, want in wezen heb je een hele uitgebreide query gemaakt, maar als die niet hetzelfde resultaat geeft als de versimpelde versie van NMe dan ben ik bang dat er in je initiele query iets fout gaat.

Je moet altijd een simpele voorbeeld query hebben waarmee je je ingewikkelde query kan controleren, ik ben bang dat je nooit je ingewikkelde query hebt gecontroleerd en dat daar dan ergens iets fout gaat...

Hij zegt toch dat de waarden cumulatief zijn? Er is niets mis met zijn query; hij moet steeds de vorige waarde eraf trekken en dat is het probleem. De query van NMe doet dat niet en daarom is de output anders.

[Gomez12]

Morrar schreef op dinsdag 05 augustus 2014 @ 23:13:
[...]


Hij zegt toch dat de waarden cumulatief zijn? Er is niets mis met zijn query; hij moet steeds de vorige waarde eraf trekken en dat is het probleem. De query van NMe doet dat niet en daarom is de output anders.

Dat moet toch zo te zien zijn? Trek in je hoofd die waardes van elkaar af die uit de query van NMe komen en je weet of het resultaat gelijk is.

De output is anders interpreteer ik als : De getallen en de verhoudingen tussen getallen komen totaal niet overeen het zijn echt totaal anders opgebouwde getallen.
Cumulatief / niet cumulatief is in mijn ogen dezelfde basis output, met 1 simpele slag ga je van het een naar het ander.

[NMe]

Alleen kun je niet-cumulatief veel makkelijker queryen en zonder verdere bewerkingen in je clientcode ophalen. Als er nog enigszins ruimte voor een refactor is zou ik veel eerder voor een mutatie-constructie kiezen dan voor de huidige opzet. Voor locatie 1 zou je dan 500, 500, 300, enz. opslaan in plaats van 500, 1000, 1300, enz. En dan kun je wél makkelijk mijn query gebruiken en nog steeds even makkelijk de cumulatieven bepalen met een SUM.

[Morrar]

Gomez12 schreef op dinsdag 05 augustus 2014 @ 23:31:
[...]

Dat moet toch zo te zien zijn? Trek in je hoofd die waardes van elkaar af die uit de query van NMe komen en je weet of het resultaat gelijk is.

De output is anders interpreteer ik als : De getallen en de verhoudingen tussen getallen komen totaal niet overeen het zijn echt totaal anders opgebouwde getallen.
Cumulatief / niet cumulatief is in mijn ogen dezelfde basis output, met 1 simpele slag ga je van het een naar het ander.

Probleem is dat de query van NMe de getallen optelt, ook als er meerdere metingen per uur zijn en ook nog voor alle locaties tezamen. In mijn query fix ik dat nu met MAX() en een groepering per locatie:

SQL:

SELECT id_locatie, HOUR(time) AS Hour, MAX(energy_total) AS Yield
FROM data_running_summed
WHERE DATE(time) = '2014-08-04'
GROUP BY id_locatie, Hour
ORDER BY id_locatie, Hour

Als je daarmee gaat rekenen kom je wel op dezelfde getallen als de TS; (huidige max locatie 1 + huidige max locatie 2) - (vorige max locatie 1 + vorige max locatie 2).

NMe schreef op dinsdag 05 augustus 2014 @ 23:38:
Alleen kun je niet-cumulatief veel makkelijker queryen en zonder verdere bewerkingen in je clientcode ophalen. Als er nog enigszins ruimte voor een refactor is zou ik veel eerder voor een mutatie-constructie kiezen dan voor de huidige opzet. Voor locatie 1 zou je dan 500, 500, 300, enz. opslaan in plaats van 500, 1000, 1300, enz. En dan kun je wél makkelijk mijn query gebruiken en nog steeds even makkelijk de cumulatieven bepalen met een SUM.

Helemaal mee eens; de huidige opzet is behoorlijk irritant. Je kan het nu wel fixen tijdens het ophalen, maar waarschijnlijk is dat makkelijker te doen als je de data invoert in de tabel. Zelf zou ik kiezen voor waarde uitlezen uit de meter, even vorige waarde uit de DB ophalen en dan het verschil opslaan.

Maar goed, misschien is dat technisch niet mogelijk...

[ Voor 44% gewijzigd door Morrar op 05-08-2014 23:45 ]

[NMe]

Morrar schreef op dinsdag 05 augustus 2014 @ 23:40:
[...]


Probleem is dat de query van NMe de getallen optelt, ook als er meerdere metingen per uur zijn en ook nog voor alle locaties tezamen. In mijn query fix ik dat nu met MAX() en een groepering per locatie.

Doordat er meerdere metingen per uur en per locatie zijn, komen de getallen van NMe niet overeen met die van de TS.

Jouw query hierboven heeft trouwens wel hetzelfde probleem: jij doet diezelfde SUM. Bartbh zit juist te goochelen met die MAX en twee subquery's om dat te voorkomen, en omdat hij cumulatieve data opslaat in zijn database valt daar in pure SQL niet veel beters van te maken. Zelfs de hybride oplossing die jij noemt is dan niet goed genoeg en moet nog een slagje ingewikkelder.

[Morrar]

NMe schreef op dinsdag 05 augustus 2014 @ 23:43:
[...]

Jouw query hierboven heeft trouwens wel hetzelfde probleem: jij doet diezelfde SUM. Bartbh zit juist te goochelen met die MAX en twee subquery's om dat te voorkomen, en omdat hij cumulatieve data opslaat in zijn database valt daar in pure SQL niet veel beters van te maken. Zelfs de hybride oplossing die jij noemt is dan niet goed genoeg en moet nog een slagje ingewikkelder.

Had t al gezien en gefixed inmiddels; SUM() vervangen door MAX(). Was even vergeten de query terug te copy-pasten uit Fiddle

[ Voor 8% gewijzigd door Morrar op 05-08-2014 23:47 ]

[Redshark]

In Oracle kun je met de analytical functions de cumulatieve waarden wel verreken naar de mutaties.
hier een mysql variant daarvan.

Ik ben niet zo thuis in mysql dus kan niet beoordelen of het werkt, maar het is misschien wel de moeite waard.

[pedorus]

TS is niet de eerste die een cumulatieve som wil berekenen in mysql natuurlijk.. http://stackoverflow.com/...ative-sum-column-in-mysql

[ Voor 4% gewijzigd door pedorus op 06-08-2014 00:46 . Reden: terug naar origineel :) ]

[Morrar]

pedorus schreef op woensdag 06 augustus 2014 @ 00:01:
TS is niet de eerste die een cumulatieve som wil berekenen in mysql natuurlijk.. http://stackoverflow.com/...ative-sum-column-in-mysql

Het probleem is juist andersom; hij heeft alleen cumulatieve data en wil de onderliggende mutaties berekenen

[pedorus]

conceptueel is dat niet heel anders toch?

SQL:

set @csum:=0;
SELECT id_locatie, HOUR(time) AS Hour, MAX(energy_total)-@csum AS Yield, 
    @csum:=MAX(energy_total) AS cumulative

Enkel helaas zit je ook nog per locatie.. Union all kan als je weet dat je 2 vaste locaties hebt. Echte oplossing is natuurlijk de data gewoon goed opslaan.

Dacht eerst dat die link van Redshark in "[MySQL] totale uurwaarde berekenen, performance issues" het goed uitwerkte, maar dat is toch net een iets andere situatie. Maar het kan vast op die manier uitgewerkt worden met oneindig locaties.

[ Voor 65% gewijzigd door pedorus op 06-08-2014 00:47 ]

[GlowMouse]

Als je query met veel records trager wordt, maar het aantal records per dag niet anders wordt, zorg dan dat je een index hebt op time en "WHERE date(time) = date('2014-08-04')" omschrijft naar "time BETWEEN begin AND einde van de dag". De query kan dan direct de relevante records vinden, en de performance neemt dan niet af bij meer records.

[NMe]

GlowMouse schreef op woensdag 06 augustus 2014 @ 01:50:
Als je query met veel records trager wordt, maar het aantal records per dag niet anders wordt, zorg dan dat je een index hebt op time en "WHERE date(time) = date('2014-08-04')" omschrijft naar "time BETWEEN begin AND einde van de dag". De query kan dan direct de relevante kolommen vinden, en de performance neemt dan niet af bij meer records.

Het probleem zit hem er vooral in dat de subquery's uit de topicstart potentieel veel data kunnen doorgeven aan de hoofdquery en intussen wel using where, using temporary en using filesort aangeven. Dat explodeert al snel, en in dit geval komt het niet door ontbrekende indexen.

[GlowMouse]

NMe schreef op woensdag 06 augustus 2014 @ 01:57:
[...]

Het probleem zit hem er vooral in dat de subquery's uit de topicstart potentieel veel data kunnen doorgeven aan de hoofdquery en intussen wel using where, using temporary en using filesort aangeven. Dat explodeert al snel, en in dit geval komt het niet door ontbrekende indexen.

De subquery bevat dezelfde WHERE, dus het is veel data maar de hoeveelheid is wel constant. Aangezien TS vroeger wel tevreden was over de snelheid, lijkt me dit de eenvoudigste oplossing.
De using where, temporary, filesort kun je nooit verhelpen bij deze query zonder de tabelstructuur te wijzigen. Hij kan wellicht simpeler, er zijn daarvoor ook voorstellen gedaan, maar die voorstellen hebben ook baat bij de door mij voorgestelde wijziging.

[Rotterdammertje]

Misschien zoiets? Bepaal het max per uur per locatie (= totaal geproduceerd), kijk of er het vorige uur wat is geproduceerd, koppel en sommeer per uur:

SQL:

select s1.measure_hour, sum(total_current_hour - ifnull(total_previous_hour, 0))
from
(
    select id_locatie, hour(time) as measure_hour, round(max(energy_total)) as total_current_hour
    from data_running_summed
    where time between '2014-08-04' and '2014-08-05'
    group by id_locatie, hour(time)
) s1
left join
(
    select id_locatie, hour(time) as measure_hour, round(max(energy_total)) as total_previous_hour
    from data_running_summed
    where time between '2014-08-04' and '2014-08-05'
    group by id_locatie, hour(time)
) s2
on (s2.id_locatie = s1.id_locatie and s2.measure_hour = s1.measure_hour - 1)
group by s1.measure_hour

Ongetest, but it might just work!

[Hipska]

kan er niet iets gedaan worden als MAX(energy_total) - MIN(energy_total) en group by op uur en locatie?

Trouwens is voor dergelijke data iets als RRD beter geschikt om data op te slaan. (RRDtool)

[ Voor 39% gewijzigd door Hipska op 06-08-2014 11:20 ]

[SPee]

Inderdaad, de min/max is nodig.

bartbh schreef op dinsdag 05 augustus 2014 @ 20:55:
Nu wil ik graag via MySQL bepalen hoeveel er totaal, per uur, opgewekt wordt door alle locaties bij elkaar.

[...]

Krijg ik netjes de volgende output.

code:

| HOUR(TIME) |      E |
|------------|--------|
|          7 | (null) |
|          8 |   1000 |
|          9 |   1300 |
|         10 |   1750 |

het klopt dat het 1e uur van de dag nu nog op 0/null komt te staan, dat is voorlopig een kleine bug die ik accepteer.

Nee, dat klopt niet! Als je goed kijkt, zie je dat locatie 1 van 500 naar 1000 is gegaan en locatie 2 van 1000 naar 1200. Dus in totaal 700 is opgewekt.

Met een max(energy) - min(energy) krijg je het verschil:

MySQL:

select
    time,
    SUM(generated_energy) as e
FROM
    (  select
        hour(time),
        id_locatie,
        round( max(energy_total) - min(energy_total) ) as generated_energy,
        max(energy_total) as hourly_energy
    from
        data_running_summed s1  
    where
        date(time) = date('2014-08-04')  
    group by
        s1.id_locatie,
        hour(time)  
    order by
        time  
    ) as totalen  
GROUP BY
    time

Dan krijg je:

code:

| HOUR |      E |
|------|--------|
|    7 |    700 |
|    8 |    300 |
|    9 |    850 |
|   10 |   1050 |

Wil je cummulatieve waardes hebben, voeg dan toe: SUM(hourly_energy) as cum

[Rotterdammertje]

[b]SPee schreef op woensdag 06 augustus 2014 @ 14:30:
Dan krijg je:

code:

| HOUR |      E |
|------|--------|
|    7 |    700 |
|    8 |    300 |
|    9 |    850 |
|   10 |   1050 |

Maar nu mis je alles wat is opgewekt in de overgang van 7 naar 8 uur. Het lijkt dus alsof in de eerste twee uur maar 1000 is opgewekt, terwijl er in werkelijkheid 1700 is opgewekt. Het is daarom beter om het maximum per uur te bepalen, en daar het maximum van het vorige uur van af te trekken. Daarmee wordt alles wat is opgewekt tussen 7 en 8 uur bij de productie van 8 uur opgeteld.

[Hipska]

Ik krijg dit als resultaat:

| ID_LOCATIE | HOUR(CUR.TIME) | TOTAL |   DIFF |
|------------|----------------|-------|--------|
|          1 |              7 |  1000 | (null) |
|          2 |              7 |  1200 | (null) |
|          1 |              8 |  1500 |    500 |
|          2 |              8 |  1700 |    500 |
|          1 |              9 |  2100 |    600 |
|          2 |              9 |  2400 |    700 |
|          1 |             10 |  3000 |    900 |
|          2 |             10 |  3250 |    850 |

Bij deze query:

SQL:

SELECT
  id_locatie,
  hour(cur.time),
  MAX(cur.energy_total) AS total,
  (
    SELECT MAX(cur.energy_total) - MAX(prev.energy_total)
    FROM data_running_summed prev
    WHERE 
      prev.id_locatie = cur.id_locatie
      AND prev.time < MIN(cur.time)
  ) AS diff
FROM data_running_summed cur
GROUP BY date(cur.time), hour(cur.time), cur.id_locatie;

Deze query neemt hoogste waarde van vorige uur en trekt daarvan de hoogste waarde van het huidige uur zoals voorgesteld. Volgens mij klopt het wel zo.

EDIT: Onderstaande query kan zelfs de waarde berekenen van het eerste uur, dan krijg je de waardes 500 en 200 er bij.

SQL:

SELECT
  id_locatie,
  hour(cur.time),
  MAX(cur.energy_total) AS total,
  IFNULL( (
    SELECT MAX(cur.energy_total) - MAX(prev.energy_total)
    FROM data_running_summed prev
    WHERE 
      prev.id_locatie = cur.id_locatie
      AND prev.time < MIN(cur.time)
    ), MAX(cur.energy_total) - MIN(cur.energy_total) ) AS diff
FROM data_running_summed cur
GROUP BY date(cur.time), hour(cur.time), cur.id_locatie;

[ Voor 20% gewijzigd door Hipska op 07-08-2014 10:00 ]

[Hipska]

@bartbh: zijn deze queries iets voor jou?