Zeitstempel aus 2 Tabellen suchen

[DerTobi]

Hallo,

ich habe zwei Tabellen in einer MariaDB, einmal vom Stromzähler, einmal von der Photovoltaikanlage, jeweils mit Zählerstand und aktueller Leistung.
Sieht dann so aus:

Tabelle Stromzähler

ID	TimeStamp	Zaehlerstand	Leistung
681414​	19.08.2023 16:00:17​	9369,33​	-2099​
681413​	19.08.2023 15:59:09​	9369,33​	-2078​
681412​	19.08.2023 15:58:01​	9369,33​	-2065​
681411​	19.08.2023 15:56:53​	9369,33​	-2022​
681410​	19.08.2023 15:55:45​	9369,33​	-1980​
681409​	19.08.2023 15:54:37​	9369,33​	-1978​
681408​	19.08.2023 15:53:29​	9369,33​	-2114​
681407​	19.08.2023 15:52:21​	9369,33​	-2715​
681406​	19.08.2023 15:51:13​	9369,33​	-2693​
681405​	19.08.2023 15:50:05​	9369,33​	-1952​

Tabelle PV

ID	TimeStamp	Zaehlerstand	Leistung
1747​	19.08.2023 16:00:01​	65,789​	2306,3​
1746​	19.08.2023 15:58:57​	65,748​	2279​
1745​	19.08.2023 15:57:54​	65,706​	2280,1​
1744​	19.08.2023 15:56:50​	65,665​	2233,7​
1743​	19.08.2023 15:55:46​	65,624​	2202,2​
1742​	19.08.2023 15:54:43​	65,585​	2192,6​
1741​	19.08.2023 15:53:39​	65,545​	2273​
1740​	19.08.2023 15:52:36​	65,498​	2773,5​
1739​	19.08.2023 15:51:32​	65,442​	3166​
1738​	19.08.2023 15:50:28​	65,388​	2315​

So, jetzt würde ich gerne die Differenz berechnen aus der aktuellen Leistung der PV Tabelle und der Leistung in der Stromzähler Tabelle.
Das Problem ist, die Zeitstempel sind unterschiedlich.

Also eigentlich würde ich gerne zu jedem Eintrag in der Stromzähler-Tabelle denjenigen Eintrag in der PV Tabelle suchen, der den nächstgelegenen Zeitstempel hat, und dann jeweils die Differenz dieser beiden Einträge berechnen.

Leider bin ich in SQL nicht so fit und Google hilft mir gerade auch nicht weiter, oder ich habe die falschen Stichworte.
Wie könnte man sowas angehen? Macht es Sinn, einen JOIN zu machen und die Tabellen zusammenzuführen?

 

[Kampfgummibaerlie]

Hier mein schneller Code:

select table1.leistung-table2.leistung from table1, table2;

wichtig dabei ist aber, dass jeder Eintrag in Tabelle1 einen Eintrag in Tabelle2 hat... wie zB in deinem Beispiel ^^

 

[DerTobi]

Ok, das ist in diesem Fall ein blödes, oder "optimales" Beispiel. Es ist nicht gewährleistet, dass immer gleich viele Einträge da sind bzw. dass die Zeitstempel dann auch zueinander passen. Manchmal hat's Aussetzer im Log, dann fehlen mal ein paar Werte, und dann passt es nicht mehr zusammen wenn man es so macht

 

[dabadepdu]

Deine Daten kommen im Minutenabstand, fast. Einmal im Abstand von 64 Sekunden, einmal im Abstand von 68 Sekunden.
Das ist "unpraktisch", um sie 1:1 gegeneinander laufen zu lassen.

Du hast seitens SQL verschiedene Möglichkeiten:
- Abgleich über definierte Zeitbereiche einer Tabelle zu Treffern, die da rein passen
- Einsatz von Window Funktions wie RANK(), die aus der Zeitangabe eine symbolische Reihenfolge machen und die von beiden Tabellen joinen
- Mittelwertbildung auf eine fixierte Größe (Takt) und die jeweils joinen

In anderen Datenbanken stehen Dir auch Typen wie (Time)range zur Verfügung, was solche Probleme übersichtlicher gestaltet.
Grundsätzlich könntest Du vor dem jetzigen Problem mal kurz innehalten und Dich fragen, warum Du diese Intervalle erhälst und wie hoch die Genauigkeit tatsächlich (also in Wirklichkeit) ist. Immerhin schafft es laut Deinen Daten irgendjemand, fast im Minutentakt Werte zu produzieren. Eben nur fast und unterschiedlich schlecht.

Physikalisch ist davon auszugehen, dass in beiden Fällen ein konstantes (analoges) Signal anliegt. Das wird durch Sensorik / Digitalisierung / Verarbeitung irgendwo zerhakt, wahrscheinlich mehrfach. Bei der Verarbeitung auf DB Ebene muss man nicht genauer sein, als die Realität. Am Ende vielleicht sogar deutlich ungenauer, weil Daten auf einem Detailgrad von Sekunden und Minuten bei diesem Thema nicht interessieren.

Ja, und dann, wenn man alles z.B. auf Stunden aggregiert, ist es wirklich nicht so ein Riesenproblem. Die Lösung hängt vom Zweck ab.

 

[BerndB]

Hi Tobi,

leg deine Daten (oder teilweise) mal auf SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions. ab, dann schau ich mal.

Gruß

Bernd

 

[ukulele]

Spass mit Messwerten:

WITH z0(ID,[TimeStamp],Zaehlerstand,Leistung) AS (
    SELECT 681414,'10.08.2023 16:00:17',9369.33,-2099 UNION ALL
    SELECT 681413,'19.08.2023 15:59:09',9369.33,-2078 UNION ALL
    SELECT 681412,'19.08.2023 15:58:01',9369.33,-2065 UNION ALL
    SELECT 681411,'19.08.2023 15:56:53',9369.33,-2022 UNION ALL
    SELECT 681410,'19.08.2023 15:55:45',9369.33,-1980 UNION ALL
    SELECT 681409,'19.08.2023 15:54:37',9369.33,-1978 UNION ALL
    SELECT 681408,'19.08.2023 15:53:29',9369.33,-2114 UNION ALL
    SELECT 681407,'19.08.2023 15:52:21',9369.33,-2715 UNION ALL
    SELECT 681406,'19.08.2023 15:51:13',9369.33,-2693 UNION ALL
    SELECT 681405,'19.08.2023 15:50:05',9369.33,-1952
    ), pv0(ID,[TimeStamp],Zaehlerstand,Leistung) AS (
    SELECT 1747,'19.08.2023 16:00:01',65.789,2306.3 UNION ALL
    SELECT 1746,'19.08.2023 15:58:57',65.748,2279 UNION ALL
    SELECT 1745,'19.08.2023 15:57:54',65.706,2280.1 UNION ALL
    SELECT 1744,'19.08.2023 15:56:50',65.665,2233.7 UNION ALL
    SELECT 1743,'19.08.2023 15:55:46',65.624,2202.2 UNION ALL
    SELECT 1742,'19.08.2023 15:54:43',65.585,2192.6 UNION ALL
    SELECT 1741,'19.08.2023 15:53:39',65.545,2273 UNION ALL
    SELECT 1740,'19.08.2023 15:52:36',65.498,2773.5 UNION ALL
    SELECT 1739,'19.08.2023 15:51:32',65.442,3166 UNION ALL
    SELECT 1738,'19.08.2023 15:50:28',65.388,2315
    ), z AS (
    SELECT    ID,convert(DATETIME,left([TimeStamp],19),104) AS [TimeStamp],Zaehlerstand,Leistung
    FROM    z0
    ), pv AS (
    SELECT    ID,convert(DATETIME,left([TimeStamp],19),104) AS [TimeStamp],Zaehlerstand,Leistung
    FROM    pv0
    ), --WITH
t AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY z.ID ORDER BY abs(datediff(second,z.[TimeStamp],pv.[TimeStamp]))) AS zeile,
            z.ID,
            z.[TimeStamp],
            z.Zaehlerstand,
            z.Leistung,
            pv.ID AS pv_ID,
            pv.[TimeStamp] AS pv_TimeStamp,
            pv.Zaehlerstand AS pv_Zaehlerstand,
            pv.Leistung AS pv_Leistung
    FROM    z
    CROSS JOIN pv
    WHERE    z.[TimeStamp] BETWEEN dateadd(minute,-2,pv.[TimeStamp]) AND dateadd(minute,2,pv.[TimeStamp])
    )
SELECT    *
FROM    t
WHERE    t.zeile = 1

Ist MSSQL, die Funktionen sollten sich aber 1:1 in was auch immer abbilden lassen. z0, z, pv0, pv sind nur die Testdaten, du fängst mit "WITH t AS... an.

Das kann jetzt in der Masse natürlich langsam werden, muss man gucken. Es wird jedem Zählerwert ein PV Wert zugeordnet der maximal 2 Minuten davor oder dannach auftritt, davon jeweils der nächstgelegene. Das heißt dann auch das ein PV Wert zwei verschiedenen Zählerständen zugeordnet werden kann oder auch gar nicht. Es geht ja aber nicht um falsch und richtig sondern einen möglichst passigen Wert zu finden und das passiert.

Da kann man jetzt natürlich noch Mathe betreiben und einen laufenden Durchschnitt von PV zum Zeitpunkt des Zählerwertes berechnen aber ich glaube das ist mehr Fetisch als sinnvoll.

 

[BerndB]

Hier noch eine andere Lösung (ohne WITH), glaube die Version kann das noch nicht,

SELECT
    s.*
  , FORMAT( pvfrom.Leistung
    + ((pvto.Leistung - pvfrom.Leistung )
    /  TIMESTAMPDIFF(SECOND, pvfrom.Timestamp, pvto.timestamp))
    * TIMESTAMPDIFF(SECOND, pvfrom.Timestamp, s.Timestamp),3) AS calc_pv_Leistung
 
  , FORMAT( pvfrom.Zaehlerstand
    + ((pvto.Zaehlerstand - pvfrom.Zaehlerstand )
    /  TIMESTAMPDIFF(SECOND, pvfrom.Timestamp, pvto.timestamp))
    * TIMESTAMPDIFF(SECOND, pvfrom.Timestamp, s.Timestamp),3) AS calc_pv_Zaehlerstand
           
  , CONCAT("from: ", TIME(pvfrom.Timestamp),' ',pvfrom.Leistung,' to: ',TIME(pvto.Timestamp),' ',pvto.Leistung) AS rawdata
FROM Stromzaehler s
LEFT JOIN PV AS pvto ON pvto.id = (
      SELECT pvid.id FROM PV AS pvid
      WHERE pvid.Timestamp >= s.Timestamp LIMIT 1 )
LEFT JOIN PV AS pvfrom ON pvfrom.id = pvto.id -1;

Hier das Beispiel :

SQL Fiddle - Online SQL Compiler for learning & practice

Discover our free online SQL editor enhanced with AI to chat, explain, and generate code. Support SQL Server, MySQL, MariaDB, PostgreSQL, and SQLite.

sqlfiddle.com

Müssten nur noch die Index gesetzt werden

 

[ukulele]

Edit zu meinem Post: Ich hatte mit einem CROSS JOIN mit WHERE angefangen weil ich dachte ich gruppiere dann irgendwie aber natürlich hätte ich am Ende auch einfach einen LEFT oder INNER JOIN nehmen können.

WITH t AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY z.ID ORDER BY abs(datediff(second,z.[TimeStamp],pv.[TimeStamp]))) AS zeile,
            z.ID,
            z.[TimeStamp],
            z.Zaehlerstand,
            z.Leistung,
            pv.ID AS pv_ID,
            pv.[TimeStamp] AS pv_TimeStamp,
            pv.Zaehlerstand AS pv_Zaehlerstand,
            pv.Leistung AS pv_Leistung
    FROM    z
    INNER JOIN pv
    ON    z.[TimeStamp] BETWEEN dateadd(minute,-2,pv.[TimeStamp]) AND dateadd(minute,2,pv.[TimeStamp])
    )
SELECT    *
FROM    t
WHERE    t.zeile = 1