Preissteigerung ermitteln - wohl über zweithöchsten Wert arbeiten

[NisRanders]

Hallo liebe Forumsteilnehmer,

für folgendes Problem finde ich keine Lösung:

Ich habe eine Tabelle mit Einkäufen. Nennen wir sie mal Obstkorb. Sie hat folgende Datenfelder: ID, Datum, Sorte, Preis.

Ich möchte wissen, wann eine Sorte im Vergleich zum vorherigen Einkauf teurer geworden ist (Menge etc. ignorieren wir mal der Einfachheit halber).
Gesucht also Sorte und Preisdifferenz.

Seit 2 Stunden versuche ich jetzt die verschiedensten Ansätze, aber ich komme nicht weiter.

Von jeder Sorte die jüngsten Einkäufe zu ermitteln ist einfach:
SELECT Sorte, Max(Datum) AS Juengster
FROM Obstkorb
GROUP BY Sorte;
Aber wie bekomme ich dann zu denen die Preise raus?

Ferner schaffe ich es nur noch, den zweitjüngsten Einkauf zu ermitteln. Aber nur noch Insgesamt, schon nicht einmal mehr pro Gruppe:
SELECT Max(Datum) AS Juengster
FROM Obstkorb
WHERE (Datum <> (SELECT Max(Datum) FROM Obstkorb))

Jetzt hoffe ich sehr, dass mir hier jemand helfen kann und will. Meine SQL-Kenntnisse sind autodidaktischer Natur, ich habe schon einiges gelöst, aber hier stoße ich an meine Grenzen. Mit ner Programmiersprache und Schleifen käme ich sicherlich zu einer Lösung, aber das muss doch auch mit SQL gehen, oder?

Ich brauche die Lösung dieses Problems für eine MS Access 2003 Datenbank, wobei ich für meinen persönlichen Lernfortschritt sehr an einer allgemeinen SQL-Lösung interessiert bin, da ich oft auch mit MySQL arbeite.

Soweit von mir.

Viele Grüße

Nis Randers

****************
Da hängt noch ein Mann im Mast, wir müssen ihn holen.

[Novi]

Von jeder Sorte die jüngsten Einkäufe zu ermitteln ist einfach:
SELECT Sorte, Max(Datum) AS Juengster
FROM Obstkorb
GROUP BY Sorte;
Aber wie bekomme ich dann zu denen die Preise raus?

Hi,

das dürfte dir weiter helfen:

http://entwickler-forum.de/showthread.php?t=58569

Wenn nicht, dann helfe ich gerne weiter...

[NisRanders]

Hallo Novi,

also ich brauche schon noch einen kleinen Anstoß ...

Klar, der IN Operator ... aber wie ich den sinnvoll anwenden soll? Wenn ich weiß, wann zum letzten Mal von der jeweiligen Sorte eingekauft wurde, dann kann ich natürlich eine neue Abfrae machen, die mir den Preis ausgibt wenn Datum und Sorte übereinstimmen. Aber wie ich das so in SQL ausdrücke, dass ich die Sorte, den Preis und das Datum des jüngsten Einkaufs bekomme ... ???

Und dann brauche ich das Ganze noch für den zweitjüngsten Einkauf ....

und muss dann noch die Differenz bilden um zu sehen, welche Sorte gegenüber dem letzten Einkauf derselben Sorte teurer wurde.

Das überfordert mich gerade. Ist das überhaupt in einer Abfrage (natürlich mit Unterabfragen) möglich, oder sollte ich doch besser das in VBA mit Scheifen lösen? Ich habe nur Angst, dass dann die Performance in die Knie geht, wenn man ein paar 10.000 Datensätze in meinem Obstkorb sind.

Viele Grüße

NisRanders

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!

[Novi]

Hi,

ich hab das jetzt mal so runter geschrieben, da ich gerade nicht viel Zeit habe. Ich hoffe es hilft dir weiter.

[highlight=sql]
SELECT Obstkorb1.Datum, Obstkorb2.Datum, (Obstkorb1.Preis - Obstkorb2.Preis) AS PreisSteigerung
FROM Obstkorb AS Obstkorb1
LEFT JOIN Obstkorb AS Obstkorb2
ON Obstkorb1.Sorte = Obstkorb2.Sorte
WHERE Obstkorb1.Datum = (
SELECT max(Obstkorb.Datum)
FROM Obstkorb
WHERE Obstkorb1.Sorte = Obstkorb.Sorte
)
AND Obstkorb2.Datum = (
SELECT max(Obstkorb.Datum)
FROM Obstkorb
WHERE Obstkorb1.Sorte = Obstkorb.Sorte
AND Obstkorb.Datum <> Obstkorb1.Datum
)
[/highlight]

[NisRanders]

Hallo Novi,

schon jetzt erstmal ganz vielen Dank für deine Mühe. Morgen ist absolut ok, heute komme ich leider auch nicht mehr dazu, dass noch fertig zu denken, denn jetzt muss ich noch an einer Baustelle arbeiten, die morgen Termin hat :-( ... manchmal hasse ich dieses Patchwork-Arbeiten.

Ein wenig habe ich es mir natürlich auch jetzt schon angeschaut. Das mit dem Self Join (so nennt man es doch, wenn man die Tabelle mit sich selbst verbindet, oder?) ist bestimmt ein guter Weg.
Die Aggregatfunktion Max müssen wir aber, so glaube ich, auf das Datum anwenden, denn ich muss ja vom Preis des letzten (also jüngsten) Einkaufs den Preis des vorletzten Einkaufs abziehen. Ist das Ergebnis dann größer Null, dann ist das Produkt im Vergleich zum vorherigen Einkauf teurer geworden.

Ok, also denken wir morgen weiter. Ich glaube, wenn die Aufgabe gelöst ist und so im Netz steht, haben auch viele andere noch was davon, denn ich habe immer wieder gegoogelt und keine wirkliche Lösung für mein Problem gefunden.

Viele Grüße

Nis Randers

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!

[frauwue]

select
Obstkorb1.Sorte, Obstkorb1.Datum, Obstkorb1.Preis, Obstkorb2.Sorte, Obstkorb2.Datum, Obstkorb2.Preis from Obstkorb as Obstkorb1,
Obstkorb as Obstkorb2
where
Obstkorb1.Datum=(select max(Obstkorb.Datum) from Obstkorb where Obstkorb1.Sorte=Obstkorb.Sorte)
and Obstkorb2.Sorte=Obstkorb1.Sorte
and Obstkorb2.Datum =
(select max(Obstkorb.Datum) from Obstkorb where Obstkorb.Sorte=Obstkorb2.Sorte and Obstkorb.Datum < Obstkorb1.Datum)

Gruß

[NisRanders]

Hallo frauwue,

erstmal vielen Dank für die Formel. Jetzt bin ich gerade noch an einer anderen Baustelle, werde mich aber gegen Abend nochmal intensiv damit befassen. Und dann natürlich auch Rückmeldung geben.

Viele Grüße

NisRanders

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!

[NisRanders]

Danke !!!

Hallo frauwue, hallo Novi,

eure Beispiele funktionieren beide hervorragend - einfaches Copy & Paste in die SQL-Ansicht einer Access-Abfrage hinein hat bereits geklappt. Und es ist auch aufschlussreich, wenn man dann in die Entwurfsansicht der Abfrage umschaltet.

Jetzt werde ich mir die Beispiele noch mal in Ruhe ansehen, damit ich auch was lerne aus der Sache und demnächst native SQL schreiben kann

Ganz vielen Dank euch beiden!

Gruß

Nis Randers

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!

[NisRanders]

Nach berechnetem Feld filtern

Hallo Forum, insbesondere Novi und frauwue,

nachdem mir euer Code gut geholfen hat, habe ich ihn ein wenig modifiziert. So hat mein eigentlicher Obstkorb natürlich nicht nur Sorte und Preis, sondern Sorte, Menge und Preis. Um zu ermitteln, ob etwas teurer geworden ist, muss ich natürlich erst einen Stückpreis ermitteln. Dies lies sich dank eurer Vorarbeit auch gut lösen und zwar so:

[highlight=sql]
SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt, (StueckpreisNeu-StueckpreisAlt) AS PreisDifferenz
FROM test AS t1
LEFT JOIN test AS t2 ON t1.Sorte = t2.Sorte
WHERE t1.Datum = (
SELECT max(test.Datum)
FROM test
WHERE t1.Sorte = test.Sorte
)
AND t2.Datum = (
SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte
AND test.Datum <> t1.Datum
)
[/highlight]

Könnt ihr mal drüber schauen und gucken, ob ich vielleicht einen logischen Fehler drin habe, den ich beim Testen nicht gemerkt habe?

Dann das eigentliche Problem, für welches ich zwar schon eine Lösung gefunden habe, aber eben diese Lösung würde ich gerne noch einmal zur Diskussion stellen:

Problem: Ich will nur die Datensätze gelistet bekommen, in denen die Preisdifferenz positiv ist.

Folgendes Vorgehen führte zu keiner Lösung bzw. dazu, dass Access das Feld Preisdifferenz nicht mehr kannte:


[highlight=sql]
SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt, (StueckpreisNeu-StueckpreisAlt) AS PreisDifferenz
FROM test AS t1
LEFT JOIN test AS t2 ON t1.Sorte = t2.Sorte
WHERE t1.Datum = (
SELECT max(test.Datum)
FROM test
WHERE t1.Sorte = test.Sorte
)
AND t2.Datum = (
SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte
AND test.Datum <> t1.Datum
)
AND PreisDifferenz > 0
[/highlight]

Wenn ich versuchte, die Abfrage auszuführen, brachte Access immer dieses Eingabefenster für einen Parameter-Wert. Das darf natürlich nicht sein. Meine Frage ist: Warum kann ich nicht nach dem Feld PreisDifferenz filtern?

Die Lösung, die ich gefunden habe, ist die, dass ich die gesamte Abfrage als Unterabfrage verwende und dann nach PreisDifferenz filtere. So klappt es:

[highlight=sql]
SELECT t1.Sorte, t1.Datum, StueckpreisNeu, t2.Datum, StueckpreisAlt, PreisDifferenz
FROM (
SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt, (StueckpreisNeu-StueckpreisAlt) AS PreisDifferenz
FROM test AS t1
LEFT JOIN test AS t2 ON t1.Sorte = t2.Sorte
WHERE t1.Datum = (
SELECT max(test.Datum)
FROM test
WHERE t1.Sorte = test.Sorte
)
AND t2.Datum = (
SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte
AND test.Datum <> t1.Datum
) )
WHERE Preisdifferenz > 0
[/highlight]

Und hierzu meine Frage:
Es funktioniert zwar, aber ist das ein guter Weg? Oder ist das eher schlecht, so von wegen Performance und so weiter?

Ok, wäre echt klasse, wenn ihr da noch mal schauen würdet.

Viele Grüße

NisRanders

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!

[Novi]

Hi,

ich kenn mich mit Access leider nicht aus, aber vielleicht darfst du ja keine Spaltenalias innerhalb der WHERE-Klausel verwenden.

Versuch mal Folgendes, wenn du es noch nicht getan hast:

[highlight=sql]
SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt
FROM test AS t1
LEFT JOIN test AS t2 ON t1.Sorte = t2.Sorte
WHERE t1.Datum = (
SELECT max(test.Datum)
FROM test
WHERE t1.Sorte = test.Sorte
)
AND t2.Datum = (
SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte
AND test.Datum <> t1.Datum
)
AND (StueckpreisNeu-StueckpreisAlt) > 0
[/highlight]

Du könntest, um die Performance zu verbessern auch mal probieren, die Bedingung der Preisdifferenz zuerst abzufragen. Dann müssten die Unterabfragen nicht so oft ausgeführt werden (vielleicht werden sie von Access aber eh gecached):

[highlight=sql]
SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt
FROM test AS t1
LEFT JOIN test AS t2 ON t1.Sorte = t2.Sorte
WHERE (StueckpreisNeu-StueckpreisAlt) > 0
AND t1.Datum = (
SELECT max(test.Datum)
FROM test
WHERE t1.Sorte = test.Sorte
)
AND t2.Datum = (
SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte
AND test.Datum <> t1.Datum
)
[/highlight]

[frauwue]

Hallo ,

zuerst mal eine prinzipielle Anmerkung zum Left Join:
1. Er ist nur sinnvoll, wenn man auch Ergebnisse haben will, wenn es in der gejointen Tabelle keine passenden Datensätze gibt. Die Werte aus der zweiten Tabelle werden dann NULL. Wenn man innerhalb des SQL-Statements Berechnungen mit NULL Werten ausführt, wird das ganze Ergebnis NULL.

2. Der Left Join in Deinen Statements ist gar kein richtiger, da Werte aus der gejointen Tabelle auch in der where Bedingung abgefragt werden. Dies macht aus dem Left Join wieder einen normalen Join. Deshalb könntest Du das "Left" genausogut weglassen.

In der Annahme, Du willst keinen Left Join, würde ich das ganze so formulieren:

SELECT t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu,
t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt
FROM test AS t1 JOIN test AS t2 ON t1.Sorte = t2.Sorte
and t1.preis/t1.Menge > t2.preis/t2.Menge
AND t2.Datum =
( SELECT max(test.Datum)
FROM test WHERE t1.Sorte = test.Sorte AND test.Datum < t1.Datum)
where t1.Datum =
( SELECT max(test.Datum) FROM test WHERE
t1.Sorte = test.Sorte)

[NisRanders]

Danke

Hallo frauwue, hallo Novi,

vielen Dank für eure Hilfe. Ich denke, jetzt habe ich es zum Laufen gebracht.

Für den Fall, dass ein anderer Forumsbesucher auch noch davon profitieren kann, stelle ich mal meinen Code ein, den ich in Access erfolgreich getestet habe und dann aus der SQL-Ansicht wieder herauskopiert habe:

[highlight=sql]
SELECT Sorten.Sorte, t1.Sorte, t1.Datum, (t1.Preis/t1.Menge) AS StueckpreisNeu, t2.Datum, (t2.Preis/t2.Menge) AS StueckpreisAlt, StueckpreisNeu-StueckpreisAlt AS VerteuerungAbsolut, (100*StueckpreisNeu/StueckpreisAlt-100) AS VerteuerungProzent
FROM Sorten INNER JOIN (test AS t1 INNER JOIN test AS t2 ON t1.Sorte = t2.Sorte) ON Sorten.ID = t1.Sorte
WHERE (((t1.Datum)=(SELECT max(test.Datum) FROM test WHERE t1.Sorte = test.Sorte))
AND ((t2.Datum)=(SELECT max(test.Datum) FROM test WHERE t1.Sorte = test.Sorte AND test.Datum < t1.Datum))
AND (([t1].[preis]/[t1].[Menge])>([t2].[preis]/[t2].[Menge])));
[/highlight]

Die wilde Klammerei kommt von Access. Es für das benutzte DBMS umzuschreiben wird aber wohl jede/jeder schaffen. Diese Abfrage hat jetzt noch drei weitere Features:
1.) Die Sorte wird natürlich nur als Referenz auf die Sortentabelle angegeben. Um die Sorte als Text zu bekommen, habe ich die Sortentabelle noch über Join verbunden.
2.) Berechnung der absoluten Verteuerung eingefügt.
3.) Berechnung der Verteuerung in Prozent eingefügt.

Ok, nochmals Danke für eure Hilfe und viele Grüße

NisRanders

*************
Da hängt noch ein Mann im Mast, wir müssen ihn holen!