Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== 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
---------- ------------ -------------
         9           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
------------ ------------ ------------- -
           9           41            32   1
         998         1001             3   1
        7898         9001          1103   1
           9           41            32   2
         998         1001             3   2
        7898         9001          1103   2

</code>

Mehr Beispiele: [[prog:oracle_analytic_functions|Oracle Analytic Functions im praktischen Einsatz]]