Как найти пропуски sql

Пересказ статьи Brent Ozar. How to Find Missing Rows in a Table

Когда кто-то говорит: “Найдите все строки, которые был удалены”, – это много проще сделать, когда таблица имеет столбец identity (id). Давайте возьмем таблицу Users в базе данных Stack Overflow:

Имеются id: -1, 1, 2, 3, 4, 5…, но нет 6 или 7. (Или 0). Если кто-нибудь попросит вас найти все id, которые были удалены или пропущены, как это сделать?

Использование GENERATE_SERIES в SQL Server 2022 и новее

Новая функция GENERATE_SERIES делает то, о чем говорит ее название: генерирует последовательность чисел. Мы можем выполнить соединение таблицы Users с этой последовательностью, чтобы найти все значения последовательности, для которых нет совпадающих строк в таблице Users:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT gs.value

FROM GENERATE_SERIES(@FirstId, @LastId, 1) gs

LEFT OUTER JOIN dbo.Users u ON gs.value = u.Id

WHERE u.Id IS NULL;

Поначалу LEFT OUTER JOIN может вам показаться несколько неестественным, но работает отлично:

Что такое, спросите вы? Почему GENERATE_SERIES подчеркнуто красным? Ну, SQL Server Management Studio не обновилась синтаксисом T-SQL, который был в последнем релизе.

К счастью, Microsoft разделила установки приложений для SSMS и самого ядра SQL Server именно по этой причине – долгое время выпуска SSMS не позволяло команде разработчиков ядра ускорить выпуск, поэтому они поместили менее часто обновляемую SSMS в свой собственный установщик.

(Я правильно понял? Простите меня, если это не так.)

Использование таблицы чисел в более старых версиях

Если вы еще не работаете на SQL Server 2022, то можете создать собственную таблицу чисел, используя следующие примеры. Только убедитесь, что ваша таблица чисел содержит по крайней мере столько строк, сколько номеров id вы ищете. Вот пример с таблицей на 100000000 строк:

DROP TABLE IF EXISTS dbo.Numbers;

CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED);  

INSERT INTO dbo.Numbers(Number)

SELECT TOP 10000000 row_number() over(order by t1.number) as N

FROM master..spt_values t1 

    CROSS JOIN master..spt_values t2

    CROSS JOIN master..spt_values t3

GO

Затем мы будем использовать это в том же ключе, что и GENERATE_SERIES:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT n.Number

FROM dbo.Numbers n

LEFT OUTER JOIN dbo.Users u ON n.Number = u.Id

WHERE u.Id IS NULL

  AND n.Number > @FirstId

  AND n.Number < @LastId

ORDER BY n.Number;

Это дает похожие результаты, но не идентичные:

В чем отличие? Да, этот метод не включает 0! Когда я наполнял мою таблицу чисел, то строил список только положительных целых. Единственной наиболее часто встречающейся мне ошибкой при использовании таблиц чисел является то, что они не охватывают всех необходимых нам чисел. Убедитесь, что они содержат как самое меньшее, так и самое большее из требуемых вам значений – проблема, которой мы не имеем с GENERATE_SERIES, поскольку там мы просто указываем начальное и конечное значения, а SQL Server делает все остальное.

Я не хочу погружаться глубже в другие способы решения этой проблемы, Itzik Ben-Gan введет вас в курс дела. Что до меня, мне нравится решать эту проблему быстро и легко с помощью GENERATE_SERIES. Кроме того, я ленив.

Время на прочтение
2 мин

Количество просмотров 32K

Разработчики и администраторы систем основанных на sql данных, наверняка, сталкивались с задачей — получить отсутствующее (пропущенное) значение в ряде id записей таблицы. Например номер договора, порядковый номер документа, телефонный номер, айпи-адрес и т.п. При работе с MySQL эта тривиальная задача непропорционально ресурсоемка.

Например, у нас есть пул внутренних телефонных номеров компании от 2001 до 2999 и таблица с выданными из них номерами для сотрудников:

• t1.phone
• 2001
• 2002
• 2003
• 2004
• 2005
• 2009
• 2015
• 2016

Нам нужно найти первое свободное значение (в данном случае 2006) чтобы выделить очередной номер очередному сотруднику. Если свободных значений нет, то нужно выделить следующий из диапазона. Знакомая задача? Решения, которыми изобилует интернет, сводятся к двум принципам:

1) Делать перебор в цикле: например в SQL создать курсор CUR i+1 c 2001 до 2999 и делать запросы

SELECT t1.phone FROM t1 WHERE phone = i

до пустого значения. Цикл можно делать и любым внешним софтом, смысл принципа не меняется.

2) Второй принцип, использовать LEFT (OUTER) JOIN последовательности 2001…2009 с таблицей t1 (WHERE t1.phone IS NULL конечно же), либо таблицы t1 с собой же со сдвигом на шаг:

SELECT MIN(t1.phone)+1 FROM t1 LEFT JOIN t1 AS diff ON (t1.phone = diff.phone+1) WHERE diff.phone IS NULL

Еще один вариант с использованием IN

SELECT ... WHERE phone NOT IN (....)

вообще не рассматриваю из-за громоздкости.

На маленьких объемах данных оба решения (и даже с IN) прекрасно работают, а при большом количестве записей эти решения либо ресурсоемки, либо продолжительны во времени, либо и то и другое.
Зависит от мощностей сервера и настроек базы, но в любом случае если перебирать миллион записей или приджойнить такую таблицу, даже на мощном сервере выполнение займет заметное время.

Я же захотел решить задачу быстро, не напрягая сервер, и, желательно, в один запрос. В один, не в один, но вот что получилось:

	/*выбираю граничные значения*/
	select 2000,2999 into @num,@maxid;

	select min(f.id) /* внешний селект дополнительный для "очистки" результата union */
	from
		(select s.num, min(s.num) /* первое расхождение рядов */ id
		from (
				select
					/* формирую ряд, в данном случае арифм.прогрессия, но может быть с любым шагом, или вычисляемым */
					@num:=@num+1 num,
					/* выбираю занятые значения */
					r.id 
				from t1 as r
				order by id
			) as s
		where 
			/* применяю условие расхождения рядов */ s.id != s.num /* причем ряды разойдутся и расхождение будет постоянным, поэтому во внешнем селекте использую только первое расхождение - min */
		/* если расхождения не было, добавляю еще одно возможное значение из диапазона граничных значений либо null, если значения закончились */
		union
		select @num+1 num, if(@num+1<@maxid,@num+1,null) id
		) as f
	where /* очищаю лишние результаты union */
		f.id is not null
	limit 1;

По сравнению с джойнами простые селекты выполняются в сотни раз быстрее.

Понятное дело, что такое решение известно, но в интернетах, как ни странно, с наскока его найти не удалось, вот и хочу поделиться эти простым «велосипедом».

UPD.
Прекрасное решение подсказал в личке участник stepmex
Без всякого дополнительного построения нумерации и сравнений рядов, изящно решил задачу через (SELECT 1 …..) IS NULL
Великолепная находка, я считаю:

SELECT (`t1`.`phone`+1) as `empty_phone`
FROM `t1`
WHERE (
	SELECT 1 FROM `t1` as `st` WHERE `st`.`phone` = (`t1`.`phone` + 1)
) IS NULL
ORDER BY `t1`.`phone`
LIMIT 1

i have a table of IDs and positions

CREATE TABLE #MissingSequence (ID INT NOT NULL, Position INT NOT NULL)
INSERT INTO #MissingSequence (ID,Position)
SELECT 36,1
UNION ALL SELECT 36,2
UNION ALL SELECT 36,3
UNION ALL SELECT 36,4
UNION ALL SELECT 36,5
UNION ALL SELECT 36,6
UNION ALL SELECT 44,1
UNION ALL SELECT 44,3
UNION ALL SELECT 44,4
UNION ALL SELECT 44,5
UNION ALL SELECT 44,6

What I am trying to find is if there is any break in the sequence of Positions by ID in this case the break between 44,1 and 44,3

I’ve managed to parse together:

SELECT  l.ID
    ,Start_Position = MIN(l.Position) + 1
    ,Stop_Position = MIN(fr.Position) - 1
FROM #MissingSequence l
LEFT JOIN #MissingSequence r 
    ON l.Position = r.Position - 1
LEFT JOIN #MissingSequence fr 
    ON l.Position < fr.Position
WHERE r.Position IS NULL
    AND fr.Position IS NOT NULL
GROUP BY l.ID

but it doesn’t work if there are multiple ID values. It does work if only a single ID, 44 exists.

thoughts, comments, suggestions?

thanks!

[Oracle] Поиск пропусков в последовательности

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

select Id
from table t1
where not exists(select * from table t2 where t2.Id = t1.Id)

… << RSDN@Home 1.1.4 stable SR1 rev. 568>>

Re[2]: [Oracle] Поиск пропусков в последовательности

Привет, Lloyd!
Вы пишешь 11 октября 2006:

L>

select Id
from table t1
where not exists(select * from table t2 where t2.Id = t1.Id)

Полный аут!

—
With best regards, Alex Cherednichenko.

Posted via RSDN NNTP Server 2.0

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

Например,

with
  d1 as (select 1 id from dual union all select 2 from dual union all select 3 from dual union all select 5 from dual union all select 6 from dual),
  d2 as (select id, lead (id) over (order by id) next_id from d1),
  d3 as (select id + 1 missed_id from d2 where id <> next_id - 1 order by id)
select
  *
from 
  d3
where
  rownum <= 1

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

это ?

  SELECT coalesce(MIN(CASE WHEN num <> rn THEN rn END), MAX(num) + 1, 1)
    FROM (SELECT num, row_number() over(ORDER BY num) AS rn FROM t)

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

Например так:

with t as (
  select 1 n from dual union all
  select 2 from dual union all
  select 3 from dual union all
  select 5 from dual union all
  select 6 from dual
)
select n+1 miss from (
select n, lead(n) over (order by N) - n diff
  from T order by n
)
 where diff > 1 and rownum <= 1

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

На основе:

select t.id,
       (select count(*)
        from table1 t1_
        where t1_.id <= t.id
       ) c
from table1 t
order by t.id

…что-то вроде:

select q.*
from
(select t.id,
        (select count(*)
         from table1 t1_
         where t1_.id <= t.id
        ) c
 from table1 t
 order by t.id
) q
where q.c <> q.id

Re: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В столбце таблицы хранятся некоторые целочеисленные значения. Как я могу найти первое пропущенное значение в последовательноти образованной сортировкой значений из таблицы по возрастанию? Например, для последовательности 1, 2, 3, 5, 6 это будет 4.

В общем, подумав, сделал сам:

  SELECT min(t.id) + 1
    FROM tbl t 
    WHERE (
      SELECT id
        FROM tbl
        WHERE id = t.id + 1 ) IS NULL

Re[3]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, Alex.Che, Вы писали:

AC>Полный аут!

AC>–
AC>With best regards, Alex Cherednichenko.

Поясни

… << RSDN@Home 1.1.4 stable SR1 rev. 568>>

Re[4]: [Oracle] Поиск пропусков в последовательности

Привет, Lloyd!
Вы пишешь 11 октября 2006:

AC>> Полный аут!

L> Поясни

Зачем?
Все и так всё поняли

ЗЫ: Если не понимаешь, погоняй свой запрос на тестовых данных.

—
With best regards, Alex Cherednichenko.

Posted via RSDN NNTP Server 2.0

Re[2]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>В общем, подумав, сделал сам:
A>

A>  SELECT min(t.id) + 1
A>    FROM tbl t 
A>    WHERE (
A>      SELECT id
A>        FROM tbl
A>        WHERE id = t.id + 1 ) IS NULL
A>

Если таблица большая, может быть не очень эффективно. Подумай все же об аналитике.

Re[5]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, Alex.Che, Вы писали:

AC>Зачем?
AC>Все и так всё поняли

AC>ЗЫ: Если не понимаешь, погоняй свой запрос на тестовых данных.

Все, дошло.

Имелось в виду следущее:

select Id
from table t1
where not exists(select * from table t2 where t2.Id = t1.Id + 1)

… << RSDN@Home 1.1.4 stable SR1 rev. 568>>

Re[3]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, wildwind, Вы писали:

W>Если таблица большая, может быть не очень эффективно. Подумай все же об аналитике.

Да, действительно медленно. Придется теперь как-то выбирать из предложенных вариантов. )

Re[2]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, bloom, Вы писали:

B>это ?
B>

B>  SELECT coalesce(MIN(CASE WHEN num <> rn THEN rn END), MAX(num) + 1, 1)
B>    FROM (SELECT num, row_number() over(ORDER BY num) AS rn FROM t)
B>

Скорее так:

SELECT MAX(CASE WHEN num = rn - 1 THEN num END)
  FROM (SELECT num, row_number() over(ORDER BY num) AS rn FROM t)

Re[4]: [Oracle] Поиск пропусков в последовательности

A>Да, действительно медленно. Придется теперь как-то выбирать из предложенных вариантов. )

Или еще раз подумать, а для чего тебе это надо

Re[3]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>Здравствуйте, bloom, Вы писали:

B>>это ?
B>>

B>>  SELECT coalesce(MIN(CASE WHEN num <> rn THEN rn END), MAX(num) + 1, 1)
B>>    FROM (SELECT num, row_number() over(ORDER BY num) AS rn FROM t)
B>>

A>Скорее так:
A>

A>SELECT MAX(CASE WHEN num = rn - 1 THEN num END)
A>  FROM (SELECT num, row_number() over(ORDER BY num) AS rn FROM t)
A>

да не

CASE WHEN num = rn - 1 THEN num END

не обеспечит желаемого результата (для прмера набор 1,2,4), да и MAX — даст не первый пропуск

ЗЫ ну разве что MAX(num) + 1 — посколкьу это для случая когда пропусков нет вообще

Re[5]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, Igor Trofimov, Вы писали:

A>>Да, действительно медленно. Придется теперь как-то выбирать из предложенных вариантов. )
iT>Или еще раз подумать, а для чего тебе это надо

Ну это я точно знаю. Попробую обяснить на прмере. Пусть есть последовательность 1, 2, 3, 5, 6, 7, мне нужно вставить в неё значение 2, но значения дублироваться не должны, поэтому предварительно нужно увеличить 2 и 3 из последовательности на 1. Так вот, чтобы знать какие из значений увеличивать, мне и нужен этот запрос. Иначе пришлось бы увеличивать все значения с 2 до 7.

Re[6]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

A>Ну это я точно знаю. Попробую обяснить на прмере. Пусть есть последовательность 1, 2, 3, 5, 6, 7, мне нужно вставить в неё значение 2, но значения дублироваться не должны, поэтому предварительно нужно увеличить 2 и 3 из последовательности на 1. Так вот, чтобы знать какие из значений увеличивать, мне и нужен этот запрос. Иначе пришлось бы увеличивать все значения с 2 до 7.

А для чего если не секрет? Уж не для упорядочивания ли?

… << RSDN@Home 1.1.4 stable SR1 rev. 568>>

Re[7]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, Lloyd, Вы писали:

A>>Ну это я точно знаю. Попробую обяснить на прмере. Пусть есть последовательность 1, 2, 3, 5, 6, 7, мне нужно вставить в неё значение 2, но значения дублироваться не должны, поэтому предварительно нужно увеличить 2 и 3 из последовательности на 1. Так вот, чтобы знать какие из значений увеличивать, мне и нужен этот запрос. Иначе пришлось бы увеличивать все значения с 2 до 7.
L>А для чего если не секрет? Уж не для упорядочивания ли?

Для него.

Re[8]: [Oracle] Поиск пропусков в последовательности

Здравствуйте, anonymous, Вы писали:

L>>А для чего если не секрет? Уж не для упорядочивания ли?

A>Для него.

Я в одном продукте подсмотрел очень интересное решение для упорядочивания. Там в при первоначальной вставке порядковые номера писались как обычно 1, 2, 3, …, но тип был не целочисленный, а с плавающей точкой. Если впоследствии возникала необходимость добавить новыю запись между двумя существующими, то использовали среднее от соседних порядковых номеров. Посмотри, возможно и тебе этот вариант подойдет.

… << RSDN@Home 1.1.4 stable SR1 rev. 568>>