wo ist der Fehler?

[Peter Grahl]

Hallo,

ich habe drei Tabellen:

tab_datum:
id|datum

tab_a:
datum_id|wert_a

tab_b:
datum_id|wert_b

Eine Abfrage wie:

select year(datum) as group_a, month(datum) as group_b, sum(wert_a) from tab_a, tab_datum
where tab_datum.id=tab_a.datum_id
and tab_datum.datum >= '2007-01-01' group by group_a, group_b

funktioniert einwandfrei (genauso natürlich mit tab_b)!

Wenn ich aber die Daten zusammenfassen möchte, kommen horrende (zu große Zahlen) raus, d.h. irgendwie sieht es nach einem Kreuzprodukt aus, das wohl irgendwie an der Gruppierung liegt:

select year(datum) as group_a, month(datum) as group_b, sum(wert_a), sum(wert_b) from tab_a, tab_b, tab_datum
where tab_datum.id=tab_a.datum_id
and tab_datum.id=tab_b.datum_id
and tab_datum.datum >= '2007-01-01' group by group_a, group_b

Muß ich das in subselects formulieren?
Wo ist der Gedankenfehler bzw. was macht mysql an dieser Stelle genau?

Danke!!!

[Frank (no reg)]

Hallo,

mit was für einer Datenbank arbeitest du (bitte inkl. Version)?

Was sagt der "Ausführungsplan" (Query Plan, Query Execution Plan) zu deiner Abfrage?

Ich würde zuerst von der Tabelle "tab_datum" selektieren und dann mit der besser lesbaren Syntax für INNER JOINS die anderen beiden Tabellen dazu-joinen. Damit schnallt die Datenbank eventuell, dass sie kein Kreuzprodukt erzeugen soll??! Rein spekulativ gesprochen.

Ciao, Frank

[Rouven]

Hello,

mit was für einer Datenbank arbeitest du (bitte inkl. Version)?

bis auf die Version wissen wir das schon, schau dir einfach SELECT vs. GROUP BY an, dann bleibt nur noch eine in der Beziehung kaputte MySQL...

MfG
Rouven

[Peter Grahl]

Hello,

mit was für einer Datenbank arbeitest du (bitte inkl. Version)?
bis auf die Version wissen wir das schon, schau dir einfach SELECT vs. GROUP BY an, dann bleibt nur noch eine in der Beziehung kaputte MySQL...

was ist kaputt?

[Rouven]

Hello,

was ist kaputt?

MySQL weicht vom SQL Standard ab, was die Verarbeitung von GROUP BY angeht. Die eigentliche Regel lautet: selektiert werden dürfen nur Spalten, die entweder im GROUP BY auftauchen oder auf Basis einer Aggregation (SUM, MAX, ...) entstanden sind und damit für die ganze Gruppe gleich sind. MySQL hat ein System auch andere Spalten in die Ergebnismenge aufzunehmen, die ich und viele andere hier auch für schlecht halten.

MfG
Rouven

[Peter Grahl]

mit was für einer Datenbank arbeitest du (bitte inkl. Version)?

MySQL 5

Was sagt der "Ausführungsplan" (Query Plan, Query Execution Plan) zu deiner Abfrage?

der was?

Ich würde zuerst von der Tabelle "tab_datum" selektieren und dann mit der besser lesbaren Syntax für INNER JOINS die anderen beiden Tabellen dazu-joinen. Damit schnallt die Datenbank eventuell, dass sie kein Kreuzprodukt erzeugen soll??! Rein spekulativ gesprochen.

kannst Du mir das anhand meines Beispiels erklären?
Die where-Anweisungen (a=b and b=c) wirken in MySQL wie ein join, bzw. sind es.

Danke,
Peter

[Ilja]

yo,

der unterschied zwischen den beiden abfragen bei dir ist, dass du im ersten fall über zwei tabellen gehst und im zweiten fall über drei. dadurch erhöht sich eventuell die anzahl der datensätze, über die dann letztlich gruppiert wird. die folge ist natürlich andere werte bei der aggregation. um dir das vor augen zu führen, kannst du ja beide abfragen ohne die gruppierung ausführen und nur die anzahl der datensätze mit COUNT() zählen.

noch drei hinweise, deine namensgebung deiner tabellen ist nicht sehr vorteilhaft und auch deine schreibweise der abfrage ist etwas unübersichtlich. folgende schreibweise macht das gleiche und ist viel schöner zu lesen. ausserdem wird nicht jedes dbms über den aliasnamen gruppierungen zulassen.

SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a), sum(wert_b)
FROM tab_datum
INNER JOIN tab_a ON tab_datum.id=tab_a.datum_id
INNER JOIN tab_b ON tab_datum.id=tab_b.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY group_a, group_b

Ilja

[Peter Grahl]

noch drei hinweise, deine namensgebung deiner tabellen ist nicht sehr vorteilhaft und auch deine schreibweise der abfrage ist etwas unübersichtlich. folgende schreibweise macht das gleiche und ist viel schöner zu lesen. ausserdem wird nicht jedes dbms über den aliasnamen gruppierungen zulassen.

Die Namen habe ich hier nur so zum Verständnis hingeschrieben.
Die heißen natürlich nicht einfach "tab_a" usw.
Bei MySQL kann man einen Alias zur Gruppierung nutzen.

SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a), sum(wert_b)
FROM tab_datum
INNER JOIN tab_a ON tab_datum.id=tab_a.datum_id
INNER JOIN tab_b ON tab_datum.id=tab_b.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY group_a, group_b

Danke, die Ergebnisse sind aber nicht anders als bei meiner Version.
Ich werde verrückt.

Wo ist der logische Fehler?

Danke!

[Ilja]

yo,

Die Namen habe ich hier nur so zum Verständnis hingeschrieben.

nun, sie machen uns das leber schwerer, namen mit sinnvollen bedeutungen machen es einfacher, weil man davon eine plastische vorstellung hat.

Bei MySQL kann man einen Alias zur Gruppierung nutzen.

ja, wie aber bereits erwähnt, machen das nicht alle dbms. sicherlich nutzt man auch immer die eigenen funktionalitäten eines dbms, zum beispiel group_contact. aber in deinem falle liegt es anderes, du verlierst die fähigkeit zu portieren, wenn dein dbms wechselt, hast aber auf der anderen seite keinen vorteil davon. deswegen würde ich deine abfrage nicht nach den aliasnamen gruppieren. wer will schon einen nachteil ohne auch einen vorteil zu haben ?

Danke, die Ergebnisse sind aber nicht anders als bei meiner Version.

sollten sie auch nicht, es sollte dir zeigen, dass die gleichen ergebnisse rauskommen, die schreibweise aber beser ist, da sie leserlicher ist.

Wo ist der logische Fehler?

ich habe dir ja bereits gesagt, dass du bei deiner ersten abfrage jeweils nur über zwei tabellen gehst und bei deiner dritten über drei. wenn die summen in der ersten abfrage stimmen, kannst du nicht einfach die dritter tabelle anhängen und dann wieder die summen über die gruppierung bilden.

was dir vermutlich helfen wird ist zwei abfragen mit jeweils nur zwei tabellen und dann beide ergebnisse mit UNION ALL miteinander verbinden.

SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a) FROM tab_datum
INNER JOIN tab_a ON tab_datum.id=tab_a.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY  year(datum), month(datum)
UNION ALL
SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a) FROM tab_datum
INNER JOIN tab_b ON tab_datum.id=tab_b.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY  year(datum), month(datum)

Ilja

[Peter Grahl]

SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a) FROM tab_datum
INNER JOIN tab_a ON tab_datum.id=tab_a.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY  year(datum), month(datum)
UNION ALL
SELECT year(datum) as group_a, month(datum) as group_b, sum(wert_a) FROM tab_datum
INNER JOIN tab_b ON tab_datum.id=tab_b.datum_id
WHERE tab_datum.datum >= '2007-01-01'
GROUP BY  year(datum), month(datum)

Danke für die Mühe!
Das hört sich logisch an!
Werde ich ausprobieren.

Mein Retter (wie es scheint).

:-)

[Rouven]

Hello,

Muß ich das in subselects formulieren?
Wo ist der Gedankenfehler bzw. was macht mysql an dieser Stelle genau?

so, jetzt hab ich mir das ganze auch erstmal angesehen. Was willst du denn eigentlich erreichen? Du willst zu jedem Datum _genau_ die Summe von A und die Summe von B haben, richtig?
Das wird in einer Abfrage ohne Subselects oder Zusatzjoins nur genau dann funktionieren, wenn es zu jedem Datum genau einen Datensatz in tab_a und tab_b gibt, denn ansonsten wird _jede Art von Join_ dazu führen, dass entweder der verdoppelte Datensatz aus A oder der aus B eben doppelt in die Wertung eingeht.
Also im Prinzip ist von der Übersicht her der Subselect mit anschl. Join wohl der Beste (wohlgemerkt Übersicht, nicht unbedingt Performance...) - sprich Select auf Join nach A mit GROUP, Select auf Join nach B mit GROUP, beide Ergebnisse über das gleiche Datum zusammenjoinen.

MfG
Rouven

[Peter Grahl]

Hello,

Muß ich das in subselects formulieren?
Wo ist der Gedankenfehler bzw. was macht mysql an dieser Stelle genau?
so, jetzt hab ich mir das ganze auch erstmal angesehen. Was willst du denn eigentlich erreichen? Du willst zu jedem Datum _genau_ die Summe von A und die Summe von B haben, richtig?
Das wird in einer Abfrage ohne Subselects oder Zusatzjoins nur genau dann funktionieren, wenn es zu jedem Datum genau einen Datensatz in tab_a und tab_b gibt, denn ansonsten wird _jede Art von Join_ dazu führen, dass entweder der verdoppelte Datensatz aus A oder der aus B eben doppelt in die Wertung eingeht.
Also im Prinzip ist von der Übersicht her der Subselect mit anschl. Join wohl der Beste (wohlgemerkt Übersicht, nicht unbedingt Performance...) - sprich Select auf Join nach A mit GROUP, Select auf Join nach B mit GROUP, beide Ergebnisse über das gleiche Datum zusammenjoinen.

Ok, Dir auch danke.
Für mich ist es zwar immer nicht nicht völlig klar, was MySQL da macht. Aber ich weiß jetzt zumindest, daß es SO nicht geht.
Ist ein Fortschritt!

Danke!!!

[Rouven]

Hello,

Für mich ist es zwar immer nicht nicht völlig klar, was MySQL da macht.

dann gucken wir halt mal nach - sagen wir mal folgendes:
tab_datum
2007-10-12 | 1

tab_a
1 | 2

tab_b
1 | 3
1 | 4

Nun schauen wir also nach was er macht

1. ein Join von tab_datum und tab_a ergibt
   2007-10-12 | 1 | 2

2. ein Join von (1) mit tab_b ergibt jetzt
   2007-10-12 | 1 | 2 | 3
   2007-10-12 | 1 | 2 | 4

3. und jetzt gruppieren wir nach Datum, also beide Zeilen zusammen und summieren, dann ist klar was passiert:
   2007-10-12 | 1 | 4 | 7
   ...und die 4 ist natürlich NICHT die Summe aus tab_A

MfG
Rouven

[Peter Grahl]

Super.
Ich würde es dennoch irgendwie als Fehler empfinden und annehmen, daß erst Summe_A und Summe_B gebildet werden und zwar aus den Werten, wo die Datums-ID stimmt.

Ihr seid Klasse, danke!