Как в запросе 1С объединить непрерывную последовательность кодов в интервалы?

← На главную

В практике разработки на платформе 1С и на технических собеседованиях нередко встречается интересная алгоритмическая задача: имеется упорядоченный набор числовых значений с разрывами (например, коды элементов, номера документов или даты), и требуется свернуть непрерывно идущие элементы с шагом +1 в интервалы вида «Начало – Конец». В классической теории реляционных баз данных эта задача известна как проблема «Островов и пропусков» (Gaps and Islands).

Посмотрим на пример исходных данных: 1, 2, 3, 4, 5, 7, 8, 9, 12, 15, 16. В результате обработки мы должны получить следующую таблицу интервалов:

1 – 5
7 – 9
12 – 12 (или отдельная точка 12)
15 – 16

Сложность реализации такого алгоритма в 1С заключается в том, что в языке запросов платформы отсутствуют аналитические оконные функции, такие как ROW_NUMBER(), LEAD() или LAG(). Разберем подробно, какими способами можно решить эту задачу исключительно средствами языка запросов 1С, а также выясним, когда эффективнее применить обход на встроенном языке.

Способ 1. Определение границ интервалов через смежные элементы

Проанализируем математическую природу непрерывной цепочки чисел. Любое число в непрерывной последовательности является:

Началом интервала, если в исходной таблице отсутствует элемент, равный текущему значению минус единица (Код - 1).
Концом интервала, если в исходной таблице отсутствует элемент, равный текущему значению плюс единица (Код + 1).

Выделив отдельно все начальные точки и все конечные точки, мы можем связать каждое начало с соответствующим ему ближайшим концом. Разберем по шагам текст запроса 1С для демонстрационного набора данных:


ВЫБРАТЬ
    1 КАК Интервал
ПОМЕСТИТЬ ИсходныеДанные
ОБЪЕДИНИТЬ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВЫБРАТЬ 9
ОБЪЕДИНИТЬ ВЫБРАТЬ 12
ОБЪЕДИНИТЬ ВЫБРАТЬ 15
ОБЪЕДИНИТЬ ВЫБРАТЬ 16
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    МАКСИМУМ(ТочкиНачала.Интервал) КАК ИнтервалНачало,
    ТочкиКонца.Интервал КАК ИнтервалКонец
ИЗ
    (ВЫБРАТЬ
        Т1.Интервал КАК Интервал
    ИЗ
        ИсходныеДанные КАК Т1
            ЛЕВОЕ СОЕДИНЕНИЕ ИсходныеДанные КАК Т2
            ПО (Т1.Интервал - 1 = Т2.Интервал)
    ГДЕ
        Т2.Интервал ЕСТЬ NULL) КАК ТочкиНачала
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            Т1.Интервал КАК Интервал
        ИЗ
            ИсходныеДанные КАК Т1
                ЛЕВОЕ СОЕДИНЕНИЕ ИсходныеДанные КАК Т2
                ПО (Т1.Интервал + 1 = Т2.Интервал)
        ГДЕ
            Т2.Интервал ЕСТЬ NULL) КАК ТочкиКонца
        ПО (ТочкиНачала.Интервал <= ТочкиКонца.Интервал)

СГРУППИРОВАТЬ ПО
    ТочкиКонца.Интервал

Рассмотрим подробнее логику работы этого запроса:

Во вложенной таблице ТочкиНачала мы отбираем только те числа, для которых левое соединение по условию Т1.Интервал - 1 = Т2.Интервал вернуло NULL. Это точки: 1, 7, 12, 15.
Во вложенной таблице ТочкиКонца отбираются элементы, у которых нет соседа справа (Т1.Интервал + 1 = Т2.Интервал вернуло NULL). Это точки: 5, 9, 12, 16.
Затем связываем точки начала и конца по неэквивалентному условию ТочкиНачала.Интервал <= ТочкиКонца.Интервал и группируем по каждому концу интервала. Функция МАКСИМУМ(ТочкиНачала.Интервал) отсекает более ранние начала и выбирает именно то начало, которое непосредственно предшествует данному концу.

Способ 2. Нумерация строк и группировка по разности значений

Еще один классический подход к проблеме островов базируется на вычислении ранга строки. Если из самого значения вычесть его порядковый номер в отсортированной последовательности, то для непрерывно идущих чисел эта разность будет постоянной константой. Как только в последовательности возникает разрыв, разность меняется на другое число, образуя уникальный идентификатор группы интервала.

Так как в языке запросов 1С нет функции нумерации строк, порядковый номер рассчитывается соединением таблицы самой с собой по условию <= с подсчетом количества элементов. Посмотрим на реализацию этого метода:


ВЫБРАТЬ
    Т.Интервал КАК Код,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТДо.Интервал) КАК ПорядковыйНомер
ПОМЕСТИТЬ ПронумерованнаяПоследовательность
ИЗ
    ИсходныеДанные КАК Т
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ИсходныеДанные КАК ТДо
        ПО (ТДо.Интервал <= Т.Интервал)
СГРУППИРОВАТЬ ПО
    Т.Интервал
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    МИНИМУМ(П.Код) КАК НачалоИнтервала,
    МАКСИМУМ(П.Код) КАК КонецИнтервала
ИЗ
    ПронумерованнаяПоследовательность КАК П
СГРУППИРОВАТЬ ПО
    (П.Код - П.ПорядковыйНомер)

Проанализируем пример: для чисел 1, 2, 3 порядковые номера равны 1, 2, 3. Разность Код - ПорядковыйНомер равна 0 для всех трех записей. Для следующего числа 7 порядковый номер будет 6, а разность 7 - 6 = 1. Группируя по этой постоянной разности, мы сразу получаем МИНИМУМ(Код) как начало интервала и МАКСИМУМ(Код) как его завершение. Этот метод отлично работает и с датами при использовании функции ДОБАВИТЬКДАТЕ(Дата, ДЕНЬ, -Номер).

Ограничения производительности запросных методов

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

Квадратичная сложность: Оба представленных запроса используют неэквивалентные соединения (по знакам <=). Это приводит к сложности порядка O(N²). На небольших таблицах (до нескольких сотен строк) запрос выполнится мгновенно, однако на массивах в десятки тысяч строк СУБД уйдет в тяжелое сканирование временных таблиц.
Дубликаты: Если в исходной таблице коды могут повторяться, перед началом алгоритма обязательно выполните операцию ВЫБРАТЬ РАЗЛИЧНЫЕ или сгруппируйте данные, иначе условия - 1 и + 1 дадут некорректный результат.
Индексация: При помещении данных во временные таблицы всегда используйте инструкцию ИНДЕКСИРОВАТЬ ПО для ключевого поля, по которому выполняется сравнение.

Способ 3. Линейный алгоритм на встроенном языке 1С

Если требуется обработать действительно большой объем данных (например, тысячи разорванных номеров документов или штрихкодов), наиболее оптимальным архитектурным решением будет выборка отсортированного списка и его линейный обход на встроенном языке со сложностью O(N).

Разберем пример программной группировки через объект ВыборкаИзРезультатаЗапроса:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ РАЗЛИЧНЫЕ
    |	Т.Интервал КАК Код
    |ИЗ
    |	ИсходныеДанные КАК Т
    |УПОРЯДОЧИТЬ ПО
    |	Код";

Выборка = Запрос.Выполнить().Выбрать();

ТаблицаИнтервалов = Новый ТаблицаЗначений;
ТаблицаИнтервалов.Колонки.Добавить("НачалоИнтервала", Новый ОписаниеТипов("Число"));
ТаблицаИнтервалов.Колонки.Добавить("КонецИнтервала", Новый ОписаниеТипов("Число"));

ТекущееНачало = Неопределено;
ПредыдущийКод = Неопределено;

Пока Выборка.Следующий() Цикл
    Если ТекущееНачало = Неопределено Тогда
        ТекущееНачало = Выборка.Код;
        ПредыдущийКод = Выборка.Код;
        Продолжить;
    КонецЕсли;
    
    Если Выборка.Код = ПредыдущийКод + 1 Тогда
        ПредыдущийКод = Выборка.Код;
    Иначе
        НоваяСтрока = ТаблицаИнтервалов.Добавить();
        НоваяСтрока.НачалоИнтервала = ТекущееНачало;
        НоваяСтрока.КонецИнтервала = ПредыдущийКод;
        
        ТекущееНачало = Выборка.Код;
        ПредыдущийКод = Выборка.Код;
    КонецЕсли;
КонецЦикла;

Если ТекущееНачало <> Неопределено Тогда
    НоваяСтрока = ТаблицаИнтервалов.Добавить();
    НоваяСтрока.НачалоИнтервала = ТекущееНачало;
    НоваяСтрока.КонецИнтервала = ПредыдущийКод;
КонецЕсли;

Такой подход выполняется за один проход по выборке, не перегружает сервер СУБД вложенными циклами и демонстрирует максимальную скорость выполнения при любых объемах данных.