Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:oracle_analytic_functions_lag

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
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
 +       998         1001             3
 +      7898         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
 +         998         1001               1
 +        7898         9001          1103   1
 +                     41            32   2
 +         998         1001               2
 +        7898         9001          1103   2
 +
 +</code>
 +
 +Mehr Beispiele: [[prog:oracle_analytic_functions|Oracle Analytic Functions im praktischen Einsatz]]
"Autor: Gunther Pipperr"
prog/oracle_analytic_functions_lag.txt · Zuletzt geändert: 2014/02/17 21:32 von gpipperr