Navigation

    Logo
    • Register
    • Login
    • Search
    • Recent
    • Tags
    • Unread
    • Categories
    • Unreplied
    • Popular
    • GitHub
    • Docu
    • Hilfe
    1. Home
    2. Deutsch
    3. Skripten / Logik
    4. JavaScript
    5. SQL Abfrage universal

    NEWS

    • Neuer Blog: Fotos und Eindrücke aus Solingen

    • ioBroker@Smart Living Forum Solingen, 14.06. - Agenda added

    • ioBroker goes Matter ... Matter Adapter in Stable

    SQL Abfrage universal

    This topic has been deleted. Only users with topic management privileges can see it.
    • hanss
      hanss last edited by

      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 1 Reply Last reply Reply Quote 0
      • hanss
        hanss @hanss last edited by

        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. O 2 Replies Last reply Reply Quote 0
        • J.A.R.V.I.S.
          J.A.R.V.I.S. Developer @hanss last edited by

          @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.
          }
          
          1 Reply Last reply Reply Quote 0
          • O
            ostseeskipper @hanss last edited by

            @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 1 Reply Last reply Reply Quote 0
            • hanss
              hanss @ostseeskipper last edited by 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;
              }
              
              O 1 Reply Last reply Reply Quote 0
              • O
                ostseeskipper @hanss last edited by 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 1 Reply Last reply Reply Quote 0
                • hanss
                  hanss @ostseeskipper last edited by

                  @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

                  O 1 Reply Last reply Reply Quote 0
                  • O
                    ostseeskipper @hanss last edited by

                    @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 1 Reply Last reply Reply Quote 0
                    • hanss
                      hanss @ostseeskipper last edited by

                      @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()
                      }
                      
                      
                      
                      O 1 Reply Last reply Reply Quote 1
                      • O
                        ostseeskipper @hanss last edited by

                        @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👍

                        1 Reply Last reply Reply Quote 0
                        • R
                          reda last edited by

                          Hallo zusammen und danke für die Vorarbeiten.
                          Ich hatte am Ende doch noch ein paar Schwierigkeiten das ganze zusammenzubauen, daher hier nochmal meine Komplettlösung, evtl. hilft das auch nochmal jemandem weiter.

                          //------------------------------------ generische SQL Abfrage ------------------------------------------------
                          function getFromSQL(id, start, end) {
                          
                              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";
                          
                              return new Promise((resolve, reject) => {
                                  sendTo('sql.0', 'query', myQuery, result => {
                                      if (result.error) { reject(result.error); }
                                      else { resolve(result.result) }
                                  });
                              });
                          }
                          
                          async function GetTestValues(){
                          var date_end = Date.now();
                          var date_start = date_end - 1000 * 3600 * 24; // gestern
                          var result = await getFromSQL('sql_name_evtl_auch_alias', date_start, date_end);
                          console.log(result[0]);
                          };
                          
                          1 Reply Last reply Reply Quote 0
                          • First post
                            Last post

                          Support us

                          ioBroker
                          Community Adapters
                          Donate

                          798
                          Online

                          31.8k
                          Users

                          80.0k
                          Topics

                          1.3m
                          Posts

                          6
                          21
                          2554
                          Loading More Posts
                          • Oldest to Newest
                          • Newest to Oldest
                          • Most Votes
                          Reply
                          • Reply as topic
                          Log in to reply
                          Community
                          Impressum | Datenschutz-Bestimmungen | Nutzungsbedingungen
                          The ioBroker Community 2014-2023
                          logo