Projekt 23 Smarthome / Datenanalyse

Aus c3RE.de
Wechseln zu: Navigation, Suche

Diese Seite gehört zum Kontext Projekt 23 Smarthome und beschreibt die Aspekte Analyse und Visualisierung der im Haus produzierten Daten.

Datenquellen

Übersicht über die Datenquellen im Haus:

Quelle Datensammlung Beschreibung
Wechselrichter (PV-Anlage) RDBMS (mariaDB) Projekt 23 Smarthome / Zugriff Wechselrichter
Stromzähler RDBMS (mariaDB) Projekt 23 Smarthome / Zugriff Stromzähler
KNX-Bus journal systemd Projekt 23 Smarthome / Zugriff KNX-Bus
Wärmepumpe

Datenbank (RDBMS)

Stromzähler und Wechselrichter

Ein Join über die Tabellen der Daten von Stromzähler und Wechselrichter bringt die gewünschte Übersicht, aus welcher sich auch die Eigenverbrauchtsrate ablesen lässt. Es wird ein left join verwendet, weil die Daten des Stromzählers kontinuierlich erfasst werden, während beim Wechselrichter Strom und Daten nur bei Tageslicht produziert werden. Solange dort keine Daten vorliegen, wird der Bereich mit "NULL" ausgewiesen:

$ echo "select now(); select date_format( m.TimeStamp, '%Y-%m') as Datum, (max( value_180) - min( value_180))/10000 as 'T 1.8.0 [KWh]', (max( value_280) - min( value_280))/10000 as 'T 2.8.0 [KWh]', round(( max( TotalYield) - min( TotalYield))/1000,3) as 'Erzeugung [KWh]', round(((( max( TotalYield) - min( TotalYield)))/1000 - (max( value_280) - min( value_280))/10000),3) as 'Verbrauch [KWh]', round((((( max( TotalYield) - min( TotalYield)))/1000 - (max( value_280) - min( value_280))/10000)) / (( max( TotalYield) - min( TotalYield))/1000) * 100, 1) as 'EV-Rate [%]', round( (((( max( TotalYield) - min( TotalYield)))/1000 - (max( value_280) - min( value_280))/10000)) / (((max( value_180) - min( value_180))/10000) + (((( max( TotalYield) - min( TotalYield)))/1000 - (max( value_280) - min( value_280))/10000))) * 100, 1) as 'Autarkie [%]', round( max( wr.Power), 0) as 'Max. Power [W]', round( avg( wr.Power), 0) as 'AVG. Power [W]' from sml.v_meter m left join SBFspot.v_DayData wr on date_format( m.TimeStamp, '%Y-%m')=FROM_UNIXTIME( wr.TimeStamp, '%Y-%m') group by Datum order by Datum desc;" | mysql -u any --table
+---------------------+
| now()               |
+---------------------+
| 2017-09-23 08:54:03 |
+---------------------+
+---------+---------------+---------------+-----------------+-----------------+-------------+--------------+----------------+----------------+
| Datum   | T 1.8.0 [KWh] | T 2.8.0 [KWh] | Erzeugung [KWh] | Verbrauch [KWh] | EV-Rate [%] | Autarkie [%] | Max. Power [W] | AVG. Power [W] |
+---------+---------------+---------------+-----------------+-----------------+-------------+--------------+----------------+----------------+
| 2017-09 |      220.9568 |      547.3225 |         711.062 |         163.740 |        23.0 |         42.6 |           9336 |           2564 |
| 2017-08 |      222.0856 |      843.0703 |        1088.167 |         245.097 |        22.5 |         52.5 |           9360 |           2473 |
| 2017-07 |      179.5711 |      959.8850 |        1219.122 |         259.237 |        21.3 |         59.1 |           9288 |           2535 |
| 2017-06 |      180.4488 |      954.0125 |        1233.760 |         279.748 |        22.7 |         60.8 |           9228 |           2538 |
| 2017-05 |      265.7407 |      932.5348 |        1262.471 |         329.936 |        26.1 |         55.4 |           9372 |           2663 |
| 2017-04 |      444.3708 |      745.3512 |        1058.289 |         312.938 |        29.6 |         41.3 |           9564 |           2593 |
| 2017-03 |      560.4303 |      724.5186 |        1017.058 |         292.539 |        28.8 |         34.3 |           9456 |           2800 |
| 2017-02 |      714.0886 |      243.2475 |         445.839 |         202.592 |        45.4 |         22.1 |           8880 |           1673 |
| 2017-01 |      959.4744 |      233.1026 |         463.289 |         230.186 |        49.7 |         19.3 |           7728 |           1846 |
| 2016-12 |      879.0000 |      191.0000 |         373.520 |         182.520 |        48.9 |         17.2 |           6684 |           1597 |
| 2016-11 |      783.0000 |      187.0000 |         376.067 |         189.067 |        50.3 |         19.4 |           8448 |           1501 |
| 2016-10 |      536.0000 |      380.0000 |         580.358 |         200.358 |        34.5 |         27.2 |           9528 |           1838 |
| 2016-09 |      220.0000 |      951.0000 |        1209.922 |         258.922 |        21.4 |         54.1 |           NULL |           NULL |
| 2016-08 |      160.0000 |      796.0000 |         984.000 |         188.000 |        19.1 |         54.0 |           NULL |           NULL |
+---------+---------------+---------------+-----------------+-----------------+-------------+--------------+----------------+----------------+

Bedeutung:

  • T 1.8.0: Eingehender Tarif (Bezug aus dem öffentlichen Netz)
  • T 2.8.0: Ausgehender Tarif (Lieferung in das öffentliche Netz)
  • Erzeugung: Von der Anlage erzeugte Energie
  • Verbrauch: Selbst verbrauchter Strom
  • EV-Rate: Eigenverbrauchsrate

Hinweis: Die vielen Berechnungen zur Datumsgruppierung erzeugen eine hohe Last und eine sehr lange Laufzeit - leider hilft hier keine Performanceoptimierung durch Indizierung. Daher wird die Anzahl der zu vergleichenden Werte (hier die letzten 7 Tage) schon innerhalb der where-Klausel eingegrenzt und nicht erst durch ein abschliessendes limit:

where (TO_DAYS(now())-TO_DAYS( date_format( m.TimeStamp, '%Y-%m-%d')))<7

Dadurch benötigt die Abfrage zwar immer noch eine vergleichsweise lange Laufzeit, aber das Wachtstum der Laufzeit durch zunehmende Datenbestände wurde begrenzt.

Auswertung ohne Passworteingabe

Weil keine Sicherheitsbedenken gegen das Auslesen der Daten vom lokalen System aus (lediglich SSH-Zugang mit PubKey-Authentifizierung möglich) ohne Anmeldung besteht, wurde ein entsprechende Userberechtigungen und Views angelegt. Der rein lesende Zugriff (select) darauf ist somit ohne Eingabe eines Passwortes möglich.

User 'any' erzeugen:

MariaDB [sml]> create user any@localhost;

Views als Select-Wrapper erzeugen:

MariaDB [sml]> create view SBFspot.v_DayData as select * from SBFspot.DayData;
MariaDB [sml]> create view sml.v_meter as select * from sml.meter;

Berechtigungen zugestehen:

MariaDB [sml]> grant select on sml.v_meter to any;
MariaDB [sml]> grant select on SBFspot.v_DayData to any;
MariaDB [sml]> flush privileges;

Als Resultat ist damit nun die Abfrage von der Kommandozeile ohne Passworteingabe für den Datenbank-Server möglich:

$ echo "select now(); select date_format( m.TimeStamp, '%Y-%m-%d') as Datum, max( value_180/10000) - min( value_180/10000) as 'Tarif 1.8.0 [KWh]', max( value_280/10000) - min( value_280/10000) as 'Tarif 2.8.0 [KWh]', max( round( TotalYield/1000, 3)) - min( round( TotalYield/1000, 3)) as 'Gesamterzeugung [KWh]', max( round( TotalYield/1000, 3)) - min( round( TotalYield/1000, 3)) - (max( value_280/10000) - min( value_280/10000)) as 'Eigenverbrauch [KWh]' from sml.v_meter m left join SBFspot.v_DayData wr on date_format( m.TimeStamp, '%Y-%m-%d')=FROM_UNIXTIME( wr.TimeStamp, '%Y-%m-%d') where (TO_DAYS(now())-TO_DAYS( date_format( m.TimeStamp, '%Y-%m-%d')))<7 group by Datum order by Datum desc;" | mysql -u any --table
+---------------------+
| now()               |
+---------------------+
| 2017-03-01 14:42:57 |
+---------------------+
+------------+-------------------+-------------------+-----------------------+----------------------+
| Datum      | Tarif 1.8.0 [KWh] | Tarif 2.8.0 [KWh] | Gesamterzeugung [KWh] | Eigenverbrauch [KWh] |
+------------+-------------------+-------------------+-----------------------+----------------------+
| 2017-03-01 |           14.2960 |            5.2922 |                13.407 |               8.1148 |
| 2017-02-28 |           22.9402 |            6.8786 |                12.774 |               5.8954 |
| 2017-02-27 |           25.7974 |            3.9530 |                 9.805 |               5.8520 |
| 2017-02-26 |           20.9973 |            3.2488 |                 9.421 |               6.1722 |
| 2017-02-25 |           22.3940 |            8.5456 |                16.704 |               8.1584 |
| 2017-02-24 |           24.1488 |           23.9429 |                33.660 |               9.7171 |
| 2017-02-23 |           27.8332 |            2.6593 |                 5.828 |               3.1687 |
+------------+-------------------+-------------------+-----------------------+----------------------+

Dies klappt dann auch beim Einsatz von 'ssh-agent' von einem entfernten Rechner aus ohne explizite Passworteingabe (genauer gesagt mit einmaliger Eingabe pro Session):

$ ssh j8-admin "echo \"select now(); select date_format( m.TimeStamp, '%Y-%m-%d') as Datum, max( value_180/10000) - min( value_180/10000) as 'Tarif 1.8.0 [KWh]', max( value_280/10000) - min( value_280/10000) as 'Tarif 2.8.0 [KWh]', max( round( TotalYield/1000, 3)) - min( round( TotalYield/1000, 3)) as 'Gesamterzeugung [KWh]', max( round( TotalYield/1000, 3)) - min( round( TotalYield/1000, 3)) - (max( value_280/10000) - min( value_280/10000)) as 'Eigenverbrauch [KWh]' from sml.v_meter m left join SBFspot.v_DayData wr on date_format( m.TimeStamp, '%Y-%m-%d')=FROM_UNIXTIME( wr.TimeStamp, '%Y-%m-%d') where (TO_DAYS(now())-TO_DAYS( date_format( m.TimeStamp, '%Y-%m-%d')))<7 group by Datum order by Datum desc;\" | mysql -u any --table"
+---------------------+
| now()               |
+---------------------+
| 2017-03-01 14:49:00 |
+---------------------+
+------------+-------------------+-------------------+-----------------------+----------------------+
| Datum      | Tarif 1.8.0 [KWh] | Tarif 2.8.0 [KWh] | Gesamterzeugung [KWh] | Eigenverbrauch [KWh] |
+------------+-------------------+-------------------+-----------------------+----------------------+
| 2017-03-01 |           14.2960 |            5.7291 |                13.753 |               8.0239 |
| 2017-02-28 |           22.9402 |            6.8786 |                12.774 |               5.8954 |
| 2017-02-27 |           25.7974 |            3.9530 |                 9.805 |               5.8520 |
| 2017-02-26 |           20.9973 |            3.2488 |                 9.421 |               6.1722 |
| 2017-02-25 |           22.3940 |            8.5456 |                16.704 |               8.1584 |
| 2017-02-24 |           24.1488 |           23.9429 |                33.660 |               9.7171 |
| 2017-02-23 |           27.8332 |            2.6593 |                 5.828 |               3.1687 |
+------------+-------------------+-------------------+-----------------------+----------------------+