Tags

, , , , , , , , , ,

One of the frequently asked questions is about Gaps and Islands problems which involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Let’s see how to find the range of missing values in a sequence of numbers or dates.

NUMBERS:

SQL> WITH DATA(num) AS(
  2  SELECT  1 FROM dual UNION
  3  SELECT  2 FROM dual UNION
  4  SELECT  3 FROM dual UNION
  5  SELECT  5 FROM dual UNION
  6  SELECT  6 FROM dual UNION
  7  SELECT  7 FROM dual UNION
  8  SELECT 10 FROM dual UNION
  9  SELECT 11 FROM dual UNION
 10  SELECT 12 FROM dual UNION
 11  SELECT 13 FROM dual UNION
 12  SELECT 20 FROM dual
 13  )
 14  SELECT MIN(missing_numbers),
 15         MAX(missing_numbers)
 16  FROM
 17    (SELECT missing_numbers,
 18      missing_numbers - row_number() OVER(ORDER BY missing_numbers) rn
 19    FROM
 20      (SELECT min_num - 1 + LEVEL missing_numbers
 21      FROM
 22        ( SELECT MIN(num) min_num , MAX(num) max_num FROM data
 23        )
 24        CONNECT BY level <= max_num - min_num + 1
 25      MINUS
 26      SELECT num FROM DATA
 27      )
 28    )
 29  GROUP BY rn
 30  ORDER BY rn;

MIN(MISSING_NUMBERS) MAX(MISSING_NUMBERS)
-------------------- --------------------
                   4                    4
                   8                    9
                  14                   19

SQL>

DATES:


SQL> WITH DATA(dates) AS(
  2  SELECT  DATE '2015-01-01' FROM dual UNION
  3  SELECT  DATE '2015-01-02' FROM dual UNION
  4  SELECT  DATE '2015-01-03' FROM dual UNION
  5  SELECT  DATE '2015-01-05' FROM dual UNION
  6  SELECT  DATE '2015-01-06' FROM dual UNION
  7  SELECT  DATE '2015-01-07' FROM dual UNION
  8  SELECT  DATE '2015-01-10' FROM dual UNION
  9  SELECT  DATE '2015-01-11' FROM dual UNION
 10  SELECT  DATE '2015-01-12' FROM dual UNION
 11  SELECT  DATE '2015-01-13' FROM dual UNION
 12  SELECT  DATE '2015-01-20' FROM dual
 13  )
 14  SELECT MIN(missing_dates),
 15         MAX(missing_dates)
 16  FROM
 17    (SELECT missing_dates,
 18   missing_dates - row_number() OVER(ORDER BY missing_dates) rn
 19    FROM
 20   (SELECT min_date - 1 + LEVEL missing_dates
 21   FROM
 22  ( SELECT MIN(dates) min_date , MAX(dates) max_date FROM data
 23  )
 24  CONNECT BY level <= max_date - min_date + 1
 25   MINUS
 26   SELECT dates FROM DATA
 27   )
 28    )
 29  GROUP BY rn
 30  ORDER BY rn;

MIN(MISSING_DATES) MAX(MISSING_DATES)
------------------ ------------------
2015-01-04         2015-01-04
2015-01-08         2015-01-09
2015-01-14         2015-01-19

SQL>

Hope it helps!