MySQL totaal berekenen over tijdspanne van 1 uur

[bartbh]

Voor een systeem waar ik mee aan het stoeien ben, heb ik een database opgezet waarin gelogd wordt hoeveel energie er opgewekt is door de zonnepanelen. Elke 5 min loggen de systemen naar een tabel en schrijven daar hun (tot op dat moment van de dag) hoeveelheid opgewekte energie op. Aan het begin van de dag is dat 0, aan het einde dus de op die dag hoeveelheid opgewekte energie.

Stel om 17:02 was energy 1500, om 17:59 was dat 2000 dan is de opwekking van 17uur 500.


De tabel ziet er als volgt uit:

id | id_system | time (datetime) | energy

Zoals gezegd loopt de hoeveelheid energie gedurende de dag op. Nu wil ik graag per uur inzichtelijk maken hoeveel er op gewekt is. Om voor een systeem (id_system =1) de hoeveelheid opgewekt energie te bereken, gebruik ik de volgende query;

MySQL:

        select
            time,
            (max(energy) - min(energy)) as hourly_energy
        from data_running_summed
        where date(`time`) = "2013-05-23"  and id_system = 1
        group by hour(time)
        order by `time`

Dus van een bepaald uur, pak ik de maximale hoeveelheid energie (dus aan het einde van dat uur) minus de minimale hoeveelheid energie (dus aan het begin van het uur) om te berekenen hoeveel energie er gedurende die tijdsspanne van 1 uur is opgewekt. (hourly_energy)

Nu wil ik ook graag een bepalen hoeveel alle systemen samen op hebben gewekt, maar ik weet eerlijk gezegd even niet hoe ik dat moet aanpakken.

Ik kan natuurlijk via php voor elk id_system de query elke keer gaan uitvoeren (op dit moment 7 keer). Maar het lijkt mij dat dit ook met 1 query moet kunnen vanuit MySQL.

Het weglaten van 'AND id_system = 1' wil niet, want dan pakt hij de hoogst gelogde hoeveelheid energie van een bepaald systeem en trekt daar het minste van (een ander systeem) weer vanaf.

De query moet dus een SUM() gaan doen, voor elke id_system in de database en de totale opwekking per interval van 1 uur berekenen.


Kortom, ik ben even de weg kwijt en heb een schop(je) in de juist richting nodig.

[_js_]

Een subquery kan onder andere een tabel vervangen in een query. Dus SELECT ... FROM ([de query die je al hebt]) GROUP BY ...

Je gebruikt al group by, dus een lijst van de totalen van elk systeem ipv alleen 1 gaat je wel lukken.

[bartbh]

Thanks! Volgens mij heb ik hem nu. Ik zal morgen even verder testen.

Voor de andere mensen

MySQL:

        select 
            time,
            sum(hourly_energy) as total_hourly_energy
        from(
            select
                time,
                (max(energy) - min(energy)) as hourly_energy
            from data_running_summed
            where date(`time`) = "2013-05-23"
            group by hour(time),id_system 
            order by `time`
            ) as testje
        group by hour(time)

Al heb ik al wel een bug/fout ontdekt in m'n originele berekening.

Stel

1702h = 1500
1759h = 1550
1802h = 1560
1859h = 1400

Via mijn uurberekening vind ik voor 17uur een opwekking van 1550-1500=50 terwijl het eigenlijk 60 moet zijn.

Maar dan moet ik "even" een nieuwe manier van berekenen gaan bedenken. De eerste waarde in een bepaald uur, verminderd met de eerste waarde van het uur daarvoor. (of andersom). Maar dat dan als MySQL query.

Maar dan loop ik in de knoop met het eerste uur op een dag (meestal van 5 uur) of het laatste uur op een dag (meestal rond 22 uur).

Dat wordt dus nog even verder puzzelen. Voorlopig houd ik deze berekening maar even aan, inclusief bijbehorende afwijking.

[TallManNL]

Waarom je systemid niet meenemen in je resultset en group by?

code:

        select 
            id_system
            hour(time) as hour, 
            (max(energy) - min(energy)) as hourly_energy 
        from data_running_summed 
        where date(`time`) = "2013-05-23" 
        group by hour(time) , id_system
        order by `time`

Zoals je zelf aangeeft krijg je hierin wel fouten omdat de productie tussen de laatste meting van een uur en de eerste meting van het volgende uur verloren gaat. Laatste voorgaande meting pakken zou dat oplossen.

Als je de energy door laat lopen over dagen heen vang je daarmee je start danwel eindfout weg en heb je meteen een totaalgetal.

[bartbh]

De id_system is voor mij voor de totale opwekking niet van belang?

Wat betreft de fout, ik moet nog even kijken hoe ik dat ga aanpakken. Het beste zou ik inderdaad de eerste uurwaarde vergelijken met de eerste uurwaarde van een uur daarvoor. Dus de eerste waarde om 1800 met die van 1700, om zo de opwekking van 17h te bepalen.

Probleem is echter, zoals ik al aangaf, dat er van ~5 uur tot ~22 uur gelogd wordt.

Ik zal hier morgen met een frisse neus en na een nachtje slaap nog eens goed voor gaan zitten.