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:

-- 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:

-- 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