Update einer Spalte durch differenz (LAG) einer anderen Spalte

Nikolaus

Neuer Benutzer
Beiträge
3
Hallo,

ich bin nicht sehr versiert mit Datennbanken und habe ein Problem, bei dem ich Hilfe benötige.

Ich logge (u.a.) meinen Stromzähler (Zählerstand und Einspeisung insNetz) in eineMariaDB für Tagesansichtenmit Grafana.
Dies läuft gut.
Nun habe ich eine neue Tabelle 'Monat' für den monatlichen Verbrauch angelegt:

Spalte Typ
DateTime datetime
Zaehlerstand decimal(11,5)
VerbrauchMonat decimal(10,5)
Einspeisung decimal(11,5)
EinspeisungMonat decimal(10,5)

Aus der Tabelle der täglichen Logs habe ich nun die entsprechenden Werte (DateTime, Zählerstand, Einspeisung) des
ERSTEN Eintrages jedes Monats in die Tabelle 'Monat' übernommen.
So weit so gut.

Ich möchte nun die Differenz des Zählerstandes von je zwei aufeinanderfolgenden Monaten in die Spalte 'VerbrauchMonat' schreiben.
Leider ohne Erfolg.

Ein SELECT auf die Tabelle funktioniert, zeigt mir ein Ergebnis an, allerdings mit einer zweiten Spalte 'VerbrauchMonat' als letzte Spalte.
SELECT *,
Zaehlerstand - Lag(Zaehlerstand, 1) OVER(
ORDER BY DateTime ASC) AS VerbrauchMonat
FROM Monat;

Der Versuch des UPDATE mit:
UPDATE Monat SET VerbrauchMonat = Zaehlerstand - Lag(Zaehlerstand, 1)

liefert allerdings : Fehler in der SQL-Abfrage (1064): Syntax error near '' at line 1

und
UPDATE Monat SET VerbrauchMonat = Zaehlerstand - Lag(Zaehlerstand, 1) WHERE ....
liefert: Fehler in der SQL-Abfrage (1064): Syntax error near 'WHERE Einspeisung=0.0000' at line 1

Wer kann den knoten in meinem Hirn lösen?

Herzlichen Dank im Vorraus.
Klaus
 
Werbung:
Du würdest vielleicht erstmal ein select schreiben, das Dir den Wert berechnet und anzeigt. Wenn die Syntaxfehler raus sind und der Wert passt, machst Du damit ein update.
Für Lag und Lead und ander Windows Functions brauchst du die Angabe des "Windows".
Code:
LAG(expr, N, default) 
          OVER (Window_specification | Window_name)
Wenn man kurz drüber nachdenkt: Ohne die Angabe der Sortierspalte wäre die Angabe von vorigem oder nächstem Datensatz auch nicht logisch.
Zumindest dann, wenn einem bewusst ist, dass die Datenausgabe eines Select niemals eine definierte Reihenfolge hat.
Das gilt im Zusammenhang mit Order By oder auch bei Updates nicht, da es um die Ausgabe Reihenfolge geht bzw. gar keine vorliegt.

Und last but not least:
Man speichert idR diese Werte nicht ab, die sich funktional aus den Daten ohne Probleme ableiten lassen. Man fragt sie einfach ab, wenn man sie braucht.
 
Henne-Ei - Problem: zum Zeitpunkt des versuchten UPDATE ist das noch gar nicht bekannt, weil nicht ausgerechnet.

Code:
postgres=# create table nikolaus(datum int, zaehler int, differenz int);
CREATE TABLE
postgres=# copy nikolaus (datum, zaehler) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1    2
>> 2    6
>> 3    10
>> 4    15
>> 5    25
>> \.
COPY 5
postgres=# select datum, zaehler, zaehler - lag(zaehler) over(order by datum) as diff from nikolaus ;
 datum | zaehler | diff 
-------+---------+------
     1 |       2 |     
     2 |       6 |    4
     3 |      10 |    4
     4 |      15 |    5
     5 |      25 |   10
(5 rows)

postgres=# with tmp as (select datum, zaehler, zaehler - lag(zaehler) over(order by datum) as diff from nikolaus) update nikolaus set differenz = tmp.diff from tmp where nikolaus.datum=tmp.datum ;
UPDATE 5
postgres=# select * from nikolaus ;
 datum | zaehler | differenz 
-------+---------+-----------
     1 |       2 |          
     2 |       6 |         4
     3 |      10 |         4
     4 |      15 |         5
     5 |      25 |        10
(5 rows)
 
Exakt, was @dabadepdu schrieb: das zu speichern ist unsinnig.

Hier mal noch wie andere Systeme mit einem besseren Fehlertext reagieren:

Code:
postgres=# update nikolaus set differenz = zaehler - lag(zaehler) over(order by zeit);
ERROR:  window functions are not allowed in UPDATE
LINE 1: update nikolaus set differenz = zaehler - lag(zaehler) over(...
                                                  ^
postgres=#
 
Herzlichen Dank für die schnellen Antworten.

Wie ich schon oben erwähnte, ein:
select DateTime, Zaehlerstand, Zaehlerstand - lag(Zaehlerstand ) over(order by DateTime) as diff from Monat

liefert das, was ich sehen will:
DateTimeZaehlerstanddiff
2023-02-05 00:00:0115653.13590NULL
2023-02-06 00:00:0215661.038707.90280
2023-02-07 00:00:0215672.3297011.29100
2023-02-08 00:00:0215681.628109.29840
2023-02-09 00:00:0215689.509407.88130
2023-02-10 00:00:0215697.989208.47980

Wenn ich allerdings das Update (entsprechend dem Vorschlag von akretschmer) umsetze meldet es Fehler:
with tmp as (select DateTime, Zaehlerstand, Zaehlerstand - lag(Zaehlerstand ) over(order by DateTime) as diff from Monat)
update Monat set VerbrauchMonat = tmp.diff from tmp where Monat.DateTime=tmp.DateTime;

liefert - wieder nicht sehr gesprächig:
Fehler in der SQL-Abfrage (1064): Syntax error near 'update Monat set VerbrauchMonat = tmp.diff from tmp' at line 2

Ich sehe das Problem leider nicht :-(
 
Hab einen View gebaut mit folgendem Command, das leider scheinbar noch einen Denkfehler hat,
da der Wert für Monat=2306 wesentlich zu klein und der für 2307 viel zu hoch.
Wer kann mir den richtigen Filter liefern?

CREATE OR REPLACE VIEW V_ErzeugungMonat
AS select TimeStamp, ETotal - lag(ETotal) over(order by TimeStamp) as ErzeugungMonat,
DATE_FORMAT(TimeStamp, '%y%m') as Monat
from SpotData WHERE TimeStamp LIKE '%%%%-%%-%% 00:00:%%' group by MONTH(TimeStamp) DESC

Das ergibt dann folgendes:
TimeStamp ErzeugungMonat Monat
2023-07-01 00:00:01 1036640 2307
2023-06-02 00:00:02 3696 2306
2023-05-31 00:00:01 NULL 2305

Eigentlich sollte in der Zeile Monat=2306 die Differenz stehen aus:
TimeStamp ETotal
2023-06-30 23:59:02 1040336 und
2023-06-01 00:00:01 1

Die SpotData Tabelle sieht so aus:
select TimeStamp, ETotal
from SpotData
WHERE TimeStamp LIKE '%%%%-%%-01 00:00:%%'
OR TimeStamp LIKE '%%%%-%%-%% 23:59:%%'
ORDER by TimeStamp


TimeStamp ETotal
2023-05-31 00:00:01 0
2023-06-01 00:00:01 1
2023-06-01 23:59:01 3696
2023-06-02 00:00:02 3696
2023-06-02 23:59:01 49944
2023-06-03 00:00:02 49944
2023-06-03 23:59:02 88081
2023-06-04 00:00:01 88081
2023-06-04 23:59:02 134666
...........
2023-06-27 00:00:02 917955
2023-06-27 23:59:02 950256
2023-06-28 00:00:01 950256
2023-06-28 23:59:02 981472
2023-06-29 00:00:01 981472
2023-06-29 23:59:03 1020990
2023-06-30 00:00:01 1020990
2023-06-30 23:59:02 1040336
2023-07-01 00:00:01 1040336
2023-07-01 23:59:02 1078233
2023-07-02 00:00:02 1078233
 
Man nennt Spalten am besten nicht wie Schlüsselwörter, z.B. Timestamp, kann problematisch sein.
Informier Dich bitte, was Group By macht, frag Dich dann, was es in Deinem Fall soll und verwende es richtig. (Das ist bei mySQL etwas gemein, vielleicht schon mal davon gehört)
Verwende richtige Spaltentypen (wieder Timestamp) und behandle den Typ dann auch entsprechend. (Ich hoffe, die Spalte Timestamp ist auch ein Timestamp und kein String. Wenn es kein String ist, vergiß die Like <string> Operation!) Mit Timestampwerten wird gerechnet oder ihre Größe verglichen, also >, <, =, dateadd(), ..
Vielleicht räumst Du das erstmal auf?

Welche Idee steckt hinter der Where Condition im View?
 
Werbung:
Zurück
Oben