Ergebnis 1 bis 15 von 15

Thema: [SQL] Zwischenzeilen berechnen

  1. #1
    Freier Denker
    Registriert seit
    Jul 2013
    Beiträge
    506

    [SQL] Zwischenzeilen berechnen

    Hallo Leute,
    ich stehe gerade ein wenig auf dem Schauch, vielleicht hat ja jemand einen guten Tipp für mich.

    Ich habe eine Tabelle mit einer ID, einem Timestamp und diversen Spalten mit irgendwelchen numerischen Werten, für die Frage unerheblich.

    Aufgrund diverser Gründe wird die Tabelle allerdings nicht sekündlich mit Werten gefüttert sondern verliert über den Tag etwa 2000 Sekunden. Selten mehr als 2 Sekunden am Stück.
    Mein Ziel ist es nun, dass ich über ein irgendwie geartetes SELECT die fehlenden Werte approximiert mit in meinem SQL-Ergebnis habe.

    Beispiel:
    Id Timestamp Value
    1 15:00:00 6
    2 15:00:01 8
    (berechnet) 15:00:02 (8+12)/2 = 10
    3 15:00:03 12

    Mit diesem SELECT würde ich mir dann einen View speichern und mit diesem dann weitere Verarbeitungen angehen. Wie gehe ich sowas an? SQL (spezielle MySQL) bietet dafür bestimmt eine Möglichkeit. Gibt es da vielleicht auch irgendwas zu beachten, was die Performance angeht? Es sind eine ganze Menge Einträge die da mitunter gefüllt werden müssten.

    Meine rohen Gedanken, die ich derzeit nicht im Stande bin zusammenzubringen:
    Code (MySQL):
    1. # mit @ kann man eigene Variablen einfügen
    2. SELECT `-` as id, (t1.timestamp+1) as timestamp, (t1.value + t2.value)/2 as value FROM tbl as t1 INNER JOIN tbl as t2 WHERE t1.timestamp +1 <> t2.timestamp AND t1.Id +1 = t2.Id;
    3. # man kann mit union dann entsprechend die Ergebnisse vereinen
    4.  
    5. # Das muss aber irgendwie noch in einer Art Schleife gepackt werden oder so.
    6.  
    Hat hier jemand einen Tipp für mich, wie ich das geschickt umsetzen kann? Bin ich darauf angewiesen alle x Sekunden / Minuten ein Skript laufen zu lassen, was fehlende Datensätze findet und berechnet/einfügt? Das muss doch auch irgendwie so gehen.

    Grüße!

  2. #2

    Re: [SQL] Zwischenzeilen berechnen

    Ich verstehe die Frage nicht genau. Möchtest Du on-the-fly Ergebnisse hinzufügen?
    Also werden diese Daten zur Echtzeit erhoben und Du möchtest immer wieder diese Fehlzeiten aktualisieren?

    Oder reicht es Dir, die Daten zu erheben und dann alle Fehlzeiten auf einmal hinzuzufügen?

    Im ersten Fall würde ich schon direkt beim INSERT-Befehl ansetzen und da ggf. die Zwischenwerte hinzufügen. Und das dann nicht datenbankseitig, sondern softwareseitig.

    Der zweite Fall wäre eigentlich genau das, was Du schon da stehen hast.

  3. #3
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Zitat Zitat von MingsPing Beitrag anzeigen
    Also werden diese Daten zur Echtzeit erhoben und Du möchtest immer wieder diese Fehlzeiten aktualisieren?
    Die Daten werden von einem Skript jede Sekunde (oder zumindest annähernd) in die Datenbank eingefügt.
    Ich möchte die fehlenden Datensätze bis jetzt und auch zukünftige mit einer Abfrage "auffüllen" lassen.

    Ich würde gerne eine unveränderte Tabelle mit echten ausgelesenen Werten behalten. Daher kam die Idee, dass ich mit einem SELECT die fehlenden Daten irgendwie bestimmen lasse und einfach um dieses SELECT einen VIEW baue, sodass ich die virtuell hinzugefügten Daten wie eine reale Tabelle behandeln kann.

    Nur wenn diese Möglichkeit ausgeschlossen ist, würde ich mich an die entsprechenden Stellen wagen und die Daten fix in die Datenbank einfügen.

  4. #4

    Re: [SQL] Zwischenzeilen berechnen

    Auf die Schnelle fällt mir keine schöne (only-SQL) Lösung für das Problem ein, wenn zwischen zwei Zeitpunkten variabel viele weitere fehlen können. Bin aber auch kein SQL-Profi, und würde (deshalb) das Problem auf die Software- (Skript-)Seite verschieben.

    Wenn immer nur höchstens ein Zeitpunkt fehlt, führt ja folgendes zum Ziel:

    Code (MySQL):
    1.  
    2. create table abc(id int, time int, val int);
    3.  
    4. insert into abc values(1, 1, 5);
    5. insert into abc values(2, 3, 7);
    6. insert into abc values(3, 4, 8);
    7. insert into abc values(4, 5, 9);
    8. insert into abc values(5, 7, 11);
    9.  
    10. select 11111111,(abc1.time + 1), ((abc1.val + abc2.val) / 2)
    11. from abc as abc1, abc as abc2
    12. where abc1.id = abc2.id - 1
    13. (not abc2.time = abc1.time + 1)
    14.  
    15.  
    16. select * from abc;
    17.  
    Für diesen Beitrag bedankt sich Roin
    Geändert von MingsPing (20.11.19 um 22:33 Uhr)

  5. #5
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Stimmt, das würde in dem Fall klappen. Leider reicht mir das so in der Form halt nicht. ich bin mir relativ sicher, dass es rein mit SQL gehen müsste. Vielleicht hat ja in den nächsten Tagen da noch jemand einen Hinweis. Sonst muss ich mir wohl überlegen, wie ich an entsprechende Stellen irgendwo noch weitere kleine Skripte zwischenhängen kann...

  6. #6

    Re: [SQL] Zwischenzeilen berechnen

    Zitat Zitat von Roin Beitrag anzeigen
    Sonst muss ich mir wohl überlegen, wie ich an entsprechende Stellen irgendwo noch weitere kleine Skripte zwischenhängen kann...
    Das wäre für mich die geschickteste Wahl:
    Vor jedem INSERT eines Datensatzes X fragst Du den letzten (in die Datenbank eingefügten) Datensatz ab und berechnest hieraus, ob etwas fehlt. Wenn ja, fügst Du die fehlenden Datensätze in eine andere Tabelle (Du möchtest sie ja getrennt haben), ein und dann Deinen neuen Datensatz X in die eigentliche Tabelle.
    Hier könntest Du dann sogar geschickt IDs vergeben.
    Für die Abfrage kannst Du dann die beiden Tabellen mit UNION verschmelzen.

    Vorteil ist, dass Du nicht bei jeder Abfrage erneut diese Fehlzeilen identifizieren und berechnen musst, sondern nur ein mal beim INSERT.

  7. #7
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Ich habe gerade ein paar Minuten Zeit gehabt und habe mich nun dran gesetzt ein kleines Skript zu schreiben, welches mir entsprechende Daten auslesen sollte, die Zwischenräume berechnen und einfügen.
    Code (MySQL):
    1. SELECT t1.timepoint as Startpoint, (SELECT timepoint FROM data WHERE timepoint < Startpoint ORDER BY timepoint DESC LIMIT 1) as Endpoint FROM data as t1 LIMIT 20;
    Ungefähr so habe ich mir das vorgestellt. Möglicherweise ist die Sortierung in der Subquery noch falsch und ich muss ASC statt DESC nutzen. Allerdings braucht diese Query ziemlich lange. Nun wollte ich hier mal fragen, ob jemand eine Idee hat, wie ich die entsprechende Query nennenswert beschleunigen kann oder ob einfach mein Datenbank-System gerade stark ausgelastet ist?
    timepoint ist in der Tabelle bereits ein Unique-Index - also auch entsprechend indiziert. Da lässt sich also nichts mehr verbessern. Vielleicht kann man die Query aber noch umstellen oder so, damit die fixer wird. Später werde ich nämlich das Limit entfernen, wenn alles klappt, und das Skript einmal über die Datenbank laufen lassen. Das wird dann vermutlich einige Stunden dauern.

    Beispiel-Output:
    Startpoint Endpoint
    2019-10-01 13:27:26 NULL
    2019-10-01 13:27:27 2019-10-01 13:27:26
    ... ...
    2019-10-01 13:27:37 2019-10-01 13:27:35


    Zitat Zitat von MingsPing Beitrag anzeigen
    Vor jedem INSERT eines Datensatzes X fragst Du den letzten (in die Datenbank eingefügten) Datensatz ab und berechnest hieraus, ob etwas fehlt.
    Diesen Teil habe ich übrigens bereits in mein Skript integriert. Klappt auch soweit, wie es scheint. Ich möchte nun nur noch die alten Daten ergänzen, daher die oben geschriebene Frage.

    EDIT:
    Zweite Frage:
    Die Erweiterung der Query wie folgt:
    Code (MySQL):
    1. SELECT t1.timepoint as Startpoint, (SELECT timepoint FROM data where timepoint < Startpoint ORDER BY timepoint DESC LIMIT 1) as Endpoint FROM data as t1 HAVING SECOND(TIMEDIFF(Endpoint, Startpoint)) > 1 LIMIT 20;
    Ist langsamer, als wenn die entsprechende Bedingung in einer WHERE-Clause verbaut wäre. WHERE wird nämlich vor der Rückgabe berücksichtigt, HAVING erst wenn die Ergebnisse vorliegen. Leider darf man bei MySQL anscheinend keinen Alias in der Where-Clause benutzen. Zumindest wird mir das regelmäßig von dem Interpreter vorgeworfen. Kann ich das noch irgendwie umbauen, sodass ich es in der Where-Clause unterbringen kann und somit die Ergebnisse bereits vorher reduzieren kann?

    EDIT 2:
    Nur so als Info. Die Query mit dem HAVING braucht bei meinem Einplatinen-Rechner auf dem die Datenbank liegt etwa 30min bei rund 1.5 Millionen Zeilen. Und ich habe noch weitere Daten, welche ebenfalls rund 1.5 Millionen Zeilen ergeben. Diese Query zu beschleunigen, wäre also durchaus nicht schlecht.
    Geändert von Roin (18.12.19 um 00:04 Uhr) Grund: Tabelle angefügt

  8. #8

    Re: [SQL] Zwischenzeilen berechnen

    Wieso die Subquery? Gilt nicht, dass die Datensätze geordnet nach IDs die gleiche Anordnung ergeben wie Ordnung nach timepoints?

    Wenn man obiges annehmen kann, so kannst Du die Werte, die noch fehlen, mit folgender Query

    Code (MySQL):
    1.  
    2. create table abc(id int, time int, val int);
    3.  
    4. insert into abc values(1, 1, 5);
    5. insert into abc values(2, 3, 7);
    6. insert into abc values(3, 4, 8);
    7. insert into abc values(4, 5, 9);
    8. insert into abc values(5, 7, 11);
    9.  
    10. select abc1.id, abc1.time, abc1.val, abc2.id, abc2.time, abc2.val
    11. from abc as abc1, abc as abc2
    12. where abc1.id = abc2.id - 1
    13. (not abc2.time = abc1.time + 1);
    zurückgeben lassen.

    Spoiler: 


    Ergebnis obiger Abfrage wäre übrigens
    Code (MySQL):
    1.  
    2. 1|1|5|2|3|7
    3. 4|5|9|5|7|11
    4.  
    da zwischen ID 1 und ID 2 Timeponints fehlen und zwischen 4 und 5.


    Diese Tabelle würde ich einem Skript (da kannst Du quasi alles, was Du oben schon für das andere Skript geschrieben hast, verwenden) übergeben, das die fehlenden Einträge nachträgt.

  9. #9
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Zitat Zitat von MingsPing Beitrag anzeigen
    Wieso die Subquery? Gilt nicht, dass die Datensätze geordnet nach IDs die gleiche Anordnung ergeben wie Ordnung nach timepoints?
    Das gilt leider nicht perse. Das Skript, welches die Daten ausließt und in die Datenbank füttert läuft erst seit ein paar Monaten. Es existieren allerdings in der Datenbank auch Datensätze, welche davor durch andere Quellen erzeugt wurden. Zudem gab es zwischendurch beispielsweise Stromausfälle, wodurch wieder Daten nachträglich eingepflegt wurden. Und wie wir bereits vorher besprochen hatten, fehlen mitunter mehr als eine Sekunde an Daten. Daher kam ich auf die Query von mir.

    EDIT:
    Es scheint so, als wenn MySQL die Subquery von mir nicht mal zu einem sinnvollen Zeitpunkt ausführt. Ich möchte eigentlich, dass er erst die WHERE clause ausführt, also beispielsweise den Bereich der Tabelle mittels IDs auf einen Bruchteil reduzieren. Anschließend möchte ich den nächsten timestamp zu einem jeden Timestamp suchen und dann direkte Nachbarn wegwerfen.
    Ich habe auch überlegt die Ergebnisse in einer Tabelle zu speichern, um die später durch ein Skript abarbeiten zu können und die eigentliche Abfrage zu reduzieren, sodass diese nur auf den "neuen" Daten laufen muss und nicht jedes Mal stundenlang suchen muss um vielleicht ein neues Ergebnis zu liefern.

    Scheint derzeit nicht so gut zu klappen...

    EDIT2:
    Aktuelle Lösung:
    • Ein Prozess befüllt weiterhin ganz normal die Tabelle wie es zuvor auch schon war.
    • Ein Prozess ließt neue Ids aus und kopiert diese mit dem timestamp in eine zweite Tabelle, genannt "missing". Struktur siehe unten.
    • Ein anderer Prozess sucht für jede Zeile in der Tabelle missing den zugehörigen nächsten Zeitpunkt (Endpoint) und fügt diesen in die Tabelle ein.
    • Ein letzter Prozess geht die Tabelle durch und fügt im Zweifel fehlende Daten in die eigentliche Tabelle ein.


    Dabei sieht die missing Tabelle wie folgt aus:
    id gleiche id wie in der data-Tabelle
    Startpoint timestamp aus data-Tabelle
    Endpoint Null oder timestamp aus data-Tabelle
    exe Wurde die entsprechende Zeile bereits verarbeitet?
    donot Hier fehlen zu viele Daten, bearbeite diese Zeile nicht.

    Benutze Queries sehen nun wie folgt aus:
    Code (MySQL):
    1.  
    2. /* Startzeitpunkte in die missing-Tabelle einfügen */
    3. /* Diese Query läuft alle paar Sekunden mit entsprechender Wartezeit, wodurch die Missing-Tabelle nach und nach gefüllt wird und später fast nichts mehr tut. */
    4. INSERT INTO missing(id, Startpoint) SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 1000;
    5.  
    6. /* Endzeitpunkte auslesen. Diese liegen in der Regel eine Sekunde nach dem Startzeitpunkt. Möglicherweise aber auch mehrere Sekunden oder sogar Stunden nach dem Startzeitpunkt. */
    7. UPDATE missing SET Endpoint = (SELECT timepoint FROM data WHERE timepoint > missing.Startpoint ORDER BY timepoint LIMIT 1) WHERE id=(SELECT MIN(id) FROM missing WHERE Endpoint IS NULL);
    8.  
    9. /* Ignoriere Zeilen, welche nur eine Sekunde Abstand haben; hier fehlen keine Daten */
    10. UPDATE missing SET exe=1 WHERE exe = 0 AND donot = 0 AND SECOND(TIMEDIFF(Endpoint, Startpoint)) = 1 LIMIT 100;
    11.  
    12. /* Lese Zeilen aus, wo der Zeitabstand größer als eine Sekunde ist. */
    13. SELECT id, Startpoint, Endpoint, SECOND(TIMEDIFF(Endpoint, Startpoint)) as Tdiff FROM missing WHERE exe = 0 AND donot = 0 AND SECOND(TIMEDIFF(Endpoint, Startpoint)) > 1 ORDER BY id LIMIT 100;
    14.  
    15. /* Lese die Originaldaten aus der data Tabelle aus, welche zu dem entsprechenden Startpoint und Endpoint gehören. */
    16. SELECT * data WHERE timepoint = %s;
    17.  
    18. /* Berechne notwenige Daten zum einfügen. Füge diese in die Tabelle ein. Danach update die missing-Tabelle: */
    19. UPDATE missing SET exe = 1 WHERE id=%s
    20.  
    Die Missing-Tabelle hat einen Primary-Key auf der id-Spalte, einen Unique-Index auf der Startpoint-Spalte und je einen normalen Index auf der Endpoint und exe-Spalte.
    Die data-Tabelle hat neben dem Primary-Key auf der id-Spalte noch einen Unique-Index auf der timepoint-Spalte.
    Geändert von Roin (18.12.19 um 16:50 Uhr)

  10. #10

    Re: [SQL] Zwischenzeilen berechnen

    War die Woche etwas eingespannt und konnte nicht alles verfolgen.
    Klappt es nun? Sieht ja schon alles etwas komplex aus... .

    Ansonsten hätte ich noch eine weitere Idee.

  11. #11
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Andere Ideen nehme ich gerne entgegen.
    Also "klappen" tut es. Allerdings ist der Rechner halt nicht sonderlich flink und der arbeitet pro Tag nur einige 10k Zeilen mehr auf als hinzugefügt werden. Bei inzwischen knapp 6 Millionen Zeilen ist das noch nicht sonderlich schnell...

    Es würde halt ungemein helfen, wenn man irgendwie die Query, wo der zugehörige Endzeitpunkt gesucht wird, irgendwie beschleunigen könnte. Aktuell bin ich mir da noch nicht so sicher, wie ich das anstellen kann...

  12. #12

    Re: [SQL] Zwischenzeilen berechnen

    Mein Vorschlag wäre, die Daten so zu migrieren, dass obiges gilt ("Gilt nicht, dass die Datensätze geordnet nach IDs die gleiche Anordnung ergeben wie Ordnung nach timepoints?").

    Damit sparst Du Dir die ganzen Subquerys, die das ganze ja so langsam machen.

    Das heißt, Du musst nur einmal die Tabelle sortieren nach Timestamps, dann diese sortierte Tabelle in eine neue einfügen mit IDs, deren Anordnung dann auch der der Timestamps entspricht.
    Geändert von MingsPing (24.12.19 um 13:42 Uhr) Grund: Link eingefügt

  13. #13
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Problem dabei wäre halt, dass die neuen Datensätze, die eingefügt werden dadurch nicht zwingend die richtige ID bekommen und ständig neu zu sortieren ist auch nicht das Ziel.
    Aufgrund der komplexen Queries bin ich dazu übergegangen die Daten in die ursprüngliche Tabelle einzufügen.
    Inzwischen habe ich durch geschicktes ignorieren von einzelnen Zeilen rund 70% der Datensätze nachpflegen können, die fehlten. Ich hole also stetig auf und noch vor Neujahr sollte das Skript kaum noch Leistung in Anspruch nehmen.

  14. #14

    Re: [SQL] Zwischenzeilen berechnen

    Zitat Zitat von Roin Beitrag anzeigen
    Inzwischen habe ich durch geschicktes ignorieren von einzelnen Zeilen rund 70% der Datensätze nachpflegen können
    Na das klingt doch vielversprechend. Dann hast Du's ja bald und das ganze ist erledigt

  15. #15
    Freier Denker

    (Threadstarter)


    Registriert seit
    Jul 2013
    Beiträge
    506

    Re: [SQL] Zwischenzeilen berechnen

    Neues Problem:
    Ich habe eine "Missing" Tabelle, welche die Zeitpunkte speichert, welche ergänzt werden müssen.

    Folgendes dauert nicht mal einen Bruchteil einer Sekunde:
    Code (MySQL):
    1. SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 100;
    Folgendes dauert aktuell undefinierbar lange (mehr als ein paar Minuten):
    Code (MySQL):
    1. INSERT INTO missing(id, Startpoint) (SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 100);
    Wieso? Hat da jemand einen Tipp? Ich habe noch freien Speicher, zum Testen hat kein Prozess auf die Datenbank zugegriffen, kein vorhandener Transaction-Lock oder dergleichen.

    EDIT:
    Wieso das der Fall ist, das das so lange dauert ist mir weiterhin unbekannt.
    Aber die Lösung ist die Query wie folgt umzuschreiben:
    Code (MySQL):
    1. INSERT INTO missing(id, Startpoint) SELECT * FROM (SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 10000) sub1;
    (Quelle)
    Geändert von Roin (26.12.19 um 19:16 Uhr)

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •