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.
    • F
      fastfoot @hanss last edited by

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

        F 1 Reply Last reply Reply Quote 0
        • F
          fastfoot @hanss last edited by

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

            1 Reply Last reply Reply Quote 0
            • 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

                                  765
                                  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