prog:oracle_analytic_functions_lag
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“ - 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!
-- 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 ---------- ------------ ------------- 9 41 32 998 1001 3 7898 9001 1103
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“ - 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!
-- 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 ------------ ------------ ------------- - 9 41 32 1 998 1001 3 1 7898 9001 1103 1 9 41 32 2 998 1001 3 2 7898 9001 1103 2
Mehr Beispiele: Oracle Analytic Functions im praktischen Einsatz
prog/oracle_analytic_functions_lag.txt · Zuletzt geändert: 2014/02/17 21:32 von gpipperr