Schwindel bei 5fach SubSelect o.ä.

[ippurk]

Hallo zusammen,

eindeutigerweise übersteigt folgendes Problem meinen Horizont:

Vorhandene Tabellen:

Liste aller durchgeführten Flüge, darin:

Datum Start Ende, von wo nach wo, mit welchem Flugzeug und mit welcher Crew

(der Eintrag Crew besteht leider aus so etwas: "GRE HAH ULM TSA" jeder "Dreilettercode" ist ein Besatzungsmitglied, kleines Unterproblem am Rande)

und:

Liste von "Drei-Letter-codes" aller Besatzungsmitglieder mit Code, Vor- und Nachnamen

Die gewünschte Ergebnisliste sieht so aus:

Crewmember (Code), Dienstzeit pro Monat (alle Monate einzeln), Anzahl der Arbeitstage, Anzahl der Übernachtungen.

Die Dienstzeit ergibt sich aus dem ersten und letzten Flug pro Tag plus einer fixen Zeitspanne,
Anzahl der Arbeitstage ist wohl klar (es gibt erst mal keine Flüge, die über Mitternacht gehen) und
eine Übernachtung ist dadurch gekennzeichnet, dass der letzte Flug nicht in Berlin geendet hat, da alle Besatzungsmitglieder in Berlin wohnen.

Die Dienstzeitberechnung konnte ich mit Hilfe von Falk Prüfer schon halbwegs in den Griff bekommen (läuft allerdings noch nicht so ganz rund).

Aber vielleicht hat jemand Spaß an einer Komplettlösung, weil mir, wie gesagt leicht schwindelig wird bei dieser Unterabfragerei. Falls jemandem was dazu einfällt, vielleicht könnte das auch mit ein paar kleinen Erläuterungen versehen werden, ich möchte das ganze ja wenigstens halbwegs durchschauen.

Also dann...

[dibo33]

Eine tabellarische Angabe von Beispielsätzen und deren gewünschtes Ergebnis dürfte für mehr Klarheit sorgen.

[ippurk]

Ok, so sehen die Ursprungsdaten ungefähr aus:

Tabelle der Flüge:

Startzeit___________| Endzeit____________| Abflug | Ankunft | Crew
2010-09-01 09:05:00 | 2010-09-01 10:05:00 | TXL__ | HAM___|TZB POP KRA HRA
2010-09-01 10:35:00 | 2010-09-01 11:25:00 | HAM__ | TXL___|TZB POP KRA HRA
2010-09-01 09:05:00 | 2010-09-01 10:10:00 | TXL__ | DUS___|GAG HHI HIH HOH
2010-09-01 10:45:00 | 2010-09-01 12:05:00 | DUS__ | TXL___|GAG HHI HIH HOH
2010-09-01 12:45:00 | 2010-09-01 14:05:00 | TXL__ | NUE___|GAG HHI HIH HOH

Tabelle der "Drei-Letter-Codes":

Code | Vorname | Nachname
TZB__| Theodor | zu Butterberg
HHI__| Heinz__ | Heinrich
etc.

Die Ergebnisliste sollte so aussehen:

Code | Monat___ | Dienstzeit pro Monat | Anzahl der Arbeitstage | Anzahl der Übernachtungen
TZT __| Jan 2010 | 123:45:00________| 20_________________| 5
TZT __| FEB 2010 | 125:35:00________| 19_________________| 8
HHI__| Jan 2010 | 153:45:00_________| 21_________________| 7
HHI__| Feb 2010 | 144:45:00_________| 15_________________| 10

Die Unterstriche sind nur für die Formatierung da, irgendwie gibts hier keine Tabs oder so was.

[dibo33]

Mangels korrektem Code habe ich mal DUS für Berlin genommen

SELECT d.code, d.vorname, d.nachname, 
    DATE_FORMAT(f.startzeit, '%b %Y') monat,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(f.endzeit, f.startzeit)))) dienstzeit,
    COUNT(*) arbeitstage, 
    COUNT(NULLIF(f.ankunft, 'DUS')) uebernachtung
FROM fzdlc d
JOIN fzfluege f
    ON FIND_IN_SET(d.code, REPLACE(f.crew, ' ', ',')) > 0
GROUP BY d.code, d.vorname, d.nachname, YEAR(f.startzeit), MONTH(f.startzeit)

[ippurk]

Hallo dibo33,

erst mal vielen Dank für die Mühe.

Nach erstem Drüberlesen: findet diese Abfrage denn den ersten und letzten Flug eines Tages des jeweiligen Crewmitgliedes, um die Dienstzeit zu berechnen ? TZB zum Beispiel fliegt ja am 1.9. erst von TXL (Berlin-Tegel) nach Hamburg und dann wieder zurück.

Die Befehle FIND_IN_SET und REPLACE sind mir auf jeden Fall neu, ich werd gleich mal probieren, das so umzusetzen.

Manchmal weiß man einfach nicht, wonach man in diesen Manuals suchen soll.

EDIT:

Ok, habs mal sozusagen 1:1 übernommen, und es kommt tatsächlich ungefähr die gewünschte Ausgabe dabei heraus. Vielen Dank !

Nur werden bei dieser Abfrage, wie vermutet, nicht die unterschiedlichen Flüge pro Tag berücksichtigt, sondern einfach nur die einzelnen Flüge. Und natürlich fließen die Pausen zwischen den Flügen eines Tages nicht in die Dienstzeit ein. Das ergibt also teilweise über 60 Arbeitstage pro Monat.

[dibo33]

Nach erstem Drüberlesen: findet diese Abfrage denn den ersten und letzten Flug eines Tages des jeweiligen Crewmitgliedes, um die Dienstzeit zu berechnen ? TZB zum Beispiel fliegt ja am 1.9. erst von TXL (Berlin-Tegel) nach Hamburg und dann wieder zurück.

Hm, nein es wird nur die reine Flugzeit berechnet, ich werde es nochmals überdenken.

Edit: bleibt die Crew den ganzen Tag zusammen?

[ippurk]

die Crew bleibt nicht unbedingt den ganzen Tag zusammen, es kommt durchaus vor, daß da Wechsel stattfinden.

[dibo33]

Hm, sollte so in etwa hinkommen

SELECT code,
    DATE_FORMAT(startzeit, '%b %Y') monat,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(endzeit, startzeit)))) dienstzeit,
    COUNT(*) arbeitstage, 
    COUNT(NULLIF(ankunft, 'TXL')) uebernachtung
FROM (
    SELECT fz.code, fz.startzeit, fz.endzeit, (
        SELECT ankunft
        FROM fzfluege
        WHERE endzeit = fz.endzeit
            AND FIND_IN_SET(fz.code, REPLACE(crew, ' ', ',')) > 0
    ) ankunft
    FROM (
        SELECT d.code, MIN(fe.startzeit) startzeit, MAX(fe.endzeit) endzeit
        FROM fzdlc d
        JOIN fzfluege fe
            ON FIND_IN_SET(d.code, REPLACE(fe.crew, ' ', ',')) > 0
        GROUP BY d.code, DATE(fe.endzeit)
    ) fz
) f
GROUP BY code, YEAR(startzeit), MONTH(startzeit)

[ippurk]

Mannomann, ganz schön unleserlich, dieser seltsame SQL-Code. Ich werd morgen mal versuchen, das zu verstehen.

Danke nochmal und schönen Abend noch.

[dibo33]

Sehe die Abfrage einfach von innen nach außen.

Ausgehend von deinen Beispielsätze:

        SELECT d.code, MIN(fe.startzeit) startzeit, MAX(fe.endzeit) endzeit
        FROM fzdlc d
        JOIN fzfluege fe
            ON FIND_IN_SET(d.code, REPLACE(fe.crew, ' ', ',')) > 0
        GROUP BY d.code, DATE(fe.endzeit)

Ergibt für jeden code die startzeit und endzeit pro tag

HHI | 2010-09-01 09:05:00 | 2010-09-01 14:05:00
TZB | 2010-09-01 09:05:00 | 2010-09-01 11:25:00

Für die Übernachtungen brauchen wir noch den Zielflughafen der endzeit

    SELECT fz.code, fz.startzeit, fz.endzeit, (
        SELECT ankunft
        FROM fzfluege
        WHERE endzeit = fz.endzeit
            AND FIND_IN_SET(fz.code, REPLACE(crew, ' ', ',')) > 0
    ) ankunft
    FROM (
        SELECT d.code, MIN(fe.startzeit) startzeit, MAX(fe.endzeit) endzeit
        FROM fzdlc d
        JOIN fzfluege fe
            ON FIND_IN_SET(d.code, REPLACE(fe.crew, ' ', ',')) > 0
        GROUP BY d.code, DATE(fe.endzeit)
    ) fz

Ergibt

HHI | 2010-09-01 09:05:00 | 2010-09-01 14:05:00 | NUE
TZB | 2010-09-01 09:05:00 | 2010-09-01 11:25:00 | TXL

Von diesem Datenpool ausgehend können nun die Berechnungen erfolgen

SELECT code,
    DATE_FORMAT(startzeit, '%b %Y') monat,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(endzeit, startzeit)))) dienstzeit,
    COUNT(*) arbeitstage, 
    COUNT(NULLIF(ankunft, 'TXL')) uebernachtung
FROM (
    SELECT fz.code, fz.startzeit, fz.endzeit, (
        SELECT ankunft
        FROM fzfluege
        WHERE endzeit = fz.endzeit
            AND FIND_IN_SET(fz.code, REPLACE(crew, ' ', ',')) > 0
    ) ankunft
    FROM (
        SELECT d.code, MIN(fe.startzeit) startzeit, MAX(fe.endzeit) endzeit
        FROM fzdlc d
        JOIN fzfluege fe
            ON FIND_IN_SET(d.code, REPLACE(fe.crew, ' ', ',')) > 0
        GROUP BY d.code, DATE(fe.endzeit)
    ) fz
) f
GROUP BY code, YEAR(startzeit), MONTH(startzeit)

HHI | Sep 2010 | 05:00:00 | 1 | 1
TZB | Sep 2010 | 02:20:00 | 1 | 0

Das wars.

[ippurk]

ok, komme der Sache gedanklich näher.

Allerdings gibt er mir in folgendem Teil die Fehlermeldung 1349: View's SELECT contains a subquery in the FROM clause, was doch eigentlich erlaubt ist, oder ?

SELECT fz.code, fz.startzeit, fz.endzeit, (
        SELECT ankunft
        FROM fzfluege
        WHERE endzeit = fz.endzeit
            AND FIND_IN_SET(fz.code, REPLACE(crew, ' ', ',')) > 0
    ) ankunft
    FROM (
        SELECT d.code, MIN(fe.startzeit) startzeit, MAX(fe.endzeit) endzeit
        FROM fzdlc d
        JOIN fzfluege fe
            ON FIND_IN_SET(d.code, REPLACE(fe.crew, ' ', ',')) > 0
        GROUP BY d.code, DATE(fe.endzeit)
    ) fz

Das SELECT in der Haupt-FROM-Klausel alleine funktioniert auf jeden Fall richtig. (das "fz")

EDIT: Ok, habs gefunden, geht nicht, weil ich hier einen View erstellen will.

[ippurk]

So, jetzt ist alles weitestgehend verstanden und funktioniert bestens, bis auf die Zeit, die die Abfrage braucht.

Zur Zeit sind da ca. 2000 Flüge und 350 Drei-Letter-Codes in den Daten und die Abfrage braucht gut 60 sec. Ist das zu erwarten oder geht das schneller ?

[Falk Prüfer]

Hallo,

bei dem "verkorksten" Datenmodell und der Form der Abfrage kannst du sicherlich keinen Performance-Turbo erwarten.

Du kannst dir ja mal mit EXPLAIN den Ausführungspfad ansehen, um evtl. Optimierungspunkte zu finden.

Gruß Falk

[ippurk]

aha, was meinst du mit "verkorstem Datenmodell" ? Wie könnte man das denn optimieren ?

[Falk Prüfer]

aha, was meinst du mit "verkorstem Datenmodell" ?

Wenn mehrere Entitäten durch Komma, Semikolon, Leerzeichen oder sonstwas getrennt in EINEM Feld stehen, dann ist das "verkorkst" und hat nichts mit Normalisierung zu tun.

Gruß Falk