• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: TABIBITOSAN

Gap Detection – Tabibitosan Method

01 Wednesday Jul 2015

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, SQL, TABIBITOSAN

Another question from the ORACLE SQL & PL/SQL Forum.

Problem:

The original poster (OP) needed to find the first “missing” entry in a series of entries.

Entries are like:

  • Set 1: 0001, 0004, 0006  First missing entry: 0002
  • Set 2: 0001, 0002, 0006  First missing entry: 0003
  • Set 3: 0001, 0002, 0003   First missing entry: 0004
  • Set 4: 0002, 0003, 0004  First missing entry: 0001

Test Data:

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
  FROM data
/

        ID ENTRY
---------- -----
         1 0001 
         1 0004 
         1 0006 
         2 0001 
         2 0002 
         2 0006 
         3 0001 
         3 0002 
         3 0003 
         4 0002 
         4 0003 
         4 0004 

 12 rows selected

Solution:

So first step would be to calculate the effective entry and the entry – “value an entry had if everything was gapless” (the column is called distance).

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
     , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id 
                                             ORDER BY entry) AS distance
  FROM data
/

        ID ENTRY   DISTANCE
---------- ----- ----------
         1 0001           0
         1 0004           2
         1 0006           3
         2 0001           0
         2 0002           0
         2 0006           3
         3 0001           0
         3 0002           0
         3 0003           0
         4 0002           1
         4 0003           1
         4 0004           1

12 rows selected

As long as the distance is 0 the entry belongs to a gapless series. To find the first missing entry, we are looking for the last entry with a distance of 0.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, MAX(DECODE(distance,0,id,NULL)) AS last_in_serie
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID LAST_IN_SERIE
---------- -------------
         1             1
         2             2
         3             3
         4     

By increasing this last_in_series value by 1 we get the first missing entry.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, TO_CHAR(NVL(MAX(DECODE(distance,0,id,NULL)),0) + 1,'FM0000') as next_entry
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID NEXT_ENTRY
---------- ----------
         1 0002      
         2 0003      
         3 0004      
         4 0001 

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...