• Hallo liebe Userinnen und User,

    nach bereits längeren Planungen und Vorbereitungen sind wir nun von vBulletin auf Xenforo umgestiegen. Die Umstellung musste leider aufgrund der Serverprobleme der letzten Tage notgedrungen vorverlegt werden. Das neue Forum ist soweit voll funktionsfähig, allerdings sind noch nicht alle der gewohnten Funktionen vorhanden. Nach Möglichkeit werden wir sie in den nächsten Wochen nachrüsten. Dafür sollte es nun einige der Probleme lösen, die wir in den letzten Tagen, Wochen und Monaten hatten. Auch der Server ist nun potenter als bei unserem alten Hoster, wodurch wir nun langfristig den Tank mit Bytes vollgetankt haben.

    Anfangs mag die neue Boardsoftware etwas ungewohnt sein, aber man findet sich recht schnell ein. Wir wissen, dass ihr alle Gewohnheitstiere seid, aber gebt dem neuen Board eine Chance.
    Sollte etwas der neuen oder auch gewohnten Funktionen unklar sein, könnt ihr den "Wo issn da der Button zu"-Thread im Feedback nutzen. Bugs meldet ihr bitte im Bugtracker, es wird sicher welche geben die uns noch nicht aufgefallen sind. Ich werde das dann versuchen, halbwegs im Startbeitrag übersichtlich zu halten, was an Arbeit noch aussteht.

    Neu ist, dass die Boardsoftware deutlich besser für Mobiltelefone und diverse Endgeräte geeignet ist und nun auch im mobilen Style alle Funktionen verfügbar sind. Am Desktop findet ihr oben rechts sowohl den Umschalter zwischen hellem und dunklem Style. Am Handy ist der Hell-/Dunkelschalter am Ende der Seite. Damit sollte zukünftig jeder sein Board so konfigurieren können, wie es ihm am liebsten ist.


    Die restlichen Funktionen sollten eigentlich soweit wie gewohnt funktionieren. Einfach mal ein wenig damit spielen oder bei Unklarheiten im Thread nachfragen. Viel Spaß im ngb 2.0.

[SQL] Zwischenzeilen berechnen

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
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:
IdTimestampValue
115:00:006
215:00:018
(berechnet)15:00:02(8+12)/2 = 10
315:00:0312

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:
[src=mysql]# mit @ kann man eigene Variablen einfügen
SELECT id, timestamp, value from tbl;
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;
# man kann mit union dann entsprechend die Ergebnisse vereinen

# Das muss aber irgendwie noch in einer Art Schleife gepackt werden oder so.
[/src]

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!
 

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #3
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.
 

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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:

[src=mysql]
create table abc(id int, time int, val int);

insert into abc values(1, 1, 5);
insert into abc values(2, 3, 7);
insert into abc values(3, 4, 8);
insert into abc values(4, 5, 9);
insert into abc values(5, 7, 11);

select 11111111,(abc1.time + 1), ((abc1.val + abc2.val) / 2)
from abc as abc1, abc as abc2
where abc1.id = abc2.id - 1
and
(not abc2.time = abc1.time + 1)

union

select * from abc;
[/src]
 
Zuletzt bearbeitet:

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #5
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...
 

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #7
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.
[src=mysql]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;[/src]
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:
StartpointEndpoint
2019-10-01 13:27:26NULL
2019-10-01 13:27:272019-10-01 13:27:26
......
2019-10-01 13:27:372019-10-01 13:27:35


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:
[src=mysql]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;[/src]
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.
 
Zuletzt bearbeitet:

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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

[src=mysql]
create table abc(id int, time int, val int);

insert into abc values(1, 1, 5);
insert into abc values(2, 3, 7);
insert into abc values(3, 4, 8);
insert into abc values(4, 5, 9);
insert into abc values(5, 7, 11);

select abc1.id, abc1.time, abc1.val, abc2.id, abc2.time, abc2.val
from abc as abc1, abc as abc2
where abc1.id = abc2.id - 1
and
(not abc2.time = abc1.time + 1);[/src]

zurückgeben lassen.

Ergebnis obiger Abfrage wäre übrigens
[src=mysql]
1|1|5|2|3|7
4|5|9|5|7|11
[/src]
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.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #9
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:
idgleiche id wie in der data-Tabelle
Startpointtimestamp aus data-Tabelle
EndpointNull oder timestamp aus data-Tabelle
exeWurde die entsprechende Zeile bereits verarbeitet?
donotHier fehlen zu viele Daten, bearbeite diese Zeile nicht.

Benutze Queries sehen nun wie folgt aus:
[src=mysql]
/* Startzeitpunkte in die missing-Tabelle einfügen */
/* 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. */
INSERT INTO missing(id, Startpoint) SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 1000;

/* Endzeitpunkte auslesen. Diese liegen in der Regel eine Sekunde nach dem Startzeitpunkt. Möglicherweise aber auch mehrere Sekunden oder sogar Stunden nach dem Startzeitpunkt. */
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);

/* Ignoriere Zeilen, welche nur eine Sekunde Abstand haben; hier fehlen keine Daten */
UPDATE missing SET exe=1 WHERE exe = 0 AND donot = 0 AND SECOND(TIMEDIFF(Endpoint, Startpoint)) = 1 LIMIT 100;

/* Lese Zeilen aus, wo der Zeitabstand größer als eine Sekunde ist. */
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;

/* Lese die Originaldaten aus der data Tabelle aus, welche zu dem entsprechenden Startpoint und Endpoint gehören. */
SELECT * FROM data WHERE id=%s;
SELECT * data WHERE timepoint = %s;

/* Berechne notwenige Daten zum einfügen. Füge diese in die Tabelle ein. Danach update die missing-Tabelle: */
UPDATE missing SET exe = 1 WHERE id=%s
[/src]

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.
 
Zuletzt bearbeitet:

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #11
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...
 

MingsPing

NGBler

Registriert
15 Juli 2013
Beiträge
345
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.
 
Zuletzt bearbeitet:

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #13
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.
 

Roin

Freier Denker

Registriert
22 Juli 2013
Beiträge
581
  • Thread Starter Thread Starter
  • #15
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:
[src=mysql]SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 100;[/src]
Folgendes dauert aktuell undefinierbar lange (mehr als ein paar Minuten):
[src=mysql]INSERT INTO missing(id, Startpoint) (SELECT id, timepoint FROM data WHERE id > (SELECT COALESCE(MAX(id), 0) FROM missing) ORDER BY id LIMIT 100);[/src]

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:
[src=mysql]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;[/src]
(Quelle)
 
Zuletzt bearbeitet:
Oben