SQL für ganz bestimmte Datensätze

[pixelfreund]

Hallo an Alle,

ähnliche Themen wurden schon mehrfach diskutiert - haben mich aber noch nicht weitergebracht.

Oracle 8.1
Es geht um die Bewertung von ID's zum jeweils gültigen Datum.

2 Tabellen
Tabelle 1: ID, Menge, Datum
Tabelle 2: ID, Preisstellungsdatum, Preis

Beispiel:
Tabelle 1:
1; 5; 08.07.2011
1; 4; 30.06.2011

Tabelle 2:
1; 01.06.2011; 50
1; 01.07.2011; 55

Ergebnissoll so aussehen:
1; 5; 08.07.2011; 01.07.2011; 55
1; 4; 30.06.2011; 01.06.2011; 50

Es sollen also zu allen Datensätzen aus der Tabelle 1 die zum Zeitpunkt Datum letztgültigen Daten aus Tabelle 2 gesucht werden (und auch nur diese). Ältere Sätze sollen nicht berücksichtigt werden.

Ein einfache Where-Anweisung liefert zu jeder ID alle Datensätze.

Vielen Dank für die Hilfe im voraus.
Pixelfreund

[mez]

Wie sollen die Datensätze denn verknüpft sein? Ich kann aus deinem Beispiel keine eindeutige Verknüpfung ersehen.

[jum]

Da gibt es sicher viele Lösungen, hier eine mit analytischen Funktionen. Das Problem wird vereinfacht auf das Suchen des Satzes mit dem kleinsten Abstand vom Preisstellungsdatum aus tab2 zum Datum aus tab1. Der hat dann dr=1.

WITH tab1 AS
 (SELECT 1 id, 5 menge, to_date('08.07.2011','DD.MM.YYYY') datum FROM dual UNION ALL
  SELECT 1   , 4      , to_date('30.06.2011','DD.MM.YYYY')       FROM dual),
     tab2 AS
 (SELECT 1 id, to_date('01.06.2011','DD.MM.YYYY')  pdatum, 50 preis FROM dual UNION ALL
  SELECT 1   , to_date('21.05.2011','DD.MM.YYYY')        , 35 preis FROM dual UNION ALL
  SELECT 1   , to_date('01.07.2011','DD.MM.YYYY')        , 55       FROM dual)
SELECT tab1.id, tab1.datum, tab2.pdatum, datum-pdatum ztage,
       DENSE_RANK() OVER (PARTITION BY tab1.datum ORDER BY datum-pdatum) dr
  FROM tab1, tab2
 WHERE tab1.id=tab2.id
   AND tab1.datum>tab2.pdatum   
 ORDER BY id, datum, dr;


id      datum      pdatum ztage   dr
--------------------------------------------
1  30.06.2011  01.06.2011   29     1
1  30.06.2011  21.05.2011   40     2
1  08.07.2011  01.07.2011    7     1
1  08.07.2011  01.06.2011   37     2
1  08.07.2011  21.05.2011   48     3

[pixelfreund]

Hallo mez,

die Tabellen sind über die id verknüpft.

Hallo jum,

danke für deine Lösung.
Allerdings klappt das bei mir noch nicht so richtig.
tab1.datum ist ein Datum-/Zeitwert und der Satz mit dem niedrigsten ztage erhält nicht dr 1
*ID datum pdatum ZTAGE DR
1 23.03.2011 05:38:59 09.11.2009 499,235405092593 4
1 23.03.2011 05:38:59 15.06.2010 281,235405092593 2
1 23.03.2011 05:38:59 06.07.2010 260,235405092593 1
1 23.03.2011 05:38:59 05.08.2010 230,235405092593 3
Eigentlich müsste doch der letzte Satz dr=1 sein?
Und wie kann ich dann die Ergebnismenge auf z.Bsp. dr=1 beschränken?

Danke und Gruß
Pixelfreund

[jum]

Dein Ergebnis ist irgendwie unlogisch, kannt Du mal das gesamte SELECT posten ?
Bei mir ergibt sich wie erwartet:

WITH tab1 AS
 (SELECT 1 id, 5 menge, to_date('23.03.2011','DD.MM.YYYY') datum FROM dual UNION ALL
  SELECT 1   , 4      , to_date('30.06.2010','DD.MM.YYYY')       FROM dual),
     tab2 AS
 (SELECT 1 id, to_date('09.11.2009','DD.MM.YYYY')  pdatum, 50 preis FROM dual UNION ALL
  SELECT 1   , to_date('15.06.2010','DD.MM.YYYY')        , 35       FROM dual UNION ALL
  SELECT 1   , to_date('06.07.2010','DD.MM.YYYY')        , 25       FROM dual UNION ALL
  SELECT 1   , to_date('05.08.2010','DD.MM.YYYY')        , 51       FROM dual)
SELECT tab1.id, tab1.datum, tab2.pdatum, datum-pdatum ztage,
       DENSE_RANK() OVER (PARTITION BY tab1.datum ORDER BY datum-pdatum) dr
  FROM tab1, tab2
 WHERE tab1.id=tab2.id
   AND tab1.datum>tab2.pdatum   
 ORDER BY id, datum, dr;

        ID DATUM      PDATUM          ZTAGE         DR
---------- ---------- ---------- ---------- ----------
         1 30.06.2010 15.06.2010         15          1
         1 30.06.2010 09.11.2009        233          2
         1 23.03.2011 05.08.2010        230          1
         1 23.03.2011 06.07.2010        260          2
         1 23.03.2011 15.06.2010        281          3
         1 23.03.2011 09.11.2009        499          4

Die Sätze mit dr=1 filtert man durch ein äußeres SELECT heraus.

WITH tab1 AS
 (SELECT 1 id, 5 menge, to_date('23.03.2011','DD.MM.YYYY') datum FROM dual UNION ALL
  SELECT 1   , 4      , to_date('30.06.2010','DD.MM.YYYY')       FROM dual),
     tab2 AS
 (SELECT 1 id, to_date('09.11.2009','DD.MM.YYYY')  pdatum, 50 preis FROM dual UNION ALL
  SELECT 1   , to_date('15.06.2010','DD.MM.YYYY')        , 35       FROM dual UNION ALL
  SELECT 1   , to_date('06.07.2010','DD.MM.YYYY')        , 25       FROM dual UNION ALL
  SELECT 1   , to_date('05.08.2010','DD.MM.YYYY')        , 51       FROM dual)
SELECT * FROM
 (SELECT tab1.id, tab1.datum, tab2.pdatum, datum-pdatum ztage,
         DENSE_RANK() OVER (PARTITION BY tab1.datum ORDER BY datum-pdatum) dr
    FROM tab1, tab2
   WHERE tab1.id=tab2.id
     AND tab1.datum>tab2.pdatum)
 WHERE dr=1;   

        ID DATUM      PDATUM          ZTAGE         DR
---------- ---------- ---------- ---------- ----------
         1 30.06.2010 15.06.2010         15          1
         1 23.03.2011 05.08.2010        230          1

[pixelfreund]

Hallo jum,

hier meine SELECT-Anweisung:
SELECT tab1.id, tab1.datum, tab2.pdatum, tab1.datum-tab2.pdatum ztage,
DENSE_RANK() OVER (PARTITION BY tab1.datum ORDER BY tab1.datum-tab2.pdatum) dr
FROM tab1, tab2
WHERE tab1.id = tab2.id
AND tab1.datum >= tab2.pdatum
ORDER BY tab1.id, tab1.datum

Gruß
Pixelfreund

[jum]

Ist mir ein Rätsel
Funktionieren denn meine Beispiel-Selects ?

[pixelfreund]

Hallo jum,

mit PL/SQL Dev. und SQL Plus kann ich deine Beispiel-Selects nicht verarbeiten.
"with Tab1 as ..." - ungültige SQL-Anweisung

Oracle SQL Dev 3 sagt mir, dass er sich mit dieser DB-Version (8.1.7) nicht verbinden kann.

Kannst du mir ein anderes Programm empfehlen oder mach ich einen Fehler?

Kann es eine DB-Einstellung geben, die dieses abweichende Ergebnis bei DENSE_RANG verursacht?

Thx
Pixelfreund

[jum]

Du benutzt tatsächlich eine Datenbank aus dem vorigen Jahrtausend
Ich könnte mir keine DB-Einstellung für dieses abweichende Ergebnis vorstellen.
Viel Erfolg

[pixelfreund]

Hallo jum,

ich würde auch gerne eine neuere Version einsetzen, aber ......
Trotzdem vielen Dank für deine Hilfe.
Vielleicht finde ich den Fehler ja noch.

Pixelfreund

[Wernfried]

Hallo

Subquery Factoring (also ein SQL Statement mit dem WITH) funktioniert bei Oracle erst seit Release 9i.
Die Funktion DENSE_RANG ist relativ neu, vermutlich gab es die bei 8.1.7 auch noch nicht.

Gruss

[pixelfreund]

Hallo wernfried,

DENSE_RANK funktiioniert auch bei 8.1.

Hallo jum,

ich hab die DENSE_RANK-Anweisung nochmal verändert:
DENSE_RANK() OVER (PARTITION BY tab1.datum ORDER BY tab2.pdatum DESC) dr

Liefert bei den ersten Test's richtige Ergebnisse.
Werde noch mit allen Daten testen.

gruß
Pixelfreund