CODEWITHSUNDEEP
sqloracle-databaseoracle12c
aartist
aartist

Reputation: 3236

Data gaps in table in oracle

I have a column with data [1,2,3,5,8,10,13,14] and I like to find the gaps in tables.

Output should look like 

MIN  MAX GAP
5    8   3
10   13  3
3    5   2 
8    10  2

Thanks.

Upvotes: 0

Views: 156

Answers (2)

user5683823
user5683823

Reputation:

You tagged your post as oracle12 - which means you can use the match_recognize clause for a simple and efficient solution. Don't worry if you are not familiar with the code in the WITH clause - I use that only to generate the "test data" (it has nothing to do with the answer to your question).

with
  dataset ( val ) as (
    select column_value from table(sys.odcinumberlist(1,2,3,5,8,10,13,14))
  )
-- End of simulated inpus (for testing only, NOT PART OF THE SOLUTION).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select min, max, gap
from   dataset
match_recognize(
  order by    val
  measures    a.val         as min,
              b.val         as max,
              b.val - a.val as gap
  after match skip to next row
  pattern     ( a b )
  define      b as b.val > a.val + 1
);

       MIN        MAX        GAP
---------- ---------- ----------
         3          5          2
         5          8          3
         8         10          2
        10         13          3

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you just want lead():

select t.col, t.next_col, (t.next_col - t.col) as gap
from (select t.*, lead(col) over (order by col) as next_col
      from t
     ) t
where t.next_col > t.col + 1;

Upvotes: 2

Related Questions