SQL-запрос

Maddoc · 05-11-10 11:15:27

Дано: таблица. Количество полей не важно, важно то, что в ней есть одно поле (пусть будет id, integer). Оно же ключевое, без автоприращения.
Необходимо: SQL-запрос, который находит первое пустое значение.
Пример: в поле содержатся значения 2, 3, 4, 7. Запрос должен выдать 1. В поле содержатся значения 1, 2, 3, 4, 7. Запрос должен выдать 5.

Tiphon · 05-11-10 18:21:46

Ты еще SQL сервер забыл сказал, тк.к. SQL-и заметно различаются.

Maddoc · 05-11-10 22:23:54

Tiphon пишет:

Ты еще SQL сервер забыл сказал, тк.к. SQL-и заметно различаются.

MSD SQL Server 2008, хотя это и неважно :-) Хотелось посмотреть различные варианты.

Tiphon · 05-11-10 23:30:48

Maddoc пишет:

хотя это и неважно :-)

важно, и синтаксис запросов и, особенно, написание сторед процедур различаются.

Различные варианты поставленной задачи для MS SQL:
stackoverflow.com/questions/684106/find … -in-ms-sql

Хотелось бы обратить внимание на один из комментов:

However, the policy you're describing is really not a good idea. ID values should be unique, but should not be required to be consecutive.
What happens if you email someone with a link to document #42, and then subsequently delete the document? Later, you re-use the id #42 for a new document. Now the recipient of the email will follow the link to the wrong document!

Я сильно поддерживаю того, кто это написал. Хотя, может, именно такое поведение тебе и нужно. Фиг знает.

Maddoc · 06-11-10 00:38:43

Tiphon пишет:

Хотя, может, именно такое поведение тебе и нужно. Фиг знает.

Мне было интересно, представит ли кто-нибудь 1 (один) запрос хоть под какой-нибудь сервер. Супер-мега-кул-программеров с миллиоами пользователей их творений на форуме хватает, а на простой вопрос ответа нету :-(

Tiphon · 06-11-10 00:51:55

Maddoc пишет:

Мне было интересно, представит ли кто-нибудь 1

Мой ответ считается или нет?)))

Maddoc · 06-11-10 01:03:13

Tiphon пишет:

Мой ответ считается или нет?)))

Что-то в ответе я не вижу даже SELECT'a :-)

Tiphon · 06-11-10 01:15:54

Maddoc пишет:

Что-то в ответе я не вижу даже SELECT'a :-)

Там их 16.

Maddoc · 06-11-10 01:30:42

Tiphon пишет:

Там их 16.

Именно что там. В MSDN их ещё больше. :-)

Maddoc пишет:

Необходимо: SQL-запрос, который находит первое пустое значение.

И, кстати, как отреагируют те запросы при

Maddoc пишет:

в поле содержатся значения 2, 3, 4, 7. Запрос должен выдать 1

А если 2, 7, 3, 4? Или 7, 2, 3,4.
Кстати, условие написал не совсем верно. Не первое, а минимальное значение.

Отредактировано Maddoc (06-11-10 01:34:28)

Tiphon · 06-11-10 01:46:31

Maddoc пишет:

Именно что там. В MSDN их ещё больше. :-)

Ну тогда это глупо, как и что ты написал тут. Сам поймешь или объяснить?

Maddoc пишет:

А если 2, 7, 3, 4? Или 7, 2, 3,4.

Первый же вариант решения с MS SQL подходит и под этот случай

Maddoc пишет:

Не первое, а минимальное значение.

Это тебе задачку в институт что ле решить надо?
А то с каждой допиской она становится более и более бредовой.

Добавлено спустя 8 ч 21 мин 01 с:
Maddoc, см лс

MOP3E · 06-11-10 10:55:38

Maddoc пишет:

А если 2, 7, 3, 4? Или 7, 2, 3,4.

Ну, сделай сначала SELECT по таблице с сортировкой по этому полю. А потом уже обрабатывай результат. Упорядоченный.

Вообще, могу подкинуть идею (сработает в том случае, если числа в таблице - натуральные целые):

1. Делаем по таблице запрос с сортировкой по возрастанию по полю ID.
2. В результате предыдущего запроса делаем нумерацию строк, и оставляем только те строки, в которых номер строки не совпадает с ID - первая из этих строк будет ПОСЛЕ срыва нумерации поля ID.
3. В результате предыдущего запроса определяем строку с наименьшим ID и по нему обнаруживаем предшествовавший ему ID.
4. Увеличиваем найденный в пункте 3 ID на 1.

Пункты 3 и 4 нужны в том случае, если разрыв в нумерации ID может быть больше, чем на 1. Если точно известно, что пропущен может быть только один ID, достаточно просто вычесть единицу из ID, найденного в пункте 2.

wr224 · 06-11-10 12:54:17

Maddoc пишет:

Необходимо: SQL-запрос, который находит первое пустое значение.

создал табличку:

create table testid (id integer);
insert into testid values (1);
insert into testid values (2);
insert into testid values (3);
insert into testid values (4);
insert into testid values (7);

а вот собственно и сам запрос:

select rnum as "пропущенное зн-е" from (select rownum as rnum, id from testid) where rnum != id;

результат:

пропущенное зн-е        
---------------------- 
5

Добавлено спустя 04 мин 02 с:

Maddoc пишет:

А если 2, 7, 3, 4? Или 7, 2, 3,4.

тогда нужно в конце запроса добавить: order by id

Добавлено спустя 12 мин 55 с:

MOP3E пишет:

Ты решил только половину задачи. Этот запрос, во-первых, будет корректно работать только с упорядоченной по возрастанию таблицей, во-вторых, выдаст все поля, начиная с id=7. Тебе же нужно в результате получить число 5.

МОРЗЕ, упорядочить можно через order by id прямо в запросе. Я его и получил, см пост выше, я отредактировал

Вот смотри что с неупорядоченной таблицей со значениями 7,2,3,4:
запрос:

select rnum as "пропущенное зн-е" from (select rownum as rnum, id from testid order by id) where rnum != id;

результат:

пропущенное зн-е        
---------------------- 
1

Отредактировано wr224 (06-11-10 13:12:31)

Tiphon · 06-11-10 14:07:11

select rnum as "пропущенное зн-е" from (select rownum as rnum, id from testid order by id) where rnum != id;

Не вернет 4, если у тебя ид будут 1 2 3 тк.к. всегда ровнам будет равен ид. Как я понял в задаче надо, чтобы в таком случае возвращалось 4.

В принципе мы еще один вариант подробно разобрали в ЛС. Если мэдок одобрит, сюда выложу.

wr224 · 06-11-10 14:31:27

Tiphon пишет:

select rnum as "пропущенное зн-е" from (select rownum as rnum, id from testid order by id) where rnum != id;Не вернет 4, если у тебя ид будут 1 2 3 тк.к

В задаче говорилось про пропущенные значения конечного ряда чисел, если я правильно ее понял, те последний член ряда определен

Отредактировано wr224 (06-11-10 14:34:14)

Tiphon · 06-11-10 14:36:32

первое пустое значение ... Кстати, условие написал не совсем верно. Не первое, а минимальное значение.

если индекс больше нуля и записаны 1 2 3 - следующее пустое значение будет 4. Ну и в личке мне мэддок чуть больше объяснил зачем это. Как я понял должно быть 4.

msAVA · 06-11-10 15:19:02

MOP3E пишет:

натуральные целые

Позор! N in Z!, Т.е. натуральное число всегда целое, но не наоборот.
Может вы хотели что-то вроде "целое без знака" иль какой другой программерский изврат?

Отредактировано msAVA (06-11-10 15:20:21)

Tiphon · 06-11-10 15:30:42

Я поздравляю тебя с тем, что ты знаешь из математики N in Z! Дальнейшее развитие офтопа и срача в этой теме повлечет предупреждение.

wr224 · 06-11-10 15:55:12

Tiphon пишет:

если индекс больше нуля и записаны 1 2 3 - следующее пустое значение будет 4. Ну и в личке мне мэддок чуть больше объяснил зачем это. Как я понял должно быть 4.

Ну тогда нужно писать обработчик событий, в каждой базе данных он будет свой, например в оракле скрипт будет выглядить примерно так:

set serveroutput on;
declare
a testid.id%type;
begin
select rnum into a from (select rownum rnum, id from testid) where rnum != id;
dbms_output.put_line('пропущенное значение = ' || a);
exception
when no_data_found then
begin
select max(id)+1 into a from testid;
dbms_output.put_line('пропущенное значение = ' || a);
end;
end;
/

Тогда вернет 4

Отредактировано wr224 (06-11-10 15:59:46)

DonDublon3 · 08-11-10 14:44:25

чесгря, удивительно, что так никто и не придумал такой запрос smile
а я придумал, хотя спецом по sql себя совершенно не считаю.

вот:
SELECT MIN(maxprev+1) FROM
(
SELECT mainid, MAX(previd) as maxprev, mainid-maxprev as diff FROM
(SELECT table1.id as mainid , table2.id as previd
FROM Table1, table1 as table2)
WHERE mainid > previd GROUP BY mainid
)
WHERE diff > 1

за быстродействие и вообще оптимальность, впрочем, не поручусь.

Tiphon · 08-11-10 18:02:00

DonDublon3 пишет:

за быстродействие и вообще оптимальность, впрочем, не поручусь.

А если цифры 5 6 7, то какое значение запрос выдаст?
В ссылке, которая приведена во втором сообщении, первый запрос как раз, по сути, этот. Только скан делает по i+1

DonDublon3 пишет:

чесгря, удивительно, что так никто и не придумал такой запрос

Tiphon пишет:

06-11-10 14:07:11
В принципе мы еще один вариант подробно разобрали в ЛС. Если мэдок одобрит, сюда выложу.

Просто мэдок бухает, поэтому не опубликовано.

DonDublon3 · 08-11-10 21:03:57

Tiphon пишет:

А если цифры 5 6 7, то какое значение запрос выдаст?

ыыыы. это не учитывается, да.

Tiphon · 08-11-10 22:10:34

Ну, вобщем, раз меддок бухает, то выложу что ле.

Решение без дурацких запросов: Дефрагментация индексов.
msdn.microsoft.com/en-us/library/ff650692.aspx

Решение с дурацким запросом:
Идея в том же поиске по ид+1. Фишка в чем, поиск с ид+1 катит, но не учитывает первые пустые поля (если числа. например 567). Для этого можно делать одну проверку на занятость ячейки с с ид=1. Если такая ячейка не занята, то сначала надо занять ее (минимальный, да). Если занята от нее уже само отсчитывается.

SELECT  TOP (1) CASE 
  WHEN (NOT EXISTS (SELECT id FROM  test AS t2 WHERE (t2.id = 1))) THEN 1 
  WHEN (NOT EXISTS (SELECT id FROM  test AS t2 WHERE (id = t1.id + 1))) THEN id + 1 
  END AS 'freeId'
FROM test AS t1
ORDER BY id

С точки зрения быстродействия, если есть ордер бай, МС БД может в любом случае перебирать таблицу от начала до конца (до поиска дырок). Чтобы этого избежать можно использовать кластеред индексы. Тогда ордер бай идет по ним и, по идее, перебор будет идти только до дырки (топ(1) остаовит).
technet.microsoft.com/en-us/library/ms190639.aspx
Если верить описанию оптимайзера МС ДБ, NOT EXISTS (SELECT id FROM test AS t2 WHERE (t2.id = 1)) будет проверена 1 раз.
Таким образом первый кейс проверяет только первый же индекс. Второй кейс перебирает до первой дырки. Поэтому это, наверное, оптимально с точки зрения быстродействия. Но это в теории. На практике - я не специалист по МС БД. Профайлер в руки и смотреть))
Можно эту логику - проверить 1-й, искать с ид+1 реализовать просто с помощью IF, ELSE в запросе и не надеяться на правильную оптимизацию. Можно записать в виде 2-х запросов с юнион. 1-й из которых возвращает 1 или нуль. Будет ли быстрее работать на практике? - без понятия, не специалист.
Решение с ров набмером:
ROW_NUMBER() не возвращает 4, если у тебя числа 1 2 3. Значит можно добавлять такой же(как в прошлом примере) проверочный запрос, только проверять последнее поле, а отсчитывать последнее поле по логике больнее, чем первое. К тому же МС СКЛ ROW_NUMBER() идет со своим сорт бай и, возможно, будет перелопачивать таблицу даже с кластеред индексами
Ссылка на проблему медленного ROW_NUMBER
weblogs.asp.net/eporter/archive/2006/10 … t-Set.aspx

Более дурацкие решения, которые рождались в процессе:

SELECT   TOP (1) id - 1 AS freeId FROM test AS t1
WHERE    (NOT EXISTS
                  (SELECT   id
                   FROM      test AS t2
                   WHERE    (id = t1.id - 1))) AND (id - 1 > 0)
UNION
SELECT   id + 1 AS freeId FROM test AS t1
WHERE   NOT EXISTS
                  (SELECT   id
                   FROM      test AS t2
                   WHERE    (id = t1.id + 1))
ORDER BY freeId

Например, если у тебя числа 5 6 8, этим запросом ты получишь 4 7 9. Т.е. если ты будешь брать первое значение этого запроса, ты будешь сначала заселять пустые места "к началу", а только потом все остальные. Будет ли такой запрос хорошо оптимизироваться по TOP(1) - не имею понятия, но предполагаю, что первое решение - лучше)))

Более громоздкие решения
Можно делать карты дырок, например, но к чему этот гемор?
Прицепить триггеры на инсерт и делет, которые будут вписывать и убирать "карту".

Форум StopLinux

Объявление

#1 05-11-10 11:15:27

SQL-запрос

#2 05-11-10 18:21:46

Re: SQL-запрос

#3 05-11-10 22:23:54

Re: SQL-запрос

#4 05-11-10 23:30:48

Re: SQL-запрос

#5 06-11-10 00:38:43

Re: SQL-запрос

#6 06-11-10 00:51:55

Re: SQL-запрос

#7 06-11-10 01:03:13

Re: SQL-запрос

#8 06-11-10 01:15:54

Re: SQL-запрос

#9 06-11-10 01:30:42

Re: SQL-запрос

#10 06-11-10 01:46:31

Re: SQL-запрос

#11 06-11-10 10:55:38

Re: SQL-запрос

#12 06-11-10 12:54:17

Re: SQL-запрос

#13 06-11-10 14:07:11

Re: SQL-запрос

#14 06-11-10 14:31:27

Re: SQL-запрос

#15 06-11-10 14:36:32

Re: SQL-запрос

#16 06-11-10 15:19:02

Re: SQL-запрос

#17 06-11-10 15:30:42

Re: SQL-запрос

#18 06-11-10 15:55:12

Re: SQL-запрос

#19 08-11-10 14:44:25

Re: SQL-запрос

#20 08-11-10 18:02:00

Re: SQL-запрос

#21 08-11-10 21:03:57

Re: SQL-запрос

#22 08-11-10 22:10:34

Re: SQL-запрос

Подвал форума