SQL trucs
Daarom
Op de pagina EHOME zijn wat statistieken te zien over mijn stroom gebruik. De data onder deze statistieken worden geleverd door SQL opdrachten uit te voeren op de SQLITE database. In dit blog deel ik wat bijzondere (vind ik) SQL opdrachten die mij hebben geholpen bij wat uitdagingen.
Gebruik van het voorafgaande record
Zoals te lezen in een van de eerdere blogs gebruik ik een P1-meter om mijn stroomverbruik te vergaren. De ontvangen data leg ik vast in mijn database. Ik leg de data vast zoals ik ze via de P1 API binnen krijg. Dat betekent dat ik 4 totaalstanden van de stroommeter krijg; export hoogtarief, export laagtarief, import hoogtarief en import laagtarief. Om het verbruik in een periode te bepalen moet ik het verschil tussen 2 meting bepalen; verbruik = laatste totaalstand - vorige totaalstand.In dit geval is het erg handig om het laatste record in de tabel te koppelen aan het voorlaatste record. Om dat zonder moeite te kunnen doen helpt het als je een automatisch recordnummer hebt opgenomen in je tabel (zoals ik heb gedaan).
Mijn tabel ESTATS ziet er als volgt uit: id, date, hour, type, amount
tabel joined met zichzelf op basis van het gelezen record (t1) met het record daaraan voorafgaand (t2).
Input:
id date hour type amount
579 2023-01-12 13 USE 20376.755
580 2023-01-12 14 USE 20377.051
581 2023-01-12 15 USE 20378.238
Proces:
SELECT t2.date, t2.hour, ROUND(t2.amount-t1.amount,5) as kWh
FROM estats t1, estats t2
WHERE t1.id=t2.id-1
Output:
date hour kWh
2023-01-12 13 0.18
2023-01-12 14 0.296
2023-01-12 15 1.187
Uitsluitend records uit de lopende maand lezen
Om het totale verbruik en kosten van de lopende maand te kunnen bepalen moet je bepalen wat de eerste datum van de lopende maand is. Vervolgens kan je alle records selecteren die groter of gelijk zijn aan die eerste datum van de maand en het totaal van die records bepalen (TOTAL).
SQL kent DATE('now', 'start of month') en dit geeft de eerste datum in de huidige maand terug.
Input:
Date
2022-12-31
2023-01-01
2023-01-02
2023-01-03
Proces:
SELECT date
FROM tib_consumption_daily
WHERE date >= DATE('now', 'start of month')
Output: (op 3 jan 2023)
date
2023-01-01
2023-01-02
2023-01-03
Bereken een 'running' totaal
Om na elk gelezen record het zgn. 'running' totaal te bepalen kan je gebruik maken van de OVER functie. In onderstaande SQL opdracht wordt elk gelezen record voorzien van een running totaal van de kosten.
SQL kent de zgn. "window" functie OVER.
Met deze functie ben je in staat om (achter de schermen) meer records te benaderen dan alleen het huidig gelezen record (zie afbeelding).
Input:
date cost
2023-01-07 4.6070118743
2023-01-08 2.9788044748
2023-01-09 4.5692928468
2023-01-10 5.6871322893
Proces:
SELECT date, SUM(ROUND(cost,2)) OVER (ORDER BY date) AS running_total
FROM tib_consumption_daily
ORDER BY date
Output:
date running_total
2023-01-07 4.61
2023-01-08 7.59
2023-01-09 12.16
2023-01-10 17.85