So, jetzt bekommst Du alle create View statements für mein Dashboard noch einmal konzentriert. Die 4. View (Betriebszustände) sollte Dir alle Events auslesen, die in vieventlog in Deine Datenbank geschrieben hat. Bei der 6. View (event_betriebszustaende_timeline) habe ich nach den für meine Wärmepumpe relevanten gefiltert. Die müsstest Du an Deine Bedürfnisse anpassen. Damit sollten dann alle Panels funktionieren. Rückfragen gerne über private Nachricht. 1. temperature_snapshots_stat CREATE VIEW 'temperature_snapshots_stat' as SELECT datetime ( substr(timestamp, 1, 19), 'localtime') as 'timestamp_mez', thermal_power * 1000 / 60 * sample_interval as 'Wärmeleistung', compressor_power / 60 * sample_interval as 'elektrische_Leistung', thermal_power * 1000 / compressor_power as 'COP_calc', time('00:00', '+' || (compressor_active * sample_interval) || ' minutes') as 'Laufzeit', * from temperature_snapshots order by installation_id desc, datetime(substr(timestamp, 1, 19), 'localtime' ) desc 2. temperature_snapshots_takte CREATE VIEW temperature_snapshots_takte AS SELECT datetime(substr(timestamp,1,19), '+1 hour') AS timestamp_mez, installation_id, compressor_active, LAG(compressor_active) OVER ( PARTITION BY installation_id ORDER BY datetime(substr(timestamp,1,19), '+1 hour') DESC ) AS next_period, LAG(compressor_active) OVER ( PARTITION BY installation_id ORDER BY datetime(substr(timestamp,1,19), '+1 hour') DESC ) - compressor_active AS compressor_ein_aus FROM temperature_snapshots 3. temperature_snapshots_takte_heute CREATE VIEW 'temperature_snapshots_takte_heute' as SELECT datetime ( substr(timestamp, 1, 19), 'localtime') as timestamp_mez, installation_id, Compressor_active, LAG ( compressor_active) over(order by installation_id desc, datetime ( substr(timestamp, 1, 19), 'localtime') desc) as next_period, LAG ( compressor_active) over(order by installation_id desc, datetime ( substr(timestamp, 1, 19), 'localtime') desc) - compressor_active as 'compressor_ein_aus' from temperature_snapshots 4. Betriebszustände CREATE VIEW 'Betriebszustaende' as SELECT error_code, error_description, human_readable from events group by error_code order by error_code 5. dashboard_view CREATE VIEW 'dashboard_view' as SELECT strftime('%F %T', event_timestamp), strftime('%T', created_at), error_code, human_readable From events Where active= 1 Order by installation_id desc, event_timestamp desc, error_code asc 6. event_betriebszustand_timeline CREATE VIEW 'event_betriebszustand_timeline' as SELECT Installation_id, gateway_serial, device_id, account_id, strftime ( '%F %T', event_timestamp) AS event_timestamp_start, CASE WHEN LAG(event_timestamp) OVER ( PARTITION BY installation_id ORDER BY event_timestamp DESC, error_code ASC ) IS NULL THEN strftime('%F %T', 'now') ELSE strftime( '%F %T', LAG(event_timestamp) OVER ( PARTITION BY installation_id ORDER BY event_timestamp DESC, error_code ASC ) ) END AS event_timestamp_end, strftime('%F %T', created_at) AS created_at, error_code, human_readable, CASE WHEN error_code in ('S.118') THEN 'S.125' WHEN error_code in ('S.127', 'S.135', 'S.176') THEN 'S.128' ELSE error_code END AS 'betriebszustand' FROM events WHERE active = 1 AND error_code <> 'S.187' 7. Bonus (sehr rechenintensiv): v_heizzyklen zeigt Dir alle reinen Heizzyklen (ohne Warmwasser und Abtauen) an (Start, Ende, Laufzeit, Zeit bis nächster Heizzyklus). Die verwende ich zur Analyse, ob meine Wärmepumpe mögliches Taktverhalten zeigt (was sie gottseidank nicht tut) CREATE VIEW v_heizzyklen AS WITH snapshots AS ( SELECT datetime(substr(timestamp, 1, 19), 'localtime') AS timestamp_mez, installation_id, compressor_active, volumetric_flow, LAG(compressor_active) OVER ( PARTITION BY installation_id ORDER BY datetime(substr(timestamp, 1, 19), 'localtime') ASC ) AS previous_period FROM temperature_snapshots ), marked AS ( SELECT *, CASE WHEN compressor_active = 1 AND previous_period = 0 THEN 'ein' WHEN compressor_active = 0 AND previous_period = 1 THEN 'aus' END AS ein_aus FROM snapshots ), numbered AS ( SELECT *, SUM( CASE WHEN ein_aus = 'ein' THEN 1 ELSE 0 END ) OVER ( PARTITION BY installation_id ORDER BY timestamp_mez ASC ROWS UNBOUNDED PRECEDING ) AS cycle_id FROM marked ), cycles AS ( SELECT installation_id, cycle_id, MIN( CASE WHEN ein_aus = 'ein' THEN timestamp_mez END ) AS start_time, MAX( CASE WHEN ein_aus = 'aus' THEN timestamp_mez END ) AS end_time, MAX( CASE WHEN compressor_active = 1 AND volumetric_flow >= 1430 AND volumetric_flow <= 1630 THEN 1 ELSE 0 END ) AS heizzyklus FROM numbered WHERE cycle_id > 0 GROUP BY installation_id, cycle_id ), heating_cycles AS ( SELECT installation_id, cycle_id, start_time, end_time, unixepoch(end_time) - unixepoch(start_time) AS laufzeit FROM cycles WHERE heizzyklus = 1 AND start_time IS NOT NULL AND end_time IS NOT NULL ), heating_cycles_next AS ( SELECT *, LEAD(start_time) OVER ( PARTITION BY installation_id ORDER BY start_time ASC ) AS next_heating_start FROM heating_cycles ) SELECT installation_id, cycle_id, start_time AS "Start", end_time AS "Ende", laufzeit AS "Laufzeit", CASE WHEN next_heating_start IS NOT NULL THEN unixepoch(next_heating_start) - unixepoch(end_time) END AS "Pause bis nächster Heizzyklus" FROM heating_cycles_next ORDER BY start_time DESC
... Mehr anzeigen
