Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:oracle_analytic_functions_lag [2014/02/17 20:38] – gpipperr
+++ prog:oracle_analytic_functions_lag [2014/02/17 21:32] (aktuell) – gpipperr
@@ Zeile 1: / Zeile 1: @@
+===== Oracle Analytic Function - Beispiel 1 - Lücken in einem Datenstrom finden =====
+In einer Tabelle soll eine normalerweise fortlaufende Nummer auf Lücken geprüft werden
+  * Die Daten werden daher nach der Nummer sortiert
+  * Der Vorgänger wird in die aktuelle Zeile "gehoben" - [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions082.htm#SQLRF00652|lag Funktion]]
+    * dazu wird die LAG Funktion mit dem Spalten Namen, die wievielte Zeile zuvor, Default wert aufgerufen
+  * Aktuelle Zeile und Wert des Vorgängers werden von einander abgezogen, ist der Wert > 1 => Lücke!
+Sortierung verwenden:
+{{:prog:ana:oracle_analytical_function_v03.png?400|Oracle ANA Sortierung}}
+\\
+<code sql>
+-- Tabelle mit einer fortlaufenden Nummer erstellen
+-- zweite Spalte dient im nächsten Beispiel als Gruppen kriterium
+--
+create table t as select rownum as id
+                      ,  '1' as mandant from all_objects
+/
+insert into t select rownum as id
+                      , '1' as mandant from all_objects
+/
+-- Lücken in der Tabelle erzeugen
+--
+delete from t where id between 10   and 40;
+delete from t where id between 999 and 1000;
+delete from t where id between 7899 and 9000;
+commit;
+-- Lücken suchen
+--
+select vorgaenger    as begin_gap
+     , id            as end_gap
+     , id-vorgaenger as missing_count
+ from (select id
+            , lag (id, 1, id) over (order by id) as vorgaenger
+          from t)
+where id-vorgaenger > 1
+/
+ BEGIN_GAP      END_GAP MISSING_COUNT
+---------- ------------ -------------
+           41            32
+         1001             3
+         9001          1103
+</code>
+In einem anderen Fall sollen die Lücken der fortlaufenden Nummer bezogen auf einen Typ ermittelt werden.
+  * Die Daten werden daher nach der Nummer über eine Partition nach der Spalte GROUPS sortiert
+  * Der Nachfolger wird in die aktuelle Zeile "gehoben" - [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions086.htm#SQLRF00656|lead Funktion]]
+    * dazu wird die lead Funktion mit dem Spalten Namen, die wievielte Zeile danach, Default Wert aufgerufen
+  * Nachfolger und Wert der aktuelle Zeile werden von einander abgezogen, ist der Wert > 1 => Lücke!
+Partitionierung verwenden:
+{{:prog:ana:oracle_analytical_function_v02.png?400|Oracle ANA Partionierung}}
+\\
+<code sql>
+-- Testdaten von zuvor werden verwendet
+select id             as begin_gap
+     , nachfolger     as end_gap
+     , nachfolger-id  as missing_count
+     , mandant
+ from (select id
+            , lead (id, 1, id) over (partition by mandant order by id) as nachfolger
+            , mandant
+          from t)
+where nachfolger-id >1
+/
+   BEGIN_GAP      END_GAP MISSING_COUNT MANDANT
+------------ ------------ ------------- -
+           41            32   1
+         1001             3   1
+         9001          1103   1
+           41            32   2
+         1001             3   2
+         9001          1103   2
+</code>
+Mehr Beispiele: [[prog:oracle_analytic_functions|Oracle Analytic Functions im praktischen Einsatz]]