MySQL fehlerhafte Werte suchen, Durchschnitt berechnen und Fehler überschreiben

Blaidd_Drwg · 8 April 2015

Hallo liebes Forum,

Ich sitze seit ner ganzen Weile schon an diesem Problem und wäre für jeden Rat dankbar. Und zwar geht es hierbei um einen sehr langen Datenatz in dem diverse Messwerte aufgetragen sind.
Alle Messwerte haben ein eindeutiges Datum, eine ID (Auto_Increment) und einen Zeitstempel, an dem sie sich eindeutig identifizieren lassen.
Alles in Allem sind die relevanten Daten verteilt auf 6 Spalten und knapp 500.ooo Zeilen. Der Haken an der Sache is, dass das Messgerät, das die Werte ermittelt wohl alle paar tausend Werte nen Aussetzer hat oder sich kalibriert und dass dann immer mindestens zwei Zeilen mit Fehlern belegt sind (bezieht sich auf alle 6 relevanten Spalten) ... Diese sind zum Glück eindeutig gekennzeichnet durch den Wert -99999.
Um meinen Datensatz nicht zu sehr zu verfälschen würde ich nun gern eine Abfrage schreiben, die mir die fehlerhaften Werte ermittelt, dazu den letzten logischen Wert und den nächsten logischen Wert. Aus den logischen Werten soll nun der Durchschnitt berechnet werden und anschließend der errechnete Wert anstelle des Fehlers eingetragen werden.

Wär echt klasse, wenn ihr mir nen Tipp hättet wie ich das anstellen kann. Bin noch absoluter Neuling auf dem Gebiet und brauch die Daten unbedingt für ein Projekt (dabei ist die Auswertung der Daten nur der Anfang -.- )

Freu mich schon auf Antworten!

Grüße, Blaidd_Drwg

Distrilec · 9 April 2015

(Da du nur einen Tipp wolltest, hier das ganze in Oracle-Syntax

)
Bitte:

Code:

With dat As
(Select 1 As vid, 1 As val From   dual Union All
  Select 2, -99999 From dual Union All
  Select 3, 2 From dual Union All
  Select 4, 3 From dual)

Update dat t
Set    t.val =
       (Select Avg(j.val) val
        From   dat p

        Left   Join dat j
        On     j.vid = p.vid - 1
        Or     j.vid = p.vid + 1

        Where  p.vid = t.vid
        Group  By p.vid)
Where  t.val = -99999

ukulele · 9 April 2015

Ich würde zunächst meine Einträge sortieren und eine Zeilennummer vergeben. In MSSQL ginge das mit ROW_NUMBER() OVER (ORDER BY [...]) aber das gibt es in MySQL wohl nicht. Hier eine Lösung für MySQL:
http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select
Die Information (also die eigentliche Tabelle und die Zeilennummer) stellst du dann als View bereit. Per Join holst du dann zu jedem ungültigen Datensatz den vorherigen und den nachfolgenden, gültigen Datensatz. Ich weiß aber nicht, wieviele Fehlerhafte Datensätze hintereinander liegen können, das ist möglicherweise etwas knifflig.

Blaidd_Drwg · 9 April 2015

Distrilec schrieb:

(Da du nur einen Tipp wolltest, hier das ganze in Oracle-Syntax

)
Bitte:

Code:

With dat As
(Select 1 As vid, 1 As val From   dual Union All
  Select 2, -99999 From dual Union All
  Select 3, 2 From dual Union All
  Select 4, 3 From dual)

Update dat t
Set    t.val =
       (Select Avg(j.val) val
        From   dat p

        Left   Join dat j
        On     j.vid = p.vid - 1
        Or     j.vid = p.vid + 1

        Where  p.vid = t.vid
        Group  By p.vid)
Where  t.val = -99999

Ja stimmt, da hab ich wohl "Tipp" geschrieben... jedenfalls benötige ich wohl doch etwas konkretere Hilfe, wenn ich das ganze in MySQL überführen möchte... Wie gesagt ich bin noch ein relativ unerfahrener SQL-Jünger, daher wäre es nett, wenn ihr mir die passende Syntax geben könntet oder eben eine Erklärung wie man das ganze dann brauchbar in MySQL umsetzen könnte. Am besten wäre natürlich eine Struktur, die ich als Anfänger auch durchschauen kann, da ich nachher auch kurz dokumentieren sollte wie ich unser Problem mit den Fehlwerten in den Griff bekommen habe.

Distrilec · 9 April 2015

Soweit ich weiß kann man in MySQL innerhalb eines Update nicht von der Tabelle selektieren... Oder irre ich mich gerade?

ukulele · 9 April 2015

Stimmt, keine Ahnung ob sich das mit einer View austricksen läßt, vermutlich nicht. Es kann übrigens auch kein WITH.

BerndB · 9 April 2015

Distrilec schrieb:
Soweit ich weiß kann man in MySQL innerhalb eines Update nicht von der Tabelle selektieren... Oder irre ich mich gerade?

Spielt doch gar keine Rolle. Man kann das Ergebnis doch in eine neue Tabelle schreiben:
INSERT INTO new_table select bla bla bla from old_table;

Blaidd_Drwg · 9 April 2015

Okay, ich bin mir immer noch nicht sicher wie ich die werte vor und nach den betreffenden störwerten miteinander verrechnen kann... ich hab jetzt eine neue tabelle erstellt in der ich die zeilen in denen die störwerte stehen mitsamt den störwerten eintragen lasse: D.h. wenn beispielsweise in zeile 50 und 51 die Störwerte eingetragen sind, dann wird in der neuen Tabelle Zeile 49, 50, 51 und 52 angezeigt.
Jetzt komme ich aber nicht mit der berechnung des durchschnitts und dem anschließenden einfügen/überschreiben der störwerte klar. Da fehlt mir einfach noch die Logik dahinter. Hat da wer nen brauchbaren Tipp/Code, den man verwenden könnte? Bzw. wenn ihr mir erklären könntet wie man dem Problem nun mit bestimmten Funktionen Herr werden könnte, wäre mir das schon eine große Hilfe, selbst wenn ihr keine endgültige Lösung habt!

... gibts hier ne Möglichkeit ne Tabelle rein zu kopieren? Ich habs bisher noch nich gefunden, würde die sache mit dem erklären um einiges vereinfachen!^^

BerndB · 9 April 2015

Hi ...,

so gehts, wir sollten aber noch mal kurz telefonieren, da ich nicht weiss was du für Datentypen verwendest und
wie die Lücken gefüllt werden sollen.

Code:

mysql> desc messwerte;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| wert1 | float            | YES  |     | NULL    |                |
| wert2 | float            | YES  |     | NULL    |                |
| wert3 | float            | YES  |     | NULL    |                |
| wert4 | float            | YES  |     | NULL    |                |
| wert5 | float            | YES  |     | NULL    |                |
| wert6 | float            | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
7 rows in set (0,00 sec)

mysql> select * from messwerte;
+----+-------+-------+-------+-------+-------+-------+
| id | wert1 | wert2 | wert3 | wert4 | wert5 | wert6 |
+----+-------+-------+-------+-------+-------+-------+
|  1 |     1 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  2 |     2 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  3 |     3 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  4 | -9999 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  5 |     5 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  6 | -9999 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  7 | -9999 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  8 | -9999 |  NULL |  NULL |  NULL |  NULL |  NULL |
|  9 |    15 |  NULL |  NULL |  NULL |  NULL |  NULL |
+----+-------+-------+-------+-------+-------+-------+
9 rows in set (0,00 sec)

mysql>
mysql> SELECT m.id, m.wert1 Wert_orig,
    -> @offset:=(SELECT (wert1 -@v) / (id - vor.id) FROM messwerte  WHERE ID > m.id  AND wert1 <> -9999 LIMIT 1) AS tmp ,
    -> @v:= IF (m.wert1 <> -9999, m.wert1, @v + @offset ) new_val
    -> FROM messwerte m
    -> LEFT JOIN messwerte vor  ON vor.id = m.id -1;

1   1   NULL   1
2   2   1   2
3   3   1   3
4   -9999   1   4
5   5   2.2   5
6   -9999   2.5   7.5
7   -9999   2.5   10
8   -9999   2.5   12.5
9   15   NULL   15
9 rows in set (0,00 sec)

mysql>
mysql> SELECT neu.id, Wert_neu
    -> FROM (
    ->     SELECT m.id, m.wert1 Wert_orig,
    ->     @offset:=(SELECT (wert1 -@v) / (id - vor.id) FROM messwerte  WHERE ID > m.id  AND wert1 <> -9999 LIMIT 1) AS tmp ,
    ->     @v:= IF (m.wert1 <> -9999, m.wert1, @v + @offset ) Wert_neu
    ->     FROM messwerte m
    ->     LEFT JOIN messwerte vor  ON vor.id = m.id -1
    -> ) AS neu;
+----+----------+
| id | Wert_neu |
+----+----------+
|  1 |        1 |
|  2 |        2 |
|  3 |        3 |
|  4 |        4 |
|  5 |        5 |
|  6 |      7.5 |
|  7 |       10 |
|  8 |     12.5 |
|  9 |       15 |
+----+----------+
9 rows in set (0,00 sec)

mysql>

Du kannst heute gerne noch bis 23:30 anrufen

02163 / 5719653

Gruss

Bernd

BerndB · 9 April 2015

Hab noch keine Mail bekommen.

Gruss Bernd

Blaidd_Drwg · 9 April 2015

Ja ich hatte nen kleinen Konflikt mit meinem Mail Programm, da werden nur 25MB max Dateigröße akzeptiert. Die Mail dürftest jetzt haben oder?
Gruß Matthias

BerndB · 11 April 2015

-- Hier das Script in einzelnen Schritten damit man
-- die Arbeitsweise nachvollziehen kann.
--

Code:

-- Zuerst selectieren wir alle ROWS in denen der erste Messwert auf -999
-- steht. Da dann immer alle 6 Messwerte in einer ROW fehlen genügt die
-- Prüfung auf den ersten Werte
--
SELECT
  m.*
FROM
  tbl_key m
WHERE
  m.U_L1 = -999;
--
--
-- Im nächsten Schritt werden die fehlerhaften Blöcke mit einer
-- eindeutigen ID versehen. Das heisst, wenn nur ein Messwert fehlt
-- bekommt diese Zeile eine ID. Fehler mehrere Zeilen so werden alle
-- Zeilen mit der gleichen ID versehen. Diese ID wird später zur
-- Gruppierung der Daten benötigt.
-- Dazu werden 2 Variablen benötigt.
-- 1. @nr - in dieser wird die Gruppennummer geführt
-- 2- idold - zu Prüfung ob die vorhergehende Zeilei (id-1)
--  auch schon fehlerhaft war. Ist dies nicht der Fall
--  wird die Gruppennummer erhöhte
-- Mit dem konstrukt  ( SELECT @nr:=0, @idold:=0 ) tmp werden nur
-- die beiden Variablen initialisiert.
--
SELECT
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id id_old,
  m.*
FROM
  tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
WHERE
  m.U_L1 = -999;
--
--
-- Nun können wir die einzelnen fehlerhaften Bereiche gruppieren
-- um somit eine Zeile je fehlerhaften Bereich zu erhalten und
-- geben mit ORDER BY m.id die Ausgabe fest. Dies hat aber nur
-- Kosmetische Gründe
--
SELECT
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id id_old,
  m.*
FROM
  tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
WHERE
  m.U_L1 = -999
GROUP BY
  nr
ORDER BY
  m.id;
--
--
-- Jetzt muss jeder Bereich (ROW) noch mit Werten angereichert werden
-- die wir in den nächsten Schritten zur Berechnung der fehlenden Werte
-- benötigen.
-- Als eerstes fügen wir die erste (firstid) und letuzte (lastid) ID
-- des Bereiches ein. Diese errechnen sich einfach durch die min/max
-- Funktion
-- Die Werte as der Messwertabelle (m) werden im Moment nur zur besseren
-- Überprüfung der Werte mit ausgegeben.
--
SELECT
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id id_old,
  min(m.id) firstid,
  max(m.id) lastid,
  m.*
FROM
  tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
WHERE
  m.U_L1 = -999
GROUP BY
  nr
ORDER BY
  m.id;
--
--
-- Nun fehlen noch der letze gültige (l) und der nächste gültige (n) Messwert.
-- Diese holen wir eifach mit je einem "LEFT JOIN" dazu, denn wir kennen ja
-- die erste ungültige ID, somit ist der letzte gültige Wert in firstid -1
-- und der nächste gültige Wert in lastid +1.
-- Damit der Join nicht auf jede einzelne Zeile angewendet wird, sondern nur
-- auf jede gruppierte Zeile verpacken wir das Query noch einmal in ein
-- SELECT Statemant
-- Diese Informationen beötigen wir um die fehlenden Werte linear zu errechnen.
-- Die Ausgaben der Felder ist ach nur zur Prüfung der Werte eingebaut.
-- Damit es nicht ganz unübersichtlich wird habe ich schon mal die Werte
-- der Messwerte Tabelle (m) ausgebaut
--
SELECT b.*,
  f.U_L1, f.U_L2, f.U_L3, f.P_L1, f.P_L2, f.P_L3,
  l.U_L1, l.U_L2, l.U_L3, l.P_L1, l.P_L2, l.P_L3
FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
) b
LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
LEFT JOIN
  tbl_key l ON l.id = b.lastid+1;
--
--
-- jetzt wird nur nur ainmal zur Veranschaulichung der Berechung der
-- OFFSET (Delta) für einen Wert errechnet. Dies ist der Wert der zum
-- letzten gültigen Wert je Zeile addiert werden muss
--
SELECT b.*,
  (l.U_L1 - f.U_L1) / (l.id-f.id) offset_u1,
  f.U_L1, f.U_L2, f.U_L3, f.P_L1, f.P_L2, f.P_L3,
  l.U_L1, l.U_L2, l.U_L3, l.P_L1, l.P_L2, l.P_L3
FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
) b
LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
LEFT JOIN
  tbl_key l ON l.id = b.lastid+1;
--
--
-- Als nächstes beseitigen wir erst mal so weit alle Spalte
-- die wie nicht benötigen.
--
SELECT b.firstid,
  b.lastid,
  f.U_L1 U_L1_org, (l.U_L1 - f.U_L1) / (l.id-f.id) offset_u1
FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
  ORDER BY m.id
) b
LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
LEFT JOIN
  tbl_key l ON l.id = b.lastid+1;
--
--
-- Nach dem nun unsere Ergebnisse nur noch die
-- Spalten
--  firstid, lastid - Erste und letzte Fehlerhafte Zeile
--  U_L1_orig  - dem letzten gültigen Wert der Spalte
--  offset_u1  - die Änderung des Wertes in jeder Zeile
-- enthält, kommen wir so langsam zuden finalen Schritten.
-- Die Spalten U_L1_orig und offset_u1 sind nur für den ersten
-- fehlerhaften Wert. Für die anderen Werte müssen diese süäter
-- noch berechnet werden.
--
-- Im aktuellen Schritt gehen wir nun hin und selecttieren erneut
-- unsere Messwert Tabelle und JOINEN diese mit unseren Ergebnissen
-- Dadurch erhalten wir eine Liste mit allen fehlerhaften Spalte in
-- der zusätzlich in jeder noch unsere, für den Bereich errechneten,
-- Werte stehen
--
SELECT x.*, tbl.* FROM tbl_key tbl
RIGHT JOIN (
  SELECT b.firstid,
  b.lastid,
  f.U_L1 U_L1_org, (l.U_L1 - f.U_L1) / (l.id-f.id) offset_u1
  FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
  ORDER BY m.id
LIMIT 10
  ) b
  LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
  LEFT JOIN
  tbl_key l ON l.id = b.lastid+1
) x ON  tbl.id BETWEEN x.firstid AND x.lastid;
--
--

geht weiter ... Beitrag war zu lang ...

BerndB · 11 April 2015

... und weiter gehts.

Code:

-- Jetzt können wir damit beginnen die fehlerhaften
-- Werte, für U_L1 zu errechen. Wenn das funktioniert
-- kann das auch für die anderen Spalten gemacht werte.
-- GESCHAFFT:
-- Mit diesem Query bekommen wir für alle fehlerhaften Werte
-- von U_l1 eine neu berechneten Wert.
-- Zusätzlich habe ich die Zeile "BETWEEN x.firstid-1 AND x.lastid+1;"
-- -1 und +1 erweitert, damit wir im Ergebnis auch den letzten
-- gültigen und den nächsten gültigen Wert nach unseren Fehlern
-- bekommen. Dies muss später wieder rückgängig gemacht werden.
--
SELECT
  tbl.id,
  FORMAT(tbl.U_L1,3) U_L1,
  FORMAT(x.U_L1_org + ((tbl.id - x.firstid + 1) * x.U_L1_offset),3) U_L1_neu
FROM tbl_key tbl
RIGHT JOIN (
  SELECT b.firstid,
  b.lastid,
  f.U_L1 U_L1_org, (l.U_L1 - f.U_L1) / (l.id-f.id) U_L1_offset
  FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
  ORDER BY m.id
  ) b
  LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
  LEFT JOIN
  tbl_key l ON l.id = b.lastid+1
) x ON  tbl.id BETWEEN x.firstid-1 AND x.lastid+1;
--
--
-- Nurch noch die Werte für die restlichen Messwerte
-- U_L2, U_L3, P_L1, P_L2 und P_L3 berechnen.
-- Dazu müssen wir nur in unserem inneren Query die Deltas
-- für die anderen Messwerte noch errechnen und uns in
-- der Ausgabe noch die notwendigen neuen Werte berechnen
-- sowie die originalen Werte ausgeben, damit wir
-- das Ergebnis nochmals prüfen können.
--
SELECT
  tbl.id,
  FORMAT(tbl.U_L1,3) U_L1, FORMAT(x.U_L1_org + ((tbl.id - x.firstid + 1) * x.U_L1_offset),3) U_L1_neu,
  FORMAT(tbl.U_L2,3) U_L2, FORMAT(x.U_L2_org + ((tbl.id - x.firstid + 1) * x.U_L2_offset),3) U_L2_neu,
  FORMAT(tbl.U_L3,3) U_L3, FORMAT(x.U_L3_org + ((tbl.id - x.firstid + 1) * x.U_L3_offset),3) U_L3_neu,
  FORMAT(tbl.P_L1,3) P_L1, FORMAT(x.P_L1_org + ((tbl.id - x.firstid + 1) * x.P_L1_offset),3) P_L1_neu,
  FORMAT(tbl.P_L2,3) P_L2, FORMAT(x.P_L2_org + ((tbl.id - x.firstid + 1) * x.P_L2_offset),3) P_L2_neu,
  FORMAT(tbl.P_L3,3) P_L3, FORMAT(x.P_L3_org + ((tbl.id - x.firstid + 1) * x.P_L3_offset),3) P_L3_neu
FROM tbl_key tbl
RIGHT JOIN (
  SELECT b.firstid,
  b.lastid,
  f.U_L1 U_L1_org, (l.U_L1 - f.U_L1) / (l.id-f.id) U_L1_offset,
  f.U_L2 U_L2_org, (l.U_L2 - f.U_L2) / (l.id-f.id) U_L2_offset,
  f.U_L3 U_L3_org, (l.U_L3 - f.U_L3) / (l.id-f.id) U_L3_offset,
  f.P_L1 P_L1_org, (l.P_L1 - f.P_L1) / (l.id-f.id) P_L1_offset,
  f.P_L2 P_L2_org, (l.P_L2 - f.P_L2) / (l.id-f.id) P_L2_offset,
  f.P_L3 P_L3_org, (l.P_L3 - f.P_L3) / (l.id-f.id) P_L3_offset
  FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
  ORDER BY m.id
  ) b
  LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
  LEFT JOIN
  tbl_key l ON l.id = b.lastid+1
) x ON  tbl.id BETWEEN x.firstid-1 AND x.lastid+1;
--
--
-- Damit sind wir schon :-) beim finalen Schritt angekommen
-- in dem wir, nach dem uns alle Ergebnissefallen, aus unserem
-- SELECT Statement ein UPDATE machen das uns nun endlich unsere
-- Tabelle wieder herstellt.
-- Nicht zu vergessen ist das das BETWEEN Statemant wieder angepasst
-- wird damit nicht zu viele Zeilen selektiert werden.
-- Eine Sicherung der Daten ist natürlich sehr empfehlenswert !!!
--
UPDATE
  tbl_key tbl
RIGHT JOIN (
  SELECT b.firstid,
  b.lastid,
  f.U_L1 U_L1_org, (l.U_L1 - f.U_L1) / (l.id-f.id) U_L1_offset,
  f.U_L2 U_L2_org, (l.U_L2 - f.U_L2) / (l.id-f.id) U_L2_offset,
  f.U_L3 U_L3_org, (l.U_L3 - f.U_L3) / (l.id-f.id) U_L3_offset,
  f.P_L1 P_L1_org, (l.P_L1 - f.P_L1) / (l.id-f.id) P_L1_offset,
  f.P_L2 P_L2_org, (l.P_L2 - f.P_L2) / (l.id-f.id) P_L2_offset,
  f.P_L3 P_L3_org, (l.P_L3 - f.P_L3) / (l.id-f.id) P_L3_offset
  FROM (
  SELECT min(m.id) firstid ,max(m.id) lastid,
  @nr:= IF( @idold+1 <> m.id , @nr+1, @nr) nr,
  @idold:=m.id
  FROM tbl_key m, ( SELECT @nr:=0, @idold:=0 ) tmp
  WHERE
  m.U_L1 = -999
  GROUP BY nr
  ORDER BY m.id
  LIMIT 3
  ) b
  LEFT JOIN
  tbl_key f ON f.id = b.firstid - 1
  LEFT JOIN
  tbl_key l ON l.id = b.lastid+1
) x ON  tbl.id BETWEEN x.firstid AND x.lastid
  SET tbl.U_L1 = ROUND(x.U_L1_org + ((tbl.id - x.firstid + 1) * x.U_L1_offset),3),
  tbl.U_L2 = ROUND(x.U_L2_org + ((tbl.id - x.firstid + 1) * x.U_L2_offset),3),
  tbl.U_L3 = ROUND(x.U_L3_org + ((tbl.id - x.firstid + 1) * x.U_L3_offset),3),
  tbl.P_L1 = ROUND(x.P_L1_org + ((tbl.id - x.firstid + 1) * x.P_L1_offset),3),
  tbl.P_L1 = ROUND(x.P_L2_org + ((tbl.id - x.firstid + 1) * x.P_L2_offset),3),
  tbl.P_L1 = ROUND(x.P_L3_org + ((tbl.id - x.firstid + 1) * x.P_L3_offset),3);

-- FERTIG !!!!
--
-- Gruss
-- Bernd Buffen
-- buffen@t-online.de
-- +49 2163 5719653

Blaidd_Drwg · 11 April 2015

Hey Bernd,

nochmal ein riesen DANKESCHÖN für diese geniale Hilfe, hätts mir nicht besser vorstellen können!!!

... aber hab da noch nen kleinen Fehler gefunden, is uns auch erst aufgefallen, als wir die Daten nochmal auf Störwerte gecheckt haben. Und zwar ist ganz am Ende der UPDATE Funktion drei mal hintereinander P_L1 aufgetragen, ich gehe mal davon aus, dass dort

SET tbl.U_L1 = ROUND(x.U_L1_org + ((tbl.id - x.firstid + 1) * x.U_L1_offset),3),
tbl.U_L2 = ROUND(x.U_L2_org + ((tbl.id - x.firstid + 1) * x.U_L2_offset),3),
tbl.U_L3 = ROUND(x.U_L3_org + ((tbl.id - x.firstid + 1) * x.U_L3_offset),3),
tbl.P_L1 = ROUND(x.P_L1_org + ((tbl.id - x.firstid + 1) * x.P_L1_offset),3),
tbl.P_L2 = ROUND(x.P_L2_org + ((tbl.id - x.firstid + 1) * x.P_L2_offset),3),
tbl.P_L3 = ROUND(x.P_L3_org + ((tbl.id - x.firstid + 1) * x.P_L3_offset),3);

stehen sollte!?

Gruß, Matthias

BerndB · 11 April 2015

Genial,

das hab ich übersehen.

Sorry, Sorry

Man hätte es noch retten können indem man

"WHERE
m.U_L1 = -999"

auf

"WHERE
m.P_L3 = -999"

geändert hätte. Dann hätte man die Tabelle nicht neu einlesen müssen.

Gruss

Bernd

MySQL fehlerhafte Werte suchen, Durchschnitt berechnen und Fehler überschreiben

Blaidd_Drwg

Benutzer

Distrilec

Datenbank-Guru

ukulele

Datenbank-Guru

Blaidd_Drwg

Benutzer

Distrilec

Datenbank-Guru

ukulele

Datenbank-Guru

BerndB

Datenbank-Guru

Blaidd_Drwg

Benutzer

BerndB

Datenbank-Guru

BerndB

Datenbank-Guru

Blaidd_Drwg

Benutzer

BerndB

Datenbank-Guru

BerndB

Datenbank-Guru

Blaidd_Drwg

Benutzer

BerndB

Datenbank-Guru