Verknüpfte Tabellen anhand mehrerer Felder gruppieren.

[suit]

Hallo, Datenbank ist MySQL 5.1.x und ich haben folgende Tabellenstruktur:

recipe:
uid | title     | fe_user
----+-----------+-----------
1   | rezept 1  | 4

rating:
uid |fe_user | recipe | pool | score
----+--------+--------+------+------
#   | 1      | 1      | 1    | 5
#   | 2      | 1      | 1    | 4
#   | 3      | 1      | 2    | 1

In der ersten Tabelle stehen Rezepte, in der zweiten Bewertungen.

Verknüpft werden beide Tabellen über recipe.uid und rating.recipe

Eine Bewertung wird von einem Benutzer (fe_user) abgegeben und wird einem Rezept zugeordnet. Die Bewertung geht von 1 bis 5 und es soll aus den Bewertungen ein Durchschnitt je Rezept errechnet werden.

Zusätzlich gibt es das Feld "pool" - jede Bewertung wird einem Pool zugeordnet - jeder Pool darf nur 1x in die Bewertung einfließen, die Reihefolge spielt dabei keine Rolle.

Mit nachfolgender Abfrage erreiche ich, dass Rezept 1 eine Durchschnittsbewertung von 3,33 erhält ((5+4+1)/3).

Da aber die Bewertung des Benutzers 1 und 2 demselben Pool angehören, darf nur einer der Werte in die Wertung einbezogen werden - das Ergebnis sollte dann 3 ((5+1)/2) oder 2,5 ((4+1)/2) sein - die Sortierung ist dabei völlig egal, wichtig ist nur, dass sobald eine Doppelung des Pools stattfindet, nur der erste Wert (ungeachtet der Sortierreihenfolge) verwandt wird.

SELECT  
  t_recipe.uid,  
  t_recipe.title,  
  AVG(t_rating.score) as mean,  
  COUNT(t_rating.score) as num  
FROM  
  recipe as t_recipe  
RIGHT JOIN rating as t_rating ON t_recipe.uid = t_rating.recipe  
WHERE  
  1=1  
GROUP BY  
  t_recipe.uid  
ORDER BY  
  mean DESC, num DESC

Die GROUP-BY-Optimierung einfach um ein t_rating.pool erweitern führt nicht zum gewünschten Ergebnis - ich steh' am Schlauch und bitte um Hinweise, wie man dieses Problem schlauerweise löst.

Danke.

[Vinzenz Mai]

Hallo,

Hallo, Datenbank ist MySQL 5.1.x und ich haben folgende Tabellenstruktur:

recipe:
uid | title     | fe_user
----+-----------+-----------
1   | rezept 1  | 4

rating:
uid |fe_user | recipe | pool | score
----+--------+--------+------+------
#   | 1      | 1      | 1    | 5
#   | 2      | 1      | 1    | 4
#   | 3      | 1      | 2    | 1

Zusätzlich gibt es das Feld "pool" - jede Bewertung wird einem Pool zugeordnet - jeder Pool darf nur 1x in die Bewertung einfließen, die Reihefolge spielt dabei keine Rolle.

ehrlich gesagt, verstehe ich das Bewertungssystem nicht.

Mit nachfolgender Abfrage erreiche ich, dass Rezept 1 eine Durchschnittsbewertung von 3,33 erhält ((5+4+1)/3).

Da aber die Bewertung des Benutzers 1 und 2 demselben Pool angehören, darf nur einer der Werte in die Wertung einbezogen werden - das Ergebnis sollte dann 3 ((5+1)/2) oder 2,5 ((4+1)/2) sein - die Sortierung ist dabei völlig egal, wichtig ist nur, dass sobald eine Doppelung des Pools stattfindet, nur der erste Wert (ungeachtet der Sortierreihenfolge) verwandt wird.

Warum soll bei gleichen Daten ein nicht unbedingt reproduzierbares Ergebnis zurückgeliefert werden? Das geht mit MySQL in der Standardkonfiguration, keine Frage. Aber warum könnte das Ergebnis nicht 2,75 sein: ((5+4)/2 + 1)/2, d.h. aus den Mittelwerten der Pools gebildet werden? Ich fände das sinnvoller.

Zurück zu Deiner Anforderung:

Wähle aus Tabelle 2 je Rezept und Pool einen Score aus:

  
-- Wir nutzen bewusst MySQLs Großzügigkeit.  
-- Es ist egal, welcher Wert in score steht.  
-- Es ist klar, dass andere DBMS Syntaxfehler melden.  
SELECT  
    recipe,  
    pool,  
    score  
FROM  
    rating  
GROUP BY  
    recipe,  
    pool  

Welcher der Werte für score genommen wird, ist nicht vorhersagbar - aber das willst Du ja. Verknüpfe das Ergebnis dieser Abfrage mit Deinen Rezepten, gruppiere und mittle wie gehabt.

SELECT  
    recipe.uid,  
    recipe.title,  
    AVG(s.score) AS mean,  
    COUNT(s.score) AS num  
FROM  
    recipe  
LEFT JOIN (  
    SELECT  
        recipe,  
        pool,  
        score  
    FROM  
        rating  
    GROUP BY  
        recipe,  
        pool) AS score  
ON  
    recipe.uid = score.recipe  
GROUP BY  
    recipe.uid,  
    recipe.title   -- könntest Du bei MySQL auch weglassen :-)  
ORDER BY  
    mean DESC,  
    num DESC  

Warum Du einen RIGHT JOIN verwendst, verstehe ich nicht. Ich kann mir vorstellen, dass es Rezepte ohne Bewertung gibt, aber nicht, dass es Bewertungen für nicht existente Rezepte gibt - und vor allem kann ich mir nicht vorstellen, was Du mit diesen Bewertungen für nicht existente Rezepte anfangen willst.

Freundliche Grüße

Vinzenz

[suit]

ehrlich gesagt, verstehe ich das Bewertungssystem nicht.

Ich auch nicht - ich habs versucht, aber der Kunde ist sehr Beratungsresistent.

Warum soll bei gleichen Daten ein nicht unbedingt reproduzierbares Ergebnis zurückgeliefert werden? Das geht mit MySQL in der Standardkonfiguration, keine Frage. Aber warum könnte das Ergebnis nicht 2,75 sein: ((5+4)/2 + 1)/2, d.h. aus den Mittelwerten der Pools gebildet werden? Ich fände das sinnvoller.

Habe ich auch vorgeschlagen - aber ich werde nochmal zumindest ein MIN() oder MAX() vorschlagen, damit der Wert reproduzierbar bleibt.

Welcher der Werte für score genommen wird, ist nicht vorhersagbar - aber das willst Du ja. Verknüpfe das Ergebnis dieser Abfrage mit Deinen Rezepten, gruppiere und mittle wie gehabt.

That's it danke - darauf bin ich nicht gekommen, fehlt allerdings noch dass "num" nun nicht mehr stimmt, aber das lässt sich auf dieselbe Weise lösen.

Warum Du einen RIGHT JOIN verwendst, verstehe ich nicht. Ich kann mir vorstellen, dass es Rezepte ohne Bewertung gibt, aber nicht, dass es Bewertungen für nicht existente Rezepte gibt - und vor allem kann ich mir nicht vorstellen, was Du mit diesen Bewertungen für nicht existente Rezepte anfangen willst.

Mein Fehler, danke für den Hinweis - ist wohl in geistiger Umnachtung passiert, da gehört natürlich ein LEFT JOIN hin.

[Vinzenz Mai]

Hallo suit,

ehrlich gesagt, verstehe ich das Bewertungssystem nicht.

Ich auch nicht - ich habs versucht, aber der Kunde ist sehr Beratungsresistent.

Du Armer! Aber wenn's bezahlt wird ...

That's it danke - darauf bin ich nicht gekommen, fehlt allerdings noch dass "num" nun nicht mehr stimmt, aber das lässt sich auf dieselbe Weise lösen.

ach so: da soll die Anzahl aller Bewertungen stehen und nicht die Anzahl der in die Wertung eingehenden Bewertungen. Auf diese Idee wäre ich nicht von selbst gekommen. Lustiges Bewertungssystem :-)

Lass es Dir die Kriterien schriftlich geben.

Freundliche Grüße

Vinzenz

[suit]

Lass es Dir die Kriterien schriftlich geben.

Gibt es schon :) aber da ändert sich ohnehin alle drei Wochen etwas :D

Danke nochmal für die Hilfe, funktioniert mittelweile einwandfrei.