prog:oracle_analytic_functions_lag
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:oracle_analytic_functions_lag [2014/02/17 20:36] – angelegt 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 " | ||
+ | * 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: | ||
+ | {{: | ||
+ | |||
+ | |||
+ | \\ | ||
+ | |||
+ | <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 | ||
+ | , ' | ||
+ | / | ||
+ | insert into t select rownum as id | ||
+ | , ' | ||
+ | / | ||
+ | |||
+ | -- 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 | ||
+ | , 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 | ||
+ | / | ||
+ | |||
+ | |||
+ | | ||
+ | ---------- ------------ ------------- | ||
+ | | ||
+ | | ||
+ | 7898 | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | 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 " | ||
+ | * 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: | ||
+ | {{: | ||
+ | |||
+ | \\ | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | |||
+ | -- Testdaten von zuvor werden verwendet | ||
+ | |||
+ | select id as begin_gap | ||
+ | , nachfolger | ||
+ | , nachfolger-id | ||
+ | , mandant | ||
+ | from (select id | ||
+ | , lead (id, 1, id) over (partition by mandant order by id) as nachfolger | ||
+ | , mandant | ||
+ | from t) | ||
+ | where nachfolger-id >1 | ||
+ | / | ||
+ | |||
+ | | ||
+ | ------------ ------------ ------------- - | ||
+ | | ||
+ | | ||
+ | 7898 | ||
+ | | ||
+ | | ||
+ | 7898 | ||
+ | |||
+ | </ | ||
+ | |||
+ | Mehr Beispiele: [[prog: |
prog/oracle_analytic_functions_lag.txt · Zuletzt geändert: 2014/02/17 21:32 von gpipperr