Гость
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Определить минимальное отсутствующее значение в таблице / 18 сообщений из 18, страница 1 из 1
02.03.2020, 19:30
    #39933184
vdix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Есть таблица с семизначными штрих-кодами, все они начинаются на 13ххххх. Проблема в том, что они не последовательны (а даже очень хаотичны) и есть коды типа 1300002, 1399999, что не позволяет использовать min()/max() этого поля, чтобы просто последовательно выбирать новое значение для штрих-кода. Ещё более неприятная штука, что в системе используется интерпретатор SQL, который не позволяет использовать конструкции типа with, for xml path, а также процедуры, к тому же этот интерпретатор разбивает скрипт на одиночные запросы, так что временные переменные и таблицы тоже не будут работать.
Пока что единственный вариант, который пришёл мне в голову - это создать временную таблицу "foo" со значениями от 0 до 99999 и джоинить на неё таблицу штрихкодов:
Код: sql
1.
2.
3.
4.
5.
select min(foo.number + 1300000) as Barcode7
from
  foo
  left outer join (select barcode from Items where barcode like '13%') A on CAST((1300000 + foo.number) AS VARCHAR(7))=A.barcode
where isnull(A.barcode, '') = ''


Ребят, может есть какое-то более нормальное решение, не требующее таблицу с нумераторами? Просто я не представляю себе, как сделать "select ROW_NUMBER() over таблица_с_полямы_которых_нет"
...
Рейтинг: 0 / 0
02.03.2020, 19:59
    #39933191
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Код: sql
1.
2.
3.
4.
5.
6.
select
 min(t.barcode)
from
 (select barcode, lead(barcode) over (order by barcode) from Items where barcode like '13%') t(barcode, barcode_next)
where
 cast(t.barcode_next as int) > cast(t.barcode as int) + 1;
...
Рейтинг: 0 / 0
02.03.2020, 20:30
    #39933197
vdix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
invm, спасибо, но это не совсем то. Ваш код возвращает существующий в таблице Items штрих-код, проверил через менеджмент студию, нужен же наоборот - певый из не существующих. Ещё одна проблема - каличный интерпретатор отругался на функцию lead(), похоже он её не понимает (что странно, row_number() он позволяет).
...
Рейтинг: 0 / 0
02.03.2020, 20:34
    #39933198
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
vdix
нужен же наоборот - певый из не существующих.
Прибавьте единицу и будет первый несуществующий.
...
Рейтинг: 0 / 0
03.03.2020, 08:41
    #39933290
mnbvcx
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
vdix
... Ещё одна проблема - каличный интерпретатор отругался на функцию lead(), похоже он её не понимает (что странно, row_number() он позволяет).

lead & lag появились в версии 2012, а row_number() - с ms sql 2005.
...
Рейтинг: 0 / 0
03.03.2020, 09:45
    #39933315
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
vdix
Ещё одна проблема - каличный интерпретатор отругался на функцию lead(), похоже он её не понимает (что странно, row_number() он позволяет).
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select
 min(c.barcode) + 1
from
 Items a outer apply
 (select top (1) barcode from Items where barcode like '13%' and barcode > a.barcode order by barcode) b(barcode_next) cross apply
 (select cast(a.barcode as int), cast(b.barcode_next as int)) c(barcode, barcode_next);
where
 a.barcode like '13%' and
 (c.barcode_next is null or c.barcode_next > c.barcode + 1);
...
Рейтинг: 0 / 0
03.03.2020, 10:03
    #39933324
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Код: sql
1.
2.
3.
4.
5.
SELECT TOP (1) barcode + 1
FROM ( SELECT barcode, barcode - ROW_NUMBER() OVER (ORDER BY barcode) grp
        FROM items
        WHERE barcode BETWEEN 1300000 AND 1399999 ) x
ORDER BY grp, barcode DESC


Одна проблема - запрос ищет именно дыру. Если отсутствует одно или несколько значений в начале диапазона - запрос не вернёт 1300000... а UNION-ить к таблице запись с 1299999 как-то некрасиво.
...
Рейтинг: 0 / 0
03.03.2020, 15:09
    #39933572
Wlr-l
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
vdix,

К "проблеме - калечный интерпретатор" можно добавить проблему калечной постановки задачи.

Я бы еще добавил условие «сначала найти самую длинную "дырку"». Вдруг потребуется ну очень большой диапазон штрих-кодов, а найденной "дырки" не хватит.

Сегодня штрих-коды выстроены в непрерывную последовательность. Через некоторое время освободившиеся штрих-коды снова дадут "дырки". Это вечная тяга к наведению порядка в множестве.


Хотя, достаточно просто использовать первый попавшийся свободный штрих-код.

Решение с опорной таблицей вполне приемлемо. Желательно, чтобы эта таблица была постоянной. Совсем не обязательно, чтобы там были числа. Запишите в нее строки от '1300000' до '1399999' и все преобразования станут лишними; лишний и isnull в секции where. Кроме left join есть еще exists. Для вывода одной строки используется top(1). Тем более, что используется «интерпретатор SQL, который не позволяет использовать конструкции типа ...».

Кстати, для внутренних потребностей штрих-код должен начинаться с 2-ки.
...
Рейтинг: 0 / 0
03.03.2020, 15:17
    #39933578
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Wlr-l,

Это сколько же там штрих кодов, что они не влезают в INT, а может и BIGINT уже не хватает? Все астероиды во вселенной решили промаркеровать что-ли?
...
Рейтинг: 0 / 0
03.03.2020, 15:29
    #39933589
Wlr-l
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
a_voronin
Wlr-l,

Это сколько же там штрих кодов, что они не влезают в INT, а может и BIGINT уже не хватает? Все астероиды во вселенной решили промаркеровать что-ли?


1399999 - 1300000 = 99999 вариантов
...
Рейтинг: 0 / 0
03.03.2020, 16:10
    #39933624
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Какой-то такой олдскул

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
declare @t table(k int primary key, v int)
insert into @t
values (1, -1), (2, 1), (3, 2), (4, 2), (5, 7), (6, 8), (7, 4), (8, 1), (9, 3), (10, 0), (11, 8), (12, 9), (13, 10), (14, 11);

declare @start_i int = 0;
declare @end_i int = 9;

select case when d.first_v > @start_i then @start_i else d.bounded_v + 1 end as available_v
  from (select min(c.this_v) as first_v, max(c.this_v) as last_v,
               min(case when c.next_v - c.this_v > 1 then c.this_v
                        when c.next_v is null        then c.this_v end) as bounded_v
          from (select max(case when e.x  = (b.r % 2) then b.r end) as this_r,
                       max(case when e.x  = (b.r % 2) then b.v end) as this_v,
                       max(case when e.x != (b.r % 2) then b.v end) as next_v
                  from (select a.v, a.k, row_number() over(order by a.v) as r
                          from @t as a
                         where a.v between @start_i and @end_i) as b
                         cross join (select 0 as x union all select 1 as x) as e
                  group by b.r + case when e.x = (b.r % 2) then 1 else 0 end) as c) as d
 where not(d.first_v = @start_i and d.bounded_v = @end_i)


Или вместо скаляров, так сделать? Что автор ожидает, если начало интервала совпадает с окончанием интервала выбора минимала?
Код: sql
1.
2.
3.
declare @boundz table(id tinyint primary key, start_i int, end_i int, check(id = 0 and start_i < end_i))
insert into @boundz 
values (0, 0, 9);
...
Рейтинг: 0 / 0
03.03.2020, 16:13
    #39933630
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Wlr-l
vdix, К "проблеме - калечный интерпретатор" можно добавить проблему калечной постановки задачи.
ППКС, запарился с граничными условиями
...
Рейтинг: 0 / 0
03.03.2020, 17:13
    #39933668
Wlr-l
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Как все сложно, хотя эта именно эта задача имеет простое решение:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with foo as (
  select *
   from (values ('1300000'),('1300001'),('1300002'),('1300003'),('1300004') 
               ,('1300005'),('1300006'),('1300007'),('1300008'),('1300009')   -- 1
         ) as T(barcode)
)
, items as (
  select *
    from (values ('1300006'),('1300009'),('1300000'),('1300004'),('1300001') ) as T(barcode)  -- 1
--    from (values ('1300006'),('1300009'),('1300002'),('1300004'),('1300001') ) as T(barcode)  -- 2
--    from (values ('1300004'),('1300002'),('1300000'),('1300003'),('1300001') ) as T(barcode)  -- 3
)

select top(1) f.barcode as barcode7
  from        foo f
  left join items i on f.barcode=i.barcode
 where i.barcode is null;



Если запустить скрипт как есть, то получим 1300002. Штрих код из середины диапазона.

Закоментируем строку 1 и раскоментируем строку 2 запроса items. Получим 1300000. Штрих код, совпадающий с началом диапазона.

Закоментируем строку 1 запроса foo; закоментируем строку 2 и раскоментируем строку 3 запроса items. Получим пустую строку, т.е. все штрих-коды уже используются.

Если очень хочется определенности, то к top можно добавить order by.
...
Рейтинг: 0 / 0
03.03.2020, 18:01
    #39933687
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Wlr-l, в том-то и дело, что так потребуется сгенерировать таблицу на 100к записей на лету, т.к. судя по сообщениям ТС хранить такую таблицу возможности нет. Выше привели лучший вариант с lead, но, т.к. данную функцию интерпретатор не поддерживает, остались варианты с top и cross apply, и ещё один вариант, более сложный для понимания, но не требующий подзапросов и самосоединений привел я.
...
Рейтинг: 0 / 0
03.03.2020, 18:15
    #39933689
vdix
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Всем огромное спасибо за помощь!
Akina, Ваше решение подходит идеально, благодарю :)
...
Рейтинг: 0 / 0
03.03.2020, 18:23
    #39933693
Alexander Us
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
nullin,

если ваш интерпретатор разрешает табличные функции, допилите фунукию для генерирования int/bigint последовательностей от до:

быстрая тут, от invm dbo.fnSequentialNumbers:
Повторить строку N раз
...
Рейтинг: 0 / 0
03.03.2020, 18:26
    #39933695
Wlr-l
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
nullin
Wlr-l, в том-то и дело, что так потребуется сгенерировать таблицу на 100к записей на лету, т.к. судя по сообщениям ТС хранить такую таблицу возможности нет. Выше привели лучший вариант с lead, но, т.к. данную функцию интерпретатор не поддерживает, остались варианты с top и cross apply, и ещё один вариант, более сложный для понимания, но не требующий подзапросов и самосоединений привел я.


1.ТС нашел решение: "единственный вариант, который пришёл мне в голову - это создать временную таблицу "foo" со значениями от 0 до 99999". Следовательно, опорную таблицу можно хранить и его интерпретатор справляется с его запросом.

2.Опорную таблицу не обязательно хранить в этой же БД, можно хранить в соседней, которая используется только для чтения.

3.Я всего лишь очистил решение ТС от хлама.

4.У Вас странное понимание подзапросов и самосоединений . Посмотрите внимательно на свой запрос и попробуйте понять, что он требует и чего не требует.
...
Рейтинг: 0 / 0
03.03.2020, 19:09
    #39933704
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить минимальное отсутствующее значение в таблице
Wlr-l, мой запрос, как я и предполагал, более сложный для понимания первое что делает, так это под капотом осуществляет функцию lead над множеством без самосоединений.
Дальше когда пошли толкования, что-же хотел ТС, после того, как ему не пошло min(t.barcode) по where cast(t.barcode_next as int) > cast(t.barcode as int) + 1, пришлось усложнить, и, добавлю, declare в моем запросе тоже не нужны, это скорее для тестов - ТС свои коды от 1.3кк вообще хардкодит и вообще даже не понятно, в чем они хранятся

Alexander Us
nullin, если ваш интерпретатор разрешает табличные функции
это интерпретатор ТСа, я его не знаю
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Определить минимальное отсутствующее значение в таблице / 18 сообщений из 18, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]