Как найти пропуски в последовательности чисел?

Question

В одной из таблиц сохраняются заранее сгенерированные серийные номера (последовательности). При сохранении этих серийных номеров, некоторые из них могут быть "потерены", т.е образуются пропуски. Моя задача, найти эти пропуски в сохранённых серийных номерах.

Например, в таблице последовательность чисел: (7001, 7002, 7004, 7005, 7006, 7010).
В этой последовательности, с 7001 до 7010, отсутствуют (7003, 7007, 7008, 7009).

Есть ли в БД какая-то встроенная функция, которая найдёт эти пропуски чисел? Если нет, то какой алгоритм решения задачи?

_{Свободный перевод How to check any missing number from a series of numbers? от участника @Samcoder}

Answer 1

Решение, если граничные значения последовательности неизвестны:

with testdata as (
    select column_value n 
    from sys.odciNumberList (7001, 7002, 7004, 7005, 7006, 7010)
)
select nmin-1+level missing 
from ( 
    select min (n) nmin, max (n) nmax
    from testdata 
) connect by level <= nmax-nmin+1
minus
select n from testdata
/

   MISSING
----------
      7003
      7007
      7008
      7009

_{Свободный перевод ответа от участника @Rob van Wijk}

Answer 2

Попробуйте так:

SELECT t1.n+1 as "From", MIN(t2.n)-1 as "To"
FROM testdata t1
JOIN testdata t2 ON t1.n < t2.n
GROUP BY t1.n
HAVING t1.n+1 < MIN(t2.n)
/

Результат для последовательности как в вопросе:

      From         To
---------- ----------
      7003       7003
      7007       7009

_{Свободный перевод ответа от участника @Patrick}

Answer 3

Учитывая, что заполнение коллекций почти всегда упорядочено, то есть, в первом элементе минимальное значение, а в последнем - максимальное, то на чистом PL/SQL решается так:

var rc refcursor
declare 
    nt1 numtab := numtab (7001, 7002, 7004, 7005, 7006, 7010);
    nt2 numtab := numtab ();
    res numtab;
begin
    nt2.extend (nt1(nt1.last) - nt1(nt1.first) + 1); 
    for ix in 1 .. nt1(nt1.last) - nt1(nt1.first) + 1 loop 
        nt2 (ix) := nt1 (nt1.first) + ix -1; end loop; 
    res := nt2 multiset except nt1;    
    open :rc for select column_value missing from table (res);
end; 
/

   MISSING
----------
      7003
      7007
      7008
      7009

БЛОГ НА HUSL

Как найти пропуски в последовательности чисел?

Ответы (3 шт):