Eine Frage an die SQL-Experten: nested SELECT [GELÖST: war timediff-Problem]

Begonnen von alkazaa, 07 Dezember 2022, 07:14:41

Vorheriges Thema - Nächstes Thema

alkazaa

Guten Morgen!

Als (blutiger) SQL-Anfänger habe ich mir folgenden Code gebastelt, mit dem aus einer DbLog Datenbank zeitgewichtete Mittelwerte aus einem Zeitraum @start bis @end erzeugt werden. Der Zeitraum wird dabei in @count Intervalle eingeteilt.

Der Code funktioniert und tut was er soll, aber nur, wenn der Zeitraum @start - @end nicht zur groß wird. Dann wird im Ergebnis nur ein Teil, nämlich der Anfang des Gesamtzeitraums ausgewertet. Maximal werden etwa 18000 Datensätze problemlos verarbeitet.

Ich vermute, dass es sich um eine Memory-Problem des DB-Servers handelt (MariaDB auf einer Synology DS216j).

Vielleicht weiß einer der SQL-Gurus die vielleicht hier mitlesen eine intelligentere Lösung. Hier mein (teilweise kommentierter) SQL Code:

SET @device = "E_Zaehler",
@reading = "power",
@start = "2022-12-01 19:01:06",
@end = "2022-12-06 18:01:06",
@count = 10;
SELECT
timestamp,
-- WeightedAverage is the quotient of timevalue and timelength:
timevalue / timelength as WeightedAverage
FROM (
SELECT
timestamp,
-- timelength is the sum of times in a timegroup interval:
sum(time_to_sec(timeDIFF(nexttimestamp, timestamp))) as timelength,
-- timevalue is the sum of the products time * value:
sum(value * time_to_sec(timeDIFF(nexttimestamp,timestamp))) as timevalue,
-- divide 'time-since-starttime' by 'total-time', mutiply by 'count' and round to integer
-- this gives an integer running from 0 to count-1 and is used for grouping the intervals
truncate(0.5 + @count * time_to_sec(timeDIFF(timestamp,@start)) / time_to_sec(timeDIFF(@end,@start)),0) as timegroup
FROM (
SELECT
timestamp,
value,
-- the LEAD function accesses the follow-up timestamp:               
LEAD(timestamp) over (order by timestamp) nexttimestamp
FROM
history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading
) dummy group by timegroup
) result;


So sieht das Ergebnis für einen relativ kurzen Zeitraum aus:
+---------------------+--------------------+
| timestamp           | WeightedAverage    |
+---------------------+--------------------+
| 2022-12-01 19:01:47 | 0.8946178338001871 |
| 2022-12-02 00:58:47 | 0.6442446920082968 |
| 2022-12-02 12:53:54 | 0.8858722884275202 |
| 2022-12-03 00:49:58 |  1.002668870678196 |
| 2022-12-04 17:22:24 | 0.9263655641715461 |
| 2022-12-05 00:23:40 | 0.8085357593900555 |
| 2022-12-05 12:16:18 | 1.2952399656827511 |
| 2022-12-06 00:15:05 | 0.7954219847328247 |
| 2022-12-06 12:04:40 | 1.2328480286063794 |
+---------------------+--------------------+
9 rows in set (0.254 sec)

Das innerste SELECT lieferte hier ca. 2400 Daten



rudolfkoenig

Ich wuerde als erstes die MariaDB Logs nach Fehlermeldungen durchsuchen.
Danach die subselects eins nach dem anderen durchfuehren, indem ich jeweils eine Tabelle mit dem Ergebnis anlege, und im naechsten Schritt diese Tabelle verwende.
Ich habe die LEAD Funktion im Verdacht, ich vermute dass sie implizit ein Join mit der gleichen Tabelle macht: 18000^2 ist 324Mio, und waere relativ viel fuer diesen Rechner.
Merkwuerdig finde ich, dass ein Teilergebnis zurueckgeliefert wird, statt einer Fehlermeldung.

Beim betrachten dieser Query fuehle ich mich uebrigens auch als blutiger Anfaenger :)

alkazaa

#2
Danke für die Antwort!

Ich habe jetzt weitergebohrt und zwar nicht die logfiles angeschaut, aber den in eine Datei umgelenkten (seeehr langen) output des inneren SELECT-query. Da war der Übeltäter schnell gefunden: die timediff-Funktion liefert als längstmögliche Zeitdifferenz einen Wert von 838:59:59!! (Keine Ahnung warum). Alles was länger ist wird abgeschnitten.

Ich habe dann alle "time_to_sec(timeDIFF(t2, t1))" ersetzt durch "to_seconds(t2)-to_seconds(t1), dann klappt es.
Werde natürlich noch weiter testen, sieht aber alles ganz gut aus.

Ein Traum wäre es nun noch, wenn @DS_Starter eine non-blocking-Version des DbRep-Befehls "sqlCmdBlocking" basteln würde (oder gibt's das schon??).

Hier der neue SQL Code in strukturierter Form:
SET @device = "E_Zaehler",
@reading = "power",
@start = "2022-10-29 00:00:00",
@end = "2022-11-29 05:00:00",
@count = 9;
SET @totaltime = to_seconds(@end)-to_seconds(@start);
SELECT
timestamp,
-- WeightedAverage is the quotient of timevalue and timelength:
timevalue / timelength as WeightedAverage
FROM (
SELECT
timestamp,
--
-- timelength is the sum of times in a timegroup interval:
sum((to_seconds(nexttimestamp)-to_seconds(timestamp))) as timelength,
-- timevalue is the sum of the products (nexttime-thistime) * value:
sum(value * (to_seconds(nexttimestamp)-to_seconds(timestamp))) as timevalue
FROM (
SELECT
timestamp,
value,
--
-- the LEAD function accesses the follow-up timestamp:               
LEAD(timestamp) over (order by timestamp) nexttimestamp,
--
-- dividing 'time-since-starttime' by '@totaltime', mutiplying by 'count' and truncating to integer
-- gives an integer running from 0 to count-1 which is used for grouping the intervals:
truncate(@count * (to_seconds(timestamp)-to_seconds(@start)) / @totaltime,0) as timegroup
FROM
history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading
) dummy group by timegroup
) result;


und für die direkte Ausführung von einer mysql Kommadozeile als oneliner:
SET @device = "E_Zaehler", @reading = "power", @start = "2022-10-29 00:00:00", @end = "2022-11-29 05:00:00", @count = 9; SET @totaltime = to_seconds(@end)-to_seconds(@start); SELECT timestamp, timevalue / timelength as WeightedAverage FROM ( SELECT timestamp, sum((to_seconds(nexttimestamp)-to_seconds(timestamp))) as timelength, sum(value * (to_seconds(nexttimestamp)-to_seconds(timestamp))) as timevalue FROM ( SELECT timestamp, value, LEAD(timestamp) over (order by timestamp) nexttimestamp, truncate(@count * (to_seconds(timestamp)-to_seconds(@start)) / @totaltime,0) as timegroup FROM history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading ) dummy group by timegroup ) result;

Für die Ausführung aus der FHEM Kommandozeile die Semikolons verdoppeln:
set <DbRep> sqlCmd SET @device = "E_Zaehler", @reading = "power", @start = "2022-10-29 00:00:00", @end = "2022-11-29 05:00:00", @count = 9;; SET @totaltime = to_seconds(@end)-to_seconds(@start);; SELECT timestamp, timevalue / timelength as WeightedAverage FROM ( SELECT timestamp, sum((to_seconds(nexttimestamp)-to_seconds(timestamp))) as timelength, sum(value * (to_seconds(nexttimestamp)-to_seconds(timestamp))) as timevalue FROM ( SELECT timestamp, value, LEAD(timestamp) over (order by timestamp) nexttimestamp, truncate(@count * (to_seconds(timestamp)-to_seconds(@start)) / @totaltime,0) as timegroup FROM history WHERE TIMESTAMP BETWEEN @start AND @end AND DEVICE LIKE @device AND READING LIKE @reading ) dummy group by timegroup ) result;
Aber: Bei Ausführung durch Eingabe im Textfeld des set-Befehls hinter der drop-down Liste 'set [sqlCmd]' dürfen die ';;' nur einfach ';' sein!



rudolfkoenig

ZitatEin Traum wäre es nun noch, wenn @DS_Starter eine non-blocking-Version des DbRep-Befehls "sqlCmdBlocking" basteln würde (oder gibt's das schon??).
Siehe https://forum.fhem.de/index.php?topic=130588

DS_Starter

Zitat
Ein Traum wäre es nun noch, wenn @DS_Starter eine non-blocking-Version des DbRep-Befehls "sqlCmdBlocking" basteln würde (oder gibt's das schon??).
Im DbRep gibt es das "set ... sqlCmd" schon als non-blocking Variante.


Also ich bin immer wieder erstaunt welche SQLs man bauen kann.  8)
Allerdings habe ich interessehalber das SQL bei mir versucht. Es kommt aber immer ein Syntxfehler.
Kann aber schon ein bisschen spät sein heute ...
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

alkazaa

Zitat von: DS_Starter am 07 Dezember 2022, 22:42:56
Allerdings habe ich interessehalber das SQL bei mir versucht. Es kommt aber immer ein Syntxfehler.
Kann aber schon ein bisschen spät sein heute ...

Es liegt wohl am Verdoppeln oder Nicht-Verdoppeln der Semikolons. Ich hab's in meinem Beitrag korrigiert.