Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как выявить дыры в заданом диапазоне чисел  [new]
kolyady
Member

Откуда:
Сообщений: 137
Тобиш задача следующая :
1) я указываю диапазон чисел для проверки на наличие "дыр" в колонке
2) мне нужно выявить все дыры и записать значение:
а) с которого начались пробелы
б) число на котором закончились
в) количество пропущенных
и так по всем дырам в заданом диапазоне
колонка много милионная

select
t1.f1,
min(t2.f1) as mins,
min(t2.f1)-t1.f1-1 as counts
from #temp1 t1, #temp1 t2
where t2.f1>t1.f1
group by t1.f1
having t1.f1+1< min(t2.f1)

Этот запрос решает проблему но тормозит на больших обьемах информации (сравнить за раз надо 10-20 млн строк)
Может у когото есть другое решение, буду рад за подсказку :)
28 фев 18, 12:55    [21224547]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
aleksrov
Member

Откуда:
Сообщений: 948
kolyady,

500 тысяч мульенов раз здесь на форуме это обсуждалось
https://www.google.ru/search?hl=en&q=gaps and island t-sql&gws_rd=cr,ssl&dcr=0&ei=RHyWWq6iOIWRsgHZ_q34Bw
28 фев 18, 12:58    [21224561]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
kolyady,

составьте нумерованный многомиллионный список и найдите разницу двух наборов.
28 фев 18, 13:09    [21224608]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
kolyady
Member

Откуда:
Сообщений: 137
Владислав Колосов,
Тобиш вы имеете ввиду создать аналогичную таблицу с таким же диапазоном чисел (только без пропусков) и сравнить с оригиналом?
28 фев 18, 13:37    [21224718]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
kolyady, страсти то какие...

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

create table #temp (id int);

insert into #temp  (id)
values  (1), (2), (4), (8);

select prev_id + 1 as gap_start, t.id - 1 as gap_end, t.id - t.prev_id - 1 as gap_count
from    (select id, lag(id) over (order by id) as prev_id
         from   #temp) as t
where t.id - t.prev_id > 1;
28 фев 18, 13:47    [21224771]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
kolyady
Member

Откуда:
Сообщений: 137
Minamoto,

спасибо вроде то что нужно :)
28 фев 18, 13:53    [21224790]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
kolyady, справедливости ради это не лучший вариант, посмотрите здесь сравнение по производительности (из ссылки, которую вам выше дали, нашел):

https://blog.waynesheffield.com/wayne/archive/2012/04/sql-server-2012-performance-test-gap-detection/
28 фев 18, 13:57    [21224810]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
kolyady,

да, генерация такой таблицы не займет много времени, а сравнение будет происходить очень быстро. Особенно полезна такая таблица при регулярном поиске. То же самое касается и календаря.
28 фев 18, 16:00    [21225391]     Ответить | Цитировать Сообщить модератору
 Re: Как выявить дыры в заданом диапазоне чисел  [new]
kolyady
Member

Откуда:
Сообщений: 137
Всем спасибо!

Ответ Minamoto, для моей конкретной ситуации целиком устраивает
Тему можно считать закрытой.
:)
28 фев 18, 16:06    [21225410]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить