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

Откуда:
Сообщений: 139
Есть таблица с номерами Таблица(int Number)

Она содержит набор случайных чисел, скажем 1,2,3,4,5,7,9,12,13...

Как можно получить ближайшее отсутствующее число? В данном примере будет 6. Таблица может быть большой, скажем миллион записей
13 фев 19, 08:48    [21808014]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Щукина Анна
Member

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

with t as (select * from (
values
(1),(2),(3),(4),(5),(7),(9),(12),(13))v(n)
)

select top(1) n + 1
  from t t0
where not exists(select null from t t1 where t1.n = t0.n + 1)
order by n
13 фев 19, 09:01    [21808023]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20208
Lessyp
В данном примере будет 6.
Каким должен быьт результат, если отсутствует запись со значением 1?
13 фев 19, 09:25    [21808036]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Lessyp
Member

Откуда:
Сообщений: 139
Супер, спасибо. Если 1 отсутствует, то должен быть 1, но это можно разрулить принудительным добавлением нуля в таблицу при ее создании
13 фев 19, 11:33    [21808203]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20208
Lessyp
это можно разрулить принудительным добавлением нуля в таблицу при ее создании
Просто переверни условие и добавь ограничение >0.
13 фев 19, 11:52    [21808234]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
with t as (select *
           from (values (1), (2), (3), (4), (5), (7), (9), (12), (13)) v(n)),
     a as (select n, row_number() over (order by n) rn from t)
select top(1) rn from a where n > rn order by n;
rn
6
with t as (select *
           from (values (10), (2), (30), (40), (5), (7), (9), (12), (13)) v(n)),
     a as (select n, row_number() over (order by n) rn from t)
select top(1) rn from a where n > rn order by n;
rn
1
13 фев 19, 15:40    [21808610]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
Lessyp
Если 1 отсутствует, то должен быть 1, но это можно разрулить принудительным добавлением нуля в таблицу при ее создании
Зачем в таблицу добавлять ноль, если можно добавить единицу в запрос?

with t as (select * from (
values
(3),(4),(5),(7),(9),(12),(13))v(n)
)

select top(1) n 
  from (
         --- Просто добаввь ... ... один ;) :) :
         select 1 as n where not exists(select null from t t2 where n = 1)
          union all
         select top(1) n + 1
           from t t0
          where not exists(select null from t t1 where t1.n = t0.n + 1)
          order by n
       ) v
 order by n
14 фев 19, 04:48    [21809038]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Lessyp
Member

Откуда:
Сообщений: 139
всем спасибо, замечательно

andrey odegov
(select n, row_number() over (order by n) rn from t)


я так думаю ваш вариант на большой таблице сьест много памяти т.к. все миллионы записей затянутся в память
14 фев 19, 06:08    [21809042]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
Проверим?
Возьмем 10 000 000 миллионов строк, сделаем дыру в районе 4 000 000 миллионов.
Выполняем здесь https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=37d75a987970762218a55792eadc5ca3.
План здесь https://www.brentozar.com/pastetheplan/?id=Skf1w_zrE.
14 фев 19, 08:04    [21809067]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20208
Lessyp
я так думаю ваш вариант на большой таблице сьест много памяти т.к. все миллионы записей затянутся в память
Только в случае, если поле с числами неиндексировано - да и то, "затянутся в память" не все записи, а только это поле, для сортировки.
14 фев 19, 08:29    [21809077]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
andrey odegov
Проверим?
Возьмем 10 000 000 миллионов строк, сделаем дыру в районе 4 000 000 миллионов.
Выполняем здесь https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=37d75a987970762218a55792eadc5ca3.
План здесь https://www.brentozar.com/pastetheplan/?id=Skf1w_zrE.

главное что бы в "набор случайных чисел" не сложилось одинаковых
14 фев 19, 10:02    [21809123]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
Заменить на dense_rank:
with t as (select *
           from (values (1), (2), (3), (4), (4), (5), (7), (9), (12), (13)) v(n)),
     a as (select n, dense_rank() over (order by n) rn from t)
select top(1) rn from a where n > rn order by n;
rn
6
14 фев 19, 10:28    [21809151]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
with t as (select *
           from (values (10), (2), (3), (4), (4), (6), (7), (9), (12), (13)) v(n)),
     a as (select n, dense_rank() over (order by n) rn from t)
select top(1) rn from a where n > rn order by n;
rn
1
14 фев 19, 10:28    [21809154]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
andrey odegov
Заменить на dense_rank:
with t as (select *
           from (values (1), (2), (3), (4), (4), (5), (7), (9), (12), (13)) v(n)),
     a as (select n, dense_rank() over (order by n) rn from t)
select top(1) rn from a where n > rn order by n;
rn
6

главное что бы в "набор случайных чисел" не попал 0, ну и сразу чтобы 2 раза не вставать отрицательные числа ещё
14 фев 19, 10:35    [21809161]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
Я вижу, ТЗ меняется на ходу :)
14 фев 19, 10:40    [21809169]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
andrey odegov
Я вижу, ТЗ меняется на ходу :)

ой всё?
автор
Есть таблица с номерами Таблица(int Number)

Она содержит набор случайных чисел,
14 фев 19, 10:41    [21809170]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
[quot TaPaK]
andrey odegov
Я вижу, ТЗ меняется на ходу :)

ой всё?
автор
Есть таблица с номерами Таблица(int Number)

Она содержит набор случайных чисел,
автор
, скажем 1,2,3,4,5,7,9,12,13...
Я не вижу чисел меньше 1.
14 фев 19, 10:45    [21809175]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
[quot andrey odegov]
TaPaK
пропущено...

ой всё?
пропущено...
пропущено...
Я не вижу чисел меньше 1.

пффф, действительно, решение простое

SELECT 6
14 фев 19, 10:46    [21809176]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Lessyp
Member

Откуда:
Сообщений: 139
andrey odegov
Проверим?
Возьмем 10 000 000 миллионов строк, сделаем дыру в районе 4 000 000 миллионов.
Выполняем здесь https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=37d75a987970762218a55792eadc5ca3.
План здесь https://www.brentozar.com/pastetheplan/?id=Skf1w_zrE.

ну скан-же в плане
14 фев 19, 12:53    [21809337]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
invm
Member

Откуда: Москва
Сообщений: 9125
Lessyp
ну скан-же в плане
И?
По-вашему это означает, что таблица будет гарантированно полностью прочитана?
14 фев 19, 12:59    [21809355]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
Lessyp
Member

Откуда:
Сообщений: 139
invm
Lessyp
ну скан-же в плане
И?
По-вашему это означает, что таблица будет гарантированно полностью прочитана?

зависит где дырка будет найдена, в данном плане считалось 4 миллиона в память
14 фев 19, 13:05    [21809366]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
invm
Member

Откуда: Москва
Сообщений: 9125
Lessyp,

Хотите сразу знать где "дырка" - ведите их учет в отдельной таблице.
14 фев 19, 13:22    [21809392]     Ответить | Цитировать Сообщить модератору
 Re: Получить ближайшее число отсутствующее в последовательности  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
TaPaK,
with
  t as (
    select *
    from (
      values (-10), (-2), (-3), (-4), (-5), (1), (2), (3), (4), (4), (5), (7), (9), (12), (13)
    ) v(n)
  )
select top(1)
  n + 1
from t
order by n - dense_rank() over (order by n), n desc;
14 фев 19, 16:38    [21809677]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить