Problem für MySQL Profis

[Hanno]

Hiho
Ich will aus einer Artikeldatenbank einen Artikel auslesen und gleichzeitig aus einer Bewertungstabelle seine Bewertungen auslesen. Dafür benutze ich "WHERE artikeltabelle.nummer = bewertung.nummer". Das klaptp auch wunderbar, jedoch nur so lange, wie auch in Bewertung eine Bewertung über den Artikel vorhanden ist. Wenn noch keine Bewertung abgegeben wurde, dann findet er den Artikel nicht. Hier das Statement:

SELECT COUNT(*) as bewertungen, SUM(bewertung.sterne) as bewertung, artikel.artikelname, artikel.artikelnummer, artikel.preis, artikel.alter_preis, artikel.sonderangebot, artikel.beschreibung, artikel.stueckzahl, artikel.verkauft, (100.00 - round(((100 * artikel.preis) / artikel.alter_preis),2)) as prozentsatz FROM sfb_artikel as artikel, sfb_bewertung as bewertung WHERE artikel.artikelnummer='1' AND bewertung.artikelnummer='1' GROUP BY bewertung.artikelnummer

Wie kriege ich es hin, dass er auch Artikel findet, die noch keine Bewertungen haben, und dann bewertungen und bewertung einfach auf 0 setzt.
Gruß
Hanno

[Klaus Mock]

Hallo,

SELECT COUNT(*) as bewertungen, SUM(bewertung.sterne) as bewertung, artikel.artikelname, artikel.artikelnummer, artikel.preis, artikel.alter_preis, artikel.sonderangebot, artikel.beschreibung, artikel.stueckzahl, artikel.verkauft, (100.00 - round(((100 * artikel.preis) / artikel.alter_preis),2)) as prozentsatz FROM sfb_artikel as artikel, sfb_bewertung as bewertung WHERE artikel.artikelnummer='1' AND bewertung.artikelnummer='1' GROUP BY bewertung.artikelnummer

Zuerst einmal ist es eine Unsitte, die dummerweise von mySQL auch noch unterstützt wird, wenn man Felder ohne Aggregat-Funktionen abfrägt, die nicht auch in der GROUP BY - Klausel vorkommen. Andere Datenbanksysteme würden hier richtigerweise einen Fehler feststellen, und die Ausführung solcher Statements verweigern.

Das Zauberwort für Dein Problem ist 'OUTER JOIN'. Abfragen über mehrere Tabellen sollten, wenn das DBMS es unterstützt, mittels JOIN-Anweisungen gebildet werden. OUTER JOINs sind JOINs, die es Erlauben Datensätze aus der einen Tabelle auch anzeigen zu lassen, welche keine zugeordneten Dtaensätze in einer verknüpften Tabelle besitzen.
Wie das genau geht kannst Du der Dokumentation der Datenbank (in Deinem Falle eben mySQL) entnehmen. http://www.mysql.com/doc/en/JOIN.html

Grüße
  Klaus

[Hanno]

Wieso ist es eine Unsitte, die Felder abzufragen !!??
Das versteh ich jetzt nicht, wozusind die Aggregatgfunktionen denn sonst da.
Gruß0
Hanno

[Klaus Mock]

Hallo,

Wieso ist es eine Unsitte, die Felder abzufragen !!??
Das versteh ich jetzt nicht, wozusind die Aggregatgfunktionen denn sonst da.

Es ist eine Unsitte, wenn man beispielsweise so etwas formuliert:
SELECT  name, strasse,  SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name

und zwar nicht deshalb, weil Du SUM(wasauchimmer) verwendest, sondern weil Du 'strasse' ohne einer Aggregatfunktion ausgeben läßt, ohne 'strasse' auch in der GROUP BY Klausel zu benutzen. Es könnte ja durchaus sein, daß 'strasse' für einen Wert aus 'name' in verschiedenen Datensätzen auch unterschiedlich sein kann, was zu einer Mehrdeutigkeit führt, welche sinnvoll von einem DBMS nicht aufgelöst werden kann.

mySQL würde obiges Statement dummerweise ausführen, andere Datenbanken jedoch, wegen der angesprochenen Mehrdeutigkeit, nicht.

Mögliche Lösungen könnten dann sein:

SELECT  name,  SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name

SELECT  name, MAX(strasse), SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name

(wobei MAX() jetzt für eine mehr oder weniger beliebige Aggregatfunktion steht)

SELECT  name, strasse,  SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name, strasse

Grüße
  Klaus

[Hanno]

Ich habe das PRoblem jetzt so gelöst:

SELECT COUNT(*) as bewertungen, SUM(bewertung.sterne) as bewertung, artikel.artikelname, artikel.artikelnummer, artikel.preis, artikel.alter_preis, artikel.sonderangebot, artikel.beschreibung, artikel.stueckzahl, artikel.verkauft, (100.00 - round(((100 * preis) / alter_preis),2)) as prozentsatz FROM sfb_artikel as artikel LEFT OUTER JOIN sfb_bewertung as bewertung ON artikel.artikelnummer=bewertung.artikelnummer WHERE artikel.artikelnummer='1' GROUP BY bewertung.artikelnummer

Ist das denn auch für dich akzeptabel ?
Gruß
Hanno

[Philipp Hasenfratz]

Halihallo Hanno

SELECT COUNT(*) as bewertungen, SUM(bewertung.sterne) as bewertung, artikel.artikelname, artikel.artikelnummer, artikel.preis, artikel.alter_preis, artikel.sonderangebot, artikel.beschreibung, artikel.stueckzahl, artikel.verkauft, (100.00 - round(((100 * preis) / alter_preis),2)) as prozentsatz FROM sfb_artikel as artikel LEFT OUTER JOIN sfb_bewertung as bewertung ON artikel.artikelnummer=bewertung.artikelnummer WHERE artikel.artikelnummer='1' GROUP BY bewertung.artikelnummer
Ist das denn auch für dich akzeptabel ?

Nein, aber wenn artikel.artikelnummer der PRIMARY KEY von artikel ist (was ich annehme),
dann muss die RDBMS zumindest nicht raten und der Query ist vom Inhalt her AFAIK OK.
_Aber_ du solltest dir wirklich einen besseren Stil zum niederschreiben der Queries
aneignen, solche Rattenschwänze sind einfach unschön zu lesen. Sieh dir die Beispiele
von Klaus an, _diese_ lassen sich schön lesen. Letzteres weswegen ich mit Nein
antworte ;)

Viele Grüsse

Philipp

[Klaus Mock]

Hallo,

Nein, aber wenn artikel.artikelnummer der PRIMARY KEY von artikel ist (was ich annehme),
dann muss die RDBMS zumindest nicht raten und der Query ist vom Inhalt her AFAIK OK.

wobei ja nach bewertung.artikelnummer gruppiert wird, was zur Folge hat, daß, sofern noch keine Bewertungen zum Artikel existieren, dieses Feld NULL ist. imho ist das Statement auch aus fachlicher Sicht nicht korrekt, abgesehen davon daß es wirklich nur unter mySQL funktionert.

_Aber_ du solltest dir wirklich einen besseren Stil zum niederschreiben der Queries
aneignen, solche Rattenschwänze sind einfach unschön zu lesen.

full ACK. Eine sinnvolle und konsistente Formatierung von SQL-Statements sind imho genauso wichtig wie es auch in anderen Programmiersprache ist. Nicht nur, daß sie unschön sind, sie sind auch ein Graus bei der Fehlersuche.

Grüße
  Klaus

[Philipp Hasenfratz]

Halihallo Klaus

Nein, aber wenn artikel.artikelnummer der PRIMARY KEY von artikel ist (was ich annehme),
dann muss die RDBMS zumindest nicht raten und der Query ist vom Inhalt her AFAIK OK.
wobei ja nach bewertung.artikelnummer gruppiert wird, was zur Folge hat, daß, sofern noch keine Bewertungen zum Artikel existieren, dieses Feld NULL ist. imho ist das Statement auch aus fachlicher Sicht nicht korrekt,

Gegenfrage: Kann man dieses fachlich korrekt formulieren? - Das Problem ist der OUTER
JOIN, welcher NULL-Values produziert. Ob die Anfrage sachlich korrekt ist und dem
gewollten Ziele dienlich ist, hängt nicht zuletzt von der Implementierung der NULL-Values
in der DBMS ab.

Aus: http://www.mysql.com/doc/en/GROUP-BY-Functions.html
---
COUNT(expr)
Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement:
mysql> SELECT student.student_name,COUNT(*)
    ->        FROM student,course
    ->        WHERE student.student_id=course.student_id
    ->        GROUP BY student_name;

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimised to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
---

Leider wurde hier nicht genannt, wann NULL-Values genau gezählt werden. Es gibt jedoch,
wenn ich die Absätze richtig interpretiere einen Unterschied zwischen COUNT(*) und
COUNT(expr). Letzterer liefert nur die Anzahl non-NULL-Values zurück, während ersterer
auch die NULL-Values zählt. SUM(expr) zählt IMHO nur non-NULL Values, sprich funktioniert
im Sinne der Aufgabenstellung. Fehler liegt also bei COUNT, durch hinzufügen des Primary
Keys in COUNT() dürfte die Abfrage fachlich korrekt im Sinne von MySQL sein. Oder
siehst du etwas anderes?

abgesehen davon daß es wirklich nur unter mySQL funktionert.

Nun ja, die Syntax ist immer Systemabhängig, liesse sich aber ohne grosse Aufwände
in andere Datenbanken portieren; oder verhalten sich etwa _alle_ anderen Datenbanken
anders? - Das glaube ich weniger...

Viele Grüsse

Philipp

[Klaus Mock]

Hallo,

Gegenfrage: Kann man dieses fachlich korrekt formulieren? - Das Problem ist der OUTER
JOIN, welcher NULL-Values produziert. Ob die Anfrage sachlich korrekt ist und dem
gewollten Ziele dienlich ist, hängt nicht zuletzt von der Implementierung der NULL-Values
in der DBMS ab.

Naja, nach dem er ja nach 'artikelnummer' gruppieren will, und noch dazu auch andere Spalten von der Tabelle 'artikel', währe es doch naheliegend (u.a.) nach 'artikel.artikelnummer' zu gruppieren, oder? Nach 'bewertung.artikelnummer' zu sortieren ist hier sinnlos, da es potentiell eben auch NULL sein kann und dadurch nicht immer mit dem Wert von 'artikel.artikelnummer' identisch ist.

Das meinte ich mit 'fachlich nicht korrekt'.

abgesehen davon daß es wirklich nur unter mySQL funktionert.

Nun ja, die Syntax ist immer Systemabhängig, liesse sich aber ohne grosse Aufwände
in andere Datenbanken portieren; oder verhalten sich etwa _alle_ anderen Datenbanken
anders? - Das glaube ich weniger...

Mir ist schon klar, daß man, je nach DBMS, unterschiedliche SQL-Dialekte verwenden kann, und oft auch muß, da sich ja i.d.R. der Funktionumfang der Systeme unterscheidet.
Aber darum geht's imho bei dieser Sache gar nicht. Es geht einfach nur darum, wie man ein SQL-Statement formuliert, welches keine Mehrdeutigkeit zuläßt. Und von einem DBMS erwarte ich mir u.a., daß es Mehrdeutigkeiten durch entsprechende Maßnahmen schlich und ergreifend verhindert.

Grüße
  Klaus

[Andreas Korthaus]

Hi Klaus!

Es ist eine Unsitte, wenn man beispielsweise so etwas formuliert:
SELECT  name, strasse,  SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name

ich muss gestehen dass ich hiermit ein kleines Problemchen habe. Ich will z.B. den Artikelnamen abfragen, mit dem geringsten Preis in der Tabelle. Ich mache das jetzt so:

SELECT Artikelname FROM table ORDER BY Preis LIMIT 1

Nur habe ich was gegen ORDER BY, denn wenn ich mich nicht irre ist hierzu ein FullTable-Scan notwendig. Zumindest ist das so wenn ich 2 Datensätze abfragen würde, denn dann muss er alle Werte 'Preis' in der Tabelle miteinander vergleichen. Ich weiß jetzt nicht ob der Query-Optimizer von mySQL den Minimal-Wert auch anderes errechnen kann, ich könnte es mir vorstellen, weiss es aber nicht.

Wie formuliert man denn sowas am besten?

Wobei mir aufgefallen ist dass sich da bei MySQL evtl was geändert hat, ich verwende Version 3.23.54, udn wenn ich da so eien Abfragwe mache:

SELECT Artikelname, MIN(Preis) FROM Table

geht das nicht(logisch!). Aber wenn ich jetzt noch ein Gruop BY Artikelname hinzufüge, dann bekomme ich den niedrigsten Preis für jeden Artikel, das ist es ja auch nicht was ich will.  Sicher könnte ich da dann auch wie oben Ordnen und limitieren, nur ist das dann am Ende dasselbe.

Viele Grüße
Andreas

[Philipp Hasenfratz]

Halihallo Andreas

Es ist eine Unsitte, wenn man beispielsweise so etwas formuliert:
SELECT  name, strasse,  SUM(wasauchimmer)
  FROM tabelle
  GROUP BY name
ich muss gestehen dass ich hiermit ein kleines Problemchen habe. Ich will z.B. den Artikelnamen abfragen, mit dem geringsten Preis in der Tabelle. Ich mache das jetzt so:
SELECT Artikelname FROM table ORDER BY Preis LIMIT 1

Jups.

Nur habe ich was gegen ORDER BY, denn wenn ich mich nicht irre ist hierzu ein FullTable-Scan notwendig. Zumindest ist das so wenn ich 2 Datensätze abfragen würde, denn dann muss er alle Werte 'Preis' in der Tabelle miteinander vergleichen. Ich weiß jetzt nicht ob der Query-Optimizer von mySQL den Minimal-Wert auch anderes errechnen kann, ich könnte es mir vorstellen, weiss es aber nicht.

Bei ORDER BY ist leider im Normalfall wesentlich mehr nötig, als ein fulltable-scan.
Zumal er erstens alle auslesen muss (fulltable-scan) und danach noch sortieren muss.
Die Komplexität bzw. Zeitaufwand beträgt nicht nur n (für Anzahl Datensätze), wie beim
Fulltable-scan, sondern gar n*log(n).
Wie du jedoch bereits vermutet hast, kann man in
http://www.mysql.com/doc/en/LIMIT_optimisation.html sehen, dass ein
LIMIT den ORDER BY gar nicht ganz auszuführen braucht, sondern einfach bei LIMIT 1 den
ersten (maximalen, minimalen) Datensatz zurückgibt, ohne alles andere sortieren zu
müssen. Die Komplexität beträgt also n wie beim fulltable-scan. Befindet sich das
Attribut allerdingt im Index ist der grösse bzw. kleinste Wert natürlich innerhalb
eines Festplattenzugriffes gefunden, ggf. ein Zweiter um den Namen des Artikels aus der
Tabelle zu lesen. Der Zeitaufwand für deine Abfrage ist minimal.

Wie formuliert man denn sowas am besten?

So wie du es hast, ist es _sehr_ performant, wenn Preis indiziert ist; noch performanter
wird es IMHO, wenn Artikelname indiziert ist, denn dann muss MySQL lediglich auf den
Index zugreifen, ein selektieren in der MYD (bei MyISAM) bleibt gänzlich aus.

SELECT Artikelname, MIN(Preis) FROM Table
geht das nicht(logisch!). Aber wenn ich jetzt noch ein Gruop BY Artikelname hinzufüge, dann bekomme ich den niedrigsten Preis für jeden Artikel, das ist es ja auch nicht was ich will.  Sicher könnte ich da dann auch wie oben Ordnen und limitieren, nur ist das dann am Ende dasselbe.

Nicht ganz. Der Zeitaufwand ist, wenn auch unbedeutend in Verbindung mit LIMIT, grösser.

@Klaus(anderes Posting): Full ACK, hatte ich übersehen (GROUP BY-Klausel).

Viele Grüsse

Philipp

[Klaus Mock]

Hallo,

SELECT Artikelname FROM table ORDER BY Preis LIMIT 1

Wobei Du Dir bewußt sein solltest, daß hier nur _ein_ Artikel abgefragt werden kann, der den kleinsten PReis hat. Sind mehrere Artikel mit dem selben Preis in der Datenbank, ist es mehr oder weniger Glückssache, welcher als Ergebnis geliefert wird.

Wie formuliert man denn sowas am besten?

Wenn Subselects möglich sind (afaik unter mySQL ab Version 4.1) dann würde ich es, denke ich, so schreiben:
SELECT Artikelname FROM table
   WHERE Preis = ( SELECT MIN(Preis) from table)

Ob's so wirklich optimal ist, kann ich nicht sagen, da ich mich nicht wirklich zu den Datenbank-Experten zähle.

Und wenn die Datenbank so etwas nicht unterstützt, kann es durchaus sein, daß man auf externe, sprich programmierte, Logik ausweichen muß.

Grüße
  Klaus

[Andreas Korthaus]

Hi Klaus!

SELECT Artikelname FROM table ORDER BY Preis LIMIT 1

Wobei Du Dir bewußt sein solltest, daß hier nur _ein_ Artikel abgefragt werden kann, der den kleinsten PReis hat. Sind mehrere Artikel mit dem selben Preis in der Datenbank, ist es mehr oder weniger Glückssache, welcher als Ergebnis geliefert wird.

Guter Einwurf, theoretisch ist das durchaus möglich, also muss ich noch nach timestamp sortieren, also:

SELECT Artikelname FROM table ORDER BY Preis,Timestamp LIMIT 1

Wie formuliert man denn sowas am besten?

|

Wenn Subselects möglich sind (afaik unter mySQL ab Version 4.1) dann würde ich es, denke ich, so schreiben:

Also MySQL 4.1 will ich dann doch nochnicht auf einem produktiven Syetem einsetzen ;-)

SELECT Artikelname FROM table
   WHERE Preis = ( SELECT MIN(Preis) from table)

Aber das ist es eigentlich was ich will, wobei, wenn ich hier noch den mit dem ältesten Timestamp finden will, brauche ich da wohl noch ein Subselect oder RDER BY + Limit, oder? Mit Subselects habe ich Dank MySQL noch nie was gemacht...

Die Abfarge soll aber mit MySQl _und_ PostgreSQL funktionieren, udn ich denek obige sollte das tun. Da gibt es nämlich so lustige Sachen wie dass LIMIT 1,2 in MySQL andere Datensätze zurück gibt als in PostgreSQL(da wäre das dann LIMIT 2,1)...
Das macht eien DB-Abstraktion nicht wirklich leichter...

Grüße
Andreas

[Klaus Mock]

Hallo,

SELECT Artikelname FROM table ORDER BY Preis,Timestamp LIMIT 1

[...]

SELECT Artikelname FROM table
   WHERE Preis = ( SELECT MIN(Preis) from table)

Aber das ist es eigentlich was ich will, wobei, wenn ich hier noch den mit dem ältesten Timestamp finden will, brauche ich da wohl noch ein Subselect oder RDER BY + Limit, oder?

Wenn Du sowieso den Artikel mit dem kleinsten Preis und dem ältesten Timestamp haben willst, finde ich Deinen Ansatz an sich nicht so schlecht, wobei Du anscheinend gerne nur 'zitzerlweise'[1] mit der ganzen Wahrheit herausrückst;-)

Die Abfarge soll aber mit MySQl _und_ PostgreSQL funktionieren, udn ich denek obige sollte das tun. Da gibt es nämlich so lustige Sachen wie dass LIMIT 1,2 in MySQL andere Datensätze zurück gibt als in PostgreSQL(da wäre das dann LIMIT 2,1)...
Das macht eien DB-Abstraktion nicht wirklich leichter...

<cite src="Sheryl Crow">
No one said it would be easy
</cite>

Günstigerweise lagert man dann die Statements in Module aus, welche konfigurationsabhängig herangezogen werden. Wobei es ishc als hilfreich erwiesen hat, wenn die Datenbankschnistelle Bind-Variablen beherrscht.

Grüße
  Klaus

[1] ähm, das ist im steirischen gerne verwendet und entspricht in etwa 'häppchenweise', aber nur im entferntesten.