nicht angemeldet
Voting DB-Abfrage?
Guten morgen!
Ich bin seit gestern am Grübeln wie ich am besten die Votingeinträge auswerten und ausgeben kann. Hoffe jemand von euch hat eine Lösung dafür!
Ich habe folgende Ausgangssituation:
3 Tables:
foto Bsp:
ID Titel userid
1 Baum 2
2 Fluss 1
etc.
user
ID Name ...
1 Hans
2 Max
etc
voting
ID fotoid vote
1 1 2
2 1 3
3 2 1
4 2 4
5 1 1
6 2 2
etc.
Ergebnis sollte folgende HTML Tabelle sein:
Rang Notendurchschnitt Fototitel Username
1 2,00000 Baum Max
2 2,33333 Fluss Hans
Sprich ich muss alle Noten zu jeweils einer Id zusammenzählen und durch die Anzahl der abgegebenen Bewertungen dividieren und dann nach dem Notendurchschnitt reihen und ausgeben.
Soweit die Theorie nur in der Praxis gestaltet sich das leider nicht so leicht.
Nach meinem Wissensstand werd ich bei der Mysqlabfrage SUM(), COUNT(), GROUP BY und ORDER BY brauchen. Is nur die Frage geht das in einer Abfrage oder in mehreren oder muss ich mir dazu eine TMP-Table anlegen (wenn ja wie)
Bitte helft mir bei diesem Problem weiter. Ich bin schon fast am verzweifeln.
Lg Martin
-
Hallo Martin!
SELECT AVG(voting.vote) AS 'Durchschnitt',
foto.Titel,
user.Name
FROM foto, user, voting
WHERE foto.userid = user.ID
AND voting.fotoid = foto.ID
GROUP BY voting.fotoid
ORDER BY durchschnitt DESCAnmerkung: Ich bin mir nicht ganz sicher, ob die letzte Zeile mit "ORDER BY ..." im Zusammenhang mit "GROUP BY ..." funktioniert.
Wenn es nicht funktioniert, dann die Abfrage ohne die letzte Zeile ausführen und in eine temporäre Tabelle speichern und mit einer zweiten Abfrage diese temporäre Tabelle mit "ORDER BY ..." sortiert ausgeben.Bei der Ausgabe bzw. der HTML-Generierung liest du Datensatz für Datensatz aus und Nummerierst die Enträge. Somit hast du auch die Rangzahlen.
-
yo,
SELECT AVG(voting.vote) AS 'Durchschnitt',
foto.Titel,
user.Name
FROM foto, user, voting
WHERE foto.userid = user.ID
AND voting.fotoid = foto.ID
GROUP BY voting.fotoid
ORDER BY durchschnitt DESCmysql schluckt solch konstrukte zwar, sollte es aber nicht. dieser "fehler" wird sehr oft gemacht, aggregat-funktionen zusammen mit anderen spalten auszugeben, über die nicht gruppiert wurde. mysql spielt da ein wenig mit dem feuer, aber meiner meinung nach mit mehr nachteilen als vorteilen.
um sich besser vor augen halten zu können, muss man sich ins bewußtsein rufen, dass eine aggregat(gesamt) funktion immer nur genau einen wert zurück liefert. das hat zur folge, dass auch bei den anderen spalten, die angezeigt werden sollen, auch nur ein wert angezeigt werden kann. welche der eventuell sehr vielen user.name soll er den nun als einzigen anzeigen ?
Ilja
-
Hy
Danke für die prompten Antworten.
Ich werd das mal so ausprobieren und schauen was dabei rauskommt.
AVG() kenn ich noch gar nicht (man lernt ja wirklich nie aus)
Sollte da wirklich nur 1 Wert rauskommen könnt ma das vielleicht auf 2 Selects aufteilen 1. selectiere ich mir mit der fotoid titel und user und in der Ausleseschleife den 2. select wo ich mir den Durchschnitt raushole???
LG Martin
-
Hallo Martin!
Danke für die prompten Antworten.
Gern geschehen!
Ich werd das mal so ausprobieren und schauen was dabei rauskommt.
AVG() kenn ich noch gar nicht (man lernt ja wirklich nie aus)
Sollte da wirklich nur 1 Wert rauskommen könnt ma das vielleicht auf 2 Selects aufteilen
- selectiere ich mir mit der fotoid titel und user und in der Ausleseschleife den
- select wo ich mir den Durchschnitt raushole???
Nein, wenn du MySQL verwendest, wird es funktionieren. Du erhälst pro Foto einen Duschnitt, den Titel des Fotos und den Namen der zu dem Foto gehört. Zu den Verhalten anderer DBMS' kann ich nichts sagen.
-
yo,
Nein, wenn du MySQL verwendest, wird es funktionieren. Du erhälst pro Foto einen Duschnitt, den Titel des Fotos und den Namen der zu dem Foto gehört. Zu den Verhalten anderer DBMS' kann ich nichts sagen.
sorry, will nicht penibel sein, aber genau dass wird nicht passieren.
Ilja
-
Sorry ich will eure Fachsimpeleien nicht stören (die bei mir eher Verwirrung stiften als Aufklärung) aber ich hab diese Abfrage so eingebaut und die Ausgabe erscheint mir auf den ersten Blick eigentlich plausibel und ich glaube das diese Abfrage den Zweck zumindest für mich erfüllt.
Aber trotzdem vielen Dank für eure Wortspenden - wie bereits geschrieben - man lernt immer wieder was dazu.
Lg Martin
-
-
Hy Xava
Nochmals danke für deine Lösung. Funktioniert super. Wäre da alleine sicher nicht draufgekommen.
Lg Martin
-
-
Hallo Ilja!
SELECT AVG(voting.vote) AS 'Durchschnitt',
foto.Titel,
user.Name
FROM foto, user, voting
WHERE foto.userid = user.ID
AND voting.fotoid = foto.ID
GROUP BY voting.fotoid
ORDER BY durchschnitt DESCmysql schluckt solch konstrukte zwar, sollte es aber nicht. dieser "fehler" wird sehr oft gemacht,
aggregat-funktionen zusammen mit anderen spalten auszugeben, über die nicht gruppiert wurde. mysql
spielt da ein wenig mit dem feuer, aber meiner meinung nach mit mehr nachteilen als vorteilen.Ich bin hier anderer Meinung: Es ist kein "Fehler". Man kann nur nicht verhersagen, welcher der Werte/Zeile zu angezeigt wird. Und hier ist in vielen Anwendungen Vorsicht geboten.
Wo steht, dass man bei "GROUP BY ..." _nur_ Aggregatsfunktionen verwenden darf bzw. ausgeben darf und die anderen Spalten nicht?
um sich besser vor augen halten zu können, muss man sich ins bewußtsein rufen, dass eine aggregat(gesamt) funktion
immer nur genau einen wert zurück liefert.Ja, genau. Es gibt in einer Tabelle mehrere Einträge zu einem Wert "x" einer bestimmten Spallte "A".
A | B
--+----
x | Hugo
x | Anton
y | Berta
y | Karl
x | FridaSo werden bei einem "GROUP BY A" nur zwei Zeilen ausgegeben:
A | B
--+---
x | ?
y | ?Die Werte für die Spalte B kann nicht verhergesagt werden.
das hat zur folge, dass auch bei den anderen spalten, die angezeigt werden
sollen, auch nur ein wert angezeigt werden kann. welche der eventuell sehr vielen user.name soll er den nun als
einzigen anzeigen ?In dem konkreten Beispiel vom Ausgangsposting werden jedoch die möglichen Werte der Spalte "foto.Titel" und "user.Name" jeweils alle gleich sein.
Zur Verdeutlichung die Ausgabe ohne "GROUP BY ..." und "AVG(...)":
voting.ID voting.fotoid voting.vote foto.Titel user.Name
1 1 2 Baum Max
3 2 1 Fluss Hans
4 2 4 Fluss Hans
5 1 1 Baum Max
6 2 2 Fluss HansWenn man nun nach voting.fotoid gruppiert, gibt es zwei Zeilen:
voting.fotoid foto.Titel user.Name
Zeile 1: 1 Baum Max
Zeile 2: 2 Fluss HansBei der 1. Zeile gibt es jeweils 2 mögliche und bei der 2. Zeile jeweils 3 mögliche Werte jeweils für die Spalten "foto.Titel" und "user.Name". MySQL sucht sich jeweils einen der jeweils möglichen Werte aus und der Wert ist somit unverhersehbar. Aber da die jeweiligen zur verfügungstehenden Werte jeweils alle gleich sind, ist es in diesem Fall egal. Bzw. Welcher von den drei "Hans" nun ausgeben wird ist egal da sie alle drei gleich sind.
-
Hallo Ilja!
mysql schluckt solch konstrukte zwar, sollte es aber nicht. dieser "fehler" wird sehr oft gemacht,
aggregat-funktionen zusammen mit anderen spalten auszugeben, über die nicht gruppiert wurde. mysql
spielt da ein wenig mit dem feuer, aber meiner meinung nach mit mehr nachteilen als vorteilen.Ich bin hier anderer Meinung: Es ist kein "Fehler". Man kann nur nicht verhersagen, welcher der Werte/Zeile zu angezeigt wird. Und hier ist in vielen Anwendungen Vorsicht geboten.
Folgendes Zitat aus dem MySQL-Handbuch:
MySQL hat die Benutzung von GROUP BY erweitert. Sie können Spalten
oder Berechnungen im SELECT-Ausdruck angeben, die nicht im GROUP
BY-Teil erscheinen. Das steht für jeden möglichen Wert für diese
Gruppe. Das können Sie benutzen, um bessere Performance zu erzielen,
indem Sie Sortieren und Gruppieren unnötiger Bestandteile vermeiden.
Zum Beispiel müssen Sie in folgender Anfrage nicht nach kunde.name
gruppieren:mysql> select bestellung.kunde_id,kunde.name,max(zahlungen)
from bestellung,kunde
where bestellung.kunde_id = kunde.kunde_id
GROUP BY bestellung.kunde_id;In ANSI-SQL müssten Sie der GROUP BY-Klausel kunde.name hinzufügen.
In MySQL ist der Name überflüßig, solange Sie nicht im ANSI-Modus
fahren.Benutzen Sie dieses Feature nicht, wenn die Spalten, die Sie im GROUP BY-Teil
auslassen, in der Gruppe nicht eindeutig sind! Sonst erhalten
Sie unvorhersagbare Ergebnisse.(Quelle: http://dev.mysql.com/doc/mysql/de/Group_by_functions.html)
Somit haben wir beide ein stückweit Recht: Es ist in MySQL _kein_ Fehler, sondern ein Feature. Im ANSI-SQL jedoch muss -- wie oben beschreiben -- die anderen Spalten in die "GROUP BY" aufgeführt werden.
Bei anderen DBMS' bzw. bei ANSI-SQL funktioniert dies nur bei gleichen Werten in den Spalten, da sie ja mit ins "GROUP BY" müssen -- bei verschiedenen Werten würden dann auch verschiedene Zeilen ausgegeben werden.
Abzuraten ist diese zusätzliche Feature für Leute, die sich ein späteren Wechsel des DBMS' vorbehalten möchten. (Dies gilt für alle DBMS und deren zusätzlichen, nicht ANSI-SQL-komformen Befehlen)
-
yo,
um den zitat zu benutzen und die wichtigen stellen hervorzuheben:
Benutzen Sie dieses Feature --> nicht <--- , wenn die Spalten, die Sie im GROUP BY-Teil
auslassen, in der Gruppe --> nicht eindeutig sind! <-- Sonst erhalten
Sie unvorhersagbare Ergebnisse.Somit haben wir beide ein stückweit Recht:
auch wenn es immer was schönes ist, wenn alle recht haben, muss ich dir immer noch widersprechen. zum einen habe ich nicht gesagt, dass mysql das als fehler bewertet, sondern dass ich es als fehler ansehe, dass mysql das so zuläßt. es verwirrt nämlich zu sehr.
zum anderen habe ich mal die wichtigen stellen aus deinem zitat verhorgehoben. und die frage ist, sind die usernamen unterschiedlich ? ich denke mal ja. man kann also diese form der syntax bei mysql nur benutzen, um performance zu gwinnen, weil bei jedem group by eine sortierung erfolgt. aber auch sie setzen vorraus, dass alle werte innerhalb der spalte identisch sind, ähnlich wie bei den group by spalten.
ILja
-
Nochmals Hallo!
[...] zum einen habe ich nicht gesagt, dass mysql das als fehler bewertet, sondern dass ich es als fehler
ansehe, dass mysql das so zuläßt. es verwirrt nämlich zu sehr.Das kann u.U. verwirren, aber dass ist doch ein grundsätzliches Problem der Zusatzfeatures (die nicht ANSI-SQL-komform sind). Und erst richtig verwirrend ist, dass nicht alle DBMS' die jeweilige verwendete SQL-Version vollständig implementiert haben.
zum anderen habe ich mal die wichtigen stellen aus deinem zitat verhorgehoben. und die frage ist, sind die usernamen unterschiedlich ? ich denke mal ja.
Ich denke mal nein. Für jede Gruppe gleicher "fotoid"'s sind die Werte aller dazugehörigen Zeilen der Spalte "user.name" gleich. Der Name bezieht sich auf das Foto und die Namen sind nicht die Voter.
man kann also diese form der syntax bei mysql nur benutzen, um
performance zu gwinnen, weil bei jedem group by eine sortierung
erfolgt. aber auch sie setzen vorraus, dass alle werte innerhalb
der spalte identisch sind, ähnlich wie bei den group by spalten.Aus Performancegründen, ja. Aber es verbietet einen auch nicht dieses Feature mit unterschiedlichen Werten zu nutzen, jedoch wird ja auch brav davor gewarnt, dass dann der ausgegebene Wert nicht vorhersehbar ist.
-
yo,
zum anderen habe ich mal die wichtigen stellen aus deinem zitat verhorgehoben. und die frage ist, sind die usernamen unterschiedlich ? ich denke mal ja.
Ich denke mal nein. Für jede Gruppe gleicher "fotoid"'s sind die Werte aller dazugehörigen Zeilen der Spalte "user.name" gleich. Der Name bezieht sich auf das Foto und die Namen sind nicht die Voter.
schau, wenn immer nur ein und derselbe user für ein bild voten würde, dann bräuschte man gar keine funktion AVG(). mit anderen worten, mehrere personen stimmen für ein und dasselbe bild ab, ergo sind die usernamen und userid unterschiedlich. und genau das widerspricht sowohk ansi-sql als auch mysql.
der sinn, warum mysql das überhaupt macht ist, dass man normalerweise jede zusätzliche spalte, die keine aggregat-funktion ist, in die group by klausel mit angeben muss, wenn man aggregat funktionen benutzt. damit aber nicht für jede spalte in der group by klausel sortiert wird, sondern nur einmal und alle anderen werte sowieso eindeutig sind, hat mysql so ein feature entwickelt.
das verwirrt aber zu sehr, wie es genau auch dir und vielen anderen immer wieder passiert, weil mysql keine fehlermeldung mehr ausgibt, wenn man spalten ebenfalls mit angibt, die eben nicht mehr eindeutig sind. und genau da liegt der hacken.
Ilja
-
-
-
-
-
-