MySQL sum über mehrere Tabellen

[Reini24]

Hallo liebe Selfhtml-Gemeinde,

ich habe ein spezielles MySQL Problem, bei dem ich leider nicht weiterkomme.

Der Aufbau meiner Datenbank und die spezielle Abfrage kann man unter folgendem Link einsehen.

http://sqlfiddle.com/#!2/f12a5/1

Bei der Abfrage kommt es zu einem Fehler da ein device doppelt gezählt wird da es in verschiedenen Gruppen in einer Struktur vorkommt.
Das richtige Ergebniss wäre 4 Fehler für die Stuktur 1.

Vielen Dank für eure Hilfe

[dedlfix]

Tach!

Bei der Abfrage kommt es zu einem Fehler da ein device doppelt gezählt wird da es in verschiedenen Gruppen in einer Struktur vorkommt.

Das ist kein Fehler, das ist so, weil es schließlich über die doppelte Gruppenzugehörigkeit in zwei Strukturen vorkommt. Definiere ein Ausschlusskriterium für diesen Fall. Und was ist, wenn es über seine Gruppen in zwei Strukturen vorkommt?

dedlfix.

[Reini24]

Ja du hast Recht es ist kein Fehler sondern die Abfrage liefert nicht das erwartete Ergebnis zurück.

Das ist kein Fehler, das ist so, weil es schließlich über die doppelte Gruppenzugehörigkeit in zwei Strukturen vorkommt. Definiere ein Ausschlusskriterium für diesen Fall. Und was ist, wenn es über seine Gruppen in zwei Strukturen vorkommt?

Strukturen sind räumlich getrennte Einheiten in denen Gruppen liegen und darin Devices. Eine Gruppe kann also nicht in mehreren Strukturen liegen ein Device aber in mehren Gruppen in der selben Struktur. Was meinst Du mit einem Ausschlusskriterium?

Reini24

[dedlfix]

Tach!

Eine Gruppe kann also nicht in mehreren Strukturen liegen ein Device aber in mehren Gruppen in der selben Struktur. Was meinst Du mit einem Ausschlusskriterium?

Ein Ausschlusskriterium ist eine Regel, die bei Dopplungen greifen soll. Diese solltest du zunächst in Worten zu formulieren versuchen. Beispielsweise wären das:

• Struktur-ID, -Name und Device-ID müssen eindeutig sein.
• Wenn Gerät in Gruppe x vorkommt, soll es in Gruppe y / den anderen Gruppen nicht gezählt werden.

Die erste Regel lässt sich mit einem DISTINCT realisieren. Zunächst werden die drei genannten Felder zusammen mit dem Status mit SELECT DISTINCT ... aus der gejointen Datenmenge geholt. Strukturname und Gerätestatus werden zwar bei der DISTINCTisierung nicht benötigt, wirken sich aber nicht weiter negativ auf das DISTINCT aus. Beide Werte müssten sonst über korrelierte (correlated) Subquerys in die Ergebnismenge geholt werden. (Das müssen sie in anderen DBMSen sowieso, weil in denen die GROUP-BY-Regeln strenger durchgesetzt werden.) Damit werden dann mit GROUP BY und SUM() die Summen gebildet, wozu die erste Query als Subquery notiert wird: http://sqlfiddle.com/#!2/f12a5/3.

Noch zwei Empfehlungen:

Ich hab die Join-Bedingungen mal explizit formuliert. Damit sieht man besser, was wie zusammengehört, als wenn man diese zusammen mit den eventuellen anderen Kriterien und fernab des jeweiligen Joins ins WHERE packt. Zudem bei einigen Join-Arten (wie LEFT (OUTER) JOIN) ist nur diese explizite Formulierung anwendbar (zumindest im MySQL-Dialekt) - du musst sie also in jedem Fall auch kennen. Vergleiche: http://wiki.selfhtml.org/wiki/Artikel:DBMS_und_SQL/Einführung_in_Joins#Platzierung_von_Kriterien

Tabellen für konkrete Dinge sind mit der Einzahl besser benannt. Es ist klar und ganz natürlich, dass eine Tabelle die Daten von mehrere dieser Dinge speichert. Das muss man nicht durch die Mehrzahl betonen. Mit dieser Einzahl liest sich dann eine Query sinnvoller, ebenso wie wenn ein Object-Relation-Mapper die Namen 1:1 in Klassennamen übernimmt.

SELECT device.id -- hier ist die id (jeweils) eines Devices gemeint
vs.
SELECT devices.id -- und nicht die von mehreren.

dedlfix.

[Reini24]

Danke die Lösung hat mir viel geholfen.

Tabellen für konkrete Dinge sind mit der Einzahl besser benannt. Es ist klar und ganz natürlich, dass eine Tabelle die Daten von mehrere dieser Dinge speichert. Das muss man nicht durch die Mehrzahl betonen. Mit dieser Einzahl liest sich dann eine Query sinnvoller, ebenso wie wenn ein Object-Relation-Mapper die Namen 1:1 in Klassennamen übernimmt.

SELECT device.id -- hier ist die id (jeweils) eines Devices gemeint
vs.
SELECT devices.id -- und nicht die von mehreren.

Das hatte ich mir am Anfang auch schon so vorgestellt aber da "Group" ein MySQL Schlüsselwort ist bin ich dann bei der Tabellenbezeichnung auf die Mehrzahl übergegangen.

Reini24

[dedlfix]

Tach!

Das hatte ich mir am Anfang auch schon so vorgestellt aber da "Group" ein MySQL Schlüsselwort ist bin ich dann bei der Tabellenbezeichnung auf die Mehrzahl übergegangen.

Ja, manche Wörter sind ein Problem. Da hilft nur, sie als Identifier (Bezeichner) zu markieren (in Backticks einfassen) oder einen detaillierteren Namen zu verwenden (DeviceGroup, SensorGroup, ...Group).

dedlfix.