mysql: SUM und DISTINCT

[Lukas.]

Hallo,

gibt es da zur Kombination zwischen SUM und DISTINCT bei gleichzeitiger Verwendung von JOINS etwas, von dem ich nichts weiß?

Konkret:

Ich habe eine Query ala

SELECT DISTINCT EK
FROM tabelle1 AS t1
LEFT JOIN tabelle2 AS t2 ON t1.ID = t2.ID
LEFT JOIN tabelle3 AS t3 ON ...
LEFT JOIN tabelle4 AS t4 ON ...
WHERE t1.ID = 4152

Ergebnissmenge:

2 3 5 4

Mache ich daraus aber

SELECT DISTINCT SUM(EK)
FROM tabelle1 AS t1
LEFT JOIN tabelle2 AS t2 ON t1.ID = t2.ID
LEFT JOIN tabelle3 AS t3 ON ...
LEFT JOIN tabelle4 AS t4 ON ...
WHERE t1.ID = 4152

dann kommt ein viel zu hoher Wert heraus.

Wenn ich in der ersten Query das DISTINCT weglasse, werden mir die 4 Werte ca. 55 mal angezeigt und ich vermute, daß das auch die viel zu hohe Summe ergibt. Ich vermute also, daß das DISTINCT in Query2 schlicht ignoriert wird.

Ist das so? Wie errechne ich dann aber die Summe, ohne meine LEFT JOINS zu vernachlässigen? Denn dort stehen z.b. noch die Mengen drin (die ich aber jetzt mal weggelassen habe, weil sie das Grundproblem selber ja nicht betreffen).

Lukas

[dedlfix]

Tach!

gibt es da zur Kombination zwischen SUM und DISTINCT bei gleichzeitiger Verwendung von JOINS etwas, von dem ich nichts weiß?

DISTINCT entfernt mehrfache Zeilen der Ergebnismenge. Ein SUM() liefert nur eine Zeile. Danach kann das DISTINCT nicht mehr viel entfernen.

Wie errechne ich dann aber die Summe, ohne meine LEFT JOINS zu vernachlässigen?

Das kommt ganz drauf an, welche Daten du zusammenrechnen willst und wo diese stehen. Ohne dieses Wissen ist es schwer, sich ein passendes Statement zu überlegen.

Der einfachste Fall aus den gegebenen Fakten wäre, das DISTINCT-Statement in ein Subquery zu verlagern und die Summe im äußeren zu bilden. Schön sieht aber anders aus. Oftmals ist das Verwendenwollen von DISTICT ein Zeichen dafür, dass die Query Verbesserungspotential hat oder die Daten ein Redundanzproblem haben.

dedlfix.

[Lukas.]

Hi dedlfix,

DISTINCT entfernt mehrfache Zeilen der Ergebnismenge. Ein SUM() liefert nur eine Zeile. Danach kann das DISTINCT nicht mehr viel entfernen.

Heißt also, daß SUM vor DISTINCT ausgeführt wird??

Das kommt ganz drauf an, welche Daten du zusammenrechnen willst und wo diese stehen. Ohne dieses Wissen ist es schwer, sich ein passendes Statement zu überlegen.

Die Werte stehen ausschließlich in t1. Die dazugehörige Anzahl in t3. Auf t3 kannst Du nur über t2 zugreifen, die wiederum über t1 gejoint wird.

Exakt:

t1: MID,Datum,Vorgangsnummer,...

t2: TBID, MID,...

t3: TBID,Menge

t4: EK, MID,...

Ich bräuchte die Summe der EKMenge, also SUM(t4.EKt3.Menge), die zu einer MID gehören. Eigentlich sogar nach MID gruppiert, was aber momentan egal ist.

Der einfachste Fall aus den gegebenen Fakten wäre, das DISTINCT-Statement in ein Subquery zu verlagern und die Summe im äußeren zu bilden.

Gute Idee. Krieg ich, glaub ich, hin.

Schön sieht aber anders aus. Oftmals ist das Verwendenwollen von DISTICT ein Zeichen dafür, dass die Query Verbesserungspotential hat oder die Daten ein Redundanzproblem haben.

Wie gehts denn schöner? Und nein, hier liegt kein Redundanzproblem vor. Ich habe zwar (insgesamt) ein paar Redundanzen eingebaut, aber hier liegt wohl eher keine vor. Häte ich eine, wärs deutlich einfacher. Die Menge z.b. redundant zu führen, hätte das Problem deutlich vereinfacht.

Lukas

[dedlfix]

Tach!

Heißt also, daß SUM vor DISTINCT ausgeführt wird??

Alles in der SELECT-Klausel (Felder und Formeln) muss zunächst vorliegen oder berechnet werden, bevor auf diese Ergebnismenge das DISTINCT angewendet werden kann.

Ich bräuchte die Summe der EKMenge, also SUM(t4.EKt3.Menge), die zu einer MID gehören. Eigentlich sogar nach MID gruppiert, was aber momentan egal ist.

Na, dann mach das doch so. Gruppiere nach MID und bilde dann die Summe. Ich nehme an, die MID ist in t1 einmalig und die Dopplungen, die du mit DISTINCT wegzubekommen versuchst, entstehen durch das Joinen von mehreren zugehörigen Datensätzen aus t2/3/4.

Der einfachste Fall aus den gegebenen Fakten wäre, das DISTINCT-Statement in ein Subquery zu verlagern und die Summe im äußeren zu bilden.

Gute Idee. Krieg ich, glaub ich, hin.

Nein, gut ist die nicht, nur einfach aus den bisherigen Fakten vorzuschlagen gewesen. Gut wird hingegen das Gruppieren sein. Ob es auch optimal ist, kann ich immer noch nicht abschließend beurteilen.

Ein anderer Vorschlag ist, die Summe in einer correlated Subquery zu bilden, besonders wenn das der einzige Wert ist, der aus den gejointen Tabellen genommen/gebildet werden soll. Damit umgeht man auch das GROUP-BY-Problem, dass man keine anderen Felder selektieren kann also die dort angegebenen, ohne eventuell Probleme zu bekommen. In dem Fall sind zwar keine zu erwarten, wenn alle Daten aus t1 kommen und nur durch das Joinen verdoppelt werden. Aber MySQL ist auch ziemlich das einzige SQL-System, dass ein Selektieren von nichtgruppierten Feldern zulässt. Es ist kein generell empfehlenswertes Vorgehen, auf dieses Feature zu setzen.

dedlfix.

[Lukas.]

Hi dedlfix,

Na, dann mach das doch so. Gruppiere nach MID und bilde dann die Summe. Ich nehme an, die MID ist in t1 einmalig und die Dopplungen, die du mit DISTINCT wegzubekommen versuchst, entstehen durch das Joinen von mehreren zugehörigen Datensätzen aus t2/3/4.

Ja, so wird es sein...

Wie ziehe ich denn die Summe einer Gruppe? Weil, nachfolgendes funktioniert nicht, sondern ergibt ebenfalls die zu hohen Werte:

SELECT
SUM(EK)
FROM tabelle1 t1
Left JOIN tabelle2 t2 ON t1.MID = t2.MID
LEFT JOIN tabelle3 t3 ON t2.TID = t3.TID
LEFT JOIN tabelle4 t4 ON t1.MID = t4.MID
WHERE
t1.MID = 4343
GROUP BY t1.MID

Ein anderer Vorschlag ist, die Summe in einer correlated Subquery zu bilden, besonders wenn das der einzige Wert ist, der aus den gejointen Tabellen genommen/gebildet werden soll. Damit umgeht man auch das GROUP-BY-Problem, dass man keine anderen Felder selektieren kann also die dort angegebenen, ohne eventuell Probleme zu bekommen.

Sorry, diesen Satz habe ich nicht verstanden.

Lukas

[dedlfix]

Tach!

Wie ziehe ich denn die Summe einer Gruppe?

SELECT SUM(feldname) ... FROM ... GROUP BY ... so wie du das schon hast.

Weil, nachfolgendes funktioniert nicht, sondern ergibt ebenfalls die zu hohen Werte:

Dann lass das GROUP BY und das SUM() weg und zähl mal händisch. Wenn das nicht stimmt oder du zu viele Datensätze siehst, dann hast du zu viel selektiert, nicht richtig verknüpft oder zu wenig eingeschränkt.

Ein anderer Vorschlag ist, die Summe in einer correlated Subquery zu bilden, besonders wenn das der einzige Wert ist, der aus den gejointen Tabellen genommen/gebildet werden soll. Damit umgeht man auch das GROUP-BY-Problem, dass man keine anderen Felder selektieren kann also die dort angegebenen, ohne eventuell Probleme zu bekommen.

Sorry, diesen Satz habe ich nicht verstanden.

Was genau hast du nicht verstanden? Wie man mit einer correlated Subquery zur Lösung kommt oder was das Problem beim GROUP-BY ist, wenn man mehr Felder selektiert als in der GROUP-BY-Klausel angegeben sind?

Siehe EN-Wikipedia-Artikel Correlated subquery, das letzte Beispiel im ersten Teil (vor Optimizing ...)

dedlfix.

[Lukas.]

Hi dedlfix,

Dann lass das GROUP BY und das SUM() weg und zähl mal händisch. Wenn das nicht stimmt oder du zu viele Datensätze siehst, dann hast du zu viel selektiert, nicht richtig verknüpft oder zu wenig eingeschränkt.

So siehts aus. Ich sehe zuviele Datensätze. Wenn ich hingegen

SELECT
EK
FROM tabelle1 t1
LEFT JOIN tabelle4 t4 ON t1.MID = t4.MID
WHERE
t1.MID = 4343

also ohne die JOINs

Left JOIN tabelle2 t2 ON t1.MID = t2.MID
LEFT JOIN tabelle3 t3 ON t2.TID = t3.TID

anfrage, erhalte ich die korrekten Datensätze.

Wie ermittel ich jetzt, woher die unnötigen Datensätze stammen?

Soll ich mal versuchen, mein Beispiel komplett in mysql nachzumodellieren, damit es für Außenstehende (und dann auch für mich) nachvollziehbar wird?

Das Doofe ist, daß ich für meine Anwendung ohnehin in php noch "nachjustieren" muß. Somit könnte ich auch dem Script ein paar Rechenaufgaben übergeben und gut ist. Andererseits würde ich gerne verstehen, was hier läuft...

Siehe EN-Wikipedia-Artikel Correlated subquery, das letzte Beispiel im ersten Teil (vor Optimizing ...)

Habe ich mir angesehen. Bekomme ich aber nicht auf mein Beispiel angewendet.

Lukas

[Lukas.]

Soll ich mal versuchen, mein Beispiel komplett in mysql nachzumodellieren, damit es für Außenstehende (und dann auch für mich) nachvollziehbar wird?

Falls interessant:

DB:

CREATE TABLE IF NOT EXISTS tabelle4 (
  KID int(11) NOT NULL AUTO_INCREMENT,
  TID int(6) DEFAULT NULL,
  MID int(6) DEFAULT NULL,
  EK decimal(10,2) DEFAULT '0.00',
  VK decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (KID),
  UNIQUE KEY TID (TID)
) ENGINE=MyISAM;

INSERT INTO tabelle4 (KID, TID, MID, EK, VK) VALUES
(415, 1924, 4343, 81.20, 170.50),
(414, 1923, 4343, 43.20, 158.20),
(413, 1922, 4343, 28.00, 89.60),
(412, 1921, 4343, 556.20, 2279.50),
(411, 1920, 4343, 5286.00, 15001.00);

CREATE TABLE IF NOT EXISTS tabelle1 (
  MID int(6) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (MID)
) ENGINE=MyISAM;

INSERT INTO tabelle1 (MID) VALUES (4343);

CREATE TABLE IF NOT EXISTS tabelle3 (
  TID int(6) NOT NULL AUTO_INCREMENT,
  TbID int(5) NOT NULL DEFAULT '0',
  Anzahl decimal(5,2) DEFAULT '0.00',
  PRIMARY KEY (TID),
  KEY TbID (TbID)
) ENGINE=MyISAM;

INSERT INTO tabelle3 (TID, TbID,Anzahl) VALUES
(1920, 1522, 1.00),
(1921, 1523, 4.00),
(1922, 1524, 2.00),
(1923, 1525, 4.00),
(1924, 1526, 3.00);

CREATE TABLE IF NOT EXISTS tabelle2 (
  TbID int(6) NOT NULL AUTO_INCREMENT,
  MID int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (TbID),
  KEY MontagenID (MID)
) ENGINE=MyISAM;

INSERT INTO tabelle2 (TbID, MID) VALUES
(1522, 4343),
(1523, 4343),
(1524, 4343),
(1525, 4343),
(1526, 4343),
(1527, 4343),
(1628, 4343),
(1636, 4343),
(1637, 4343),
(1638, 4343),
(1639, 4343);

Query:

SELECT EK
FROM tabelle1 t1
LEFT JOIN tabelle2 t2 ON t1.MID = t2.MID
LEFT JOIN tabelle3 t3 ON t2.TbID = t3.TbID
LEFT JOIN tabelle4 t4 ON t1.MID = t4.MID
WHERE t1.MID =4343

Ergebnismenge:

55 Datensätze, anstelle von 5 Datensätzen. Vermutlich genau die 11 (t2) x 5 (t4) Datensätze.

Lukas

[dedlfix]

Tach!

Ergebnismenge:

55 Datensätze, anstelle von 5 Datensätzen. Vermutlich genau die 11 (t2) x 5 (t4) Datensätze.

Ah, da entsteht ein kartesiches Produkt: jeder mit jedem.

SELECT EK
FROM tabelle1 t1
LEFT JOIN tabelle2 t2 ON t1.MID = t2.MID
LEFT JOIN tabelle3 t3 ON t2.TbID = t3.TbID
LEFT JOIN tabelle4 t4 ON t1.MID = t4.MID
WHERE t1.MID =4343

Es entstehen zwei Teilmengen:

1. t1 gejoint mit t4
2. t1 gejoint mit t2 und t2 mit t3

Die bilden dann das kartesische Produkt.

Ich bin grad nicht mehr in der Lage, das Problem mit der notwendigen Aufmerksamkeit zu analysieren und schlage mal auf gut Glück was vor. Eine Hauptquery fragt die t1-Daten ab. Eine correlated Subquery fragt t4 und SUM(EK) ab. Eine weitere correlated Subquery geht auf t2 und t3 und holt da die SUM(Anzahl) - unter der Annahme, dass du da auch die Summe möchtest.

Vielleicht ist die Lösung aber auch, t3 über TID an t4 zu hängen und t2 wegzulassen. Dann kannst du gruppieren und die Summen bilden.

Wenn das alles nicht das ist, was du brauchst, muss ich später nochmal drüber nachdenken.

dedlfix.

[Lukas.]

Hi dedlfix,

Vielleicht ist die Lösung aber auch, t3 über TID an t4 zu hängen und t2 wegzulassen. Dann kannst du gruppieren und die Summen bilden.

Stimmt. TID wird in t4 redundant geführt...

Wenn das alles nicht das ist, was du brauchst, muss ich später nochmal drüber nachdenken.

Geht mir ganz ähnlich... ich meld' mich morgen wieder dazu. Danke Dir auf jeden Fall schonmal.

Lukas

[Lukas.]

Hi dedlfix,

Vielleicht ist die Lösung aber auch, t3 über TID an t4 zu hängen und t2 wegzulassen. Dann kannst du gruppieren und die Summen bilden.

Stimmt. TID wird in t4 redundant geführt...

Erste Rückmeldung:

SELECT EK
FROM tabelle1 t1
LEFT JOIN tabelle4 t4 ON t1.MID = t4.MID
LEFT JOIN tabelle3 t3 ON t4.TID = t3.TID
WHERE t1.MID =4343

liefert mir (in meinem Bsp.) das korrekte Ergebnis.

Deshalb, und weil mir diese Query auch einen JOIN erspart, möchte ich hierauf aufbauend "weiter forschen". Jetzt muß ich mal schauen, ob ich alle Werte (meine Bsp.-Query war ja nur sehr abgespeckt) hierauf aufbauend ermitteln kann. Wenn nicht, melde ich mich wieder. Lustig in diesem Zusammenhang ist, daß ich die Spalte "TID" genau deshalb redundant führe, um es etwas einfacher bei der Entwicklung zu haben. Aber nicht, damit ich damit neue JOIN-Möglichkeiten habe, sondern damit ich die Rechenoperationen besser kontrollieren kann (es geht mir also um bessere Lesbarkeit im db-Backend). Noch einfacher ginge es natürlich, wenn ich auch die mengen in t4 redundant geführt hätte, das war mir dann aber doch "zu viel des Guten"...

Lukas

[dedlfix]

Tach!

Wie ermittel ich jetzt, woher die unnötigen Datensätze stammen?

Das musst du anhand deiner konkreten Daten machen. Du wirst dazu nachvollziehen müssen, was das DBMS im Falle der jeweiligen Joins draus macht. Nimm nicht nur EK in die Ausgabe sondern alle Felder der Tabellen, also t1.,t2.,...

Siehe EN-Wikipedia-Artikel Correlated subquery, das letzte Beispiel im ersten Teil (vor Optimizing ...)

Habe ich mir angesehen. Bekomme ich aber nicht auf mein Beispiel angewendet.

Du musst deine Abfrage teilen. Der eine Teil muss von t1 die IDs liefern

SELECT MID FROM t1 WHERE MID=4711;

Der zweite muss aus t4 die Summe liefern

SELECT SUM(EK) FROM t4 WHERE t4.MID=4711;

Und das verknotest du nun zu einer Query

SELECT MID, (SELECT SUM(EK) FROM t4 WHERE t4.MID = t1.MID) SumEK FROM t1 WHERE MID=4711;

Die Correlation ist dabei, dass in der Subquery mit t1.MID auf das ein Feld der äußeren Query zugegriffen wird. Für jeden Datensatz der Ergebnismenge der äußeren Query wird die Subquery aufgerufen. Das geht aber so nur, wenn du aus t4 nur einen einzigen Wert bekommen möchtest.

dedlfix.

[Lukas.]

Hi dedlfix,

Das musst du anhand deiner konkreten Daten machen.

Hast Du gesehen? Ich hab die Daten mal nachmodelliert und hier hereingestellt.

Du wirst dazu nachvollziehen müssen, was das DBMS im Falle der jeweiligen Joins draus macht. Nimm nicht nur EK in die Ausgabe sondern alle Felder der Tabellen, also t1.,t2.,...

Werde ich morgen mal anwenden/ausprobieren.

Die Correlation ist dabei, dass in der Subquery mit t1.MID auf das ein Feld der äußeren Query zugegriffen wird. Für jeden Datensatz der Ergebnismenge der äußeren Query wird die Subquery aufgerufen. Das geht aber so nur, wenn du aus t4 nur einen einzigen Wert bekommen möchtest.

Werde ich auch morgen anwenden/ausprobieren.

Danke erstmal, ich melde mich wieder.

Lukas