SQL SELECT mit SUM, GROUP BY und ORDER BY anhand Summen

[mw197]

Hallo Leute,

ich habe 3 Tabellen die wie folgt aussehen:

Tabelle1:
`ID` | `betrag1` | `betrag2` | `betrag3` | `kundenID`

Tabelle2:
`ID` | `name` | `preis` | `kundenID`

Tabelle3:
`ID` | `sID` | `kundenID`

Ich möchte nun wissen, welcher Kunde (anhand KundenID) den meisten Umsatz gebracht hat. Ich habe dazu wie folgt gearbeitet, weiß aber nicht so recht, ob das die passende und beste Lösung ist:

SELECT
   SUM(`t1`.`betrag1`) +
   SUM(`t1`.`betrag2`) +
   SUM(`t1`.`betrag3`) AS `summe`,
   (
      SELECT SUM(`preis`) FROM `tabelle2` `t2`
      WHERE `t2`.`kundenID` = `t1`.`kundenID`
   ) AS `summe2`,
   (
      SELECT COUNT(`ID`) * 5 FROM `tabelle3` `t3`
      WHERE `t3`.`kundenID` = `t1`.`kundenID`
   ) AS `summe3`
FROM `tabelle1` `t1`
GROUP BY `t1`.`kundenID`
ORDER BY
   `summe` DESC,
   `summ2` DESC,
   `summe3` DESC
LIMIT 5

Ist das so korrekt oder geht das einfacher UND PERFORMANTER! Das Laden dauer (200.000 Datensätze) um die 3 Sekunden...
Und eigentlich müsste ich ja sortieren nach den 3 Summen! Kann man das? Momentan sortiere ich jede Summe, das stimmt doch nicht oder?
Würde mich über einen Tipp freuen.

Danke!

 

[Jesterfox]

Die Frage ist: was genau willst du überhaupt selektieren? Beschreibe es mal in einfachen Worten, ich befürchte nämlich das dein Aufbau komplett Falsch ist...

Aktuell verhauen dir jedenfalls die Subselects die Performance, aber deine Frage nach der Sortierung macht mich stutzig, evtl. ist das ganze Vorgehen für die gesuchte Lösung falsch.

 

[mw197]

Die Frage ist: was genau willst du überhaupt selektieren? Beschreibe es mal in einfachen Worten, ich befürchte nämlich das dein Aufbau komplett Falsch ist...

Aktuell verhauen dir jedenfalls die Subselects die Performance, aber deine Frage nach der Sortierung macht mich stutzig, evtl. ist das ganze Vorgehen für die gesuchte Lösung falsch.

Ok. Also in der Datenbank befinden sich aus 3 Bereichen verschiedene Umsätze.

• Die Kunden können entweder etwas gekauft haben (einen Artikel)
• Die Kunden können eine Dienstleistung in Anspruch genommen haben (maximal 3 gleichzeitig wie in Tabelle 1 als "betrag1-3" gespeichert)
• oder die Kunden können einen Gutschein gekauft haben

Nun möchte ich wissen, welcher Kunde aus allen 3 Bereichen insgesamt den meisten Umsatz eingebracht hat.
Die Kunden stehen dazu in einer Tabelle namens `kunden`.

 

[Jesterfox]

Müsstest du dann nicht noch eine Summe über die 3 Einzelsummen bilden um zu Wissen was der gesamte Umsatz aus allen 3 Bereichen war?

 

[Bong Smoka]

Ich finde das Datenbankschema ziemlich daneben, ich hoffe das liegt an deiner "Anonymisierung"
Ich verstehe auch nicht was Tabelle3 für eine Rolle spielt..

mal schnell gehackt.. wahrscheinlich fehlerhaft aber vielleicht hilfts ja

SELECT u1+u2 as umsatz FROM (SELECT SUM(betrag1 + betrag2 + betrag3) as u1 FROM Tabelle1 GROUP BY kundenID) t1, (SELECT SUM(preis) as u2 FROM Tabelle2 WHERE t1.kundenID=t2.kundenID GROUP BY kundenID) t2 ORDER BY umsatz DESC

 

[Jesterfox]

Ja, die Struktur kommt mir auch etwas suboptimal vor...

 

[VikingGe]

Unabhängig von den nicht gerade aussagekräftigen Namen wie "Tabelle1" und der Tatsache, dass ich əbenfalls meine Zweifel daran habe, dass das Datenbankschema für das Vorhaben ideal ist (Stichwort: Flexibilität - was machst du, wenn aus drei Bereichen 30 werden?), müsste man die Subqueries aktuell 1:1 in Joins überführen können, was möglicherweise schon die Performance erhöht.

Nicht getestet, aber wenn man

SELECT
   SUM(t1.betrag1) + SUM(t1.betrag2) + SUM(t1.betrag3) AS summe,
   SUM(t2.preis) AS summe2,
   COUNT(t3.ID) * 5 AS summe3
   FROM tabelle1 AS t1
   JOIN tabelle2 AS t2 ON t1.kundenID = t2.kundenID
   JOIN tabelle3 AS t3 ON t1.kundenID = t3.kundenID
   [...]

schreibt und ich nicht völlig daneben liege (in dem Fall möge mich bitte jemand korrigieren), müsste eigentlich das gewünschte Ergebnis herauskommen. Die Subqueries machen ja nichts, was in irgendeiner Weise unabhängig von der Hauptquery ausgeführt werden müsste, im Gegenteil.

Übrigens ist es gerade im Hinblick auf Joins eine eher schlechte IDee, Felder mit gleichem Namen zu haben, wenn nicht auch die Bedeutung dieselbe ist. Das verhindert nämlich die Nutzung des NATURAL JOIN, der über alle gleichnamigen Felder joint.

 

[mw197]

@BongSmoka: So habe ich es mal gemacht, kann aber vom Ergebnis her nicht stimmen :|
Da gibt es nun etliche die 0,00 € als Wert haben und das kann nicht sein, auch stimmen die Werte pro Kunde bei allen Stichproben nicht, die ich gerade nachgerechnet habe.

Wieso ist das Schema denn daneben?
Es klingt vielleicht etwas merkwürdig mit meinem Beispiel etc. aber es hat einen Sinn. Bei Tabelle1 geht es um Rezepte von einem Arzt, der maximal pro Rezept 3 Therapien verordnen kann. Jede Therapie speichere ich zu dem Rezept ab und auch gleichzeitig die entsprechenden Werte von den Krankenkassen, die sich wiederum nach den Versicherungen und Verordnungen, sowie Heilmitteln richten. Das ist total kompliziert und wäre hier zu übertrieben für meine Fragestellung. Daher gibt es bei der Tabelle1 drei Spalten mit Zahlen die ich summieren möchte.

Bei Tabelle2 und Tabelle3 geht es dann um ähnliche Dinge.

Danke für eure Hilfe schon mal!

 

[Jesterfox]

Joins hab ich mir auch schon überlegt, aber da dürfte nicht das gewünschte Ergebnis raus kommen, da ja vor dem Gruppieren gejoint wird, das führt zu einer Vervielfältigung der Einträge und verfälscht die Summen. Wenn dann müsste man über Subjueries die den groupBy schon haben den join machen. Bin mir aber nicht sicher ob das einen Performence-Vorteil bringt.

 

[mw197]

Joins hab ich mir auch schon überlegt, aber da dürfte nicht das gewünschte Ergebnis raus kommen, da ja vor dem Gruppieren gejoint wird, das führt zu einer Vervielfältigung der Einträge und verfälscht die Summen. Wenn dann müsste man über Subjueries die den groupBy schon haben den join machen. Bin mir aber nicht sicher ob das einen Performence-Vorteil bringt.

Habs gerade getestet, jetzt bin ich gerade Millionär, lt den Zahlen

Ich kann es sonst auch irgendwie zu Fuß mit mehreren Querys machen und dann in Arrays packen, zusammenzählen und sortieren. Dann würde ich diese Statistik einfach cachen und nachts per Cronjob aktualisieren lassen, wenn das nicht besser geht. Aber finde ich etwas übertrieben

 

[Jesterfox]

Hm, was ist mit meiner Frage aus #4? Denn irgendwie ist mir noch nicht ganz klar wie du genau das mit den maxima sortieren willst. Wenn es tatsächlich nur um das Gesamtmaximum geht könnte man den Query evtl. auch ganz anders aufbauen.

 

[mw197]

Hm, was ist mit meiner Frage aus #4? Denn irgendwie ist mir noch nicht ganz klar wie du genau das mit den maxima sortieren willst. Wenn es tatsächlich nur um das Gesamtmaximum geht könnte man den Query evtl. auch ganz anders aufbauen.

Ja, das müsste man. Rein rechnerisch würde ich alle Werte des Kunden zusammenfassen wollen und dann daran sortieren. Nur dann habe ich eigentlich die wirkliche Summe des Kunden.

Ergänzung (15. Oktober 2015)

Wäre es vielleicht klüger von einer anderen Position zu starten, ohne Group?

Man könnte doch bestimmt auch die Kunden durchgehen und von da aus die Summen der Bereiche auslesen. Also z.B. so:

SELECT
   SUM(t1.betrag1) + SUM(t1.betrag2) + SUM(t1.betrag3) AS summe,
   SUM(t2.preis) AS summe2,
   COUNT(t3.ID) * 5 AS summe3
   FROM `kunden` AS k
   JOIN tabelle1 AS t1 ON k.kundenID = t2.kundenID
   JOIN tabelle2 AS t2 ON k.kundenID = t2.kundenID
   JOIN tabelle3 AS t3 ON k.kundenID = t3.kundenID
   [...]

Das probiere ich auch mal aus

 

[Jesterfox]

Hm, dann würde ich das glaub ich über 3 Selects - Union - Group by / sum machen. Also grob so:

SELECT SUM(werte) Summe, kundenID FROM ((SELECT ... Werte, kundenID FROM Tabelle1 GROUP BY kundenID) UNION ALL (SELECT ... Werte, kundenID FROM Tabelle2 GROUP BY kundenID) UNION ALL (SELECT ... Werte, kundenID FROM Tabelle 3 GROUB BY kundenID)) GROUP BY kundenID ORDER BY Summe

Wobei ... die jeweilige Summenberechung für die Tabelle ist.


Edit: wobei das recht ähnlich zu der angesprochenen join Lösung auf die Gruppierten Ergebnisse ist, das würde sich also auch mit den Einzelergebnissen wie in deinem Beispiel lösen lassen.