SQL-Abfrage: „verschachtelte“ Summen

[Volker]

Hallo zusammen,

ich habe eine Frage bzgl. einer kniffligen SQL-Abfrage, welche über eine Art Bewertungsbogen laufen soll.

Zunächst eine Beschreibung des Szenarios. Ich gebe dabei jede Tabelle mit ihren relevanten Spalten an und kennzeichne Schlüssel durch ein #-Zeichen:

Ich habe mehrere Datensätze in einer Tabelle:
katalog: #idkatalog name

Ein Katalog besteht aus mehreren Kategorien, eine Kategorie kann eindeutig einem Katalog zugeordnet werden. Jede Kategorie hat eine numerische Gewichtung:
kategorie: #idkategorie #katalog_idkatalog gewichtung

Eine Kategorie besteht aus mehreren Parametern, ein Parameter kann eindeutig einer Kategorie zugeordnet werden. Jeder Parameter hat eine numerische Gewichtung und eine maximal zu erreichende Punktzahl:
parameter: #idparameter #kategorie_idkategorie gewichtung maximalpunkte

Um einen Objekt zu bewerten, existiert eine Tabelle, die jedem Objekt eine erreichte Punktzahl pro Parameter zuordnet:
objekt: #idobjekt #parameter_idparameter punkte

Ich will am Ende der SQL-Abfrage für ein gegebenes Objekt (!) einen numerischen Wert pro Katalog als Ergebnis haben, wie folgt:

1. Pro Parameter werden die erreichten Punkte durch die Maximalpunkte des Parameters geteilt und mit der Gewichtung des Parameters multipliziert, das Ergebnis hier als Parameterbewertung bezeichnet.
2. Pro Kategorie wird die Summe aller Parameterbewertungen durch die Summe aller Parametergewichtungen geteilt und mit der Gewichtung der Kategorie multipliziert, das Ergebnis hier als Kategoriebewertung bezeichnet.
3. Pro Katalog wird die Summe aller Kategoriebewertungen durch die Summe aller Kategoriegewichtungen geteilt.

Meine SQL-Abfrage deckt 1) und 2) ab, aber bei der Ergänzung der Abfrage zu 3) stehe ich auf dem Schlauch, denn ich bekomme die Summe aus 3) nicht integriert:

SELECT katalog.name, SUM(objekt.punkte / parameter.maximalpunkte * parameter.gewichtung) / SUM(parameter.gewichtung) * kategorie.gewichtung  
FROM objekt, parameter, kategorie, katalog  
WHERE objekt.idobjekt = **OBJEKTID**  
  AND objekt.parameter_idparameter = parameter.idparameter  
  AND parameter.kategorie_idkategorie = kategorie.idkategorie  
  AND kategorie.katalog_idkatalog = katalog.idkatalog  
GROUP BY kategorie.idkategorie

Diese Abfrage gibt mir nun für jede Kategorie Schritt 2) zurück. Wie komme ich zu Schritt 3)?

Ich weiß, es ist viel Text, wenn sich da einer durcharbeiten und mir helfen kann, bin ich sehr dankbar.

Viele Grüße

Volker

[Ilja]

moin,

ein paar tipps vorweg:

• benutze keine expliziten joins, es gibt keinen grund dafür.
• du benutzt mysql, jedes andere dbms würde dir bei der abfrage ein fehlermeldung ausgeben, halte dich an die ANSI standard, was gruppierungen betrifft.
• die bezeichnung deiner fremdschlüssel ist "doppelt gemopellt". nimm zum beispiel #katalog_idkatalog. du brauchst nicht noch den namen der tabelle katalog hinzuzufügen, wenn aus der id bezeichnung schon hervorgeht, auf welche tabelle der fremdschlüssel sich bezieht.

Meine SQL-Abfrage deckt 1) und 2) ab, aber bei der Ergänzung der Abfrage zu 3) stehe ich auf dem Schlauch, denn ich bekomme die Summe aus 3) nicht integriert:

ich kann das in der abfrage nicht erkennen, da fehlen zum einen die alias namen in der projektion und zum aderen scheint mir da auch was falsch zu sein. ich vermute das eine "/" zeichen sollte eher ein "," sein ?

auch verstehe ich noch nicht den sinn der gruppierung. wenn ich das richtig lese, dann bekomst du für ein objekt nur einen parameter, damit auch nur eine kategorie und einen katalog oder sehe ich da was falsch ?

Ilja

[Volker]

Hallo,

danke für dein Feedback.

• die bezeichnung deiner fremdschlüssel ist "doppelt gemopellt". nimm zum beispiel #katalog_idkatalog. du brauchst nicht noch den namen der tabelle katalog hinzuzufügen, wenn aus der id bezeichnung schon hervorgeht, auf welche tabelle der fremdschlüssel sich bezieht.

Die Bezeichnungen sind im Produktivsystem eh ganz andere. Ich habe das Problem mal auf das Wesentliche reduziert und Bezeichner hier für das Posting umbenannt.

ich kann das in der abfrage nicht erkennen, da fehlen zum einen die alias namen in der projektion und zum aderen scheint mir da auch was falsch zu sein. ich vermute das eine "/" zeichen sollte eher ein "," sein ?

Nein, / soll wirklich geteilt sein, wie in der textuellen Beschreibung steht. Das erste / ist das aus 1), das zweite das aus 2).

auch verstehe ich noch nicht den sinn der gruppierung. wenn ich das richtig lese, dann bekomst du für ein objekt nur einen parameter, damit auch nur eine kategorie und einen katalog oder sehe ich da was falsch ?

Ein Objekt hat viele Bewertungsparameter. Ein Parameter gehört zu einer Kategorie, eine Kategorie zu einem Katalog.
Das GROUP BY ist das richtige für Schritt 1) und 2). Für die Entwicklung der Abfrage zu Schritt 3) muss es wohl geändert werden.
Nur wie ist die Frage …

Viele Grüße

Volker

[Ilja]

moin,

ich kann das in der abfrage nicht erkennen, da fehlen zum einen die alias namen in der projektion und zum aderen scheint mir da auch was falsch zu sein. ich vermute das eine "/" zeichen sollte eher ein "," sein ?

Nein, / soll wirklich geteilt sein, wie in der textuellen Beschreibung steht. Das erste / ist das aus 1), das zweite das aus 2).

hmm, dann zeige mir doch mal in deiner abfrage, wo der wert für die Parameterbewertung steht und wo der wert für Kategoriebewertung.

Ein Objekt hat viele Bewertungsparameter.

wie kann den ein objekt mehrere parameter haben, wenn der fremdschüssel der parametertabelle in der tabelle der objekte steht. so modelliert kann ein objekt nur einen parameter haben, bzw ein parameter kann mehrere objekte haben.

Ilja

[Volker]

Hallo,

hmm, dann zeige mir doch mal in deiner abfrage, wo der wert für die Parameterbewertung steht und wo der wert für Kategoriebewertung.

Parameterbewertung findet im ersten SUM() statt.
Die Kategorienbewertung ist der gesamte Teil: SUM(objekt.punkte / parameter.maximalpunkte * parameter.gewichtung) / SUM(parameter.gewichtung) * kategorie.gewichtung

wie kann den ein objekt mehrere parameter haben, wenn der fremdschüssel der parametertabelle in der tabelle der objekte steht. so modelliert kann ein objekt nur einen parameter haben, bzw ein parameter kann mehrere objekte haben.

Nehmen wir an, Objekt 1 hat 3 Parameter:
objekt: #idobjekt #parameter_idparameter punkte
         1         1                     10
         1         2                     11
         1         3                     9

Ein Objekt hat viele Parameter, ein Paramater kann viele Objekte haben. Objekt-ID und Parameter-ID sind beides Schlüssel, also eine n-zu-m-Beziehung.

Viele Grüße

Volker

[DaBear]

Nehmen wir an, Objekt 1 hat 3 Parameter:
objekt: #idobjekt #parameter_idparameter punkte
         1         1                     10
         1         2                     11
         1         3                     9

Ein Objekt hat viele Parameter, ein Paramater kann viele Objekte haben. Objekt-ID und Parameter-ID sind beides Schlüssel, also eine n-zu-m-Beziehung.

http://de.wikipedia.org/wiki/Normalisierung_%28Datenbank%29

1. Lesen
2. Verstehen
3. Umsetzen
[Optional: 4. Nachfragen]

[Volker]

1. Lesen
2. Verstehen
3. Umsetzen
   [Optional: 4. Nachfragen]

Brauche ich nicht lesen, verstehe ich, umsetzen geht nicht, das Design ist nicht veränderbar.
Es wird im Produktivsystem teilweise nicht einmal die erste Normalform erfüllt.
Das ist allerdings auch gar nicht mein Hilfegesuch, ich hatte mir Hilfe zur Abfrage erhofft.

[Ilja]

moin,

das schwierigste bei den meisten SQL fragen hier ist weniger die abfrage selbst, sondern das verstehen, was voliegt und was das ergebnis sein soll. und ich muss sagen, deine beschreibungen sind für einen alten mann wie mich verwirrend, was aber auch an mir liegen kann.

hmm, dann zeige mir doch mal in deiner abfrage, wo der wert für die Parameterbewertung steht und wo der wert für Kategoriebewertung.

Parameterbewertung findet im ersten SUM() statt.
Die Kategorienbewertung ist der gesamte Teil: SUM(objekt.punkte / parameter.maximalpunkte * parameter.gewichtung) / SUM(parameter.gewichtung) * kategorie.gewichtung

die SUM() werte bilden ja in deinem fall keine eigene spalten, sondern sind teil einer spalte, nämlich der Kategorienbewertung, wenn ich dich jetzt richtig verstanden habe. und wenn du in deinem ausgangsposting die punkte aufzählst, was die abfrage leisten soll, dann finde ich keine Parameterbewertung in der ausgabe, sondern nur die Kategoriebewertung. hinzu kommt, dass sich die "virtuelle" Parameterbewertung nicht pro parameter bezieht, sondern eben nach der kategorie gruppiert wird.

wie kann den ein objekt mehrere parameter haben, wenn der fremdschüssel der parametertabelle in der tabelle der objekte steht. so modelliert kann ein objekt nur einen parameter haben, bzw ein parameter kann mehrere objekte haben.

Nehmen wir an, Objekt 1 hat 3 Parameter:
objekt: #idobjekt #parameter_idparameter punkte
         1         1                     10
         1         2                     11
         1         3                     9

Ein Objekt hat viele Parameter, ein Paramater kann viele Objekte haben. Objekt-ID und Parameter-ID sind beides Schlüssel, also eine n-zu-m-Beziehung.

auch hier wäre es besser gleich zu erwähnen, dass es sich also nur um die beziehungstabelle, und nicht um die objekttabelle, handelt. kann man vielleicht drauf kommen, aber mir war das nicht gleich klar. insofern kann man getrost auch dir normalisierung aussen vor lassen (die ich sowieso für überholt finde), es scheint ja alles in bester ordnung zu sein. irgendwo existiert dann eben noch die objekt tabelle und du hast uns schon die beziehungstabelle (n:m) gegeben. dann ist es auch verständlich, das ein objekt mehrere parametere haben kann und umgekert.

jetzt aber genug gemeckert, schließlich geht es ja um eine lösung. wenn du punkt 2) und punkt 3) in einer abfrage abbilden willst, dann muss dir klar sein, dass du die werte für punkt 3) eventuell mehrfach angezeigt werden, nämlich immer dann, wenn zwei unterschiedliche kategorien zu dem selben katalog gehören. das liegt daran, dass du über die kategorie gruppierst, weil du ja auch eine ausgabe für jede unterschiedliche bezüglich 2) haben willst. du kannst nicht über die katalod id gruppierung, weil du sich dann unterschiedliche kategorien dahinter verbegen können. sprich du musst die zweite form der gruppierung in einer unterabfrage einbauen. aber vorher sollte man noch klären, was du genau brauchst, ist mir leider immer noch nicht bewußt, vor allem was die Parameterbewertung betrifft und wie die gebildet wird. am besten wären wohl beispieldaten mit einer ausführlichen erklärung.

Ilja

[Volker]

Hallo,

das schwierigste bei den meisten SQL fragen hier ist weniger die abfrage selbst, sondern das verstehen, was voliegt und was das ergebnis sein soll. und ich muss sagen, deine beschreibungen sind für einen alten mann wie mich verwirrend, was aber auch an mir liegen kann.

Ich habe mich bemüht, die Sache so präzise wie möglich niederzuschreiben.

auch hier wäre es besser gleich zu erwähnen, dass es sich also nur um die beziehungstabelle, und nicht um die objekttabelle, handelt. kann man vielleicht drauf kommen, aber mir war das nicht gleich klar. insofern kann man getrost auch dir normalisierung aussen vor lassen (die ich sowieso für überholt finde), es scheint ja alles in bester ordnung zu sein. irgendwo existiert dann eben noch die objekt tabelle und du hast uns schon die beziehungstabelle (n:m) gegeben. dann ist es auch verständlich, das ein objekt mehrere parametere haben kann und umgekert.

Das mit der „Beziehungstabelle“ stimmt, das Objekt an sich wird in einer anderen Tabelle beschrieben. Da habe ich wohl zu viel weggelassen, als dass es auf den ersten Blick erkennbar ist.

Aber ich merke schon, dass die Lösung für mein Problem vielleicht doch nicht so trivial ist, wie ich am Anfang noch vermutet habe.

Danke für die Mühe, ich werde wohl selbst noch mal intensiv an der Abfrage arbeiten oder mit Workarounds leben müssen.

Viele Grüße

Volker

[Ilja]

moin,

Aber ich merke schon, dass die Lösung für mein Problem vielleicht doch nicht so trivial ist, wie ich am Anfang noch vermutet habe.

SQL technisch sehe ich da weniger probleme, das problem ist eben, dass du nach unterschiedclichen kriterien in einer abfrage gruppieren (verdichten) willst. das kann man machen, aber man msus sich der ergebnisse bewußt sein. wie gesagt, am besten wären mal beispieldaten mit dem gewünschten ergebnis, was du haben willst. dann wird auch vieles klarer.

Ilja

[Volker]

Hallo,

wie gesagt, am besten wären mal beispieldaten mit dem gewünschten ergebnis, was du haben willst. dann wird auch vieles klarer.

Nun gut, ich will es mal mit Beispieldaten beschreiben. Für jeden meiner drei Berechnungsschritte gebe ich die gewünschten Ergebnisse an. Zunächst die Tabellen mit ihren Daten:

-------------------------------------------------------------------------
katalog: #idkatalog name
          1         Katalog1
          2         Katalog2
-------------------------------------------------------------------------
kategorie: #idkategorie #katalog_idkatalog gewichtung
            1            1                 0.1
            2            1                 0.2
            3            2                 0.8
            4            2                 0.9
-------------------------------------------------------------------------
parameter: #idparameter #kategorie_idkategorie gewichtung maximalpunkte
            1            1                     1.1        4
            2            1                     1.1        4
            3            2                     0.5        4
            4            3                     0.6        4
            5            4                     1          4
-------------------------------------------------------------------------
objekt_hat_parameter: #idobjekt #parameter_idparameter punkte
                       1         1                     1
                       1         2                     2
                       1         3                     3
                       1         4                     4
                       1         5                     1
-------------------------------------------------------------------------

Für ein gegebenes Objekt mit der ID idobjekt 1:

1. Pro Parameter werden die erreichten Punkte durch die Maximalpunkte des Parameters geteilt und mit der Gewichtung des Parameters multipliziert, das Ergebnis hier als Parameterbewertung bezeichnet:
   idparameter 1:  1 / 4 * 1.1  = 0.275
               2:  2 / 4 * 1.1  = 0.55
               3:  3 / 4 * 0.5  = 0.375
               4:  4 / 4 * 0.6  = 0.6
               5:  1 / 4 * 1    = 0.25

2. Pro Kategorie wird die Summe aller Parameterbewertungen durch die Summe aller Parametergewichtungen geteilt und mit der Gewichtung der Kategorie multipliziert, das Ergebnis hier als Kategoriebewertung bezeichnet:
   idkategorie 1:  (0.275 + 0.55) / (1.1 + 1.1) * 0.1  = 0.0375
               2:  (0.375)        / (0.5)       * 0.2  = 0.15
               3:  (0.6)          / (0.6)       * 0.8  = 0.8
               4:  (0.25)         / (1)         * 0.9  = 0.225

3. Pro Katalog wird die Summe aller Kategoriebewertungen durch die Summe aller Kategoriegewichtungen geteilt:
   idkatalog 1:  (0.0375 + 0.15)  / (0.1 + 0.2)  = 0.625
             2:  (0.8    + 0.225) / (0.8 + 0.9)  = 0.603

Als Ergebnis der Abfrage will ich also haben:
#idkatalog wert
 1         0.625
 2         0.603

Hier nochmals meine Abfrage für 1) und 2):

SELECT katalog.name, SUM(objekt_hat_parameter.punkte / parameter.maximalpunkte * parameter.gewichtung) / SUM(parameter.gewichtung) * kategorie.gewichtung  
FROM objekt_hat_parameter, parameter, kategorie, katalog  
WHERE objekt_hat_parameter.idobjekt = **OBJEKTID**  
  AND objekt_hat_parameter.parameter_idparameter = parameter.idparameter  
  AND parameter.kategorie_idkategorie = kategorie.idkategorie  
  AND kategorie.katalog_idkatalog = katalog.idkatalog  
GROUP BY kategorie.idkategorie

Viele Grüße

Volker