SQL Abfrage universal

hanss

Hallo,
immer wieder benötige ich eine Abfrage der SQL Datenbank mit immer wieder den selben Problemen.
Mein Wunsch wäre eine Funktion, welche die bestimmte Tabelle auswertet, ungefähr so:

Tabelle = 'javascript.0.haus.Heizung.Kueche.Temperatur'
vonDatum = new Date(datum.valueOf() - 10006060*24)
bisDatum = new Date()

AskSQL(Tabelle,vonDatum,bisDatum) {

}

Die Funktion AskSQL sollte folgende Werte als Objekt zurückliefern, die zwischen vonDatum und bisDatum liegen:
callbackfertig true/false // Abfrage fertig, callback wurde aufgerufen
min // der minimal Wert im Zeitfenster mit Datum
max // der maximal Wert im Zeitfenster mit Datum
summe // die Summe aller Werte
durchschnitt // mittelwert aller Werte
anzahl // wieviele Werte im Zeitfenster

Das Problem:
https://forum.iobroker.net/topic/29712/gelöst-mysql-query-liefert-falsche-werte-bei-datum-xy
sollte auch berücksichtigt werden.

Bevor ich jetzt mit meinen sehr bescheidenen SQL und JS Kenntnissen zu basteln anfange, wäre es sehr schön,
wenn jemand schon so etwas hätte. Vielen Dank schon mal dafür.

paul53

@hanss sagte:

Funktion, welche die bestimmte Tabelle auswertet, ungefähr so:

sendTo('sql.0', 'getHistory', ...)

function askSQL(id, start, end, callback) {
    sendTo('sql.0', 'getHistory', {
        id: id,
        options: {
            start:      start,
            end:        end,
            count:      2000, // default: 500
            aggregate: 'none'
        }
    }, function (data) {
        let min = data.result[0].val;
        let max = min;
        let sum = 0;
        for (let i = 0; i < data.result.length; i++) {
            sum += data.result[i].val;
            if(data.result[i].val < min) min = data.result[i].val;
            if(data.result[i].val > max) max = data.result[i].val;
        }
        callback(min, max, sum / data.result.length, data.result.length);
    });
}

function logResult(min, max, mean, cnt) {
    log('Min: ' + min);
    log('Max: ' + max);
    log('Mittel: ' + mean);
    log('Anzahl: ' + cnt);
}

askSQL('javascript.0.haus.Heizung.Kueche.Temperatur', Date.now() - 86400000, Date.now(), logResult);

hanss

Hallo Paul53,
vielen Dank. Unglaublich, was es da für Möglichkeiten gibt.

Wäre es aber nicht schneller, die Datenbank nach den Ergebnissen zu fragen,

var myQuery="SELECT sum(val) AS Du_Temp  \
                 FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                 AND (DATE(FROM_UNIXTIME(SUBSTRING(ts,1,10) ))  = '"+datum+"');";)

anstatt jeden Wert einzeln zu bestimmen
(for i<data.result.length : min,max,sum), ich denke da z.B. an die Durchschnittstemperatur über einen Monat bzw. Jahr, da gibt es tausende von Werten.
Hast Du da schon einmal Vergleichsmessungen gemacht?

VG
Hans

paul53

@hanss sagte:

Hast Du da schon einmal Vergleichsmessungen gemacht?

Nein, mit Datenbanken kenne ich mich nicht aus.

fastfoot

@hanss sagte in SQL Abfrage universal:

Wäre es aber nicht schneller, die Datenbank nach den Ergebnissen zu fragen,

Ja ist es, Datenbanken sind für solche Berechnungen optimiert, weshalb es immer einer Berechnung auf dem Client vorzuziehen ist. Dazu kommt noch der Traffic jedes einzelnen Datensatzes, falls der DB-Server auf einem anderen Rechner liegt. Mir fehlt leider die Datenbasis um das zu testen, interessant wäre es schon zu wissen wieviel das genau ausmacht. Für das Beispiel mit einem Tag ist das aber sicherlich vernachlässigbar

hanss

@fastfoot
Ich habe beide Abfragen einmal verglichen (173 Datensätze):
Mit for-next Schleife: 32msec.
Mit SQL Abfrage: 13msec.
Ist schon ein ordentlicher Unterschied!

Weist Du, wie ich den Zeitstempel(ts) von dem minimal bzw. maximal Wert
bzw. dem ersten und letzten Datensatz
bekommen kann?

var myQuery="SELECT sum(val) AS sqlSU, avg(val) AS sqlAV,   \
                COUNT(*) AS sqlAZ, min(val) AS sqlMIN, max(val) AS sqlMAX \
                FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                AND FROM_UNIXTIME(substring(ts,1,10)) \
                AND  ts >= '"+ start +"' \
                AND  ts <= '"+ end +"'  "


         // log(myQuery);
    sendTo('sql.0', 'query', myQuery, function (data) {
                let min = data.result[0].sqlMIN
                var min_ts = data.result[0].ts
                let max = data.result[0].sqlMAX
                var max_ts = data.result[0].ts

fastfoot

@hanss sagte in SQL Abfrage universal:

AND FROM_UNIXTIME(substring(ts,1,10)) \

das macht ja gar keinen Sinn:-)

select max(ts) , min(ts) sollte es tun

Da du aber schon beim Optimieren bist, wie wäre es mit:

    var query = `
        SELECT a.id, 
            name,
            MIN(ts) AS Startzeit,
            MAX(ts) AS EndeZeit,
            COUNT(*) AS Anzahl,
            MIN(val) AS Minimum,
            MAX(val) AS Maximum,
            ROUND(AVG(val),2) AS Mittelwert,
            SUM(val) AS Summe
        FROM iobroker.datapoints a INNER JOIN iobroker.ts_number b ON a.id = b.id
        WHERE a.name LIKE "` + id + `"
          AND ts >= ` + vonDatum + `
          AND ts < ` + bisDatum + `;`;

Meiner Meinung nach müsste der Inner Join auch noch etwas Performane bringen

hanss

@fastfoot
AND FROM_UNIXTIME(substring(ts,1,10)) \ da habe ich etwas übersehen, Danke.

var myQuery="SELECT sum(val) AS sqlSU, avg(val) AS sqlAV,   \
                COUNT(*) AS sqlAZ, min(val) AS sqlMIN, max(val) AS sqlMAX, \
                max(ts) AS sqlMAXts, min(ts) AS sqlMINts    \
                FROM iobroker.ts_number WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                AND  ts >= '"+ start +"' \
                AND  ts <= '"+ end +"' ORDER BY ts ASC "

bzw.

for (var i = 0; i < data.result.length; i++) {
            
            if (i==0){
                first = data.result[i].ts
            }
            if (i==(data.result.length-1)){
                last = data.result[i].ts
            }
           
            sum += data.result[i].val;
            if(data.result[i].val < min) {min = data.result[i].val; min_ts = data.result[i].ts;}
            if(data.result[i].val > max) {max = data.result[i].val; max_ts = data.result[i].ts;}
        }

Die Werte für min und max stimmen zwar überein, aber der TimeStamp (ts) ist bei min u. max
ein anderer.
Siehst Du da einen Fehler?

Den Inner Join muß ich mir einmal genauer ansehen, ist jedenfalls sehr interessant.

fastfoot

@hanss sagte in SQL Abfrage universal:

Die Werte für min und max stimmen zwar überein, aber der TimeStamp (ts) ist bei min u. max
ein anderer.
Siehst Du da einen Fehler?

Einen Fehler direkt nicht, jedoch gibt es bestimmt mehrere min und max werte, die gleich sind, hier wird dann nur der erste gefundene genommen. Evtl hilft es mit <= bzw >= zu vergleichen, da du ja vorher sortiert hattest

hanss

@fastfoot
"jedoch gibt es bestimmt mehrere min und max werte"
Nein, jeweils nur einen, gerade überprüft.

Weist Du, wie ich den Zeitstempel(ts) von dem ersten und letzten Datensatz
bekommen kann - das sind max(ts) u. min(ts)

Jetzt fehlt mir nur noch der TimeStamp von min u. max.

hanss

Vielleicht kann es ja jemand brauchen:
Ich habe die universelle SQL Abfrage fertig, einmal über SQL Funktionen, einmal mit getHistory.
Die Version mit den SQL Funktionen ist besonders bei vielen Datensätzen bedeutend schneller.

Möglicherweise hat ja ein mySQL Spezialist eine Idee, wie man die 4 SELECT Anweisungen reduzieren könnte,
ist so nicht gerade elegant gelöst, aber funktioniert.
Danke schon einmal an paul53 und fastfoot.

//------------------------------------  mit SQL Abfrage ------------------------------------------------
function askSQL (id, start, end, callback) {   
    var StartTime = millis()
    var myQuery="select m.*, maxt.val as mxval, maxt.ts as mxts, mint.val as mival, mint.ts as mits \
                from \
                (SELECT ROUND(sum(val),2) AS sqlSU, ROUND(avg(val),2) AS sqlAV,   \
                COUNT(*) AS sqlAZ, min(val) AS sqlMIN, max(val) AS sqlMAX, \
                max(ts) AS sqlLast,     \
                min(ts)  AS sqlFirst    \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                AND  ts >= '"+ start +"' \
                AND  ts <= '"+ end + "') m, \
                \
                (SELECT val, ts \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                AND  ts >= '"+ start +"' \
                AND  ts <= '"+ end + "' order by val desc limit 0,1) maxt,  \
                \
                (SELECT val, ts \
                FROM iobroker.ts_number \
                WHERE  id=(select id from iobroker.datapoints where name='"+id+"') \
                AND  ts >= '"+ start +"' \
                AND  ts <= '"+ end + "' order by val asc limit 0,1) mint"

         // log(myQuery);
    sendTo('sql.0', 'query', myQuery, function (data) {
                var min = data.result[0].mival
                var min_ts = data.result[0].mits            
                var max = data.result[0].mxval
                var max_ts = data.result[0].mxts          
                var sum = data.result[0].sqlSU
                var mean = data.result[0].sqlAV
                var anzahl = data.result[0].sqlAZ
                var first = data.result[0].sqlFirst  // first value            
                var last = data.result[0].sqlLast   // last value             

        callback(min, min_ts, max, max_ts, mean, sum, anzahl,first,last, StartTime);
    })

} // askSQL

//------------------------------------  mit getHistory ------------------------------------------------
function askSQLgh(id, start, end, callback) {
    var StartTime = millis()
    sendTo('sql.0', 'getHistory', {
        id: id,
        options: {
            start:      start,                       
            end:        end,
            aggregate: 'none'
        }
    }, function (data) {
        var min = data.result[0].val;
        var min_ts = data.result[0].ts
        var max = min;
        var max_ts = data.result[0].ts
        var sum = 0;
        var mean = 0;
        var anzahl = data.result.length  // ACHTUNG: liefert nur 500 Ergebnisse
        var first = 0  // first value
        var last = 0   // last value
        for (var i = 0; i < anzahl; i++) {
            
            if (i==0){
                first = data.result[i].ts
            }
            if (i==(data.result.length-1)){
                last = data.result[i].ts
            }
           
            sum += data.result[i].val;
            if(data.result[i].val < min) {min = data.result[i].val; min_ts = data.result[i].ts; }
            if(data.result[i].val > max) {max = data.result[i].val; max_ts = data.result[i].ts; }
        }
        mean = sum / anzahl
        callback(min, min_ts, max, max_ts, mean, sum, anzahl,first,last, StartTime);
        
    });
}  // askSQLgh

function logResult(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    var EndTime = millis()
    log('Min: ' + min + ' am ' + DatTimestr( new Date(min_ts) ));
    log('Max: ' + max + ' am ' + DatTimestr( new Date(max_ts) ));
    log('Mittel: ' + mean);
    log('Summe: ' + sum);
    log('Anzahl: ' + cnt);
    log('Erster Wert: ' + DatTimestr( new Date(first) ));
    log('Letzter Wert: ' + DatTimestr( new Date(last) ));
    log('Ausführungszeit: '+  (EndTime - StartTime ) +' msec.' )
}

/*----------------    Hauptprogramm   -------------------------------------------------------------------------*/
var DatumHeuteUnix =  Date.now()                          // = new Date().valueOf()
var vonDatum = new Date(DatumHeuteUnix - 1000*3600*24)    // gestern
    vonDatum = new Date(DatstrSQL(vonDatum) + ' 00:00:00' )
 //   vonDatum = new Date('2020-07-21' + ' 00:00:00' )
var vonDatumUnix = vonDatum.getTime() 

var bisDatum =  new Date(vonDatumUnix)
    bisDatum =  new Date(DatstrSQL(bisDatum) + ' 23:59:59' )          // oder '2020-07-22 00:00:00'
 //   bisDatum = new Date('2020-07-21' + ' 23:59:59' )
var bisDatumUnix = bisDatum.getTime()

var SQLTable = 'mqtt.0.haus.keller.HumiFan.Sensor.Aussen.Temperatur'

log('vonDatum ---->'+ new Date(vonDatumUnix) + '<----');
log('bisDatum ---->'+  new Date(bisDatumUnix) + '<----');

// askSQLgh(SQLTable,vonDatumUnix,bisDatumUnix, logResult);       // getHistory Version
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, logResult);            // SQL Version

hanss

Problem Asynchron:

Mit der oben vorgestellten Funktion möchte ich nun gerne verschiedene Berechnungen durchführen,
scheitere aber immer an dem synchronen Ablauf, bzw. mit callback Funktionen wird das Programm
sehr unübersichtlich.
Wie kann man das übersichtlicher machen:

//-------------------Hauptprogramm:
let SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.Regenmesser.menge'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_Niederschlag);

SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.SprinklerMenge'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_SprinklerIst);  

SQLTable = 'mqtt.0.haus.outdoor.MaxGarage.RegenMorgen'
askSQL(SQLTable,vonDatumUnix,bisDatumUnix, askSQL_RegenM;   

// Hier soll erst weitergerechnet werden,
// wenn alle SQL Abfragen fertig sind

var x = Niederschlag-SprinklerIst+RegenM
log("Differenz: "+x)
//-------------------Hauptprogramm Ende



function askSQL_Niederschlag(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    Niederschlag = sum/1000
}

function askSQL_SprinklerIst(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    SprinklerIst = sum*1000     // in Ltr.
}
function askSQL_RegenM(min, min_ts, max, max_ts, mean, sum, cnt,first,last, StartTime) {
    RegenM = sum    
}

J.A.R.V.I.S.

@hanss das Stichwort lautet async/await und Promise.

function askSQLgh(id, start, end) {

    const startTime = millis()

    return new Promise(resolve => {
		sendTo(
			'sql.0',
			'getHistory',
			{
				id: id,
				options:
				{
					start: start,
					end: end,
					aggregate: 'none'
				}
			},
			data => {

				let min = data.result[0].val;
				const min_ts = data.result[0].ts
				let max = min;
				const max_ts = data.result[0].ts
				let sum = 0;
				let mean = 0;
				const anzahl = data.result.length  // ACHTUNG: liefert nur 500 Ergebnisse
				let first = 0  // first value
				let last = 0   // last value

				for (let i = 0; i < anzahl; i++) {
					if (i==0){
						first = data.result[i].ts
					}

					if (i==(data.result.length-1)){
						last = data.result[i].ts
					}

					sum += data.result[i].val;

					if(data.result[i].val < min) {
						min = data.result[i].val; min_ts = data.result[i].ts;
					}

					if(data.result[i].val > max) {
						max = data.result[i].val; max_ts = data.result[i].ts;
					}
				}

				mean = sum / anzahl

				resolve(
					{
						min,
						min_ts,
						max,
						max_ts,
						mean,
						sum,
						anzahl,
						first,
						last,
						startTime
					}
				);
			}
		);
	});
}

und der Aufruf geschieht dann wie folgt:

const sqlResult = await askSQLgh('deineID', start, end);

Der Aufruf muss aber in einer Async Funktion stehen.

async function test() {
	const sqlResult = await askSQLgh('deineID', start, end);

       //hier kannst du dann mit dem zurückgegebenen Objekt weiterarbeiten.
}

ostseeskipper

@hanss
Hallo Zusammen,

ich versuche gerade das Script zum Laufen zu überreden.
allerdings kann es im Hauptprogramm mit DatstrSQL nichts anfangen.
vonDatum = new Date(DatstrSQL(vonDatum) + ' 00:00:00' )

muss irgendwie noch ein NPM Modul in den JS Adapter geladen werden?

var DatumHeuteUnix =  Date.now()                          // = new Date().valueOf()
var vonDatum = new Date(DatumHeuteUnix - 1000*3600*24)    // gestern
    vonDatum = new Date(DatstrSQL(vonDatum) + ' 00:00:00' )
 //   vonDatum = new Date('2020-07-21' + ' 00:00:00' )
var vonDatumUnix = vonDatum.getTime() 
var bisDatum =  new Date(vonDatumUnix)
    bisDatum =  new Date(DatstrSQL(bisDatum) + ' 23:59:59' )          // oder '2020-07-22 00:00:00'

hanss

@ostseeskipper

// liefert Datum im mySQL Format: 'JJJJ-MM-TT'
function DatstrSQL(utx){ 
    let ut = new Date(utx)
    let jahr = ut.getFullYear();
    let monat = str2(ut.getMonth()+1);
    let tag = str2(ut.getDate());
    return jahr + '-' + monat + '-' + tag;
}

ostseeskipper

@hanss
Hallo Hanss,

nun meckert es wegen "str2"
Ist das noch eine weitere eigene Funktion ?
DatTimestr wird aktuell auch noch als Hinweis bei mir im Editor unterstrichen.

Hast du dir zufällig ne eigene Funktionsbibliothek zusammengestellt?
Kenn das so von SQL Servern und Office AddIns. Ich beschäftige mich erst seit Feb mit JS und ioBroker daher bin ich mit den Bibliotheken noch nicht wirklich vertraut.
Gibt es die Möglichkeit, Funktionen die man in mehreren Scripten benötigt, auszulagern oder sich seine eigene Funktionsbibliothek zu schaffen?

hanss

@ostseeskipper

// new Date(unixtime in ms.) in "TT.MM.JJJJ SS:MM:SS"
function DatTimestr(utx){
    return Datstr(utx) + ' '+ Uhrstr(utx)
}

function str2(x){
   if(x.toString().length == 1) x = '0'+x
return x
}

https://forum.iobroker.net/topic/3248/funktion-scriptübergreifend-aufrufen

ostseeskipper

@hanss
Hallo hanss.

Danke für den Link, das sollte helfen

In der Funktion DatTimestr sind nun wiederum noch Datstr und Uhrstr als eigene Funktionen drin.
Magst Du die bitte auch noch schicken.

millis für die Ausführungszeit hab ich mir schon einfach mit

    var StartTime = new Date().getTime();

geholt.

Noch eine Frage da ich einfach nur neugierig bin.
Dauert der Programmlauf durch die Abarbeitung der eigenen Funktion millis nicht länger ?

hanss

@ostseeskipper sagte in SQL Abfrage universal:

Datstr

// new Date(unixtime in ms.) in "TT.MM.JJJJ"
function Datstr(utx){
    let ut = new Date(utx)
    let jahr = ut.getFullYear();
    let monat = str2(ut.getMonth()+1);
    let tag = str2(ut.getDate());
    return tag+"."+monat+"."+jahr
}

// new Date(unixtime in ms.) in "SS:MM:SS"
function Uhrstr(utx){
    let ut = new Date(utx)  
    let std = str2(ut.getHours());
    let min = str2(ut.getMinutes());
    let sec = str2(ut.getSeconds());
    return std+":"+min+":"+sec
}

// gibt millisec. zurück wie im Arduino
function millis(){
    let EndTime = new Date()
    return EndTime.getMilliseconds()
}

ostseeskipper

@hanss
Super nun ist alles zusammen und läuft.
Hab ein paar meiner Datenpunkte ausprobiert und ich bekomme die Werte. Braucht 8-9 millisekunden um aus ca 700 Werten das Min, Max, Mittelwert, Summe, Anzahl, Wann erster und wann letzer Wert zu ermitteln.
Besten Dank

NEWS

SQL Abfrage universal

Support us

1.5k
Online

30.4k
Users

76.6k
Topics

1.2m
Posts

NEWS

SQL Abfrage universal

Support us

1.5kOnline

30.4kUsers

76.6kTopics

1.2mPosts

1.5k
Online

30.4k
Users

76.6k
Topics

1.2m
Posts