Это сайт — моя персональная записная книжка. Интересна мне, по большей части, история, своя жизнь и немного программирование.

Поиск пропусков в таблице

У нас тут сегодня на работе возникла гипотетическая потребность найти пропуски в конкретной таблице базы данных. Так как я кроме как с MySQL в последнее время ни с чем не имел, прикинул как бы я решил эту задачу там. Получилось что-то такое:

SELECT id FROM (
    SELECT id, id-@prev AS diff, @prev:=id
    FROM
        (SELECT @prev:=NULL) ``,
        sourceofdata
   ORDER BY id
) `` WHERE diff>1

Внутри есть напрасный запрос «SELECT @prev:=NULL», который там только затем, чтобы решение получилось в одну строку.

В общем-то, у меня вопрос. Это не очень изящно и сверху есть запрос, который перебирает всю таблицу, не знаю что там делает с ним оптимизатор, сгружает ли данные во временную или фильтрует поверх. Если рассматривать худший случай, стоимость такого запроса может быть немаленькой.

Может кто-то придумать проще? Не важно для какой СУБД.

9 комментариев
HiltoN 2013

В Oracle как-то так:

select * from (
select id,
id — lag(id, 1, 0) over (order by id) as diff
from sourceofdata)
where diff > 1

HiltoN 2013

Для других баз можно как-то так:

select id
from sourceofdata t1
where not exists (
select 1 from sourceofdata t2
where t1.id = t2.id + 1
) order by id

Евгений Степанищев (bolknote.ru) 2013

Комментарий для HiltoN:

Прочитал про LAG, спасибо! Не работал с аналитическими функциями, буду знать!

Евгений Степанищев (bolknote.ru) 2013

Комментарий для HiltoN:

Тут умножение же таблиц, не сказал бы что так лучше.

HiltoN 2013

Комментарий для Евгения Степанищева:

Без привязки к конкретной СУБД нельзя сказать, что будет быстрее. Для MySQL скорее ваш первый вариант предпочтительнее, а Oracle вполне себе соптимизирует мой вариант (но аналитические функции всё равно быстрее будут).

Астродроид Женя (efix.livejournal.com) 2013

А что значит «пропуски в таблице»? Это когда id записей должны идти подряд, но есть места, где пропущены номера?
Например 1, 2, 3, 4, 6, 7 -​-​ пропущено 5?

Евгений Степанищев (bolknote.ru) 2013

Комментарий для efix.livejournal.com:

Да.

masterspammer (masterspammer.livejournal.com) 2013

в лоб:
select t1.id+1 from table_name t1 left join table_name t2 on t2.id=t1.id+1 where t2.id is null

Евгений Степанищев (bolknote.ru) 2013

Комментарий для masterspammer.livejournal.com:

Это первый вариант, который мне в голову пришёл :) Но тут умножение таблицы на саму себя, вот что плохо.