MYSQL: Werte ausdehnen auf mehrer Zeilen? (belegte Leitungen berechnen)

[dedlfix]

Tach!

Ich lass mal die bereits hervorgebrachten Argumente über die Sinnhaftigkeit einer Auflösung in 3 Millionen Schritten unbeachtet.

Ich habe hier Daten in einer MYSQL Tabelle, die kommen aus einer Telefonmaschine. Die Tabelle hat folgende (für diesen Fall wichtige) Felder: datum, anrufzeitpunkt (hh:mm:ss), dauer (in Sekunden), team

Das ist ein nicht sehr günstiger Aufbau für das Vorhaben. Wenn du die 3 Mio Datensätze aus der Zahlentabelle mit den Datensätzen der Telefonanlage verknüpfen möchtest, muss du für jeden Datensatz erstmal die Nummer in ein Datum bestehend aus Einzelwerten konvertiert werden oder aus den Einzelwerten eine Nummer gebildet werden. Und das für jeden Datensatz einzeln. Ein Index kann jedenfalls nicht verwendet werden. Ein Nebenziel sollte also sein, die Anzahl der Rechenoperationen auf ein Minimum zu reduzieren, am besten so, dass ein Index verwendet werden kann.

Das heißt, dass die Werte vorher so umformatiert werden müssen, dass sie sich direkt vergleichen lassen, ohne berechnet zu werden.

Du wirst die Daten aus der Telefonanlage sicher nicht als MySQL-Tabelle bekommen sondern sie zeilenweise in einer Datei vorliegen haben und sie irgendwie anders ins das DBMS einlesen. Dabei solltest du eine Normalisierung der Daten vornehmen, so dass zwei DATETIME-Felder entstehen. Eins für den Beginn und eins für das Ende der Leitungsbelegung.

Nun möchte ich berechnen wieviele Leitungen belegt waren. Belegt ist eine Leitung ab anrufzeitpunkt bis anrufzeitpunkt+dauer. Gibt es eine Möglichkeit dies in Mysql zu bewerkstelligen?

Sicherlich mehrere.

An der Zahlentabelle führt meines Erachtens kein Weg vorbei. Ansonsten gibt es nur Ergebnisse für Zeiten, in denen mindestens eine Leitung belegt war. Ein Weg ist, die Zahlentabelle zu selektieren und für jeden Eintrag in einer Subquery die betroffenen Datensätze zu zählen. Das ist vor allem einfacher zu verstehen und einfacher separat zu testen als ein großes Join-Gebilde anzufertigen. Man kann die Subquery nämlich einzeln auf einen bestimmten Zeitpunkt loslassen und so stichprobenhaft kontrollieren, ob das gelieferte Ergebnis stimmt, ohne erst eine große Ergebnismenge aus allen Daten zu erstellen.

Mein erst Versuch wäre, in der äußeren Query aus den Nummern in der Zahlentabelle und dem Monatsanfang einen konkreten Zeitpunkt als DATETIME zu berechnen. Das gibt dann zwar 3 Mio Berechnungen, aber das ist der Kompromiss. Sonst müsste die Zahlentabelle eine DATETIME-Tabelle werden und für jeden Monat neu erstellt werden. (Es kann aber durchaus sein, dass das aus Performancegründen so gemacht werden muss.) Jedenfalls kannst du diese Query schon einmal abschicken und dich überzeugen, dass sekundenfeine Ergebnisse entstehen und auch gleich einen Eindruck von der Laufzeit bekommen, wenn sie nur diese eine Aufgabe erfüllt.

Der nächste Schritt ist, die spätere Subquery zu erstellen - zunächst einmal ohne die äußere Query. Diese zählt alle Datensätze, für die ein bestimmter Zeitpunkt BETWEEN Beginn AND Ende liegt. An dieser Stelle findet keinerlei Berechnung statt, nur zwei Vergleiche, die das DBMS mit je einem Index auf den beiden Spalten sehr schnell erledigen können sollte. Stichproben liefern plausible (oder noch besser korrekte) Ergebnisse? Fein, dann die Subquery in die äußere Query einfügen und finale Tests starten. Laufzeit auch ok? Wenn nicht, dann kommt die Performanceoptimierung an die Reihe.

SUM(IF(anrufzeitpunkt<=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND) AND (anrufzeitpunkt+dauer)>=date_add('2015-04-01 00:00',INTERVAL nummern-1 SECOND),1,0)) as leitungen

Gibt es einen Grund, warum du für einen betroffenen Datensatz eine 1 aufsummierst, statt die betroffenen Datensätze zu zählen?

dedlfix.