Progetto Home Automation: Estrapolare i totali giornalieri di produzione e consumo dai dati istantanei salvati su InfluxDB


Era da tempo che volevo farlo e finalmente ho avuto il tempo di imparare un po’ meglio InfluxDB e Grafana per poter estrapolare i totali giornalieri dai dati grezzi che salviamo sul database.
Siccome a noi non interessa il valore preciso al milliWatt/h ma ci basta una stima “a grandi linee” per capire se siamo stati bravi nell’autoconsumo/consumo si possono attuare una serie di approssimazioni che rendono il problema apparentemente semplice.

La soluzione più intuitiva (e la prima che ci viene in mente) infatti è quella di fare la media di tutti i valori salvati in quel giorno (quindi è come se consumassimo costantemente quel valore) e moltiplicarla per le ore del giorno ottenendo il totale giornaliero.

Qui purtroppo mi sono scontrato con la dura realtà, cioè che quando i pannelli non producono non viene salvato nessun dato. Questo è un comportamento di per sé sensato e deriva dal fatto che quando ho configurato i sensori mqtt su homeassistant non ho impostato il force_update a true, poichè se il valore non cambia non voglio salvare dati inutili (e di solito il valore non cambia appunto quando i pannelli non producono).
E in tutto questo il problema dove sta? Beh semplicemente che la media che ottenevo dalla produzione energetica era enormemente più alta poichè non considerava i momenti in cui la produzione era a 0 nella media. E ora? Come fare?

Beh informandomi un pochino sulle capacità di InfluxDB, sfruttando le subquery sono riuscito a tirarmi fuori i dati come volevo io, quindi vediamo rapidamente come aggiungere queste informazioni a un grafico su Grafana!

Per prima cosa creiamo una un nuovo pannello dove andremmo a visualizzare i dati , come in figura qua sotto.

Il segreto è tutto nella query:

SELECT mean("mean")*24 FROM (SELECT mean("value") FROM "state" WHERE ("entity_id" = 'consumption') AND $timeFilter GROUP BY time(30s) fill(linear) tz('Europe/Rome') ) GROUP BY time(1d) fill(0) tz('Europe/Rome')

Per scriverla così dovrete andare su Raw Edit mode nelle query di Grafana (la matitina tra le icone in alto a destra della query). Ovviamente questa riguarda i consumi, per gli altri dati andrà cambiato ‘consumption’ nel valore desiderato.

Ma vediamo di capire meglio cosa fa questa query andando a capire un pelo più a fondo la struttura di una query di database (non sono espertissimo di database, quindi gli esperti de settore non me ne vogliano… l’idea è dare solo una infarinatura per chi non ha mai visto queste cose).

Una subquery sostanzialmente è una query fatta sui risultati di un’altra query.
Quindi se la struttura classica di una query è

SELECT * FROM database.table WHERE condizioni e tag aggiuntivi

in una subquery al posto di database.table ci sarà un’altra query.

Iniziamo a vedere la prima query al database:

(SELECT mean("value") FROM "state" WHERE ("entity_id" = 'consumption') AND $timeFilter GROUP BY time(30s) fill(linear) tz('Europe/Rome'))

Questa query dice: fa la media (SELECT mean(“value”)) da “state” (FROM “state”, che è il nome che da grafana al suo datasource (credo)) dove l’entity_id = consumption ed è all’interno del timefilter, dove timefilter è l’intervallo temporale dei dati settato da Grafana (WHERE (“entity_id” = ‘consumption’) AND $timeFilter), il tutto raggruppato in gruppi di 30s (GROUP BY time(30s)), dove non hai valori (nei 30 secondi) fa un’interpolazione lineare (fill(linear)) con timezone Europe/Rome (tz(‘Europe/Rome’)).

Una volta ottenuti i dati da questa query, viene fatta un’altra query su questi dati:

SELECT mean("mean")*24 FROM (*primaquery*) GROUP BY time(1d) fill(0) tz('Europe/Rome')

dove si dice di fare la media della media (poichè quello che otteniamo dalla prima query è una media), ottenendo così la media esatta nelle 24 ore (che poi moltiplicheremo per 24) prendendo i dati di un giorno (GROUP BY time(1d) fill(0) ) e settando a zero i giorni per cui non abbiamo dati. Infine settiamo il solito timezone in modo da avere la finestra di un giorno che inizi alle 00.

Bene ora dovreste avere i grafici con i totali di ogni giornata. Spero con questa breve guida di esser riuscito anche a spiegarvi la logica che sta dietro una query a database (e non me ne vogliano gli esperti del settore per eventuali errori, anzi correggetemi!).

Alla prossima 😉

Questa voce è stata pubblicata in Arch Linux, Home automation e contrassegnata con , , , , , , , , , , , , , , . Contrassegna il permalink.

4 risposte a Progetto Home Automation: Estrapolare i totali giornalieri di produzione e consumo dai dati istantanei salvati su InfluxDB

  1. Marco ha detto:

    Grazie mille per la guida!! veramente molto chiara! Ti faccio una domanda se posso. Come posso modifcare la query facendo in modo che mi consideri i kw solo se il risultato di un’altra query su un’altra tabella è 1 ? ad esempio Select mean (“value”) From state (where entity_it=”nomecolonna” and value=1 ?
    Grazie mille!

    "Mi piace"

  2. danse94 ha detto:

    Ciao Marco, mi fa molto piacere esserti stato d’aiuto! 🙂 Alla fine è il motivo per cui quando riesco scrivo i miei esperimenti qui sul blog.
    Sinceramente non sono così esperto di query su database da darti una risposta certa. Io al tuo posto proverei qualcosa del tipo (… WHERE entity_id=”nomecolonna” AND ((“miaQueryDovePrendoIlValue)=1). Ma forse ci sono funzioni (tipo CASE/JOIN) più adatte al tuo scopo. Fammi sapere se e come riesci a risolvere, e se posso aiutarti in qualche altro modo, volentieri!

    "Mi piace"

    • Marco Milone ha detto:

      Grazie mille, per la query sto cercando in ogni dove come risolvere ma ammetto che è tutt’altro che banale.
      In merito al tuo articolo ti chiedo ancora una cosa. Come filtro i giorni per tempo assoluto anzichè relativo? Mi spiego meglio. Se metto il filtro in grafana “7d” lui mi considera gli ultimi 7 gg dall’ora in cui effettuo la consultazione. Ad esempio, oggi è dominca e sono le 17, i dati che vedo nei grafici partiranno da domenica scorsa ore 17. Vorrei invece separarli per giorni solari.. Non so se mi sono spiegato. Al momento la mia query è questa come la tua: SELECT mean(“mean”)*24 FROM (SELECT mean(“value”) FROM “KWh” WHERE (“entity_id” = ‘pdc_pt_in_w’) AND $timeFilter GROUP BY time(5s) fill(linear) tz(‘Europe/Rome’) ) GROUP BY time(1d) fill(0) tz(‘Europe/Rome’)

      Impostazioni del grafico:
      Max data point : 1232 = widht of the panel
      Min interval no limit
      Interval 10m (calcolato automaticamente)
      Relative time 7d
      Time shif (blank)

      Grazie mille! a presto!

      "Mi piace"

  3. danse94 ha detto:

    Mi suona molto strano questo comportamento, la parte finale della query (GROUP BY time (1d) bla bla bla) dovrebbe fare proprio quello che stai cercando di fare tu. Ovviamente nella giornata corrente non avrai dei valori realistici (o meglio moltiplichi la media attuale delle n ore trascorse nella giornata per 24 quindi non è detto che sarà il valore che avrai alla fine della giornata). Ma per il resto mi aspetto funzioni come stai cercando di fare tu. Purtroppo in seguito ad aggiornamenti di InfluxDB la query è cambiata e ora utilizza il nuovo linguaggio FLUX per chiedere i dati. Ti metto qua sotto il mio codice attuale:
    // 24h0m0s is a variable referring to the current optimized window period (currently: $interval)
    from(bucket: “HomeAssistantDB”)
    |> range(start: 2022-01-09T09:08:22Z, stop: 2022-02-08T09:08:22Z)
    |> filter(fn: (r) => r[“_field”] == “value” and r[“_measurement”] == “state” and r[“domain”] == “sensor” and r[“entity_id”] == “production”)
    |> aggregateWindow(every: 1m, fn: mean)
    |> group()
    |> aggregateWindow(every: 1h, fn: mean)
    |> aggregateWindow(every: 1d, fn: sum)
    |> set(key: “_field”, value: “Production”)
    |> set(key: “domain”, value: “”)
    |> set(key: “entity_id”, value: “”)

    Riguardo alla query che si basa sul risultato di un’altra query credo sia abbastanza complesso più che altro per la struttura di come Grafana e InfluxDB sono fatti e comunicano tra loro. Magari potresti valutare qualche modo più “furbo” per salvarti i dati ed andare meglio ad estrapolarti i dati poi. Lavorando con database a volte i problemi ad ottenere i dati nel modo giusto sono causa di un salvataggio “sbagliato”.

    "Mi piace"

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...