SQL Access GROUP BY + Spalte ohne Aggregatfunktion

[_thetiger_]

Hallo an alle,
ich habe ein Problem mit Access 2010 und einer sich darin befindlichen SQL Abfrage.
Die Tabelle darunter zeigt deutlich vereinfacht welche Daten mir in meiner Tabelle zur Verfügung stehen. Ich will nun für jede Person das Datum herausfinden, zu dem Sie ihren größten Erlös "erwirtschaftet" hat.
[table="width: 500, class: grid"]
[tr]
[td]ID[/td]
[td]Person[/td]
[td]Erlös[/td]
[td]Datum[/td]
[/tr]
[tr]
[td]1[/td]
[td]1[/td]
[td]13€[/td]
[td]22.03.13[/td]
[/tr]
[tr]
[td]2[/td]
[td]1[/td]
[td]15€[/td]
[td]20.01.13[/td]
[/tr]
[tr]
[td]3[/td]
[td]2[/td]
[td]20€[/td]
[td]20.01.12[/td]
[/tr]
[tr]
[td]4[/td]
[td]2[/td]
[td]17€[/td]
[td]01.01.14[/td]
[/tr]
[/table]

Normalerweise würde man das in SQL ja so formulieren:

SELECT Person, MAX(Erlös), Datum
FROM Tabelle
GROUP BY Person;

Das Problem hierbei ist nur dass Access bei SQL-Abfragen mit GROUP BY bei Spalten, nach denen nicht gruppiert wurde (in dem Fall Erlös und Datum), eine Aggregatfunktion verlangt.

Ich habe es schon mit HAVING Erlös= MAX(Erlös) versucht in der Hoffnung dass dann alle Datensätze mit kleineren Erlösen verschwinden, allerdings meckert Access dann wieder über die fehlende Aggregatfunktion...

Wie schaffe ich es nun mein oben genanntes Anliegen umzusetzen, ohne auf z.B. Unterabfragen auszuweichen, hat vielleicht irgendjemand eine Idee?

Gruß
_thetiger_

 

[mugam]

Da mußt du schon den max. Erlös pro Person ermitteln und dann anschließend in einem weiteren Schritt das Datum dazuselektieren ... Das von der geschildete Verhalten ist doch normal ...

 

[_thetiger_]

Wenn ich dich richtig verstehe meinst du folgendes:

SELECT a.Person, a.Datum, b.Erlös
FROM Tabelle a
INNER JOIN (
SELECT Person, MAX(Erlös) AS Erlös
FROM Tabelle GROUP BY Person) b
ON a.Person = b.Person
WHERE a.Erlös = b.Erlös;

Das ist leider keine praktikable Lösung, da die Unterabfragen meine sowieso schon sehr lange Abfrage noch komplexer machen würden.

Und was meinst du damit, dass das Verhalten normal sei? Soweit ich weiß funktioniert im normalen SQL das Statement aus meinem ersten Post. Warum Access das nicht kann will mir noch nicht so einleuchten...

Gruß
_thetiger_

 

[fancy]

Deine Abfrage gibt zwar für jede Person den höchsten Erlös aus, bekommt die Zuordnung zum Datum aber nicht hin. Ganz ohne Trickserei geht das nicht.
Hier sind einige Lösungsmöglichkeiten beschrieben:
http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column

 

[benneq]

Geht auch nur bedingt.

Nimm mal an, du hast folgende Tabelle:

ID, PERSON, ERLÖS, DATUM
1   1       4      1.1.10
2   1       9      2.1.10
3   2       11     3.1.10
4   2       11     4.1.10

Wenn du nun
SELECT Person, Max(Erlös) FROM ... GROUP BY Person
machst, dann erhältst du dein bekanntes Ergebnis. Aber wenn du nun auch noch das Datum dazu willst, welches soll er da nehmen für Person=2? Den 3.1. oder den 4.1.? Person und Erlös sind schließlich identisch. Also könntest du sowas wie MAX(Datum) nehmen, um gezielt das spätere Datum zu selektieren.

Im Prinzip gilt es aber erstmal klarzustellen, was genau er hier selektieren soll, wenn es bei Erlös und Datum "Duplikate" gibt.

Und wenn dein Query zu komplex ist, hast du wahrscheinlich bei der Organisation deiner Daten in der DB Mist gebaut (5. Normalform ist in den seltensten Fällen praktikabel). Alternativ könntest du in deiner Client Applikation das ganze via Cache lösen oder dich mal nach einer anderen Datenbank umschauen, mit der du deine Daten besser abbilden kannst *auf's NoSQL-Lager schiel*.

Vielleicht kannst du ja mal das komplette Statement posten.

 

[mambokurt]

Ich wage zu behaupten: wenn sein Query komplex ist liegt das nicht an der Datenbank ^^ Davon ab ist komplex relativ, komplex geht los wenn du Queries schreibst die aus Datenbankfeldern Queries zusammenbauen die aus Datenbankfeldern Queries zusammenbauen

SELECT DISTINCT ID, PERSON, ERLÖS, DATUM FROM Tabelle tbl1 WHERE erlös = (select max(erlös) AS maximum FROM Tabelle tbl2 WHERE tbl1.person = tbl2.person)

Ich hab das jetzt nicht getestet(wie auch), damit sollte er im inneren Select für den jeweiligen Satz vom äußeren Select den maximalen Betrag ziehen, das äußere wird dann mit DISTINCT zusammengefasst (ist das selbe wie ein GROUP BY auf alle Felder). Musst mal ausprobieren in wie weit das klappt

 

[benneq]

Klar kann das an der DB liegen, in einer Relationalen DB musst du jeden Scheiß JOINen. Wenn er große Dokumente, die nicht aus Teil-Entitäten bestehen ablegen will, wäre z.B. ein Dokumentstorage wie MongoDB angebracht. Und wenn es um Graphen geht eben eine Graphdatenbank ala Neo4J, etc. pp.
Damit kann man für bestimmte Datenstrukturen die Querys extrem vereinfachen. Aber auch sonst kann man in einer relationalen DB bestimmte Teile denormalisieren, um einfachere Querys zu erhalten. Kommt halt immer drauf an, was gegeben ist.


Bzgl. deines Querys: Das DISTINCT ist nutzlos, weil darin die ID enthalten ist, da ist dann in diesem Query per Definition jede Zeile Unique.

 

[_thetiger_]

Hab es jetzt folgendermaßen gelöst:

SELECT DISTINCT a.Person, a.Datum, a.Erlös
FROM Tabelle a
LEFT JOIN Tabelle b
ON (a.Person = b.Person AND a.Erlös < b.Erlös)
WHERE b.Person IS NULL;

Dadurch fällt mir sogar die Notwendigkeit zum Gruppieren weg. Auch wenn man schon sagen muss, dass es nicht die verständlichste Lösung ist, es hat mir zumindest eine Unterabfrage erspart.

Hab die Lösung aus fancys Link, danke dafür.

Ich frage aus der Datenbank nur ab, kann also für die Organisation der Daten nichts. Ein anderes DBMS ist leider auch keine Möglichkeit...

MfG
_thetiger_

 

[mambokurt]

Klar kann das an der DB liegen, in einer Relationalen DB musst du jeden Scheiß JOINen. Wenn er große Dokumente, die nicht aus Teil-Entitäten bestehen ablegen will, wäre z.B. ein Dokumentstorage wie MongoDB angebracht. Und wenn es um Graphen geht eben eine Graphdatenbank ala Neo4J, etc. pp.

Bzgl. deines Querys: Das DISTINCT ist nutzlos, weil darin die ID enthalten ist, da ist dann in diesem Query per Definition jede Zeile Unique.

Dann lassen wir die ID halt weg, die wollte er eh nicht War spät gestern

Zum Joinen: ja, dafür ist es ja relational, es geht ja gerade darum nicht jeden Mist doppelt zu haben und Redundanzen zu vermeiden. Wie du schon geschrieben hast wird das in der Praxis aber sowieso nie so laufen.

Davon abgesehen war ich nie ein Freund davon 2 MB PDF Dateien in eine Datenbank zu schieben, die häng ich lieber in ein extra Verzeichnis und speicher nur die Links in der DB( und davon abgesehen reden wir hier immer noch über eine Access Datenbank, ich glaube nicht dass da groß Manpower dahinter ist um die Daten nach NOSQL zu holen und ein passendes Programm zu schreiben).

 

[benneq]

Ich versteh schon was du meinst, aber du hast definitiv den Documentstorage nicht verstanden (PDF speichern?! ). Da geht's nicht um "BLOBs" (um es im SQL Jargon auszudrücken), sondern eher um Verschachtelung von Objekten.

Ich hab in einem Projekt z.B. "Bestellungen". Und jede Bestellung besteht aus einer Liste von Produkten. In einer relationalen DB würdest du nun mindestens 2 Tabellen erstellen, und dann im "Produkt" via Foreign Key auf die "Bestellung" referenzieren.
In einer Dokument-Datenbank wie MongoDB muss ich diese 2 Dinge aber gar nicht separat speichern, sondern kann in der Bestellung einfach eine Liste von Produkten ablegen.
Diese Art der Speicherung ist vor allem dann sinnvoll, wenn man (wie hier) eine one-to-many oder one-to-one relation hat und die Child-Elemente nie ohne den Parent abgerufen werden. D.h. die einzelnen "Produkte" sind völlig uninteressant und werden nur im Kontext einer "Bestellung" benutzt.
Natürlich hat die Datenbank auch Nachteile Es gibt keine JOINs. D.h. die müssen alle manuell in Software gelöst werden. Was man aber natürlich möglichst vermeiden sollte, da man sonst ganz schnell mit der Performance wieder ganz weit unten ist. Deshalb denormalisiert man dort meist einen Teil der Daten, da Speicherplatz im Gegensatz zu Performance nichts kostet.

Ich hab in meinen Projekten meist mindestens 2 verschiedene Datenbanksysteme im Hintergrund laufen, weil sich manche Daten einfach nicht schön in bestimmten DBS abbilden lassen oder dann evtl. die Performance entsprechend leiden würde. Sehr gutes Beispiel dafür ist: "Über wie viele Personen ist Person a mit Person b befreundet?". In einer relationalen DB ergibt das einen rekursiven JOIN, was ziemlich hässlich enden kann. Für solch einen Fall nimmt man dann eine Graph DB, da geb' ich dann einfach Start- und Endknoten an und die Datenbank findet das Ergebnis extrem schnell, weil sie eben genau darauf ausgelegt ist.

Aber natürlich ist eine relationale DB für 80% der Fälle vollkommen ausreichend und meist auch die richtige Wahl. Ich wollte nur aufzeigen, dass es für verschiedene Datenstrukturen und Aufgaben eben auch verschiedene Datenbanken gibt.


@_thetiger_: Mit deinem Query bekommst du nun jede Person mit dem höchsten Erlös und dazu ein zufälliges Datum, das zum höchsten Erlös gehört. Deine Lösung funktioniert zwar, aber ist nicht sonderlich schön, weil dein JOIN mit einem "<" Operator läuft, d.h. intern wird da bei vielen Einträgen für eine einzelne Person alles kreuz und quer gejoint. Und das macht das Query vermutlich viel komplexer als alles andere was dieses Query sonst noch so beinhalten könnte.
Oder geht's dabei eher um eine kleinere DB mit hunderten oder tausenden Einträgen und nicht Millionen? Dann kann es dir natürlich egal sein. Als Benutzer merkst du dann eh nicht, ob die Abfrage nun 0,01 oder 0,1 Sekunde dauert

EDIT: Beim zweiten Drübersehen bin ich mir nicht mal sicher, ob das Query überhaupt funktioniert, wenn du mehr als 2 Einträge in der DB hast und was passiert, wenn Duplikate drinstehen.

ID, PERSON, ERLÖS, DATUM
1   1       4      1.1.10
2   1       9      2.1.10
3   2       14     3.1.10
4   2       12     4.1.10
5   2       10     2.1.10
6   3       20     8.1.10
7   3       20     9.1.10
8   4       16     1.1.10
9   4       18     1.1.10
10  4       18     1.1.10

Lass dein Query mal auf diesen Datensatz los, da könnten diverse Bugs ans Tageslicht kommen!

 

[_thetiger_]

Danke für deinen Hinweis, beneque.

Tatsächlich hätte mir diese Abfrage in der jetzigen Form 2 oder mehrere Datensätze geliefert zu jeder Person, wenn mehrere gleiche höchste Erlöse existiert hätten.

Da bei meinem Beispiel nur die Problemstellung aus der wirklichen Datenbank abstrahiert wurde und das Feld, zu dem das Maximum gesucht werden soll prinzipbedingt nicht zweimal den gleichen Wert haben kann, so macht das letztendlich für mich keinen Unterschied.

Um das zu umgehen hätte ich das GROUP BY wieder hinzufügen und oben jede abgefragte Spalte mit der Aggregatfunktion FIRST() eingeben müssen. Dann hätte ich genau einen Eintrag pro Person gehabt.


Performancemäßig ist das relativ egal, da die Anzahl der Datensätze mit der gearbeitet wird in den Hunderten liegt.

Danke für deine und eure Unterstützung.

Gruß
_thetiger_