Vorschlag: Sammlung von SQL Statements (als Funktionen)

ehome

Hallo zusammen,

mittlerweile sammle ich schon einiges an Daten (PV Anlage, Smartmeter etc.). Für das Auswerten der Datenflut habe ich allerdings noch nicht so das Richtige gefunden. Zwar gibt ein sehr aufwendiges Skript (der Betriebsstundenzaehler BSZ Extended von Looxer01 - http://forum.iobroker.net/viewtopic.php … &hilit=bsz) – doch irgendwie gefällt mit der Gedanke nicht die Daten einerseits in die DB zu pumpen, andererseits aber die Auswertung (z.B. für den erzeugten PV Strom pro Monat) an der Datenbank "vorbei" zu betreiben

:idea: Mein Vorschlag:

JavaScript Funktionssammlung zur Auswertung der ioBroker Mysql Datenbank des Mysql-History Adapters. Wenn ihr auch SQL-Abfragen zur Berechnung von Statistiken nutzt könnte man damit so etwas wie eine Funktions-Bibliothek zusammenstellen.

Wie ist eure Meinung zu dem Vorschlag ?

Bin mal gespannt …..

Ich fange mal mit einem ersten Versuch an.

Die Funktion berechnet den Verbrauch jeden Monats für die letzten 12 Monate. Es werden 2 Übergabe Parameter erwartet.

Das IO-Broker Objekt des Counters von dem die SQL Werte gespeichert werden
Das Ergebnis Objekt – dort werden die Daten dann im Json Format abgelegt (und können dann direkt in VIS als Tabelle angezeigt werden).


function statistiken_counter_12_monate (id,id_ergebnis) {
      var myQuery="SELECT CONCAT(YEAR(FROM_UNIXTIME(substring(ts,1,10))),'-', MONTH(FROM_UNIXTIME(substring(ts,1,10)))) AS Monat, round(max(val)-min(val),0) AS Verbrauch \
                 FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                 AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 12 month) \
                 AND CURRENT_DATE() \
                 GROUP BY YEAR(FROM_UNIXTIME(substring(ts,1,10))),MONTH(FROM_UNIXTIME(substring(ts,1,10))) \
                 ORDER BY YEAR(FROM_UNIXTIME(substring(ts,1,10))) DESC, MONTH(FROM_UNIXTIME(substring(ts,1,10))) DESC;";
    log(myQuery);
    sendTo('sql.0', 'query', myQuery, function (result) {
        if (result.error) {
            log(result.error);
        } else {
            // show result
            setState(id_ergebnis,JSON.stringify(result.result),true);
        }
    });
}

Bei Mir sieht das dann so aus:

Aufruf:

statistiken_counter_12_monate(„wrapper.1.Strom.Aktuell.a_count_pv-gesamtproduktion“  , „javascript.0.Statistiken.12_monate.count_pv-gesamtproduktion“)

Ergebinis (ist dann Inhalt von javascript.0.Statistiken.12_monate.count_pv-gesamtproduktion)

[{"Monat":"2017-11","Verbrauch":65},{"Monat":"2017-10","Verbrauch":235},{"Monat":"2017-9","Verbrauch":341}]

(Habe die Daten erst seit September deshalb nur 3 Werte)

Dutchman

Ich finde die idee sehr interessant und auch Kandidat als Adapter aus zu bauen die Statistiken.

Ich stehe mir dann vier das man dem Adapter definiert weicher Date so zusammengefasst werden sollen.

Dieser werden dann aus der DB gelesen und wie in deinen bespiel als object festgelegt.

Das wäre eine schöne erreicherung zB zum Messen des energy Verbrauchs, Gewinnung oder so einig andere Beispiele

Zum Anfang ist allein die Möglichkeit dass mit JS machen zu können bereits genial.

Ich hatte vor einigen Monaten Mal eine vergleichbare Frage gestellt (ohne Lösung) zur Statistik Berechnung einer Wärme Gewinnung Installation

Nice!

–-----------------------

Send from mobile device

apollon77

Es gibt auch immer noch die Idee eines "Sattistik/Aggregator"-Adapters der einfache Dinge wie Summen, Differenzen u.ä. machen könnte …

https://trello.com/c/4tzXcP7X

mal schauen ob/wann ich mal zu sowas komme

ehome

So ein erstes Skript mit „sql“ Funktionen zur Erstellung von Statistiken.

Der Code ist sicher etwas holprig (bin JavaScript Anfänger - funktioniert aber für mich).

Verbesserungsvorschläge sind herzlich willkommen !

Das Skript zieht aus der DB folgende Statistiken:

12_Monate:

Monatlicher Verbrauch der letzten 12 Monate (Json)

7_Tage

Täglicher Verbrauch der letzten 7 Tage (Json)

seitJahresbeginn/Monatsbeginn/Wochenbeginn/Tagesbeginn

Verbrauch seit Jahresbeginn (einzelner String)

Die Counter für die man die Statistiken haben möchte werden oben einfach in das Array eingetragen.

Im zweiten Array stehen sprechende Namen damit die Variablen auch vernünftig benannt werden.

//
// Berechnet counter differenzen 
//

var logging=true;

// instanz und pfad
var instanz="javascript." + instance + ".Statistiken.";

// wrapper Objekt für aktuellen Zaehlerstand
// sind alle in kWh

var wrapperobj='wrapper.1.Counter.';

var counter=[];
counter[0]='pv-eigenverbrauch';
counter[1]='pv-gesamtproduktion';
counter[2]='hausnaschluss-abgabe';
counter[3]='wallbox';
counter[4]='hausnaschluss-bezug';
counter[5]='waschmaschine';
counter[6]='trockner';

var label=[];
label[0]='PV Eigenverbr.';
label[1]='PV Produktion';
label[2]='PV Einspeisung';
label[3]='Wallbox';
label[4]='Bezug aus Netz';
label[5]='Waschmaschine';
label[6]='Trockner';

//
// 12 Monats Statistik
//

var statistik12monate="12_Monate.";
var namebez="12 Monate.";
counter.forEach(function(element) {
    createState(instanz+statistik12monate+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });

});

//
// 7 Tage Statistik
//

var statistik7tage="7_Tage.";
var namebez="7 Tage";
counter.forEach(function(element) {
    log("7 Tage->"+element);
    createState(instanz+statistik7tage+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });

});

// seit Tagesanfang

var statistik_anfangTag="seitTagesbeginn.";
var namebez="seit Tagesbeginn";
counter.forEach(function(element) {
    createState(instanz+statistik_anfangTag+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });

});

var statistik_anfangWoche="seitWochenbeginn.";
var namebez="seit Wochenbeginn";
counter.forEach(function(element) {
    createState(instanz+statistik_anfangWoche+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });

});

var statistik_anfangMonat="seitMonatsbeginn.";
var namebez="seit Monatsbeginn";
counter.forEach(function(element) {
    createState(instanz+statistik_anfangMonat+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });

});

var statistik_anfangJahr="seitJahresbeginn.";
var namebez="seit Jahresbeginn";
counter.forEach(function(element) {
    createState(instanz+statistik_anfangJahr+element, {
            name: namebez+' '+element,
            type: 'string',
            unit: 'kWh',
    });
});

schedule("36 0 * * *", function () {
    counter.forEach(function(element) {
       var id=wrapperobj+element;

       // 12 Monate
       var id_ergebnis=instanz+statistik12monate+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_counter_12_monate(id,id_ergebnis);

       // 7 Tage
       id_ergebnis=instanz+statistik7tage+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_counter_7_tage(id,id_ergebnis);

    });
});

schedule("*/15 0 * * *", function () {  

    counter.forEach(function(element) {
       var id=wrapperobj+element;

       // seit Jahresanfang
       var jahresanfang=datum_jahresanfang(new Date());
       id_ergebnis=instanz+statistik_anfangJahr+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_differenz_seit_datum(id,id_ergebnis,jahresanfang);

       // seit Monatsanfang
       var monatsanfang=datum_monatsanfang(new Date());
       id_ergebnis=instanz+statistik_anfangMonat+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_differenz_seit_datum(id,id_ergebnis,monatsanfang);

       // seit Wochenanfang
       var wochenanfang=datum_wochenanfang(new Date());
       id_ergebnis=instanz+statistik_anfangWoche+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_differenz_seit_datum(id,id_ergebnis,wochenanfang);

       // seit Tagesanfang
       var tagesanfang=datum_tagesanfang(new Date());
       id_ergebnis=instanz+statistik_anfangTag+element;
       tolog(logging,id+" -> "+id_ergebnis);
       statistiken_differenz_seit_datum(id,id_ergebnis,tagesanfang);

    });

});

function statistiken_differenz_seit_datum (id,id_ergebnis,datum) {
    var ergebnis;
    var myQuery="SELECT round((max(val)-min(val)),0) as delta  \
                 FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                 AND (FROM_UNIXTIME(substring(ts,1,10)) >= '"+datum+"');";
         //log(myQuery);
    sendTo('sql.0', 'query', myQuery, function (result) {
        if (result.error) {
            //log(result.error);
        } else {
            ergebnis=String(result.result[0].delta);
            tolog(logging,id_ergebnis+" -> "+ergebnis);
            setState(id_ergebnis,ergebnis,true);
        }
    });
}

function statistiken_counter_7_tage (id,id_ergebnis) {

    var myQuery="SELECT CONCAT(MONTH(FROM_UNIXTIME(substring(ts,1,10))),'-', DAY(FROM_UNIXTIME(substring(ts,1,10)))) AS Tag, round(max(val)-min(val),0) AS Verbrauch \
                 FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                 AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 7 day) \
                 AND CURRENT_DATE() \
                 GROUP BY MONTH(FROM_UNIXTIME(substring(ts,1,10))),DAY(FROM_UNIXTIME(substring(ts,1,10))) \
                 ORDER BY MONTH(FROM_UNIXTIME(substring(ts,1,10))) , DAY(FROM_UNIXTIME(substring(ts,1,10))) ;";

    sendTo('sql.0', 'query', myQuery, function (result) {
        if (result.error) {
            //log(result.error);
        } else {
            // show result
            setState(id_ergebnis,JSON.stringify(result.result),true);
        }
    });
}

function statistiken_counter_12_monate (id,id_ergebnis) {

    var myQuery="SELECT CONCAT(YEAR(FROM_UNIXTIME(substring(ts,1,10))),'-', MONTH(FROM_UNIXTIME(substring(ts,1,10)))) AS Monat, round(max(val)-min(val),0) AS Verbrauch \
                 FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                 AND FROM_UNIXTIME(substring(ts,1,10)) BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 12 month) \
                 AND CURRENT_DATE() \
                 GROUP BY YEAR(FROM_UNIXTIME(substring(ts,1,10))),MONTH(FROM_UNIXTIME(substring(ts,1,10))) \
                 ORDER BY YEAR(FROM_UNIXTIME(substring(ts,1,10))) , MONTH(FROM_UNIXTIME(substring(ts,1,10))) ;";

    sendTo('sql.0', 'query', myQuery, function (result) {
        if (result.error) {
            //log(result.error);
        } else {
            // show result
            setState(id_ergebnis,JSON.stringify(result.result),true);
        }
    });
}

// allgemeines

function datum_jahresanfang (date) {
     return (date).getFullYear()+"-01-01";
}

function datum_monatsanfang (date) {
     var monat=1+date.getMonth();
     return (date).getFullYear()+"-"+monat+"-01";
}

function datum_tagesanfang (date) {
    var jahr=(date).getFullYear();
    var monat=1+date.getMonth();
    var tag = date.getDate();
    return jahr+"-"+monat+"-"+tag;
}

function datum_wochenanfang(date){
        var iDayOfWeek = date.getDay();
        var iDifference = date.getDate() - iDayOfWeek + (iDayOfWeek === 0 ?  -6:1);
        var woa=new Date(date.setDate(iDifference));
        return woa.toISOString().slice(0,10);
} 

function datum_gestern(datum){
   var yesterday = new Date(datum.valueOf() - 1000*60*60*24);
   return yesterday.toISOString().slice(0,10);
}

function kalenderwoche (d){
    d = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()));
    d.setUTCDate(d.getUTCDate() + 4 - (d.getUTCDay()||7));
    var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
     var weekNo = Math.ceil(( ( (d - yearStart) / 86400000) + 1)/7);
    return weekNo;
}

function tolog(logging,val) {
    if (logging===true) {
        log(val);
    }
}

AlCalzone

~~@ehome:~~

Verbesserungsvorschläge sind herzlich willkommen ! `
Wenn dir der Index bei den Arrays egal ist, kannst du die auch im Block erzeugen:

var counter = [
    'pv-eigenverbrauch',
    'pv-gesamtproduktion',
    'hausnaschluss-abgabe',
    'wallbox',
    'hausnaschluss-bezug',
    'waschmaschine',
    'trockner'
];

Elemente am Ende einfügen (ohne den Index zu kennen) geht per

counter.push('whatever');

Die Instanz würde ich dynamisch berechnen, eventuell führt jemand das Skript in Instanz 1, 2 oder höher aus:

var instanz="javascript." + instance + ".Statistiken.";

ehome

jo, danke.

Index bei den Arrays ist allerdings wg. der Benennung nicht egal.

AlCalzone

~~@ehome:~~

Index bei den Arrays ist allerdings wg. der Benennung nicht egal. `
Ok. Nur falls das nicht klar wurde: mit meinem Vorschlag erhältst du die gleichen Indizes (wird automatisch ab 0 durchnummeriert), sie stehen halt nicht explizit im Quelltext.

Wenn du aus irgendeinem Grund öfters von Hand nachschauen musst, dann bietet sich deine ausführlichere Variante an.

ehome

Danke danke - ist klar geworden. :lol:

thomassch

Hallo Ihr,

Danke für das tolle Script, leider verwende ich einen SQL Express, der die Umwandlung mittels

FROM_UNIXTIME nicht unterstützt!

script.js.Energie.Verbrauchsstatistik: RequestError: 'FROM_UNIXTIME' is not a recognized built-in function name.

Gibt es Alternativen dazu den Zeitstempel umzuwandlen?

Gruss

Thomas

Stabilostick

Wegen SQL Server Express und UNIXTIMESTAMP:

Da man sowieso dieses Konvertierung immer wieder braucht, ist mein Vorschlag, einfach dem SQL Server diese Funktion beizubringen. Dazu das SQL Server Managementstudio (ist ebenfalls kostenlos) öffen und zur iobroker-Datenbank oben links im Pulldownmenü wechseln. Dann den folgenden Text in das Abfragefenster einfügen und anschließend auf "Ausführen" klicken:

7446_2018-07-09_21_20_31-sqlquery1.sql_-_arbeitszimmer.png

Der Funktionscode ist:

CREATE FUNCTION [dbo].[fn_ConvertToDateTime] (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (dateadd(ms, @AdjustedLocalDatetime%(3600*24*1000), dateadd(day, @AdjustedLocalDatetime/(3600*24*1000), '1970-01-01 00:00:00.0')))
END

Das Ergebnis ist ein Datum mit dem Datentyp datetime. Der ist auf 10ms genau. Man sieht das daran, das die letzte Stelle entwerder 0, 3 oder 7 ist. Das reicht aber in der Regel für uns. Die Funktion nimmt alledings keine Rücksich auf Sommer-Winterzeit.

Testabfrage:

Select top 10 *,dbo.fn_ConvertToDateTime(ts) as Datum from ts_number

Das ergibt in der Ausgabe eine neue Spalte mit dem berechneten Datum aus den vorhandenen Timestamp-Werten.

Damit wird aus der ersten Abfrage

    var myQuery="SELECT round((max(val)-min(val)),0) as delta  \
                 FROM ts_number WHERE  id=(select id from datapoints where name='"+id+"') \
                 AND (dbo.fn_ConvertToDateTime(ts) >= '"+datum+"');";

und aus der zweitern Abfrage:

    var myQuery="SELECT CONCAT(MONTH(dbo.fn_ConvertToDateTime(ts)),'-', DAY(dbo.fn_ConvertToDateTime(ts))) AS Tag, round(max(val)-min(val),0) AS Verbrauch \
                 FROM ts_number WHERE  id=(select id from datapoints where name='"+id+"') \
                 AND dbo.fn_ConvertToDateTime(ts) BETWEEN dateadd(day,-7,GETDATE()) AND GETDATE() \
                 GROUP BY MONTH(dbo.fn_ConvertToDateTime(ts)),DAY(dbo.fn_ConvertToDateTime(ts)) \
                 ORDER BY MONTH(dbo.fn_ConvertToDateTime(ts)) , DAY(dbo.fn_ConvertToDateTime(ts)) ;";

Im Quelltext die Anweisungen so anpassen.

PS: Habe ich ohne Ausprobieren gemacht, sollte aber passen.

thomassch

Vielen Dank!!

Perfekt - genau das was ich gesucht habe. Die Funktion konnte ich einspielen und Sie geht.

Mal sehen ob die Abfragen damit klappen.

Danke

Thomas

piForscher

Ich möchte auch ein bisschen SQL-Statistik machen und die Skripte oben haben wir schon viel geholfen.

Jetzt eine weiterführende Frage:

Wie kann ich per SQL ermitteln, wie lange (pro Tag) ein boolean 'true' war?

Also wie lange z.B. ein Fenster offen war?

Vielen Dank!

piForscher

ple

Ehm, wie läuft ne dein Query ohne Wrapper? Kann ich auch nur den DAtenpunktpfad angeben oder muss ich die ID vom SQL Datenpunkt angeben.

Oder ist es einfacher den Wrapper zu installieren, nur den hab ich bisher noch nicht gefunden.

Gruß und Danke

Update:

Ich hab es soweit hinbekommen, dass ich Daten vom SQL bekomme. ich musste auf Mysql noch was in der Config ändern, damit die Abfrage lief.

wurde das Script eventuel noch erweitert? Von meinen Wärmezähler kommen leider nur Wh, kein kWh. Bei den Geräten müsste man noch einen Parameter übergeben, ob noch mal ein Teiler genommen wird oder nicht.

Gruß

NEWS

Vorschlag: Sammlung von SQL Statements (als Funktionen)

Support us

871
Online

31.7k
Users

79.9k
Topics

1.3m
Posts

NEWS

Vorschlag: Sammlung von SQL Statements (als Funktionen)

Support us

871Online

31.7kUsers

79.9kTopics

1.3mPosts

871
Online

31.7k
Users

79.9k
Topics

1.3m
Posts