Kniffliges Problem - LEFT JOIN

[Faddin]

Hallo,

ich bastele und probiere an einer MySQL-Abfrage nun 2 Tage rum und komme zu keinen zufriedenstellenden Ergebnis.

Struktur:

[quote]
mysql> SELECT * FROM partners;
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+
| id | domain             | pw          | email           | session                          | klick | view |
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+
|  1 | domain.de           | domain | [e-mail]   | NULL                             | 1     | 1    |
|  2 | domain2.de | domain2      | [e-mail] | b1c1f05c320f508d10053058c3497b83 | 1     | 1    |
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+

mysql> SELECT * FROM p_rechnung;
+----+------------+--------+--------+------------+----------------------------------+-----+
| id | partner_id | anzahl | status | zeit       | link                             | typ |
+----+------------+--------+--------+------------+----------------------------------+-----+
|  1 |          1 |   1000 | 1      | 1108685695 | domain-200701-1000klick           | 0   |
|  4 |          2 | 100000 | 1      | 1169138570 | domain2-200701-10000      | 1   |
|  5 |          2 |     50 | 1      | 1169564199 | domain2-200701-50klick    | 0   |
|  6 |          2 | 200000 | 1      | 1169813082 | domain2-200701-200000view | 1   |
+----+------------+--------+--------+------------+----------------------------------+-----+

mysql> SELECT * FROM p_view;
+----+------------+-------+------+--------+
| id | partner_id | monat | jahr | views  |
+----+------------+-------+------+--------+
|  4 |          2 | 1     | 2007 | 268743 |
|  5 |          2 | 2     | 2007 | 277775 |
+----+------------+-------+------+--------+

[/quote]

Meine derzeitige Abfrage:

  
SELECT p.id, p.domain, r.link, COUNT( r.anzahl ), SUM(COALESCE(v.views)), SUM( r.anzahl )  
FROM partners p  
INNER JOIN p_rechnung r ON p.id=r.partner_id  
INNER JOIN p_view v ON p.id=v.partner_id  
WHERE r.status = '1'  
AND r.typ = '1'  
GROUP BY p.id  
HAVING SUM( r.anzahl ) > SUM(COALESCE(v.views))/COUNT( r.anzahl )  
ORDER BY RAND()  
LIMIT 1  

Was ich möchte:

Eine Zufalls-ID bei der die Rechnungen bezahlt wurden.
In p_rechnung werden Rechnungen gespeichert, mit ANzahl der freien Klicks bzw. Bannerimpressionen. r.tatus 1 entspricht bezahlt und kann eingeblendet werden. r.typ 1 bedeutet dass Bannereinblendungen also keine Bannerklicks bezahlt wurden.

Ist bisschen verwirrend aber sollte gehen.

Das Problem ist halt, dass COUNT( r.anzahl ) die Anzahl aller Rechnungen ausgibt und nicht eines bestimmten Partners. GEnau so ist es bei den SUM().

SOlltet ihr Verständnisfragen haben, könnt ihr diese gerne Stellen.

[Jan]

Hallo,

leider weiss ich auf die Schnelle keine Antwort muss erst ein
paar Ansätze testen.

Aber mal zwischendurch eine Gegenfrage( pfui unhöflich ...;-),
ja ich weiss aber interessiert mich schon lange.

Wie machst du diese DB Darstellung? Manuell oder gibt es da
ein hilfreiches Tool für so eine Darstellung?

mysql> SELECT * FROM partners;
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+
| id | domain             | pw          | email           | session                          | klick | view |
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+

Gruss
Jan

[steckl]

Hi,

Wie machst du diese DB Darstellung? Manuell oder gibt es da
ein hilfreiches Tool für so eine Darstellung?

Das geht auf der Kommandozeile mit mySql.
Du musst dazu in das entsprechende Verzeichnis .../mysql/bin wechseln und dort "mysql" ausführen (evtl musst du noch Benutzername und PW angeben). Dann kommst du in die mysql-Konsole. Hier kannst du dann die mysql-Kommandos direkt absetzen, so wie es hier steht:

mysql> SELECT * FROM partners;

als Ergebnis bekommst du dann sowas:

+----+--------------------+-------------+-----------------+----------------------------------+-------+------+
| id | domain             | pw          | email           | session                          | klick | view |
+----+--------------------+-------------+-----------------+----------------------------------+-------+------+

mfG,
steckl

[Ilja]

yo,

du unterliegst einem vielgenutzen mysql "bug", mysql würde wohl von einem feature reden. gemeint ist die verwendung von group by unter mysql. alle anderen dbms würden dir mit deiner abfrage eine fehlermeldung ausgeben, was auch sehr sinnvoll ist. nur mysql beschreitet dort andere wege und läßt damit regelmäig ihre nutzer in eine falle tappen.

der grundsatz, den du mit deiner abfrage verletzt hast, ist, alle spalten, die angezeigt werden sollen und keine aggregat-funktionen sind, müssen auch in die group by klausel. und genau das ist bei dir nicht der fall. ok, das war das eine problem.

Das Problem ist halt, dass COUNT( r.anzahl ) die Anzahl aller Rechnungen ausgibt und nicht eines bestimmten Partners. GEnau so ist es bei den SUM().

nein, was du machst ist, du gruppierst nach der id der partner-tabelle. damit beziehen sich auch alle agggregat-funktionen genau auf diese gruppierung. hat also ein partner 3 rechnungen und 2 views, dann wird count(*) 2 * 3 ausgeben sprich 6 in diesem falle. du zählst also mit dem count nicht die anzahl aller rechnungen, sondern die anzahl aller datensätze über die beiden INNER JOINs (somit sind keine null werte möglich).

ich sehe auf die schnelle zwei ansätze, wobei ich dich dabei bitte, erst einmal richtig zu gruppieren. erstens kannst du korrelierte unterabfragen einsetzen. ich selbst mag diese sehr und benutze sie sehr oft, wobei sie im allgmeinen doch ein wenig schief angesehen werden. oder aber du setzt in den aggregat-funktionen count()  ein DISTINCT vor den r.spaltennamen, wobei dieser wert eindeutig sein muss, zum beispiel COUNT(DISTINCT r.id). was die summe betrifft, da wird es dann doch wohl eine korrelierte unterabfrage, weil die werte sicherlich auch gleich sein können.

Ilja

[Faddin]

der grundsatz, den du mit deiner abfrage verletzt hast, ist, alle spalten, die angezeigt werden sollen und keine aggregat-funktionen sind, müssen auch in die group by klausel.

  
SELECT p.id, p.domain, r.link, SUM( COALESCE( v.views ) ) , COUNT( DISTINCT (  
r.anzahl  
) ) , SUM( r.anzahl )  
FROM partners p  
LEFT JOIN p_rechnung r ON p.id = r.partner_id  
LEFT JOIN p_view v ON p.id = v.partner_id  
WHERE r.status = '1'  
AND r.typ = '1'  
GROUP BY p.id, p.domain, r.link  
HAVING SUM( r.anzahl ) > SUM( COALESCE( v.views ) )  
ORDER BY RAND( )  
LIMIT 1  

So sollte es also richtig gruppiert sein?

COUNT( DISTINCT (
r.anzahl
) ) zeigt jetzt aber 1 an obwohl es 3 sein müssten...

Zu Unterabfragen: damit meinst du "SELECT ... FROM ... WHERE r.partner_id = p.id AND ...."

Damit hatte ich das auch schon versucht. Leider erhalte ich garkein Ergebnis, wenn in der Tabelle p_view kein Datensatz vorhanden ist.

[Ilja]

yo,

So sollte es also richtig gruppiert sein?

nein, du musst danach gruppieren, wonach du gruppieren willst. was ich gesagt habe ist, dass du keine spalten ausgeben lassen kannst, die keine aggregat-funktionen sind und nicht in der group by klausel stehen. nimmst du den r.link mit rein, dann veränderst du auch die gruppierung und deswegen bekommst du mit dem distincten count auch eine 1 anstelle von drei. mit anderen worten, du musst die spalte r.link weglassen oder aber als korrelierte unterabfrage einbauen, wobei du dich entscheiden musst, welchen der mehreren r.link werte du den überhaupt nehmen willst.

auch musst du beachten, wonach du gruppierst, bzw. welche joins du bildest. dem entsprechend wirken sich deine aggregat-funktionen wie sum oder count aus. genau das ist dein problem.

Ilja

[Faddin]

Ilja

  
SELECT p.id, p.domain, r.link, SUM( COALESCE( v.views ) ) , COUNT( r.anzahl ) , SUM( r.anzahl )  
FROM partners p, p_view v, p_rechnung r  
WHERE r.status = '1'  
AND p.id = r.partner_id  
AND p.id = v.partner_id  
AND r.typ = '1'  
GROUP BY p.id, p.domain  
HAVING SUM( r.anzahl ) > SUM( COALESCE( v.views ) )  
ORDER BY RAND( )  
LIMIT 1  

Irgendwie bekomme ich das immer noch nicht hin. Kannst du mir nicht ein Beispiel nennen oder eine verständliche Seite wo ich das nachlesen kann.

[Ilja]

yo,

Irgendwie bekomme ich das immer noch nicht hin. Kannst du mir nicht ein Beispiel nennen oder eine verständliche Seite wo ich das nachlesen kann.

nenne mir beispieldaten, welche daten vorhanden sind (falls die von deinem ersten posting nicht komplett sind) und welche daten sollen als ergebnis rauskommen und ich schreibe dir die abfrage.

Ilja

[Faddin]

nenne mir beispieldaten, welche daten vorhanden sind (falls die von deinem ersten posting nicht komplett sind) und welche daten sollen als ergebnis rauskommen und ich schreibe dir die abfrage.

Ilja

Die Daten oben sind ok. ANstatt der "277775" einfach ne kleine Zahl wie "3" nehmen.

Als Ergebnis brauche ich p.id, p.domain wobei "2 | domain2.de " als Ergebnis rauskommen sollen. Es sollen die IDs ausgegeben werden, wo für die Bannerimpressionen (typ==1)$ der Status bezahlt ist (status==1).

Wäre dir wirklich dankbar dafür, weil meine Motivation langsam zuneige geht.

[Ilja]

yo,

bin wieder da, ich zocke gelegentlich gerne mal im internet...

Als Ergebnis brauche ich p.id, p.domain wobei "2 | domain2.de " als Ergebnis rauskommen sollen.

ok, dann können wir schon mal die kompletten joins weglassen, da du eh nur die daten von der tabelle partner haben willst und der rest sind aggregatfunktionen.

Es sollen die IDs ausgegeben werden, wo für die Bannerimpressionen (typ==1)$ der Status bezahlt ist (status==1).

ja, das ist kein problem, legen wir los. ich versuche das ein wenig im erzählstil zu schreiben. das erste was wir brauchen ist die tabelle mit den partner.

SELECT p.id, p.domain
FROM partners p

ok, das war einfach ;-) der zweite schritt besteht darin, die partner durch deine bedingungen einzuschränken, ohnen einen join zu bilden. dies machen wir mit einer korrelierten unterabfrage.

SELECT p.id, p.domain
FROM partners p
WHERE p.id IN
  (SELECT r.partner_id
   FROM p_rechnung r
   WHERE p.id = r.partner_id
   AND r.status = '1'
   AND r.typ = '1'
  )

so, die richtigen partner sollten nun gefunden sein. der dritte schritt besteht darin, die aggregat-funktionen einzubinden.

SELECT p.id, p.domain,
 (SELECT COUNT(*) FROM p_rechnung r2 WHERE p.id = r2.partner_id) AS Anzahl,
(SELECT SUM(r3.anzahl) FROM p_rechnung r3 WHERE p.id = r3.partner_id) AS Summe_Rechnungen,
(SELECT SUM(v.views) FROM p_view v WHERE p.id = v.partner_id) AS Summe_Views
FROM partners p
WHERE IN
  (SELECT r.partner_id
   FROM p_rechnung r
   WHERE p.id = r.partner_id
   AND r.status = '1'
   AND r.typ = '1'
  )

die abfrage wird erst ab mysql 4.1+ funktionieren, weil erst ab dieser version unterabfragen unterstützt werden. den sinn der bedinung in der having klausel habe ich noch nicht ganz verstanden. wenn die noch eingebracht werden soll, dann sag noch mal, wofür sie fachlich steht, am besten immer mit beispielen.

Du kannst die NULL Werte bei den Aggregat-funktionen noch durch eine Funktion abfangen, falls es keine einträge in den entsprechenden tabellen gibt. aber das sollte dann auch kein problem mehr sein.

Ilja

[Faddin]

die abfrage wird erst ab mysql 4.1+ funktionieren, weil erst ab dieser version unterabfragen unterstützt werden. den sinn der bedinung in der having klausel habe ich noch nicht ganz verstanden. wenn die noch eingebracht werden soll, dann sag noch mal, wofür sie fachlich steht, am besten immer mit beispielen.

Du kannst die NULL Werte bei den Aggregat-funktionen noch durch eine Funktion abfangen, falls es keine einträge in den entsprechenden tabellen gibt. aber das sollte dann auch kein problem mehr sein.

Ilja

Ok wird bei mir laufen. Ok ich versuche es zu erklären:

Was ich damit erreichen möchte ist ein kleines Werbesystem auf meiner Site. In der Tabelle p_rechnung werden von mir einträge gemacht, sobald jemand werben möchte. Hier trage ich dann ein wieviele Bannerimpressionen gezahlt wurden. (r.anzahl)

So in der Tabelle p_view wird für die einzelnen Partner eine einfache Statistik abgelegt. Die SPalte views wird inkrementiert sobald der Banner angezeigt wird.

Jetzt zurück zu der Hauptabfrage. Diese soll nur dann ein Ergebnis liefern wenn die Summe der bezahlten Views größer als die SUmme der bereits gezeigten Banner(r.views) ist. Ich wusste vorher nicht wie ich das lösen sollte :)

Aber danke erstmal für die ausführliche Erklärung.

[Ilja]

yo,

Jetzt zurück zu der Hauptabfrage. Diese soll nur dann ein Ergebnis liefern wenn die Summe der bezahlten Views größer als die SUmme der bereits gezeigten Banner(r.views) ist.

wenn ich das jetzt richtig verstanden habe, dann meinst du sicherlich v.views und nicht r.views ? also in der p_rechung steht die anzahl der bezahlten und in der p_view die anzahl der gezeigten banner. und du willst quasi nur noch die anzeigen lassen, deren Summe von bezahlten bannern größer ist, als die summe der bereits angezeigten banner ? das wäre einfach einzubauen, wir nehmen beide summen eines partner und vergleichen sie miteinander, ähnlich wie du es in der having klausel gemacht hast. aber ich verstehe noch nicht ganz, die teilung durch den count in der having klausel. außerdem übersteigt in deinem beispiel die anzahl der gezeigten banner (views) die anzahl der bezahlten banner für die partnerid =  2.

Ilja

[Faddin]

wenn ich das jetzt richtig verstanden habe, dann meinst du »»sicherlich v.views und nicht r.views ?

Ja stimmt, sorry.

»»und du willst quasi nur noch die anzeigen lassen, deren Summe von »»bezahlten bannern größer ist, als die summe der bereits angezeigten »»banner ?

Genau das möchte ich damit erreichen.

»»das wäre einfach einzubauen, wir nehmen beide summen eines partner »»und vergleichen sie miteinander, ähnlich wie du es in der having »»klausel gemacht hast. aber ich verstehe noch nicht ganz, die »»teilung durch den count in der having klausel.

Das hatte ich eingebaut, weil es lief solange keine weiteren Rechnungen anderer Partner vorhanden waren glaube ich. Also sollte es raus. :)

außerdem übersteigt in deinem beispiel die anzahl der gezeigten banner (views) die anzahl der bezahlten banner für die partnerid =  2.

Ilja

Ja ist richtig, deswegen meinte ich in einem Posting, dass der eine Eintrag von beiden auf eine kleine Zahl gesenkt werden muss.

Wo baue ich die Having-Bedingung genau rein? =) Bist anscheinend immer noch am zocken ^^

[Ilja]

yo,

Wo baue ich die Having-Bedingung genau rein? =)

gar nicht in der having klausel, wir haben ja auch kein group by mehr. das problem bei dir und dem group by ist, dass du im prinzip zwei group by miteinander über die joins verschatechlt hast, nämlich die summen der bezahlten banner und die summen der angezeigten banner. beides hat die partnerid. und wenn du danach gruppierst, dann verfälschen sich die beiden tabellen gegenseitig. wenn du nur eines von beiden angezeigt haben willst, dann würde group by wunderbar funktionieren. wir bauen das nun in die where klausel ein.

SELECT p.id, p.domain,
 (SELECT COUNT(*) FROM p_rechnung r2 WHERE p.id = r2.partner_id) AS Anzahl,
(SELECT SUM(r3.anzahl) FROM p_rechnung r3 WHERE p.id = r3.partner_id) AS Summe_Rechnungen,
(SELECT SUM(v.views) FROM p_view v WHERE p.id = v.partner_id) AS Summe_Views
FROM partners p
WHERE IN
  (SELECT r.partner_id
   FROM p_rechnung r
   WHERE p.id = r.partner_id
   AND r.status = '1'
   AND r.typ = '1'
  )
AND (SELECT SUM(r3.anzahl) FROM p_rechnung r3 WHERE p.id = r3.partner_id) > (SELECT SUM(v.views) FROM p_view v WHERE p.id = v.partner_id)

eventuell kannst du bei mysql den teil mit den aliasnamen ersetzen, bin mir da aber nicht sicher, also den letzten teil:

AND Summe_Rechnungen > Summe_Views

Bist anscheinend immer noch am zocken ^^

jepp ertappt ;-)

Ilja

[Faddin]

gar nicht in der having klausel, wir haben ja auch kein group by mehr. das problem bei dir und dem group by ist, dass du im prinzip zwei group by miteinander über die joins verschatechlt hast, nämlich die summen der bezahlten banner und die summen der angezeigten banner. beides hat die partnerid. und wenn du danach gruppierst, dann verfälschen sich die beiden tabellen gegenseitig. wenn du nur eines von beiden angezeigt haben willst, dann würde group by wunderbar funktionieren. wir bauen das nun in die where klausel ein.

Jap so sieht es aus als wenn es funktioniert. Zumindest habe ich jetzt das Ergebnis, welches ich erwarte. Was ich noch hinzugefügt habe ist " AND r3.typ='1'" weil ich ansonsten die Summe aller bezahlten Banner(Views und Klciks) erhalte. Hier die aktuelle Version , die ich gleih im PHP-Script testen werde :)

  
SELECT p.id, p.domain, (  
  
SELECT COUNT( * )  
FROM p_rechnung r2  
WHERE p.id = r2.partner_id  
) AS Anzahl, (  
  
SELECT SUM( r3.anzahl )  
FROM p_rechnung r3  
WHERE p.id = r3.partner_id AND r3.typ='1'  
) AS Summe_Rechnungen, (  
  
SELECT SUM( v.views )  
FROM p_view v  
WHERE p.id = v.partner_id  
) AS Summe_Views  
FROM partners p  
WHERE p.id  
IN (  
  
SELECT r.partner_id  
FROM p_rechnung r  
WHERE p.id = r.partner_id  
AND r.status = '1'  
AND r.typ = '1'  
)  
AND (  
  
SELECT SUM( r3.anzahl )  
FROM p_rechnung r3  
WHERE p.id = r3.partner_id  
AND r3.typ = '1'  
) > (  
SELECT SUM( v.views )  
FROM p_view v  
WHERE p.id = v.partner_id )  
ORDER BY RAND()  
LIMIT 1  

eventuell kannst du bei mysql den teil mit den aliasnamen ersetzen, bin mir da aber nicht sicher, also den letzten teil:

AND Summe_Rechnungen > Summe_Views

Geht leider nicht... Fehlermeldung: Kennt die Spalte Summe_Rechnungen nicht. Naja...

Wie sieht das perfomancemäßig bei den Unterabfragen aus? Hätte nie gedacht, dass die so kompliziert werden wird...

Ansonsten danke dass du dich mit meinem speziellen Problem beschäftigt hast. Etwas gelernt habe ich auf jeden FAll. Danke

[Ilja]

yo,

Jap so sieht es aus als wenn es funktioniert. Zumindest habe ich jetzt das Ergebnis, welches ich erwarte. Was ich noch hinzugefügt habe ist " AND r3.typ='1'" weil ich ansonsten die Summe aller bezahlten Banner(Views und Klciks) erhalte. Hier die aktuelle Version , die ich gleih im PHP-Script testen werde :)

ich blicke da ehrlich gesagt noch nicht ganz durch, wann du den status und den typ mit reinbringst und wann nicht. zum beispiel ist er im count nicht drinne, in der summe schon... aber du machst das schon wo sie rein sollen. ;-)

denk daran, NULL werte mit einer funktion abzufangen, falls es keine einträge in den unterabfragen gibt.

was die performance betrifft, so haben unterabfragen einen schlechten ruf und das meiner meinungnach zuunrecht. oftmals sind sie wesentlich schneller als joins. aber der teufewl steckt im detail, gerade bei den korrelierten unterabfragen kann es zu problemen kommen. aber schau einfach mal, wie schnell die abfrage durch geht. tuning kommt immer erst dann ins spiel, wenn es überhaupt handlungsbedarf gibt.

Ilja

[Faddin]

ich blicke da ehrlich gesagt noch nicht ganz durch, wann du den status und den typ mit reinbringst und wann nicht. zum beispiel ist er im count nicht drinne, in der summe schon... aber du machst das schon wo sie rein sollen. ;-)

Ja hast Recht, da sollte der auch rein (COUNT). Da ich den aber garnicht brauche, habe ich den sowieso schon rausgenommen. :)

denk daran, NULL werte mit einer funktion abzufangen, falls es keine einträge in den unterabfragen gibt.

Wie fange ich die ab? PHP-mäßig oder mit IF-Bedingungen innerhalb von Mysql?

was die performance betrifft, so haben unterabfragen einen schlechten ruf und das meiner meinungnach zuunrecht. ....

Warum ich frage: Ich gewöhne mich dann jetzt dran und habe am Ende dann mehr zu tun, wenn ich das alles wieder ändern muss :)