У меня есть довольно большой набор номеров телефона (приблизительно 2 миллиона) в таблице базы данных. Эти числа были вставлены в блоки, таким образом, существует много непрерывных диапазонов чисел, чего-либо от 10 чисел до 10 тысяч в диапазоне. Некоторые из этих чисел используются и поэтому отмечены как недоступные, остальные доступны. Учитывая конкретное число мне нужен способ найти непрерывные диапазоны чисел, и выше и ниже того числа. Диапазон должен продолжиться, пока он не находит недоступное число или встречается с границей двух диапазонов.
Например, учитывая следующий набор:
1000
1001
1002
1010
1011
1012
1013
1020
1021
1022
При выполнении поиска с помощью 1 012, поскольку параметр должен возвратиться 1010, 1011, 1012, 1013.
Что такое хороший способ сформировать запрос для нахождения этих диапазонов? Мы используем NHibernate сверху SQL-сервера, решение с помощью любого прекрасно.
Теоретически элементы в наборе не имеют определенного значения, поэтому я предполагаю, что у вас также есть некоторый непрерывный столбец идентификатора, который определяет порядок чисел. Примерно так:
ID Number
1 1000
2 1001
3 1002
4 1010
5 1011
6 1012
7 1013
8 1020
9 1021
10 1022
Вы можете создать дополнительный столбец, содержащий результат Number - ID
:
ID Number Diff
1 1000 999
2 1001 999
3 1002 999
4 1010 1006
5 1011 1006
6 1012 1006
7 1013 1006
8 1020 1012
9 1021 1012
10 1022 1012
Числа в том же диапазоне будут иметь тот же результат в столбце Diff.
SQL не может сделать это в одном запросе (разве что есть встроенные улучшения SQL, о которых я не знаю), потому что SQL не может получить доступ к строке "до" или "после".
Вам нужно пройти через последовательность в цикле.
Вы можете попробовать NHibernates Enumerable
, который не загружает сущности в память, а только создает их прокси. На самом деле я не думаю, что это хорошая идея, потому что это создаст прокси для всех 2 миллионов чисел.
План Б - использовать подкачку. Приблизительно это выглядит так:
List<PhoneNumber> result = new List<PhoneNumber>();
int input = 1012;
int pageSize = 100;
int currentPage = 0;
int expectedNumber = input;
bool carryOn = true;
while(carryOn)
{
var numbers = session
.CreateQuery("from PhoneNumber pn where pn.Number > :input")
.SetInt("input", input)
.SetFirstResult(currentPage * pageSize)
.SetMaxResult(pageSize)
.List<PhoneNumbers>();
foreach(var number in numbers)
{
expectNumber++;
if (number.Number != expectedNumber)
{
carryOn = false;
break;
}
result.Add(number);
}
currentPage++;
}
И то же самое для диапазона до этого в другом направлении.
Используйте вспомогательную таблицу всех возможных последовательных значений или материализуйте одну в CTE, например
WITH
-- materialize a table of sequential integers
l0 AS (SELECT 0 AS c UNION ALL SELECT 0),
l1 AS (SELECT 0 AS c FROM l0 AS a, l0 AS b),
l2 AS (SELECT 0 AS c FROM l1 AS a, l1 AS b),
l3 AS (SELECT 0 AS c FROM l2 AS a, l2 AS b),
l4 AS (SELECT 0 AS c FROM l2 AS a, l3 AS b),
l5 AS (SELECT 0 AS c FROM l2 AS a, l4 AS b),
nums AS (SELECT row_number() OVER(ORDER BY c) AS n FROM l5),
-- materialize sample table
MyTable (ID) AS
(
SELECT 1000
UNION ALL
SELECT 1001
UNION ALL
SELECT 1002
UNION ALL
SELECT 1010
UNION ALL
SELECT 1011
UNION ALL
SELECT 1012
UNION ALL
SELECT 1013
UNION ALL
SELECT 1020
UNION ALL
SELECT 1021
UNION ALL
SELECT 1022
),
-- materialize parameter table
params (param) AS (SELECT 1012)
SELECT MIN(N1.n) - 1 AS last_in_sequence
FROM nums AS N1
CROSS JOIN params AS P1
WHERE N1.n > P1.param
AND NOT EXISTS
(
SELECT *
FROM MyTable AS T1
WHERE N1.n = T1.ID
);
Если вы используете SQL-сервер, у вас должна быть возможность сделать рекурсивный запрос , который будет соединяться по root.number = leaf.number + 1
Если вы выберете число от корня и от последней рекурсии, а также уровень рекурсии у вас должен быть рабочий запрос.
Я бы сначала проверил производительность этого метода, а затем, если он не был удовлетворительным, перешел к подходу на основе курсора / строки (который в этом случае будет выполнять работу с одним полным сканированием, когда рекурсия может завершиться ошибкой при достижении максимальной глубины рекурсии).
В противном случае вы можете по-другому хранить данные и поддерживать список минимальных и максимальных чисел, связанных с таблицей.
На самом деле это может быть реализовано в триггерах с не такими высокими штрафами за обновления одной строки (обновления одной строки базовой таблицы будут либо обновлять, либо удалять, либо разделять строку в таблице min-max; это можно определить. путем запроса только "предыдущей" и "следующей" строки).