datadexx
Goto Top

MySQL Abfrage um JOIN erweitern

Hallo SQL Experten,
ich bräuchte mal wieder eure Hilfe...

Ich habe folgende Abfrage für eine Statistik

SELECT SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) AS umsatz,   
SUM(IF(status != 3 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Untern_Verguetung, 0)) AS kosten,   
SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Untern_Verguetung, 0)) AS ergebnis,   
((SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0))) - SUM(IF(status != 3 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Untern_Verguetung, 0)))*100 / (SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0))) AS rendite,   
COUNT(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , aanr, 0)) AS sendungen,   
(SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0))) / COUNT(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , aanr, 0)) AS durchschnitt,   
(SUM(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Gesamt, 0)) - SUM(IF(status != 3 AND TransAnr = 'Gutschrift' AND TYPE=3 AND RG_NUMMER !='Dienstgut' , Gesamt, 0))) / COUNT(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , aanr, 0)) - (SUM(IF(status != 3 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , Untern_Verguetung, 0)) / COUNT(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , aanr, 0))) AS ergebnisd,   
DAYOFMONTH(DATE(DATUM)) AS day 
FROM posten 
WHERE EXTRACT(YEAR_MONTH FROM DATUM) = '{$yearMonth}'  
GROUP BY DATE(DATUM)

welche soweit auch funktioniert. Jedoch liefert die Zeile
COUNT(IF(status !=3 AND status !=4 AND TransAnr != 'Gutschrift' AND RG_NUMMER !='Dienstgut' , aanr, 0)) AS sendungen,  
ein falsches Ergebnis.

Besser wäre wenn ich einen JOIN auf die Tabelle aauftrag machen könnte, der mir dort mit COUNT(aanr) die Anzahl der Sendungen für den Tag holt, dessen Status !=3 ist. Der Status 3 besagt das der Auftrag storniert ist. Das Feld in der Tabelle aauftrag zum ermitteln des Datums für den Tag heißt ALadetag. Also im Grunde soll in der Tabelle aauftrag die Anzahl der Sendungen für den jeweiligen Tag ermittelt werden mit den gleichen Kriterien der restlichen Abfrage.

Vielen Dank vorab für eure Hilfe!

Content-Key: 324092

Url: https://administrator.de/contentid/324092

Ausgedruckt am: 19.03.2024 um 02:03 Uhr

Mitglied: atze187
atze187 16.12.2016 um 14:22:17 Uhr
Goto Top
Na mach das doch.
Mitglied: datadexx
datadexx 16.12.2016 um 14:25:43 Uhr
Goto Top
hey atze, wenn ich das hinbekommen würde... alle Versuche sind gescheitert... würde sonst auch nicht hier nachfragen...
Mitglied: atze187
atze187 16.12.2016 um 14:45:42 Uhr
Goto Top
Grütze im Kopf. Du brauchst kein Join dafür! Mein Fehler, man sollte niemals nebenbei arbeiten face-smile
Mitglied: datadexx
datadexx 16.12.2016 um 15:02:55 Uhr
Goto Top
hm, dann wüsste ich gerne wie es geht...
Mitglied: atze187
atze187 16.12.2016 um 15:07:28 Uhr
Goto Top
Die Fragen lauten:

  1. Was sollte rauskommen?
  2. Was kommt raus?

Es sind deine IF-Bedingungen die nicht stimmen werden. Ohne Beispiele wird dir aber niemand sagen können, wo genau es hakt.
Mitglied: em-pie
em-pie 16.12.2016 um 15:29:55 Uhr
Goto Top
Moin,

ganz ehrlich?
Das Teil sieht sehr imperformant aus...

bau die erstmal eine Tabelle/ View, mit der du die Daten mit deinen Standardabfragen einschränkst:

(ich kürze View dann immer mit VI ab, findet man schneller)
 Create or replace View "VI_POSTEN1"  
   select 
      SUM(Gesamt) as Gesamt
      Date(Datum)
   from 
      posten
   where
      status !=3 
      AND status !=4 
      AND TransAnr != 'Gutschrift'   
      AND RG_NUMMER !='Dienstgut'   
   GROUP BY
      Date(Datum)
Dann hättest du schonmal eine erste verdichtete Tabelle, welche man Joinen kann und beim ersten Aufruf erzeugt wird.

Gleiches dann nochmal, aber ohne deine EInschränkung
 Create or replace View "VI_POSTEN2"  
   select 
      SUM(Gesamt) as Gesamt
      Date(Datum)
   from 
      posten
   where
      status !=3 
      AND TransAnr != 'Gutschrift'   
      AND RG_NUMMER !='Dienstgut'   
   GROUP BY
      Date(Datum)
Denn die kommt auch häufiger vor....

Mit denen kannst du erstmal dein Statement entschlacken

Für dein finales Statement machst du dann den Join via
Select tbl1.Gesamt-tbl2.Gesamt, tbl1.Gesamt-tbl3.Gesamt, ...
From Posten as tbl1
Inner Join VI_Posten1 as tbl2 on tbl1.Datum = tbl2.Datum
Inner Join VI_Posten2 as tbl3 on tbl1.Datum = tbl3.Datum
....
So in die Richtung würde es jedenfalls gehen, aber das ist nur Blind geraten, ohne genaueres zu kennen...

Ansonsten wären Tabellenköpfe, und dein erwartetes Ziel ganz interessant. Per Hand eingetragenene Excel-Daten (anonymisiert) reichen erstmal, damit man mal ein Bild hat...

Gruß
em-pie
Mitglied: datadexx
datadexx 16.12.2016 um 15:45:53 Uhr
Goto Top
Also folgende Tabelle kommt dabei raus (Werte sind fiktiv)...
Wie bereits gesagt sind die Daten bei "Anzahl Sendungen" nicht korrekt. Richtig ermitteln kann man die aus der Tabelle aauftrag. Das Datumsfeld um den Tag der Sendung ermitteln zu können heißt "ALadetag" und der "Status" des Auftrags darf nicht 3 sein. Die If-Abfragen sind notwendig da die gleiche Tabelle mit unterschiedlichen Bedingungen zum Ermitteln der richtigen Werte benötigt werden. Als Beispiel darf bei der Ermittlung des Umsatzes gesamt der status nicht 4 sein, bei der Ermittlung der Kosten darf er aber 4 sein...

Datum, Umsatz, Kosten, Ergebnis, Rendite, Anzahl Sendungen, Mittelwert je Sendung, Mittelwert Ergebnis je Sendung
01.01.2016
02.01.2016
03.01.2016
04.01.2016 15.034 € 11.673 € 3.362 € 22,36 % 39 385 € 86 €
05.01.2016 8.987 € 6.824 € 2.163 € 24,06 % 26 346 € 83 €
06.01.2016 13.238 € 10.135 € 3.103 € 23,44 % 42 315 € 74 €
07.01.2016 10.659 € 7.894 € 2.765 € 25,94 % 32 333 € 86 €
08.01.2016 11.657 € 9.542 € 2.116 € 18,15 % 30 389 € 71 €
09.01.2016
10.01.2016
11.01.2016 12.793 € 10.304 € 2.489 € 19,46 % 36 355 € 69 €
12.01.2016 13.727 € 10.369 € 3.358 € 24,46 % 40 343 € 84 €
13.01.2016 12.036 € 9.313 € 2.722 € 22,62 % 34 354 € 80 €
14.01.2016 6.367 € 5.230 € 1.137 € 17,86 % 21 303 € 54 €
15.01.2016 10.316 € 7.951 € 2.365 € 22,93 % 28 368 € 84 €
16.01.2016 178 € 133 € 45 € 25,30 % 1 178 € 45 €
17.01.2016
18.01.2016 16.178 € 12.541 € 3.637 € 22,48 % 41 395 € 89 €
19.01.2016 16.190 € 12.524 € 3.666 € 22,64 % 37 438 € 99 €
20.01.2016 14.250 € 10.781 € 3.469 € 24,34 % 37 385 € 94 €
21.01.2016 9.515 € 7.023 € 2.492 € 26,19 % 31 307 € 80 €
22.01.2016 12.925 € 9.842 € 3.083 € 23,86 % 35 369 € 88 €
23.01.2016
24.01.2016
25.01.2016 18.549 € 13.833 € 4.716 € 25,42 % 51 364 € 92 €
26.01.2016 16.108 € 11.782 € 4.325 € 26,85 % 39 413 € 111 €
27.01.2016 12.333 € 9.454 € 2.879 € 23,34 % 38 325 € 76 €
28.01.2016 22.470 € 17.604 € 4.866 € 21,66 % 56 401 € 87 €
29.01.2016 14.875 € 11.926 € 2.950 € 19,83 % 42 354 € 70 €
30.01.2016
31.01.2016
Mitglied: Biber
Lösung Biber 18.12.2016, aktualisiert am 20.12.2016 um 23:19:43 Uhr
Goto Top
Moin datadexx,

ich würde ein paar Sachen anders angehen.

  • BEVOR du mit irgendwelchem GROUP BY und Rum-Aggregieren anfängst, weisst du doch schon, dass du aus der "posten"-Tabelle nur die Datensätze brauchst, die deinem Parameter '{$yearMonth}' entsprechen und wo RG_Nummer != 'Dienstgut' ist.Dann hol doch nur die aus "Posten" mit einem WHERE

  • Skeptisch macht mich das "GROUP by DATE(Datum)" - ist das denn ein TEXT-Feld oder warum willst du das nochmal casten?

  • ein JOIN auf die aauftrag-Tabelle ist keine tolle Idee - du müsstest erst "posten" wie "aauftrag" einzeln nach Datum gruppieren und dann über Datum verjoinen. Die Detail-Tabellen über Datum verknüpfen würde ein unsinniges Ergebnis beim JOIN bringen (ein JOIN von 12 Posten am letzten Dienstag auf 3 Auftrags-Sendungen am letzten Dienstag sind wieviel Sätze?)

  • unterm Strich brauchst du aus der "aggregierten Posten-Tabelle" nur 2 Zwischensummen zur Berechnung vom "umsatz" und die "Kosten", alles andere ist abgeleitet. und aus "aauftrag" brauchst du nur die "Anzahl Sendungen".

Dann würde sich dein Gestrunkele auf dieses hier reduzieren (ungetestet natürlich)

SELECT umsatz
  , kosten
  , Ergebnis
  , rendite
  , sendungen,
  , IF(sendungen != 0, umsatz/sendungen, 0) as Durchschnitt
  , IF(sendungen != 0 , ergebnis/sendungen, 0) as Ergebnisd
  , DAYOFMONTH(dateausposten) AS day 
FROM (
  Select zwsum1-zwsum2 as umsatz
    , kosten
    , zwsum1 -zwsum2 - kosten as Ergebnis
    , ((zwsum1 -zwsum2 - kosten)*100 /(zwsum1 - zwsum2)) as rendite
    , (select Count(*) From aauftrag 
       WHERE Date(aLadetag) = dateAusposten 
        and status != 3 ) as Sendungen
    , dateausPosten
    FROM (
    SELECT 
          sum(IF (status != 3 AND status != 4  AND TransAnr != 'Gutschrift' , Gesamt, 0)) as zwsum1  
         , sum(IF (status != 3  AND TransAnr = 'Gutschrift' AND TYPE = 3 ,Gesamt, 0)) AS zwsum2   
         , sum(IF (status != 3 AND TransAnr != 'Gutschrift' , Untern_Verguetung, 0)) AS kosten  
         , date(datum) as dateAusposten
          FROM ( SELECT *
                 FROM posten 
                 WHERE EXTRACT (YEAR_MONTH FROM DATUM) = '{$yearMonth}'  
                 AND RG_Nummer != 'Dienstgut'  
               ) p
          GROUP BY DATE (DATUM)
    ) pg
 ) pgXSendungen   
;

Auf ein GROUP BY der Tabelle aauftrag nach Tagen habe ich verzichtet - für deine max 31 Tage mache ich dann lieber 31x ein Inline-Select mit einem Count-Alles-was-kein-Storno-ist.
Die Aliasnamen ergeben sich als p=posten, pg=postenGruppiert, pgXSendungen=pg mit Sendungen.
Ein künstliches Feld "DateAusPosten" habe ich noch zum Hochreichen in den SELECTs zugefügt.
Ach ja, und dort wo dividiert wird bei den Mittelwertberechungen habe ich noch das DIV/0 abgefangen.

Grüße
Biber
[Edit] In Zeile 13 den Nenner (zwsum1-zwsum2) in Klammern gesetzt... wegen Punkt-vor-Strich-Rechnung face-wink [/Edit]
Mitglied: datadexx
datadexx 20.12.2016 um 15:34:45 Uhr
Goto Top
Hey biber,

vorab erstmal vielen Dank!!!

Irgendwie hab ich gerade erst ne Mail bekommen, das ein neuer Kommentar da ist. Ich werde das spätestens morgen testen und dann Bescheid geben.

Greetz
Mitglied: datadexx
datadexx 21.12.2016 um 20:25:42 Uhr
Goto Top
Hey Biber, Du bist der Beste. Läuft perfekt! Anpassungen gemacht und rennt...
Besten Dank dafür!
Mitglied: Biber
Biber 22.12.2016 um 17:17:48 Uhr
Goto Top
Moin datadexx,

freut mich, wenn es passt.

Aber noch mal meine Rückfrage von oben:
Sind denn "posten.Datum" und "aauftrag.ladetag" nicht ohnehin vom Datentyp "Date"?

Im Moment werden beide (vermutlich überflüssigerweise) gecastet, was sicherlich jegliche Nutzung eines vorhandenen Index unterbindet.
Wenn die schon den richtigen Datentyp haben, dann könnte das Statement ohne diese Date(xxx)-Zwangspressung signifikant schneller fertig werden.

Grüße
Biber
Mitglied: datadexx
datadexx 22.12.2016 um 21:04:12 Uhr
Goto Top
Hi Biber!

Ja, beide Felder sind vom Datentyp date.
Schneller fertig werden hört sich super an...

Viele Grüße, frohe Weihnachten und paar ruhige Tage!

Greetz
Mitglied: Biber
Biber 22.12.2016 aktualisiert um 23:18:03 Uhr
Goto Top
Moin datadexx,


Zitat von @datadexx:


Ja, beide Felder sind vom Datentyp date.
Dann nimm oben die beiden Date()-Anweisungen raus.
Schneller fertig werden hört sich super an...
Falls da irgendwie durchschimmert, dass diese Abfrage nicht so performant ist...
-> auch da würde ich ein paar Sachen anders machen.
Eine Grundregel habe ich oben auch verletzt: immer nur die Felder holen, die ich auch brauche.
Ein "SELECT * from posten" ist zwar fix getippt, aber hier vollkommener Bullshit.
Am Ende des Tages willst du doch von den vermutlich 20 Feldern in "posten" nur dreieinhalb... hier könnte man mit ein ein wenig mehr Tipperei ganz für lau Performance herausholen.
In die selbe Kerbe -Tippfaulheit kostet Performance- geht auf jeden Fall der Parameter "'{$yearMonth}".
Klar, ist schnell eingetippt und ist auch zweifelsohne die "richtige" GROUP BY.Granularität.

Wenn du aber diesen Parameter '{$yearMonth}', also zb '201611' abfragst und rüberbringst als 2 Parameter '{$vonDatum}' und '{$bisDatum}', dann könntest du statt das
...WHERE EXTRACT (YEAR_MONTH FROM DATUM) = '{$yearMonth}'   
...bei dem niemals nich' irgendein Zugriff über Index möglich ist und IMMER die ganze ver###te "posten"-Tabelle von Anbeginn der Zeit bis heute durchgenudelt werden muss (full table scan), ändern in ein...
 
...WHERE DATUM >= '{$vonDatum}' and DATUM <=  '{$bisDatum}'   

Da sicherlich ein Index auf dem Datumsfeld liegt (oder innerhalb einer Minute angelegt werden kann), kannst du da relativ schnell ein bisschen tunen.
Denn der eigentliche Auswertungszeitraum ist sehr klein (ein Monat), die gesammelten Posten-Daten dagegen enthalten evtl das Hundertfache (100 Monate sind 8 Jahre ~ Daten seit 2008-> ist nicht unrealistisch).
Wenn Du also gezielt über einen Index auf das eine Hundertstel der Daten zugreifen kannst, das auswertungsrelevant ist, dann bekommst du unter Umständen einen anerkennenden Blick vom Cheffe oder ähnliches.

Frohe Weihnachten
Biber
Mitglied: datadexx
datadexx 23.12.2016 um 10:32:00 Uhr
Goto Top
Moin Biber!

Performance ist bei der Geschichte super, denn....

was der "Chef" will ist, diese Abfrage und zwar für jeden Monat des Jahres. Ich hab die Abfrage hinsichtlich der benötigten Felder bei mir bereits geändert, gibt also bei mir keine SELECT * mehr... und die Datumsabfrage könnte auch geändert werden jep. Teste ich gleich. Im PHP Script läuft eine Schleife die die Abfrage je nach Anzahl Monaten wiederholt . Das geht von der Geschwindigkeit her wenn Januar oder Februar ist, jetzt ist ja bereits Dezember und somit wird das ganze 12x durchgeführt. Chef will halt wenn er auf die Statistik klickt, quasi die Daten vom 01.01. bis zum aktuellen Tag haben. Das Ganze für jeden Tag im Monat wie jetzt gemacht, dann den Monat Gesamt und dann das Ganze noch aufgeschlüsselt nach Wochen des Monats. So (zahlen fiktiv) wie hier dargestellt und dann fortlaufend je nach dem wann im Jahr wir sind.

Datum, Umsatz, Kosten, Ergebnis, Rendite, Anzahl Sendungen, Mittelwert je Sendung, Mittelwert Ergebnis je Sendung
01.01.2016
02.01.2016
03.01.2016
04.01.2016 15.034 € 11.673 € 3.362 € 22,36 % 39 385 € 86 €
05.01.2016 8.987 € 6.824 € 2.163 € 24,06 % 26 346 € 83 €
06.01.2016 13.238 € 10.135 € 3.103 € 23,44 % 42 315 € 74 €
07.01.2016 10.659 € 7.894 € 2.765 € 25,94 % 32 333 € 86 €
08.01.2016 11.657 € 9.542 € 2.116 € 18,15 % 30 389 € 71 €
09.01.2016
10.01.2016
11.01.2016 12.793 € 10.304 € 2.489 € 19,46 % 36 355 € 69 €
12.01.2016 13.727 € 10.369 € 3.358 € 24,46 % 40 343 € 84 €
13.01.2016 12.036 € 9.313 € 2.722 € 22,62 % 34 354 € 80 €
14.01.2016 6.367 € 5.230 € 1.137 € 17,86 % 21 303 € 54 €
15.01.2016 10.316 € 7.951 € 2.365 € 22,93 % 28 368 € 84 €
16.01.2016 178 € 133 € 45 € 25,30 % 1 178 € 45 €
17.01.2016
18.01.2016 16.178 € 12.541 € 3.637 € 22,48 % 41 395 € 89 €
19.01.2016 16.190 € 12.524 € 3.666 € 22,64 % 37 438 € 99 €
20.01.2016 14.250 € 10.781 € 3.469 € 24,34 % 37 385 € 94 €
21.01.2016 9.515 € 7.023 € 2.492 € 26,19 % 31 307 € 80 €
22.01.2016 12.925 € 9.842 € 3.083 € 23,86 % 35 369 € 88 €
23.01.2016
24.01.2016
25.01.2016 18.549 € 13.833 € 4.716 € 25,42 % 51 364 € 92 €
26.01.2016 16.108 € 11.782 € 4.325 € 26,85 % 39 413 € 111 €
27.01.2016 12.333 € 9.454 € 2.879 € 23,34 % 38 325 € 76 €
28.01.2016 22.470 € 17.604 € 4.866 € 21,66 % 56 401 € 87 €
29.01.2016 14.875 € 11.926 € 2.950 € 19,83 % 42 354 € 70 €
30.01.2016
31.01.2016
, Umsatz, Kosten, Ergebnis, Rendite, Anzahl Sendungen, Mittelwert je Sendung, Mittelwert Ergebnis je Sendung
gesamt 368.348 € 308.926 € 60.950 € 19,83 % 730 354 € 70 €
Woche, Umsatz, Kosten, Ergebnis, Rendite, Anzahl Sendungen, Mittelwert je Sendung, Mittelwert Ergebnis je Sendung
1. Woche 16.108 € 11.782 € 4.325 € 26,85 % 39 413 € 111 €
2. Woche 12.333 € 9.454 € 2.879 € 23,34 % 38 325 € 76 €
3. Woche 22.470 € 17.604 € 4.866 € 21,66 % 56 401 € 87 €
4. Woche 14.875 € 11.926 € 2.950 € 19,83 % 42 354 € 70 €

Greetz