===== 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}}
\\
-- 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" - [[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}}
\\
-- 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: [[prog:oracle_analytic_functions|Oracle Analytic Functions im praktischen Einsatz]]