При разработке отчетов, анализе движений регистров и решении задач планирования в 1С программисты часто сталкиваются с необходимостью преобразовать плоский список дат в последовательные временные интервалы. Классическая постановка задачи выглядит следующим образом: имеется набор документов или записей с определенными датами, и требуется получить таблицу интервалов, где дата каждой новой записи становится датой окончания периода для предыдущей записи.
Например, из исходного списка дат:
Мы должны получить следующие пары «Начало — Конец»:
В этой статье мы подробно разберем четыре различных метода решения этой задачи: от классического тета-соединения до современных функций платформы 1С и механизмов Системы компоновки данных (СКД).
Этот метод основан на связи таблицы с самой собой по условию «меньше» (<). Он работает на любой версии платформы 1С (как 8.3, так и старых 8.2, 8.1) и не требует специфических функций СУБД. Суть метода заключается в том, что для каждой даты из первой таблицы мы ищем все даты из второй таблицы, которые строго больше нее, а затем выбираем из них самую минимальную.
Рассмотрим по шагам, как устроен этот запрос:
ВТ1) с ее же копией (ВТ2) по условию ВТ1.ДатаДок < ВТ2.ДатаДок.ВТ1.ДатаДок.МИНИМУМ(ВТ2.ДатаДок) получаем ближайшую следующую дату, которая и будет являться датой окончания периода.ГДЕ.Посмотрим на практический пример реализации такого запроса:
ВЫБРАТЬ ПЕРВЫЕ 5
РасходТоваров.Дата КАК ДатаДок
ПОМЕСТИТЬ ВТ_Данные
ИЗ
Документ.РасходТоваров КАК РасходТоваров
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ1.ДатаДок КАК ДатаНач,
МИНИМУМ(ВТ2.ДатаДок) КАК ДатаКон
ИЗ
ВТ_Данные КАК ВТ1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ВТ2
ПО ВТ1.ДатаДок < ВТ2.ДатаДок
ГДЕ
ВТ2.ДатаДок ЕСТЬ НЕ NULL
СГРУППИРОВАТЬ ПО
ВТ1.ДатаДок
Важный нюанс оптимизации: данный алгоритм имеет квадратичную сложность $O(N^2)$, так как СУБД вынуждена сопоставлять каждую строку со всеми последующими строками. Если в вашей временной таблице ВТ_Данные окажется несколько тысяч записей, запрос может начать критически тормозить. Чтобы избежать падения производительности, обязательно индексируйте временную таблицу по полю связи:
ВЫБРАТЬ
РасходТоваров.Дата КАК ДатаДок
ПОМЕСТИТЬ ВТ_Данные
ИЗ
Документ.РасходТоваров КАК РасходТоваров
ИНДЕКСИРОВАТЬ ПО
ДатаДок
;
Наличие индекса позволит СУБД мгновенно находить минимальное следующее значение без полного сканирования таблиц, что сведет время выполнения запроса к минимуму.
Начиная с версии платформы 1С:Предприятие 8.3.13, разработчикам стала доступна встроенная функция языка запросов АВТОНОМЕРЗАПИСИ(). С ее помощью мы можем пронумеровать строки временной таблицы по порядку, а затем соединить таблицу саму с собой по условию смещения индекса: Т1.НомерСтроки = Т2.НомерСтроки - 1.
Выясним особенности использования функции АВТОНОМЕРЗАПИСИ(), которые необходимо учитывать:
ПОМЕСТИТЬ).АВТОНОМЕРЗАПИСИ() - 1 не получится — платформа выдаст синтаксическую ошибку. Сдвиг на единицу нужно делать на следующем шаге пакета запросов, при связи таблиц.Т1.НомерСтроки = Т2.НомерСтроки - 1 отработает абсолютно корректно.Проанализируем пример реализации данного метода:
ВЫБРАТЬ
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки,
ВТ_Данные.ДатаДок КАК ДатаДок
ПОМЕСТИТЬ ВТ_СНомерами
ИЗ
ВТ_Данные КАК ВТ_Данные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.ДатаДок КАК ДатаНач,
Т2.ДатаДок КАК ДатаКон
ИЗ
ВТ_СНомерами КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СНомерами КАК Т2
ПО Т1.НомерСтроки = Т2.НомерСтроки - 1
Этот метод работает значительно быстрее классического тета-соединения на больших объемах данных, так как соединение по точному равенству индексов (=) выполняется СУБД в разы эффективнее, чем по неравенству (<).
Что делать, если вам приходится работать на старых конфигурациях и версиях платформы ниже 8.3.13, где функции АВТОНОМЕРЗАПИСИ() еще не существовало? В этом случае нумерацию строк можно сэмулировать искусственным путем с помощью соединения по условию «больше или равно» (>=).
Давайте посмотрим, как выглядит этот обходной путь:
ВЫБРАТЬ
Т1.ДатаДок КАК ДатаДок,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.ДатаДок) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Пронумерованная
ИЗ
ВТ_Данные КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
ПО Т1.ДатаДок >= Т2.ДатаДок
СГРУППИРОВАТЬ ПО
Т1.ДатаДок
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.ДатаДок КАК ДатаНач,
Т2.ДатаДок КАК ДатаКон
ИЗ
ВТ_Пронумерованная КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Пронумерованная КАК Т2
ПО Т1.НомерСтроки = Т2.НомерСтроки - 1
Внимание: Эмуляция нумерации через подсчет количества различных записей создает колоссальную нагрузку на СУБД при больших объемах данных. На таблицах емкостью более 1000 строк этот метод использовать не рекомендуется — в таких случаях классический Метод 1 (минимизация через тета-соединение) покажет гораздо лучшую производительность.
Если ваша конечная цель — вывод данных в отчет, то самым производительным, элегантным и правильным решением будет перенос логики вычисления интервалов с уровня базы данных на уровень платформы 1С (клиент или сервер приложений) с помощью Системы компоновки данных. Это полностью избавит СУБД от тяжелых операций соединения таблиц.
Для реализации этого метода выполним следующие шаги:
ДатаОкончания).ВычислитьВыражение("ДатаПериода", , , "Следующая", "Следующая")
Эта функция работает мгновенно, так как СКД обращается к уже полученной и находящейся в оперативной памяти выборке данных, просто считывая значение из следующей строки вывода.
Подведем итоги нашего анализа: