Tags
consecutive values, dates, gaps, gaps and islands, islands, numbers, oracle, range, sequence, series, SQL
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!