Nachberechnen von Statistikwerten

[PichlAlex]

Hallo,

ich nutze seit ca 2 Jahren fhem und seit längerem das Statistikmodul "statistics" - jetzt bin ich auf die Idee gekommen die gesammelten Daten mal genauer auswerten zu wollen.

Im DbLog schreibe ich die AvgLast-Werte die ich dann auswerten möchte:
      timestamp      |         device          |  type  |              event              |          reading          | value | unit
---------------------+-------------------------+--------+---------------------------------+---------------------------+-------+------
2015-12-29 23:59:55 | Arbeitszimmer_temp      | CUL_HM | statTemperatureDayAvgLast: 21.4 | statTemperatureDayAvgLast | 21.4  |


jetzt hab ich nur ein Problem - erst seit dem ich den Wert zum DbLog aufgenommen habe sind sie im DbLog (war ja zu erwarten) - ich hätt nur gern die älteren auch - dh ich möchte bis zum Anfang zurück die Statistikwerte nachberechnen lassen und im DbLog ablegen.

gibts da bereits etwas fertiges oder muss ich ein (SQL)Script schreiben dass die Werte aus der DB ausliest, den Mittelwert berechnet und dann den AvgLast-Wert wieder einfügt?

ich bin für jeden Hinweis dankbar 

Alex

[PichlAlex]

... habe begonnen ein SQL Script zu schreiben ...     Das Ergebnis wird dann hier gepostet

[PichlAlex]

bin fertig!


das ganze besteht aus 3 Files:

File 1: "./calc_statistics_for_1_month.postgres.sql"   - Berechnet für 1 Monat

Code Auswählen Erweitern

\i 'calc_statistics_functions.postgres.sql'
\set VERBOSITY terse
begin work;
SELECT updateStat1Month('2015-12-01 00:00:00 CET');
commit work;

File 2: "./calc_statistics_for_1_day.postgres.sql"   - Berechnet für 1 Tag

Code Auswählen Erweitern

\i 'calc_statistics_functions.postgres.sql'
\set VERBOSITY terse

begin work;

SELECT updateStat1day('2015-11-20 00:00:00 CET');

commit work;

File 3: "./calc_statistics_functions.postgres.sql"   - Kernfunktionalität der Berechnung

Code Auswählen Erweitern


CREATE OR REPLACE FUNCTION updateStat1day(timestamp with time zone) RETURNS void AS
$BODY$
DECLARE
    startTimestamp timestamp;
    endTimestamp timestamp;
    r history%rowtype;
    countDelete integer;
    countInsert integer;
BEGIN
    -- Startzeitpunkt ist immer Mitternacht - Endzeitpunkt Mitternacht am nächsten Tag
    startTimestamp :=  date_trunc('day', $1);
    endTimestamp := startTimestamp + interval '1 day';

    -- alte Statistiken löschen
    raise notice 'day %  deleting old statistics', startTimestamp;
    countDelete := '-1';
    delete  from history
            where timestamp >= startTimestamp
                  and timestamp < endTimestamp
                  and reading like 'stat%Last';

    if FOUND then
       get diagnostics countDelete = ROW_COUNT;
    end if;
    raise notice 'day %  deleted %', startTimestamp, countDelete;
    raise notice 'day %  selecting-inserting new (Timerange: % - %)', startTimestamp, startTimestamp, endTimestamp;
    countInsert := '0';

    FOR r IN  select max( timestamp ) as timestamp,
                     device,
                     min(type) as type ,
                     '' as event ,
                     ('stat' || upper(substring(reading from 1 for 1)) || substring(reading from 2) || 'DayAvgLast') as reading ,
                     trunc(avg( cast(value  as numeric )),2) as value ,
                     '' as unit
                from history
                where timestamp >= startTimestamp
                  and timestamp < endTimestamp
                  and (value ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$') = 't'
                  and reading not like 'stat%'
                group by device, reading, date_trunc('day', timestamp)
    LOOP
        -- build new statistics entry
        r.event := r.reading || ': ' || r.value;
        r.unit = '';
        insert into history values (r.timestamp, r.device, r.type, r.event, r.reading, r.value, r.unit);
        raise notice 'insert %|%|%|%', r.timestamp, r.device, r.reading, r.value;
        countInsert := countInsert + 1;
    END LOOP;

    raise notice 'day %  % new entries', startTimestamp, countInsert;

    RETURN;

EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
END;
$BODY$
LANGUAGE 'plpgsql' ;

CREATE OR REPLACE FUNCTION updateStat1Month(timestamp with time zone) RETURNS void AS
$BODY$
DECLARE
    startTimestamp timestamp;
    startTimestamp2 timestamp;
    endTimestamp timestamp;
BEGIN
    startTimestamp :=  date_trunc('month', $1);
    startTimestamp2 := startTimestamp;
    endTimestamp := startTimestamp + interval '1 month' - interval '1 day';

    raise notice 'Started Month % at: % (End: %)', startTimestamp, timeofday(), endTimestamp;

    loop

         raise notice '--------------------------------- new day -----------------------------------------------------------------';
         raise notice 'NOW: %', timeofday();
         raise notice 'Started Day %', startTimestamp2;

         PERFORM updateStat1day(startTimestamp2);

         raise notice 'Finished Day %', startTimestamp2;

         startTimestamp2 := startTimestamp2 + interval '1 day';
         exit when startTimestamp2 > endTimestamp;
    end loop;

    raise notice 'Finished Month % at: %', startTimestamp, timeofday();
    RETURN;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
END;
$BODY$
LANGUAGE 'plpgsql' ;





Aufruf der Scripte  (Datenbank-Schema "fhemDB"; Unix-User unter dem Postgres läuft "postgres"):
Scriptinhalt anpassen (Timestamp auf den gewünschten Tag/Monat ändern)
Berechnung für 1 Tag: sudo -u postgres psql -d fhemDB -a -f ./calc_statistics_for_1_day.postgres.sql  2>&1 | tee ./statRecalc.log
Berechnung für 1 Monat: sudo -u postgres psql -d fhemDB -a -f ./calc_statistics_for_1_month.postgres.sql  2>&1 | tee ./statRecalc.log

Tagesberechnung ist immer von 00:00:00 - 23:59:59,999999
Monatsberechnung ist immer vom jeweiligen 1. bis zum monatsletzten.


meine Sytemumgebung:

• Cubietruck mit Igor-Image
• /-Partition auf externe SSD ausgelagert
• DB-Log schreibt in einen Postgres SQL-Server 9.3
• Devices in Modulen die im DBLOG landen
    CUL_HM     : 51 (real ca 20 Endgeräte - viele haben mehrere Devices)
    EC3000     : 5
    SYSMON     : 1
• DB wird durch die 5 EC-Devices "geflutet" - jedes erstellt im 5 Sekunden-Takt einen neuen Wert

Laufzeit (ca 190.000 - 320.000 Werte in DB pro Tag - 7.400.000 pro Monat):

• 1 Tag: ca 50 s
• 1 Monat: ca 7 Minuten

was ich damit mache:
-) einmalig Monatsweise die Statistiken in der DB nachrechnen lassen (calc_statistics_for_1_month.postgres.sql)
-) laufend per Cron-job kurz nach Mitternacht die Vortagesstatistiken berechnen lassen (calc_statistics_for_1_day.postgres.sql)

Verbesserungsvorschläge sind willkommen und werden hier eingearbeitet.
Implementierung als Cron folgt in ein paar Tagen.

Übernahme ins Wiki kann gerne erfolgen!