Vorschlag: Sammlung von SQL Statements (als Funktionen)

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ß

Community Forum

NEWS

Vorschlag: Sammlung von SQL Statements (als Funktionen)

Support us

376

32.4k

81.4k

1.3m