[php|mysql] Durchschnittswert einer Spalte (ohne dupli)?

[Wuppi]

Hi

ich hab mal wieder ein kleines Datenbankausleseproblem. Ich habe 2 relevante Spalten:
ID | Note

ID ist nicht unique und kommt demnach mehrfach vor - aber IMMER mit der gleichen Note.

ID | Note
1 | 10
1 | 10
2 | 9
3 | 8
1 | 10
4 | 5

Ich möchte jetzt folgendes per php rausbekommen: Wie ist die Durchschnittsnote ohne das doppelte IDs berücksichtigt werden. Also (10 + 9 + 8 + 5) / 4 = 8

Das will mir net so gelinge
Ich muss hier wohl mit distinct arbeiten - das setze ich auf ID an.

Mit ...

Code: Alles auswählen

SELECT distinct id, `note` FROM `test` where id != ''

(id != ... id ist INT und da steht sonst NULL wenn es noch keine ID gibt; so filter ich die NULLer raus)
... bekomme ich zumindest schonmal angezeigt welche ID welche Note hat, ohne das doppelte IDs berücksichtigt werden:
ID | Note
1 | 10
2 | 9
3 | 8
4 | 5

Mit

Code: Alles auswählen

SELECT count(distinct id, note) as treffer FROM `test` where id != ''

bekomme ich raus wieviele relevanten Treffer ich habe:
treffer
4

Jetzt müsste ich diese beiden verknüpfen und kann per php teilen.
Treffer | Note
4 | 32

per php lese ich beide Werte aus ... 32/4 = 8 - fertig (wie ich auf die 8 komme ist mir letzendlich egal - wenn ich die direkt aus sql rausbekomme - auch gut )

Aber das will nicht so recht

Hab schon mal mit sum/avg versucht zu arbeiten - aber da bekomme ich kein distinct eingebaut. Er berechnet mit die Summe/Schnitt ALLER Noten

Hat da jemand eine Idee?

Gruß

[Pyramide]

ID ist nicht unique und kommt demnach mehrfach vor - aber IMMER mit der gleichen Note.

Hört sich nach fehlender normalisierung an...wie sieht denn die komplette Tabellenstruktur aus? Vermutlich kann man das Problem mit einer passenderen Tabellenstruktur viel einfacher lösen.

[Wuppi]

Hi

es ist nicht DIE ID. Sondern eine von vielen!
Es gibt noch eine normale Nummerierung - diese ist natürlich Unique

Es handelt sich um eine Filmdatenbank ... DVD, BD usw. sind gelistet - mit einer laufenden Nummer für JEDEN Eintrag. Nur manchmal hab ich Filme in 3-4 Qualitätsstufen - da die noch im Bestand sind, sind diese dort noch gelistet (teils auch uncuts als dvb-Aufnahme und Kino-Version als DVD). Aber jeder Film (unabhängig von Datenträger/Qualität) hat zusätzlich seine OFDB-ID ... um die ID geht es. Hab ich den Film 3x hab ich 3x diese identische OFDB-ID. Jetzt möchte ich aber die Benotung der Filme aus der OFDB übernehmen (Import-Script ist bereits fertig) ... und weil ich einen Statistikfimmel habe , möchte ich wissen wie der Notenschnitt der Sammlung ist ). Nur einen Film 3x für den Durchschnitt zu erheben bringt nichts - das verfälscht das Gesamtergebniss. (bei meinen Testfiles Note 6,66 [unique] zu 6,87 [mit duplikaten]) [ja alles spielerei *G*]

Von daher wüsste ich nicht was sich da verbessern lässt.

Ich plane schon seit mehreren Jahren eine andere Tabellenstruktur - aber genauso lang rätsel ich daran wie diese auszusehen hat. Es gibt viele Filmverwaltungsdatenbanken - aber KEINE - wirklich KEINE bietet diese Ausführlichkeit die ich vorhabe! (webbasierte; die anderen hab ich mir net angeschaut). Also selber machen - nur die Menge an Infos optimal unterbringen ist nicht leicht. (ein MKV hat ganz andere Archivierbare Infos als eine DVD; ein .avi hat noch weniger; eine Serienfolge unterscheidet sich Gewaltig von einem Film usw.; und eine Doku ist ganz anders als Film/Serie)

Gruß

Gruß

[gn#36]

Die von Pyramide angesprochene Normalisierung hilft dir sehr gut, eine effiziente und gut abfragbare Tabellenstruktur zu erstellen. Im Prinzip ist dein Problem ja Redundanz, denn du hast mehrfach bis auf die Zähl-ID identische Daten in der Datenbank. Wenn du die Datenbank normalisierst würdest du die redundanten Daten in eine Tabelle legen und einen neuen, eindeutigen Primärschlüssel definieren, diesen würdest du dann in der ursprünglichen Tabelle den eindeutigen Daten zuordnen so dass jeder Datensatz nur ein mal in der neuen Tabelle steht. Oft sind auch mehr als zwei Tabellen sinnvoll.

In deinem Beispiel würdest du jeden Film nur ein mal in der einen Tabelle ablegen und z.b. diese OFDB-ID als Primärschlüssel wählen. In der anderen Tabelle hast du dann nur eine Kombi aus ID und OFDB-ID, sowie alle Daten die sich nur auf die spezielle Kopie des Films beziehen (bei einem Verleih z.B. Kunden ID und Rückgabedatum o.ä.) die Note wäre Filmbezogen und die Auswertung entsprechend ganz einfach.

Die Idee ist so lange systematich Tabellen einzufügen, bis die Redundanzen verschwunden sind (also keine Tabelle mit den gleichen Daten auf zwei Datensätzen in einer Spalte). Ganz auf die Spitze treibt man es meist nicht und behält einige sinnvollere redundanzen bei, aber ein wenig normalisierung kann nicht schaden.

PHP seitig die vorhandene Struktur auswerten geht natürlich auch nicht schwer. Per select distinct nur die benötigten ids und noten holen und dann in php summieren und mittelwert bilden. Per Subselect dürfte es auch möglich sein die Daten zu filtern:

Code: Alles auswählen

Select sum(spalte)/count(*) From
(Select distinct spalte from tabelle where id <> '')

[Wuppi]

Hi

bei dem "auslagern" war ich schon mal bei der Nachfolge-DB ... das wären über 20 Tabellen geworden ;/ (und zwischenzeitlich sind mir noch einige weitere sachen eingefallen) ... das war auch der Hauptgrund warum das Gerüst seit Jahren auf Platte liegt. Allein das durchprobieren ob die Tabellen so auch alle von überall aus "erreichbar" sind oder ob ich Tabellen angelegt habe die keine referenz irgendwo hin habe war mir erstmal zu stressig. Das ganze dann noch in php zum auslesen machen ... Thema Import ist noch ein größeres ... 20 Tabellen füllen ... testen testen testen - daher existiert der Nachfolger auch nur auf "Papier" (excel, txt) ... mir fehlt da einfach die Zeit mal paar Wochen am STÜCK drin zu investieren Das Hauptproblem ist bei mir - und das hat nix mit epeen zu tun ... ausser mir sieht die DB keiner - mein Statistiktick - zu ALLES was man auswerten kann, möchte ich auch eine Auswertung machen - ALLES.

Daher pimpe ich lieber die vorhandene Datenbank weiter auf. Ist halt einfacher immer wieder mal ein neues features hinzuzufügen als komplett neu zu machen. Aktuell ist das Bewertungsthema dran. 2 neue Spalten sind drin - Anzeige der Bewertung in der Ausgabe ist auch erledigt. Importscript für die Bewertungen ist lauffähig. Mir fehlt jetzt nur das statistische Element. Also durchschnittliche Bewertung der Sammlung (und der Auswahl)

Daher wäre es mir lieb das aktuelle Problem ohne umbauten zu lösen - dürfte die stressfreiste Variante sein.

Hab ja schon viel gesucht - aber so recht find ich nichts. Man kann mehrere SELECTS verschachteln - aber so halbwegs passende Beispiele hab ich da nicht gefunden. Ich könnte ja eigentlich die erste Abfrage von mir so lassen (die mit distinct; s. o.) - als Ergebniss bekomme ich ja quasi eine Tabelle mit ID | Note ... aber keine doppelten IDs (will ich ja) ... auf diese Tabelle müsste ich das nächste SELECT ansetzen avg(note) usw. Aber das verschachteln versteh ich noch nicht so wirklich. Am einfachsten wäre es aber imho wenn ich avg() einsetzen könnte unter berücksichtigung von disctinct. Mein Ansatz: distinct id, avg(note) klappt nicht - ich bekomme zwar ein Durchschnitt raus - allerdings von ALLEN IDs die eine Note haben das disctinct wirkt hier nicht ... oder ist falsch platziert

EDIT:

Code: Alles auswählen

Select sum(note)/count(*) From (Select distinct id from test where id<> '')

klappt nicht:
#1248 - Every derived table must have its own alias

EDIT 2:

Code: Alles auswählen

Select sum(note)/count(*) From test where id in (Select distinct id from test where id<> '')

gibt mir wieder nur ein Ergebniss über alle ids ... also wieder falscher Schnitt (statt 6,66 bekomm ich 6,87 - es werden also weiterhin die ID miteinbezogen die ich mit distinct ausschließen möchte)


Gruß

[Pyramide]

Wenn du unbedingt mit der vorhandenen Struktur arbeiten willst, dann geht es so:

Code: Alles auswählen

SELECT sum(a.note) / count(a.id) as durchschnitts_note FROM (
  SELECT t.id, min(t.note) as note
  FROM test AS t
  GROUP BY t.id
) AS a

Alternativ max(t.note), wenn du bei unterschiedlichen Noten die höchste nehmen willst.

Allerdings kann ich dir nur dringend zu der von gn#36 beschriebenen Trennung in Film und Qualitätsstufe raten - dann entstehen solche Probleme nämlich gar nicht erst (außerdem ist bei großen Datenmengen die Belastung solcher SQL-Abfragen wie oben i.d.R. deutlich höher als eine "normale" Abfrage bei einer sauberen Tabellenstruktur).

[Wuppi]

Hi

super! Danke dir - klappt. Sowohl in der Version "über alles" und "nur Suchergebniss".

Vielleicht pack ich heute noch den Performancetest


Wie gesagt: neue Tabellen - das würde auf neu hinauslaufen - das gedenke ich schon lange zu tun - aber da fehlt mir die Zeit und auch etwas an Motivation (da ist mir mein Web-Notizblock viel wichtiger - den brauch ich nämlich dringend *G*)

Gruß