SQL-запрос для поиска дыр между min_numbers и max_number в таблице
Быстрый вопрос для гуру SQL.
У меня есть таблица, которая содержит, помимо прочего, два столбца - min_number и max_number. Я безуспешно пытался написать запрос, который находит первую дыру размером n между минимальным и максимальным числами.
пример
min max
1. 100 200
2. 250 300
3. 330 400
Если я хочу найти отверстие размером 50, будет возвращено максимальное значение 200 строки 1 (между этим есть отверстие 50 и минимальное значение строки 2), отверстие 20 вернет максимальное число 300 строки 2 и т. Д. Если не подходит размер отверстия существовал, последний максимум (400) будет возвращен.
Спасибо
6 ответов
Отредактировано: окончательный ответ внизу.
Почему так много вопросов SQL забывают имя таблицы?
-- Buggy: should reference (lo.max + 1)
SELECT lo.max + 1 AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max - 1) >= 40 -- Example won't work with 50
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
Предложение NOT EXISTS имеет решающее значение - оно гарантирует, что вы рассматриваете только смежные диапазоны.
Это касается случая "достаточно большой разрыв".
Номинально вы можете справиться с "нет достаточно большого разрыва" с помощью предложения UNION:
...
UNION
SELECT MAX(max)+1
FROM example
WHERE NOT EXISTS(
SELECT lo.max + 1 AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max - 1) >= 40 -- Example won't work with 50
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
)
Внутренний SELECT является прямой транскрипцией первого, с отступом.
Вышеуказанный SQL не был протестирован. Первая часть работает (особенно на тестовых данных) - но может дать несколько ответов. Таким образом, это должно быть исправлено (исправление, я думаю, ошибки в два раза):
SELECT MIN(lo.max + 1) AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max + 1) >= 40 -- Example won't work with 50
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
Предложение UNION вызывает у меня некоторое горе... не дает ожидаемого ответа.
Синтаксически мне пришлось изменить это:
SELECT MIN(lo.max + 1) AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max + 1) >= 40 -- Example won't work with 50
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
UNION
SELECT MAX(solo.max)+1
FROM example AS solo
WHERE NOT EXISTS(
SELECT MIN(lo.max + 1) AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max - 1) >= 40 -- Example won't work with 50
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
)
Это позволяет обойти проблемы с ключевым словом MAX, используемым в качестве имени столбца (возможно, я мог бы написать example.max вместо solo.max, Но это не дает мне ответ, который я ожидаю.
UNION эквивалентен OR, конечно, в этом случае, и этот запрос, кажется, дает ответ, который я хочу:
SELECT MIN(lo.max + 1) AS min_range
FROM example lo, example hi
WHERE (hi.min - (lo.max + 1) >= 40
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
)
OR lo.max = (SELECT MAX(solo.max) FROM Example AS Solo)
;
Крайне важно, чтобы пункт OR приводил lo.max и не hi.max; в противном случае вы получите неправильный ответ.
ОК - версия UNION обречена, потому что SQL неправильно определяет поведение MIN. В частности, если нет подходящих строк, то MIN возвращает одну строку со значением NULL, а не возвращает никаких строк. Это означает, что первое предложение UNION возвращает NULL, если не найдено ни одной строки; второе предложение можно "исправить", пропустив MIN из SELECT внутри NOT EXISTS, но вы все равно получите две строки (NULL и правильное значение) из оператора, что на самом деле неприемлемо. Итак, используется версия OR - и SQL снова кусается со значениями NULL.
Строго избегать нулей можно, обрамляя UNION в табличном выражении в предложении FROM. Это в конечном итоге немного проще:
SELECT MIN(min_range)
FROM (SELECT (lo.max + 1) AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max + 1) >= 49
AND NOT EXISTS (SELECT * FROM example AS mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
UNION
SELECT MAX(solo.max + 1) AS min_range
FROM example AS solo
);
Первая половина UNION может вернуть любое количество слотов, включая ноль; вторая всегда возвращает значение (при условии, что в таблице есть какие-либо строки). Внешний запрос затем выбирает самое низкое из этих значений.
Эту версию, конечно, можно использовать для выделения строк:
INSERT INTO Example(min, max)
SELECT MIN(min_range) AS min, MIN(min_range) + (50 - 1) AS max
FROM (SELECT (lo.max + 1) AS min_range
FROM example lo, example hi
WHERE hi.min - (lo.max + 1) >= 50
AND NOT EXISTS (SELECT * FROM example mid
WHERE mid.min > lo.max
AND mid.max < hi.min
)
UNION
SELECT MAX(solo.max + 1) AS min_range
FROM example AS solo
);
SELECT
MIN(T1.max_value)
FROM
My_Table T1
LEFT OUTER JOIN My_Table T2 ON
T2.min_value BETWEEN (T1.max_value + 1) AND (T1.max_value + @range)
WHERE
T2.id IS NULL
Я предполагаю, что, так как вы ищете идентификаторы для назначения, вам нужен диапазон значений, полностью исключающий max_value и min_value.
Вы также можете выполнить вышеуказанный запрос с предложением NOT EXISTS. Попробуйте это с обоими и посмотрите, что работает лучше для вас.
Еще одна вещь, которую стоит рассмотреть, действительно ли вам нужно повторно использовать идентификаторы? Будут ли ваши значения идентификаторов настолько высокими, а диапазон - настолько низким, что вам потребуется это сделать? Я не знаю специфики вашей системы, но кажется, что вы потратили много усилий, а затем использовали много дополнительной обработки для решения проблемы, которой на самом деле не существует.
select min(n+1) from myTable where n+1 NOT IN (select n from myTable)
- Р Доэрти
Есть ли в MySQL модель предложения? Если да, вы можете сделать это с помощью запроса.
"Отверстие в 20 вернуло бы строку 2 с максимумом 300 и т. д." Я не следую вашей логике там - разрыв между максимумом строки 2 (300) и минимумом строки 3 (330) равен 30 (если вы включаете либо минимальное или максимальное значения, 29, если нет). Означает ли это, что вы ищете первый пробел "больше или равно" указанному значению, или этот пробел должен быть точным? Если оно "больше или равно", то, безусловно, первое возвращаемое совпадение будет строкой 1, в которой будет промежуток> 20 между ней и строкой 2?
Во всяком случае, если ваша таблица имеет идентификатор строки некоторого вида, как показывает пример, то вы можете попробовать что-то вроде этого (предположим, что таблица MyTable со столбцами RowID, MinVal и MaxVal, заполненная данными в вашем примере):
SELECT TOP 1
a.RowID,
a.MinVal,
a.MaxVal, -- this is the value you want to return
ISNULL(b.MinVal, 9999) AS MinVal_NextRow,
ISNULL(b.MinVal, 9999) - a.MaxVal AS Diff
FROM MyTable a
LEFT JOIN MyTable b ON a.RowID = ( b.RowID - 1 )
WHERE ( ISNULL(b.MinVal, 9999) - a.MaxVal ) = 20
В этом примере выбирается первая строка, где разрыв составляет ровно 20. Если вы искали первый разрыв не менее 20, вы можете изменить предложение WHERE на:
WHERE ( ISNULL(b.MinVal, 9999) - a.MaxVal ) >= 20
Запрос заменяет произвольно большое число (9999), когда строка является последней доступной строкой - это то, что возвращает последний (самый большой) MaxVal, если нет пропусков подходящего размера. Вам нужно будет отрегулировать это число так, чтобы оно имело смысл для ваших данных (то есть больше, чем любые возможные значения в данных).
Лично я бы не пытался сделать это в SQL - AIUI сложно выполнить анализ по разным строкам без необходимости эффективно сканировать таблицу в O(n^2) в худшем случае. Хотя, возможно, было бы проще использовать хранимую процедуру.
Моим решением, если вы сможете, было бы изменить схему и код вашей базы данных таким образом, чтобы при каждом добавлении новой строки предыдущая строка обновлялась с разницей между максимальным значением этой строки и минимальным значением новой строки, с этим значением разницы, хранящимся в своем собственном столбце.
Поиск первой строки, в которой имеется достаточно большой зазор, становится относительно тривиальным.