В практике разработки на платформе 1С часто возникает задача, когда необходимо сопоставить две таблицы: таблицу периодических событий (например, установка маршрута, назначение оклада или смена статуса объекта) и таблицу фактических данных (пробег за день, ежедневные продажи или часы работы). Сложность заключается в том, что фактические данные нужно сгруппировать по «плавающим» интервалам времени, которые задаются первой таблицей.
В этой статье мы подробно разберем, как реализовать подобную логику исключительно средствами языка запросов 1С. Мы пройдем путь от классического метода соединения таблиц до современных оптимизаций с использованием оконных функций.
Представим, что у нас есть две таблицы. Первая — Маршруты, где фиксируется дата установки нового маршрута для автомобиля. Вторая — Пробег, где ежедневно записывается количество пройденных километров. Наша цель — получить отчет, в котором будет указано, сколько всего километров проехал автомобиль по каждому из маршрутов в течение заданного периода.
Основная проблема заключается в том, что в таблице Маршруты есть только дата начала действия (Период). Дата окончания действия текущего маршрута — это дата начала следующего маршрута минус одна секунда (или просто строгое сравнение). Без даты окончания мы не сможем эффективно «прицепить» данные из таблицы пробега по условию МЕЖДУ.
Проанализируем первый и самый важный этап: превращение списка дат начала в полноценную таблицу периодов с колонками ДатаНачала и ДатаОкончания. Рассмотрим классический способ, который работает на любых версиях платформы 8.3.
Для этого нам понадобится левое соединение таблицы самой с собой. Мы берем текущую запись о маршруте и ищем минимальную дату следующей записи для этого же автомобиля.
Рассмотрим пример кода:
ВЫБРАТЬ
ДатыНачал.Автомобиль КАК Автомобиль,
ДатыНачал.Маршрут КАК Маршрут,
ДатыНачал.Период КАК ДатаНачала,
ЕСТЬNULL(МИНИМУМ(ДатыОкончаний.Период), &КонецПериодаОтчета) КАК ДатаОкончания
ПОМЕСТИТЬ ВТ_ПериодыМаршрутов
ИЗ
РегистрСведений.МаршрутыАвтомобилей КАК ДатыНачал
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.МаршрутыАвтомобилей КАК ДатыОкончаний
ПО ДатыНачал.Автомобиль = ДатыОкончаний.Автомобиль
И ДатыОкончаний.Период > ДатыНачал.Период
ГДЕ
ДатыНачал.Период <= &КонецПериодаОтчета
СГРУППИРОВАТЬ ПО
ДатыНачал.Автомобиль,
ДатыНачал.Маршрут,
ДатыНачал.Период
;
Разберем, что происходит в этом запросе:
МаршрутыАвтомобилей (псевдоним ДатыНачал).ДатыОкончаний) по условию, что дата окончания должна быть строго больше даты начала для того же автомобиля.МИНИМУМ, мы находим «самую раннюю из поздних» дат. Это и будет моментом смены маршрута.ЕСТЬNULL позволяет нам обработать последний маршрут в периоде: так как для него не найдется «следующей» записи, мы подставляем конец периода отчета.Теперь, когда у нас есть временная таблица ВТ_ПериодыМаршрутов, в которой для каждой строки четко определены границы ДатаНачала и ДатаОкончания, мы можем легко присоединить данные о пробеге.
Выясним причину, почему здесь важно правильно настроить условия сравнения дат. Если мы используем оператор МЕЖДУ, то данные на самой границе (например, ровно в 00:00:00 даты смены маршрута) могут попасть в оба периода. Чтобы избежать двойного счета, рекомендуется использовать строгое неравенство для верхней границы.
ВЫБРАТЬ
Периоды.Маршрут,
СУММА(ПробегДанные.Километраж) КАК ОбщийПробег
ИЗ
ВТ_ПериодыМаршрутов КАК Периоды
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПробегАвтомобилей КАК ПробегДанные
ПО Периоды.Автомобиль = ПробегДанные.Автомобиль
И ПробегДанные.Период >= Периоды.ДатаНачала
И ПробегДанные.Период < Периоды.ДатаОкончания
СГРУППИРОВАТЬ ПО
Периоды.Маршрут
Проанализируем ситуацию, когда маршрут был установлен задолго до начала периода отчета. Если мы просто выберем данные за месяц, мы потеряем информацию о том, какой маршрут действовал на 1-е число. Для решения этой проблемы необходимо добавить в таблицу периодов «виртуальную» запись, полученную через СрезПоследних.
Рассмотрим, как дополнить логику:
СрезПоследних на начало периода.ОБЪЕДИНИТЬ ВСЕ) эти данные с записями, которые появились внутри периода.ДатаНачала, устанавливая ее равной &НачалоПериода.Если ваша база работает на платформе версии 8.3.20 и выше, разберем более изящный и производительный способ решения задачи. Оконные функции позволяют получить данные из следующей строки без тяжелого соединения таблицы самой с собой.
Посмотрим на пример запроса с оконной функцией:
ВЫБРАТЬ
М.Автомобиль,
М.Маршрут,
М.Период КАК ДатаНачала,
LEAD(М.Период, 1, &КонецПериода) OVER (ПОДРАЗДЕЛИТЬ ПО М.Автомобиль УПОРЯДОЧИТЬ ПО М.Период) КАК ДатаОкончания
ИЗ
РегистрСведений.МаршрутыАвтомобилей КАК М
Функция LEAD «заглядывает» на одну строку вперед в рамках группировки по автомобилю. Это работает значительно быстрее на больших объемах данных, так как СУБД делает всего один проход по индексу вместо вложенных циклов поиска минимального значения.
При работе с большими объемами данных (например, если пробег фиксируется датчиками GPS каждые несколько минут), соединение по условию «больше-меньше» может стать очень медленным. Выясним, как ускорить выполнение запроса:
Автомобиль.Пробег по периоду отчета, поместите ее во временную таблицу и проиндексируйте поле Период.ВНУТРЕННЕЕ СОЕДИНЕНИЕ, оно работает быстрее ЛЕВОГО.Важное примечание по регистрам расчета: В обсуждении упоминались РегистрыРасчета. Это мощный инструмент для задач перерасчета (например, если изменился оклад задним числом и нужно пересчитать зарплату). Однако для учета пробега они чаще всего избыточны. Их стоит использовать только в том случае, если ваша конфигурация уже построена на механизме начислений (как ЗУП) и вам требуется автоматическое вытеснение периодов (например, маршрут «Командировка» перекрывает маршрут «Город»).
Таким образом, мы изучили универсальный алгоритм суммирования данных по периодам. Использование временных таблиц с интервалами дат — это надежный и понятный способ, который позволяет решать задачи любой сложности, от учета ГСМ до анализа эффективности работы персонала.