Festtext in korreliertem Update oder mit Abfrageergebnissen rechnen

[Muschelpuster]

Moin zusammen,

ich bilde für die in meiner Datenbank eintreffenden Datensätze mit Messwerten in Form von Zeiträumen über Trigger Schnellsummen, da die gewünschte Auswertung aller Datensätze in einem Zeitraum bei größeren Datenmengen zu lange dauerten. Dank der Unterstützung hier funktioniert das einwandfrei, aber jetzt bin ich wieder an einem Punkt wo ich nicht weiter komme.
Hier mal eine Beispielhafte Konfig:

CREATE TABLE `daten` (
  `d_start` bigint(20) NOT NULL,
  `d_ende` bigint(20) NOT NULL,
  `d_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `daten`
  ADD PRIMARY KEY (`d_start`,`d_ende`,`d_messpunkt`);

INSERT INTO `daten` (`d_start`, `d_ende`, `d_messpunkt`) VALUES
(11, 20, 'P2'),
(18, 40, 'P1'),
(19, 31, 'P2'),
(20, 30, 'P1'),
(22, 40, 'P1')
(20, 49, 'P3');

CREATE TABLE `schnellsummen` (
  `s_zeit` bigint(20) NOT NULL,
  `s_zaehler` int(11) NOT NULL,
  `s_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `schnellsummen`
  ADD PRIMARY KEY (`s_zeit`,`s_messpunkt`);

DELIMITER $$
CREATE TRIGGER `Schnellsummen` AFTER INSERT ON `daten`
 FOR EACH ROW BEGIN
REPLACE INTO `messzeitpunkte` (`m_zeit`) VALUES (NEW.d_ende);
REPLACE INTO `messzeitpunkte` (`m_zeit`) VALUES (NEW.d_start);
REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
REPLACE INTO schnellsummen SELECT d_ende as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_ende >= NEW.d_start AND d_ende <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
END$$
DELIMITER ;

So kann ich jetzt aus den Schnellsummen sehr schnell auch längere Datenreihen lesen und visualisieren. Doch ich kann keine Datenreihe lesen, die z.B. eine Summe von den Messpunkten P1 und P2 abbildet. Wenn ich einfach sage, WHERE messpunkt = 'P1' or messpunkt = 'P2' bekomme ich ja eine völlig falsche Kurve.
Ich müsste also z.B. alle Werte von P1 im gewünschten Zeitraum lesen und dazu immer den direkt vor der gelesenen Zeit liegenden Wert von P2 addieren. Die Werte bekomme ich mit SQL gelesen, aber beim Addieren wird es wohl eng und folgenden Gedanken kann man wohl nicht abbilden, oder?

SELECT s_zeit AS Zeit,  ((SELECT s_zaehler FROM schnellsummen WHERE s_zeit <= Zeit AND s_messpunkt = 'P1' ORDER BY s_zeit DESC limit = 1) + (SELECT s_zaehler FROM schnellsummen WHERE s_zeit <= Zeit AND s_messpunkt = 'P2' ORDER BY s_zeit DESC limit = 1)) FROM schnellsummen WHERE (s_messpunkt = 'P1' OR s_messpunkt = 'P2') AND (s_zeit >= 'Startzeit_Auswertung' OR s_zeit <= 'Endzeit_Auswertung');

Also bliebe es ja den Trigger zu erweitern und eine neue Schnellsumme zu bilden:

REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = `P1` or `d_messpunkt` = `P2`))  AS Zaehler, "Festtext_für_P1&P2* FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND (`d_messpunkt` = `P1` or `d_messpunkt` = `P2`);

Aber wenn ich das richtig sehe, bekomme ich da auch den festen Text nicht rein.
Hat da noch jemand eine Idee, wie ich auch diese Hürde nehmen kann?

Niels

 

[dabadepdu]

Ist Dein Beispiel Code vielleicht nicht richtig konsistent bzw. vollständig?
Es ist immer hilfreich, Beispieldaten anzugeben, in Deinem Fall z.B. Select auf Schnellsummen, vor Insert und nach Insert (das aufgeführt ist).
Ist Ergebnis, Soll Ergebnis ist auch nicht schlecht.
Die Challenge sollte nicht sein, die Frage zu verstehen oder das Gemeinte zu erraten, sondern eine Antwort zu geben.

 

[Muschelpuster]

Danke dabadepdu,

ich gebe Dir absolut Recht. Wenn man über dem Problem brütet ist man gerne mal viel zu tief drin und schreibt viel, aber nicht das Richtige. Ich versuche das mal mit einem einfachen Beispiel zu erklären, was mir gerade zu obigen Beispielwerten eingefallen ist. Nehmen wir an, ich habe 3 Parkplätze, P1-P3. Von jedem Parkvorgang habe ich 3 Informationen:

• Beginn des Parkvorganges (d_start)
• Ende des Parkvorganges (d_ende)
• genutzter Parkplatz (d_messpunkt)

Beidspieldaten hatte ich ja oben mal mit rein geschrieben:

INSERT INTO `daten` (`d_start`, `d_ende`, `d_messpunkt`) VALUES
(11, 20, 'P2'),
(18, 40, 'P1'),
(19, 31, 'P2'),
(20, 30, 'P1'),
(22, 40, 'P1')
(20, 49, 'P3');

Der Trigger schreibt mir meine gewünschten Ergebnisse in meine Schnellsummentabelle, so das ich da jetzt folgende Daten habe:

• 11 | 1 | P2
• 18 | 1 | P1
• 19 | 2 | P2
• 20 | 2 | P1
• 20 | 1 | P2
• 20 | 1 | P3
• 22 | 3 | P1
• 30 | 2 | P1
• 31 | 0 | P2
• 49 | 0 | P3
• 40 | 0 | P1

Die Auslastung der Parkplätze im Zeitraum 2-39 möchte ich gerne grafisch darstellen. Dazu habe ich ursprünglich nur alle Start- und Endzeiten als in eine extra Tabelle geschrieben (wie oben die 1. und 2. Zeile des Trigger noch zeigen), denn das sind ja die Zeitpunkte, zu denen sich die Auslastung ändern kann. Die habe ich jetzt ja in meinen Schnellsummen, also ginge das erst einmal ganz gut mit:

SELECT s_zeit as Zeit, (SELECT COUNT(`d_messpunkt`) FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = 'P1') AS Zaehler, s_messpunkt AS Messpunkt
FROM `schnellsummen` WHERE s_zeit >= 2 AND s_zeit <= 39 AND s_messpunkt = 'P1';

Das gibt eine schöne Timeline, die ich visualisieren kann:

• 18 | 1 | P1
• 20 | 1 | P1
• 22 | 3 | P1
• 30 | 2 | P1

So weit, so gut. Doch nun sind die Parkplätze groß und ich habe mehrere Tausend Messzeitpunkte pro Woche. Das funktioniert, nur meine Geduld ist gefragt. Daher habe ich mir gesagt, dass ich über die Trigger immer den Zeitraum des neuen Datensatzes neu berechne. So habe ich immer etwas 'Grundrauschen' auf dem Server, aber es klemmt nicht beim Abruf eines Reports und der kommt schön schnell. Dazu muss ich ja die Daten nun nur pur abfragen:

SELECT * FROM schnellsummen WHERE s_zeit >= 2 AND s_zeit <= 39 AND s_messpunkt = 'P1';

Doch nun will ich eine Summe von P1 und P2 über den Zeitraum ermitteln. Mit der langsamen Abfrage geht das (noch etwas langsamer):

SELECT s_zeit as Zeit, (SELECT COUNT(`d_messpunkt`) FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = 'P1' OR `d_messpunkt` = 'P2')) AS Zaehler, s_messpunkt AS Messpunkt
FROM `schnellsummen` WHERE s_zeit >= 2 AND s_zeit <= 39 AND (s_messpunkt = 'P1' OR s_messpunkt = 'P2');

Das geht aber nicht über meine Schnellsummen, da ich keinen einheitlichen Zeitpunkt habe. Ich müsste die Werte von P1 lesen und dazu den Wert mit dem nächstkleineren Zeitstempel von P2 addieren. Die einzelnen Abfragen sind ja nicht das Thema, aber ich muss sie noch addieren. Zudem fehlen mir dann teilweise die genauen Zeitpunkte der Zähleränderung, weil die Zeitstempel von P2 ja ignoriert würden. Am Ende ist das wieder ein recht aufwändiges und perfomancehungriges Statement, wenn es überhaupt möglich ist.
Besser wäre es sicher weitere Schnellsummen zu bilden, da die möglichen Kombinationen definiert werden können. Und eine weitere Kombination ist nachträglich auch noch machbar, man muss nur die Schnellsummen 1x über ein SQL-Statement berechnen lassen und etwas Geduld mitbringen.
Das ginge dann ja in etwa mit folgender Erweiterung des Triggers:

REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2')) AS Zaehler, ??'P1_und_P2'?? FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2');
REPLACE INTO schnellsummen SELECT d_ende as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2')) AS Zaehler, ??'P1_und_P2'?? FROM `daten` WHERE d_ende >= NEW.d_start AND d_ende <= NEW.d_ende AND (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2');

Und genau hier klemmt es jetzt zwischen den Fragezeichen, da ich an dieser Stelle nicht einfach einen festen String einbringen kann, bzw. nicht weiß wie.
Das Ergebnis soll ja am Ende lauten:

• 2 | 0 | P1_und_P2
• 11 | 1 | P1_und_P2
• 18 | 2 | P1_und_P2
  
• 19 | 3 | P1_und_P2
  
• 19 | 3 | P1_und_P2
  
• 20 | 3 | P1_und_P2
  
• 30 | 2 | P1_und_P2
  
• 31 | 1 | P1_und_P2

Sorry, war schon wieder mehr Text als ich schreiben wollte, aber vielleicht habe ich es ja geschafft, mein Gedanken nun halbwegs verständlich zu formulieren.

Niels

 

[dabadepdu]

Also was ist Deine Vorstellung von der Leistungskraft eines Lesers/Helfers hier? Ich zumindest kann nicht durch Lesen ein meterlanges Statement parsen und feststellen, dass da was nicht stimmt. Daten sind ein Anfang, aber wie heißen die Spalten? Nun dieses Fragen wären alle nicht brisant, wenn der gepostete Code durch eine DB Engine laufen würde/könnte. Die kann nämlich parsen, dafür ist sie gemacht.
Dumm nur, wenn die auch hängen bleibt, weil die Angaben lückenhaft oder falsch sind.
Also als Übung für Dich, nimm Dir eine leere DB, trage Deine Angaben aus dem Thread hier ein und schau, ob es überhaupt läuft. Modifiziere Deine Angaben, bis es läuft und poste es so, dass es jeder laufen lassen kann.
Schön ist auch, wenn die Daten zum Fließtext und zur Fragestellung passen.
Von - Bis Zeitangaben, die nicht (vollständig) in den Daten zu finden sind, machen sich bspw. nicht gut.

Ich kann verstehen, wenn einem bei einer Aufgabe der Schädel raucht. Genau das sollte Anlass sein, die Welt in möglichst kleine, überschaubare Bröckchen zu zerlegen und funktierende, kleine Code (und Daten) Beispiele zu stricken, die bis zu dem Problem führen, was man nicht gelöst bekommt. Oft platzt der Knoten schon genau bei dieser Tätigkeit. Wenn man sorgfältig "für andere" sein Problem aufbereitet.

 

[Muschelpuster]

Moin dabadepdu,

natürlich zerlege ich die Aufgaben in Teilaufgaben. So habe ich natürlich die Trigger-Jobs in Einzelteilen getestet. Und auch der Code ist getestet, bzw. sogar aus meiner Test-DB kopiert. Irgendwie ist da aber ein Komma verloren gegangen. Und der Export ist dahin gehend nicht konsistent, dass zuerst die Daten importiert werden und dann die erst die Trigger. Dann hätte ich natürlich au die Daten der Tabelle 'schnellsummen exportieren sollen. Also hier jetzt lauffähig:

CREATE TABLE `daten` (
  `d_start` bigint(20) NOT NULL,
  `d_ende` bigint(20) NOT NULL,
  `d_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `daten`
  ADD PRIMARY KEY (`d_start`,`d_ende`,`d_messpunkt`);

CREATE TABLE `schnellsummen` (
  `s_zeit` bigint(20) NOT NULL,
  `s_zaehler` int(11) NOT NULL,
  `s_messpunkt` varchar(20) COLLATE utf8mb4_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
ALTER TABLE `schnellsummen`
  ADD PRIMARY KEY (`s_zeit`,`s_messpunkt`);

DELIMITER $$
CREATE TRIGGER `Schnellsummen` AFTER INSERT ON `daten`
 FOR EACH ROW BEGIN
REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
REPLACE INTO schnellsummen SELECT d_ende as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and `d_messpunkt` = NEW.d_messpunkt) AS Zaehler, d_messpunkt AS Messpunkt FROM `daten` WHERE d_ende >= NEW.d_start AND d_ende <= NEW.d_ende AND d_messpunkt = NEW.d_messpunkt;
END$$
DELIMITER ;

INSERT INTO `daten` (`d_start`, `d_ende`, `d_messpunkt`) VALUES
(11, 20, 'P2'),
(18, 40, 'P1'),
(19, 31, 'P2'),
(20, 30, 'P1'),
(22, 40, 'P1'),
(20, 49, 'P3');

Think simple versuche ich auch immer, aber wie dokumentiert gelingt es mir nicht immer :-(
Mein Ziel ist es ja nun den Trigger um die Erzeugung von Schellsummen für ein Gesamtergebnis von P1+P2 zu ergänzen. Und hier ist ja die Frage wie ich dieses Ergebnis über einen Festtext in der Zieltabelle kennzeichnen kann:

REPLACE INTO schnellsummen SELECT d_start as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2')) AS Zaehler, ??'P1_und_P2'?? FROM `daten` WHERE d_start >= NEW.d_start AND d_start <= NEW.d_ende AND (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2');
REPLACE INTO schnellsummen SELECT d_ende as Zeit, (SELECT COUNT(`d_messpunkt`) as Messpunkt FROM `daten` where `d_start` <= Zeit and `d_ende`> Zeit and (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2')) AS Zaehler, ??'P1_und_P2'?? FROM `daten` WHERE d_ende >= NEW.d_start AND d_ende <= NEW.d_ende AND (`d_messpunkt` = 'P1' OR  `d_messpunkt` = 'P2');

Und genau hier klemmt es jetzt zwischen den Fragezeichen, da ich an dieser Stelle nicht einfach einen festen String einbringen kann, bzw. nicht weiß wie.

Eine (vielleicht nicht so schlechte) Alternative wäre für diese Schnellsummen eine extra Tabelle anzulegen. Dann muss ich mich nicht im Statement verrenken.

Niels