Benutzer-Werkzeuge

Webseiten-Werkzeuge


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!

Sortierung verwenden: 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“ - 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: 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: Oracle Analytic Functions im praktischen Einsatz

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/oracle_analytic_functions_lag.txt · Zuletzt geändert: 2014/02/17 21:32 von Gunther Pippèrr