Как найти минимальное свободное значение в диапазоне?

Question

[Us59]

Заданное число: 10

В столбце number записаны занятые значения:

10.000
10.001
10.002
10.003
10.005

Как узнать, сколько раз нужно к заданному числу прибавить по 0.001, чтобы найти number которого еще нет в таблице? Как составить SQL запрос?

Comments

[Дмитрий]

Max, если они по порядку идут

[Сергей Соловьев]

Если надо найти минимальное подходящее, то надо отсортировать и идти по порядку. Если просто любое, то Дмитрий уже дал ответ.

Здесь проблема еще будет с дробными числами, т.к. не факт, что после добавления 0.001 значение увеличится ровно на 0.001

[Us59]

Сергей Соловьев, нужно минимальное, если брать из примера, то 0.004 свободно, оно и должно быть результатом. А как решается проблема с дробными числами?

[Akina]

В столбце number записаны занятые значения

Тип данных поля какой?

[Дмитрий]

А версия мускул какая?

Answer 1

[Akina]

Предполагая, что тип данных поля - DECIMAL(xx, 3):

SELECT MIN(value) + 0.001
FROM test t1
WHERE NOT EXISTS (
  SELECT NULL
  FROM test t2
  WHERE t1.value = t2.value - 0.001
  );

https://dbfiddle.uk/9Yyi-pol

PS. Возвращаемое значение не может быть меньше минимального из уже имеющихся в таблице.

Comments

[Us59]

Akina, тип данных у меня decimal (8,8).

Ваш запрос работает, но выполняет не ту задачу, которую нужно :)

Я хочу проверять от Заданного числа. т.е. существует ли заданное значение в таблице, если да, то прибавлять к ЗАДАННОМУ значению 0.001 и проверять снова, чтобы понять, существует ли 10+0.001, если да, то прибавляем еще и ищем до тех пор, пока не найдем минимальный НЕ существующий number.

[Us59]

Akina, если объяснить еще проще, то: Я делаю select с заданным значением (10) и проверяю нашлось ли что-то в БД. Если нашлось, то я создал цикл на PHP в котором к заданному значению прибавляю 0.001 и делаю select снова, чтобы проверить, найдется ли что-то теперь и так цикл выполняется до тех пор, пока не найдет число, которого не существует в таблице. Однако делать такой цикл мне кажется не очень разумно, поэтому хочу реализовать поиск напрямую через SQL, но пока что не знаю как.

[Akina]

Us59, ну добавьте в условия отбора ещё и условие, что value >= 10, какие сложности-то?

выполняет не ту задачу, которую нужно

Вы однозначно можете получить только запрос для решения той задачи, которую озвучили, причём так, как эту задачу удалось понять. Если постановка задачи так себе, то и ответ может не подходить...

тип данных у меня decimal (8,8)

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

если да, то прибавляем еще и ищем до тех пор, пока

Итерации - это гарантированно медленно.

[Us59]

ну добавьте в условия отбора ещё и условие, что value >= 10, какие сложности-то?

Сложность как я вижу в том, что мы задаем в выборке найти значение из БД, а в задаче требуется не искать значение из таблицы, а узнать, сколько раз нужно к заданному числу нужно прибавить по 0.001, чтобы найти number которого еще нет в таблице.

По поводу decimal - 25,8 на практике в базе.

[Akina]

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

Слушайте, но это уже вообще арифметика. Ну или с учётом того, что нецелые числа - математика, класс, наверное, пятый...

Answer 2

[alexalexes]

WITH RECURSIVE
-- Тестовая выборка. Если есть "живая" таблица, то эту выборку можно убрать  
 t as (select 10.000 value union
           select 10.001 union
           select 10.002 union
           select 10.003 union
           select 10.005 union
           select 10.009 -- специально добавил контр. пример
          ),
-- Таблица-генератор последовательности чисел от min(t.value) до max(t.value) с шагом 0.001 с помощью CTE (есть ограничение по глубине рекурсии!)
num_series AS (
  SELECT (select min(value) from t)  AS num
  UNION ALL
  SELECT num + 0.001 FROM num_series
   WHERE num < (select max(value) from t)
)
-- Запрос, в котором сопоставляем таблицу генератор и исходную таблицу, чтобы найти недостающие кванты
SELECT * 
  FROM num_series
 where not exists(select t.value from t where t.value = num_series.num)
     and num_series.num >= 10.000

PS: Нужен MySQL 8 версии и выше, postgres, или СУБД из интерпрайзного сегмента.

Comments

[Us59]

alexalexes Судя по тому, что я не вижу где вы используете заданное число 10 полагаю как и автор поста выше ваш запрос выполняет другую задачу. Мне нужно искать ближайшее минимальное число от заданного числа 10 прибавляя по 0.001

[alexalexes]

Заданное число: 10

В столбце number записаны занятые значения:
10.000
10.001
10.002
10.003
10.005

Ну, тогда вопрос, какую выборку вы ходите получить от запроса при таких условиях?

[Us59]

alexalexes, Хочу найти число, прибавив которое к заданному мы получим свободное значение в БД (не существующее ближайшее число, которое мы можем записать как новое)

[Us59]

alexalexes, если объяснить еще проще, то: Я делаю select с заданным значением (10) и проверяю нашлось ли что-то в БД. Если нашлось, то я создал цикл на PHP в котором к заданному значению прибавляю 0.001 и делаю select снова, чтобы проверить, найдется ли что-то теперь и так цикл выполняется до тех пор, пока не найдет число, которого не существует в таблице. Однако делать такой цикл мне кажется не очень разумно, поэтому хочу реализовать поиск напрямую через SQL, но пока что не знаю как.

[alexalexes]

Переделал запрос.
Как насчет того, чтобы в исходные данные добавить 10.009?
Получится вот такая выходная выборка:

+--------+
| num    |
+--------+
| 10.004 |
| 10.006 |
| 10.007 |
| 10.008 |
+--------+

[Us59]

alexalexes, спасибо за ваши ответы и время, однако это не то, что нужно, судя по всему, простым sql запросом мою задачу не решить, поэтому наверное лучше оставить как я сделал через PHP цикл: Я делаю select с заданным значением number (10) и проверяю нашлось ли что-то в БД с таким же number. Если нашлось, то я создал цикл на PHP в котором к заданному значению (10) прибавляю 0.001 и делаю select снова, чтобы проверить, найдется ли что-то теперь, и так цикл выполняется до тех пор, пока не найдет число, которого не существует в таблице.

[Akina]

судя по всему, простым sql запросом мою задачу не решить

Ну не говорите ерунды-то... если вы до сих пор не освоили способ мышления СУБД (обработка массива, а не итерация по нему) и не знаете SQL, то это не основание говорить, что задача ТАК не решается. Решается, и гораздо проще, чем внешним языком.
Но вот ПОСТАНОВКА задачи и ФОРМУЛИРОВАНИЕ её условия - с этим у вас и есть основная проблема. Когда вы наконец выложите нормальное и полностью формализованное описание, тогда и будет решение. Раньше - разве что случайно кто-то попадёт пальцем в небо.

[Us59]

Akina, задача-то простая, просто каждый понимает её по-своему, действительно сформулировать вопрос для меня это проблема, однако я рассказал как я делал на PHP и должно быть понятным.

Вот для примера код на PHP:

$isUnique = false;
$number = 10;
$i = 0;
while ($i < 1000 && !$isUnique) {
	$number = $number + $i;
	$countRows = mysqli_num_rows(dquery("SELECT id from table where number='" . $number . "'"));
	if ($countRows === 0) {
		echo $number;
		$isUnique = true;
	}
	$i++;
}

Вопрос: Как сделать тоже самое только через sql запрос? Как вы видите мы через цикл добавляем к 10+1 и ищем первое отсутствующее в БД значение, это и будет нашим результатом. Может так понятнее задача)?

[alexalexes]

Какого типа данных поле table.number в базе?
Если вы задали тип строка или любой символьный тип, то строка '10.000' не равна строке '10' на первой итерации, и ваш код вернет 10.
Если вы задали какой-нибудь тип для хранения чисел, то число 10.000 равно числу 10 при неявной конвертации строки '10' (если еще не попадете на ошибку округления, если тип будет с плавающей точкой), и ваш код вернет 11.
В общем, нужно полный дамп как инструкции create table так и insert-ы таблицы, откуда извлекаете данные для работы этого клочка кода.

[Us59]

alexalexes, тип decimal(25,8)

[Us59]

alexalexes,

CREATE TABLE mytable (
    number DECIMAL(25, 8)
);

INSERT INTO mytable (number) VALUES
(10.00100000),
(10.00200000),
(10.00500000),
(15.00000000);