Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

[ch.eick]

Es gibt ein Modul DBIx::ProcedureCall -> https://metacpan.org/pod/DBIx::ProcedureCall

Kann den Use Case jetzt nicht übersehen, aber in der Erläuterung zum Modul steht:

Wie gesagt, ein call einer Procedur geht auch jetzt schon, nur die Rückmeldung wird nicht in einem reading geliefert.
Ich sehe aber "SqlResultRow_1 5839" was ja auch schon mal was ist

[DS_Starter]

Probiere mal die V 8.50.8 aus meinem contrib.

[ch.eick]

Probiere mal die V 8.50.8 aus meinem contrib.

Puh,
zum Glück hatte ich schon das Datenaufkommen reduziert

Code Auswählen Erweitern


Internals:
   CFGFN     
   DATABASE   fhem
   DEF        LogDB
   FUUID      63a1d5db-f33f-61a8-a5da-bba48bb7336b3fec
   FVERSION   93_DbRep.pm:v1.1.1-s26865/2022-12-21
   LASTCMD    sqlCmd call dwd_load(curdate());
   MODEL      Client
   NAME       LogDBRep_PV_prognose
   NOTIFYDEV  global,LogDBRep_PV_prognose
   NR         741431
   NTFY_ORDER 50-LogDBRep_PV_prognose
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       1
   eventCount 24
   HELPER:
     DBLOGDEVICE LogDB
     GRANTS     ALL PRIVILEGES,USAGE
     IDRETRIES  2
     MINTS      2019-04-03 00:23:42
     PACKAGE    main
     VERSION    8.50.5
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      0
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   Helper:
     DBLOG:
       state:
         LogDB:
           TIME       1671550427.75645
           VALUE      initialized
   OLDREADINGS:
   READINGS:
     2022-12-21 13:23:08   SqlResultRow_0001 MYDATETIME|MYTIMESTAMP|YEAR|MONTH|DAY|HOUR|TTT|DD|VV|N|NEFF|R101|RRS1C|SUND|RAD1H|SUND1|SUNAZ|SUNALT|YIELD|FORECAST|FORECAST1|FORECAST2|FORECAST3|FORECAST4|FORECAST5
     2022-12-21 13:23:08   SqlResultRow_0002 2021-11-21 06:00:00|1637474400|2021|11|21|6|0|0|0|0|0|0|0|0|0|0|101.7|-16.3|0|0|0|0|0|0|0
     2022-12-21 13:23:08   SqlResultRow_0003 2021-11-21 07:00:00|1637478000|2021|11|21|7|0|0|0|0|0|0|0|0|0|0|112.6|-7.1|0|0|0|0|0|0|0
< snip >
     2022-12-21 13:23:08   SqlResultRow_1449 2022-12-21 13:00:00|1671627600|2022|12|21|13|9.2|204|10400|92|92|204|0|0|270|60|190.4|16.1|45|0|0|0|0|0|0
     2022-12-21 13:23:08   sqlCmd          call dwd_load(curdate());
     2022-12-21 13:23:08   sqlResultNumRows 1448
     2022-12-21 13:23:08   state           done
Attributes:
   DbLogExclude .*
   allowDeletion 0
   comment    Version 2022.12.21 12:00
   room       System
   verbose    0


[DS_Starter]

  ... sonst wären es eben 5839 Zeilen

brauchbar ?

[ch.eick]

... sonst wären es eben 5839 Zeilen

Musst Du eigentlich nicht arbeiten, weil Du meistens so schnell bist? 
Das würde für mich so passen, es führen ja viele Wege nach Rom...

[DS_Starter]

Mittagspause ... und es war nur eine kleine Änderung.
Manchmal geht das 

Probiere noch ein bisschen. Heute Abend würde ich die V ins Repo laden wenn es soweit passt.

[ch.eick]

Mittagspause ... und es war nur eine kleine Änderung.
Manchmal geht das 

Probiere noch ein bisschen. Heute Abend würde ich die V ins Repo laden wenn es soweit passt.

Ich habe noch auf die Schnelle eine Bremse für das Anzeigen eingebaut [show|none] als Parameter

Code Auswählen Erweitern


call dwd_load(curdate(),'none');

In der Procedure kann man ja auch im SQL mit IF THEN ELSE arbeiten.

[DS_Starter]

Ich glaube ich muß mich mal wieder mit DbRep befassen wenn ich mit der DbLog V 5 durch bin (verfolgst du wahrscheinlich auch).

[ch.eick]

Ich glaube ich muß mich mal wieder mit DbRep befassen wenn ich mit der DbLog V 5 durch bin (verfolgst du wahrscheinlich auch).

Na klar, ab jetzt

[DS_Starter]

Ich habe im Wiki ein Beispiel zur Ermittlung des monatlichen und durchschnittlichen Gasverbrauches mit Vaillant und eBusd MQTT mit einem einzelnen DbRep eingestellt. Interessant für User ist vllt. in diesem Beispiel die Verwendung der Attribute userExitFn und stateFormat.

Schöne Feiertage @all.

[alkazaa]

Hier ist ja in letzter Zeit einiges an recht komplexen SQL queries veröffentlicht worden. Da traue ich mich, obwohl immer noch im SQL-Anfänger-Zustand, auch mal etwas beizutragen.

Hintergrund: Im fronthem/SmartVISU Unterforum wurde kürzlich diskutiert, wie am besten gewichtete Mittelwerte von Zeitreihen in das fronthem Interface 99_fronthemUtils.pm integriert werden können. Bisher war dort die Funktion 'get ... webchart ...' aus dem DbLog Modul verwendet worden, die aber nur ungewichtet mittelt. Ein gewichtetes Mitteln bietet zwar das DbRep Modul, aber ich habe keine Ahnung, wie die (non-Blocking) Funktion 'set ... averageValue...' in z.B. 99_fronthemUtils.pm eingebunden werden kann.
Außerdem fiel mir auf, dass diese Funktion so implementiert ist, dass bei einer Einteilung eines längeren Daten-Zeitraums in einzelne Mittelungsintervalle die jeweils letzten Datenpunkte eines Mittelungsintervalls verworfen werden (was kein großes Problem bei vielen Daten pro Intervall ist, aber bei geringer Datendichte doch auffallen könnte).

Aus diesen Gründen bin ich dann bei einer anderen Lösung gelandet, bei der das Mitteln ausschließlich in SQL stattfindet und die mit 'sqlCmdBlocking' ausgeführt wird. Es kann über Minuten, Viertelstunden, Stunden, Vierteltage, Tage, Wochen, Monate, Vierteljahre oder Jahre gemittelt werden:

Code Auswählen Erweitern

--### Mittelwertbildung, Variante 3
--### Bei dieser Variante wird beim gewichteten Mitteln der Wert am Ende des Mittelungsintervalls auf beide Intervall verteilt.
--### Der Wert wird gemäß dem zeitlichen Anteil an den beiden Intervallen aufgeteilt.
--### Mögliche Werte für @sortformat: minute, qhour, hour, qday, day, week, month, qyear, year
SET @sortformat="hour", @weighted="yes", @device = "E_Zaehler", @reading = "power", @start = "2022-10-29 00:00:00", @end = "2022-10-29 05:00:00";
SELECT
   avgtim,
   CASE @weighted
      WHEN "yes" THEN CAST((SUM(val * weight)/SUM(weight)) AS DECIMAL(12,4))
      ELSE CAST(sum(val) / count(val) AS DECIMAL(12,4))
   END AS avrg
FROM (
   SELECT 
      tim,
      avgtim,
      CASE @sortformat WHEN "week"
         THEN date_format(tim, "%Y-%u 00:00:00")
         ELSE avgtim
      END AS grouptim,
      CASE
         WHEN avgtim!=preavgtim THEN to_seconds(nexttim)-to_seconds(avgtim)
         WHEN avgtim!=nextavgtim THEN to_seconds(nextavgtim)-to_seconds(tim)
         ELSE to_seconds(nexttim)-to_seconds(tim)
      END AS weight,   
      CASE
         WHEN avgtim!=preavgtim THEN
            CASE @weighted WHEN "yes"
               THEN CASE WHEN avgtim!=nextavgtim
                     THEN (preval*(to_seconds(tim)-to_seconds(avgtim)) + val*(to_seconds(nextavgtim)-to_seconds(tim)))/(to_seconds(nextavgtim)-to_seconds(avgtim))
                     ELSE (preval*(to_seconds(tim)-to_seconds(avgtim)) + val*(to_seconds(nexttim)-to_seconds(tim)))/(to_seconds(nexttim)-to_seconds(avgtim))
                  END
               ELSE val
            END
         ELSE val
      END AS val
   FROM (
      SELECT
         tim,
         nexttim,
         val,
         preval,
         avgtim,
         LAG(avgtim) OVER (ORDER BY tim) AS preavgtim,
         LEAD(avgtim) OVER (ORDER BY tim) AS nextavgtim
      FROM (
         SELECT
            timestamp AS tim,
            LEAD(timestamp) OVER (ORDER BY timestamp) AS nexttim,
            value AS val,
            LAG(value) OVER (ORDER BY timestamp) AS preval,
         CASE @sortformat
            WHEN "minute" THEN date_format(timestamp, "%Y-%m-%d %H:%i:00")
            WHEN "qhour"  THEN concat(date_format(timestamp, "%Y-%m-%d %H:"),LPAD(15*(date_format(timestamp,"%i") div 15),2,"0"),":00")
            WHEN "hour"   THEN date_format(timestamp, "%Y-%m-%d %H:00:00")
            WHEN "qday"   THEN concat(date_format(timestamp, "%Y-%m-%d "),LPAD(6*(date_format(timestamp, "%H") div 6),2,"0"),":00:00")
            WHEN "day"    THEN date_format(timestamp, "%Y-%m-%d 00:00:00")
            WHEN "week"   THEN date_format(timestamp, "%Y-%m-%d 00:00:00")
            WHEN "month"  THEN date_format(timestamp, "%Y-%m-01 00:00:00")
            WHEN "qyear"  THEN concat(date_format(timestamp, "%Y-"),LPAD(3*(date_format(timestamp, "%m") div 3),2,"0"),"-01 00:00:00")
         ELSE date_format(timestamp, "%Y-01-01 00:00:00")
         END AS avgtim         
         FROM
            history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading ORDER BY timestamp
      ) select3
   ) select2
) select1 GROUP BY grouptim

Vielleicht ist dieses SQL-Script ja für den einen oder anderen von Nutzen.

Oder auch dieses (Aufteilung eines Gesamtzeitraums in 'count' Intervalle ohne Rücksicht auf bestimmte Zeitgrenzen):

Code Auswählen Erweitern

--#########################
--###
--### Mittelwertbildung bei Unterteilung des Zeitraums @start...@end in '@count' Teilintervalle:
--###
--#########################
SET @weighted="yes", @device = "E_Zaehler", @reading = "power", @start = "2022-10-29 00:00:00", @end = "2022-10-29 05:00:00", @count = 5;
SELECT
   tim,
   CASE
      WHEN @weighted="yes" THEN
         CAST(sum(val * (to_seconds(nexttim)-to_seconds(tim))) / sum((to_seconds(nexttim)-to_seconds(tim))) AS DECIMAL(12,4))
      ELSE CAST(sum(val) / count(val) AS DECIMAL(12,4))
   END AS avrg
FROM (
   SELECT
      timestamp as tim,
      value as val,
      LEAD(timestamp) over (order by timestamp) nexttim,
      truncate(@count * (to_seconds(timestamp)-to_seconds(@start)) / (to_seconds(@end)-to_seconds(@start)),0)/@count as avgtim
   FROM
      history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading
) select1 group by avgtim


-Franz

[DS_Starter]

Hallo Franz,

ich finde deinen Beitrag äußerst hilfreich als Anregung und Nachnutzung.
Es ist immer schade wenn dieses Wissen im Forum Universum versiegt.

Könntest du einen Beitrag in diesem Wiki-Artikel hinterlegen ?
Das wäre super, ich habe ihn gerade dafür angelegt.

[alkazaa]

Könntest du einen Beitrag in diesem Wiki-Artikel hinterlegen ?

Ist geschehen, siehe hier

[ch.eick]

Hallo zusammen,
ich arbeite gerade an der Reformatierung von SQL Statements und hätte da mal was für Euch zum ausprobieren.

Hier ist das verwendete HTTPMOD im RAW

Code Auswählen Erweitern


defmod SQL_Format HTTPMOD none 0
attr SQL_Format DbLogExclude .*
attr SQL_Format comment Version 2023.01.02 12:00
attr SQL_Format enableCookies 1
attr SQL_Format room System
attr SQL_Format set01Data reindent=1&sql=$val
attr SQL_Format set01ExtractAllJSON 1
attr SQL_Format set01Method POST
attr SQL_Format set01Name 01_SQL_Format
attr SQL_Format set01ParseResponse 1
attr SQL_Format set01TextArg 1
attr SQL_Format set01URL https://sqlformat.org/api/v1/format
attr SQL_Format showBody 0
attr SQL_Format showError 1
attr SQL_Format verbose 0

Aufruf in der FHEM Kommandozeile mit einem Beispiel, oder mit dem SQL Kommando innerhalb des Devices in der set Zeile.

Code Auswählen Erweitern


set SQL_Format 01_SQL_Format SELECT * FROM (SELECT h.TIMESTAMP, h.READING, IF (h.READING LIKE '%Rate%' OR h.READING LIKE '%Autarky%', h.VALUE, cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-31'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) x1 USING(TIMESTAMP,READING)) WR_1_API UNION ALL SELECT h.TIMESTAMP, concat('WB_0_', h.READING) AS READING, h.VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB_0' AND READING LIKE 'lp_%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) WB_1 USING(TIMESTAMP,READING) UNION ALL SELECT h.TIMESTAMP, concat('WB_1_', h.READING) AS READING, h.VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP, READING FROM history WHERE DEVICE = 'WB_1' AND READING LIKE 'lp_%_kWhCounter_Year' AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d') AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d') GROUP BY READING) WB_1 USING(TIMESTAMP,READING)

Das Ergebnis sieht dann so aus

Code Auswählen Erweitern


SELECT *
FROM
  (SELECT h.TIMESTAMP,
          h.READING,
          IF (h.READING LIKE '%Rate%'
              OR h.READING LIKE '%Autarky%',
                 h.VALUE,
                 cast(h.VALUE/1000 AS decimal(6))) AS VALUE
   FROM history h
   INNER JOIN
     (SELECT max(TIMESTAMP) AS TIMESTAMP,
             READING
      FROM history
      WHERE §device§
        AND §reading§
        AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-31'), '%Y-%m-%d')
        AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d')
      GROUP BY READING) x1 USING(TIMESTAMP,READING)) WR_1_API
UNION ALL
SELECT h.TIMESTAMP,
       concat('WB_0_', h.READING) AS READING,
       h.VALUE
FROM history h
INNER JOIN
  (SELECT max(TIMESTAMP) AS TIMESTAMP,
          READING
   FROM history
   WHERE DEVICE = 'WB_0'
     AND READING LIKE 'lp_%_kWhCounter_Year'
     AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d')
     AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d')
   GROUP BY READING) WB_1 USING(TIMESTAMP,READING)
UNION ALL
SELECT h.TIMESTAMP,
       concat('WB_1_', h.READING) AS READING,
       h.VALUE
FROM history h
INNER JOIN
  (SELECT max(TIMESTAMP) AS TIMESTAMP,
          READING
   FROM history
   WHERE DEVICE = 'WB_1'
     AND READING LIKE 'lp_%_kWhCounter_Year'
     AND TIMESTAMP > STR_TO_DATE(CONCAT(YEAR(CURDATE())-1, '-12-01'), '%Y-%m-%d')
     AND TIMESTAMP < STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'), '%Y-%m-%d')
   GROUP BY READING) WB_1 USING(TIMESTAMP,READING)


VG  Christian

[DS_Starter]

Funktioniert