✔ Oracle 10g: analytische funktionen zeile vorher/nacher ausgeben ohne LAG/LEAD

[planb2000]

Hallo Alle,

nachdem wir in der letzten Woche die mächtigen analytischen funktionen http://www.tutorials.de/forum/relat...rozentualen-anteil-pro-gruppe-oracle-10g.html besprochen haben stoße ich an eine Verständnisfrage:

Meine Testdaten enthalten Zahlen 1 - 6
Jetzt möchte ich zum Beispiel die aktuelle Zelle PLUS die vorhergehende addieren -> das klappt
Jetzt möcht ich zum Beispiel die aktuelle Zelle PLUS vorhergende und der danach addieren -> das klappt
Jetzt möchte ich die Zellen kommulativ addieren und ausgeben -> das klappt

FRAGE: Wie kann ich aber die AUSGABE der z.B. vor der Aktuellen Zelle steuern, ohne über den LAG un LEAD Ansatz, geht das überhaubt mit meinem Ansatz? Ich frage das, weil, es könnten ja auch Datumswerte oder Strings in der zu bewertenden Spalte stehen... Die Schwierigkeit scheint zu sein das man immer eine Agregierungsfunktion vorwählen muß, oder ?

SELECT TYPE_
,Sum(TYPE_) over (ORDER BY TYPE_ ROWS BETWEEN 1 preceding AND 1 following)          add_line_before_and_after
,Sum(TYPE_) over (ORDER BY TYPE_ ROWS BETWEEN 1 preceding AND CURRENT ROW)          add_line_before
,Sum(TYPE_) over (ORDER BY TYPE_ ROWS BETWEEN unbounded preceding AND CURRENT row)  add_line_kommulativ
-- Hier sollte jetzt der Ansatz für die Ausgabe stehen...
, 'inhalt der Zeile vorher/nacher u.s.w.'  FRAGE
FROM
(
SELECT 1 AS TYPE_ FROM dual
UNION
SELECT 2 AS TYPE_ FROM dual
UNION
SELECT 3 AS TYPE_ FROM dual
UNION
SELECT 4 AS TYPE_ FROM dual
UNION
SELECT 5 AS TYPE_ FROM dual
UNION
SELECT 6 AS TYPE_ FROM dual
)

Vielen dank für Eure Hilfe

Grüße

 

[Exceptionfault]

FRAGE: Wie kann ich aber die AUSGABE der z.B. vor der Aktuellen Zelle steuern, ohne über den LAG un LEAD Ansatz, geht das überhaubt mit meinem Ansatz? Ich frage das, weil, es könnten ja auch Datumswerte oder Strings in der zu bewertenden Spalte stehen...

Zunächst einmal, nach meinem Verständnis ist der LAG und LEAD Ansatz doch genau das was du möchtest, oder?

SELECT TYPE_,
	LAG( TYPE_, 1) OVER ( ORDER BY TYPE_ ) LAG,
	LEAD( TYPE_, 1) OVER ( ORDER BY TYPE_ ) LEAD	
FROM
(
	SELECT 	LEVEL AS TYPE_
	FROM   	DUAL
	CONNECT BY LEVEL <= 6
);

          TYPE_             LAG            LEAD
--------------- --------------- ---------------
              1                               2
              2               1               3
              3               2               4
              4               3               5
              5               4               6
              6               5


SELECT last_name, hire_date, 
   LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" 
   FROM employees WHERE department_id = 30;

LAST_NAME                 HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely                  07-DEC-94 18-MAY-95
Khoo                      18-MAY-95 24-JUL-97
Tobias                    24-JUL-97 24-DEC-97
Baida                     24-DEC-97 15-NOV-98
Himuro                    15-NOV-98 10-AUG-99
Colmenares                10-AUG-99

Das untere ist ein Beispiel aus der Doku und soll zeigen, dass auch DATE kein Problem ist um LAG oder LEAD zu verwenden.

Man muss ein bisschen aufpassen, wenn man bei Oracle über analytische Funktionen spricht, denn oftmals meint man damit eigentlich auch die Aggregationsfunktionen. Tatsächlich sind machen Funktionen sogar in beiden Kategorien mit unterschiedlicher Syntax zu finden, z.B. die Funktion DENSE_RANK.

Die Schwierigkeit scheint zu sein das man immer eine Agregierungsfunktion vorwählen muß, oder ?

Jein. Wenn du beispielsweise ein Statement hast mit AVG(...) OVER (...) dann benutzt du keine analytische Funktion mehr sondern eine Aggregationsfunktion mit der "Windowing" Klausel. Klar, ist ein bisschen Haarspalterei...

Wenn du also unbedingt auf die analytischen Funktionen LAG und LEAD verzichten möchtest (warum auch immer?!), dann könntest du dir eine eigene Aggregationsfunktion schreiben.

Siehe: http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciaggref.htm
und: http://youngcow.net/doc/oracle10g/appdev.102/b14289/dciaggfns.htm

 

[planb2000]

Hallo Exceptionfault,

danke für die WIE IMMER ausführliche Antwort. Natürlich macht LAG u. LEAD das aber wenn ich zum Beispiel gleich partitionieren möchte scheint es da einen Denkfehler meinerseits gegeben zu haben, nun passt es. Die partitioning Klausel kann man ja gleich mit reinschreiben.
FRAGE1: Übrigens, die generierung Deiner Versuchsdaten ist hervorragend, gibt es das auch für z.B. Datumsbereiche? Spart ja eine Menge Tipperei.
FRAGE2: Das heißt man kann die Funktion des hin-und herschieben des Cursors nur in "AGGR_FUNCITON(...) OVER (...)" Ausdrücken verwenden?

Hier mein Ansatz, mit z.b. Monatsnamen als PartitionierungsTag:

SELECT TYPE_, part,

    LAG( TYPE_, 1)  OVER ( ORDER BY TYPE_ ) LAG,
    LEAD( TYPE_, 1) OVER ( ORDER BY TYPE_ ) Lead, 
    LAG( TYPE_, 1)  OVER (PARTITION BY part ORDER BY TYPE_ ) LAG_part,
    LEAD( TYPE_, 1) OVER (PARTITION BY part ORDER BY TYPE_ ) LEAD_part   


FROM
(
SELECT 1 AS TYPE_, 'jan' AS part FROM dual
UNION
SELECT 2 AS TYPE_, 'jan' AS part FROM dual
UNION
SELECT 3 AS TYPE_, 'jan' AS part FROM dual
UNION
SELECT 4 AS TYPE_, 'feb' AS part FROM dual
UNION
SELECT 5 AS TYPE_, 'feb' AS part FROM dual
UNION
SELECT 6 AS TYPE_, 'feb' AS part FROM dual
)

Viele Grüße

 

[Exceptionfault]

FRAGE1: Übrigens, die generierung Deiner Versuchsdaten ist hervorragend, gibt es das auch für z.B. Datumsbereiche? Spart ja eine Menge Tipperei.

Dieser Trick macht sich die Verwendung von rekursivem SQL über CONNECT BY PRIOR zu Nutzen. Als Ergbenis erhält man eine fortlaufende Zahlenreihe aus der Variablen LEVEL. Um ein Datumsbereich zu erhalten muss man eigentlich nur wissen, dass der "Integer" Wert 1, exakt 1 Tag als DATE entspricht, d.h. SYSDATE + 1/24 ist JETZT + 1 Stunde. Somit kann man sich im Grunde alle beliebigen zusammenhängenden Reihen erzeugen.

SELECT  SYSDATE + LEVEL
FROM    DUAL
CONNECT BY LEVEL <= 6

SYSDATE+LEVEL
-------------------
03.07.2008 11:34:44
04.07.2008 11:34:44
05.07.2008 11:34:44
06.07.2008 11:34:44
07.07.2008 11:34:44
08.07.2008 11:34:44

SELECT TRUNC(SYSDATE) + ((LEVEL-1)/24) 
FROM   DUAL 
CONNECT BY LEVEL <= 24;

TRUNC(SYSDATE)+((LE
-------------------
02.07.2008 00:00:00
02.07.2008 01:00:00
02.07.2008 02:00:00
02.07.2008 03:00:00
...
02.07.2008 21:00:00
02.07.2008 22:00:00
02.07.2008 23:00:00

FRAGE2: Das heißt man kann die Funktion des hin-und herschieben des Cursors nur in "AGGR_FUNCITON(...) OVER (...)" Ausdrücken verwenden?

LAG und LEAD erzeugen kein hin- und herschieben des Cursors. Oracle hat prinzipiell nur "forward-only" Cursor, d.h. sobald eine Zeile gelesen ist, ist sie "weg". Zudem kann in der OVER( ) Klausel ja eine völlig andere Sortierung oder Gruppierung als für den Rest des Statements angegeben werden. Somit führt Oracle für jeden Satz den es im "Hauptcursor" liest, einen Subselect zur Ermittlung des Ergebnisses der analytischen Funktion durch.

Was du in deinem Beispiel gemacht hast, ist den Bereich für die LAG und LEAD Funktion definiert, auf den du diese Funktion anwenden möchtest, genauso wie bei deinen obigen Beispielen mir SUM. Die PARTITION BY Klausel sagt nur, dass die LAG Funktion innerhalb eines Monats nach Vorgängern und Nachfolgern suchen soll, oder über das gesamte Resultset hinweg. Deutlicher wird das durch die Funktion FIRST und LAST

SELECT TYPE_, part,
  FIRST_VALUE( TYPE_)  OVER (ORDER BY TYPE_  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FIRST,
  LAST_VALUE( TYPE_ ) OVER (ORDER BY TYPE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST,
  FIRST_VALUE( TYPE_)  OVER (PARTITION BY part ORDER BY TYPE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FIRST_part,
  LAST_VALUE( TYPE_ ) OVER (PARTITION BY part ORDER BY TYPE_ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_part
FROM
(...);

TYPE_ PART FIRST  LAST FIRST_PART LAST_PART
----- ---- ----- ----- ---------- ---------
    1 jan      1     6          1         3
    2 jan      1     6          1         3
    3 jan      1     6          1         3
    4 feb      1     6          4         6
    5 feb      1     6          4         6
    6 feb      1     6          4         6

Eine analytische Funktion hat also nichts mit einer Verschiebung des Cursors zu tun, sondern definiert schlichtweg nur das Subselect, dass pro Zeile ausgeführt wird. Man könnte sich das (als Pseudocode, vereinfacht! => rownum wird so nicht funktionieren...) etwa so vorstellen:

SELECT 
   TYPE_, 
   part,
   LAG( TYPE_, 1)  OVER (PARTITION BY part ORDER BY TYPE_ ) LAG
FROM
(...)

' => wird zu

SELECT 
   TYPE_, 
   part,
   ( SELECT TYPE_ 
     FROM   ... AS inner
     WHERE  inner.part   = outer.part
     AND    inner.rownum = outer.rownum + 1
   )
FROM ... AS outer

 

[planb2000]

Hallo Exceptionfault,

danke für den Input und die Lösungen.

Grüße