При работе с периодическими регистрами сведений разработчики часто сталкиваются с задачей выбора среза данных за определенный интервал времени. Ситуация усложняется, когда в регистре присутствуют пересекающиеся периоды по одному и тому же аналитическому разрезу (например, по контрагенту), и бизнес-логика требует выбрать строго одну запись — ту, у которой значение ресурса (суммы) является максимальным. При этом в результирующую выборку обязательно нужно выгрузить сопутствующие даты: дату начала периода и дату окончания действия.
Попытка решить эту задачу «в лоб» с помощью простой группировки и использования агрегатной функции МАКСИМУМ приводит к тому, что СУБД возвращает несколько строк вместо одной. Разберем подробно, почему так происходит, и рассмотрим правильные варианты решения этой проблемы с точки зрения оптимизации запросов и архитектуры базы данных 1С.
Рассмотрим исходный запрос, с которым возникли трудности у автора темы:
ВЫБРАТЬ
ЛадСогласСуммаСрезПоследних.Период КАК ДатаНачала,
ЛадСогласСуммаСрезПоследних.ДатаОкончанияДействия КАК ДатаКонца,
МАКСИМУМ(ЛадСогласСуммаСрезПоследних.Сумма) КАК СогласСумма
ИЗ
РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
СГРУППИРОВАТЬ ПО
ЛадСогласСуммаСрезПоследних.Период,
ЛадСогласСуммаСрезПоследних.ДатаОкончанияДействия
Проанализируем работу этого запроса. Когда мы пишем секцию СГРУППИРОВАТЬ ПО, СУБД группирует записи по уникальной комбинации всех указанных в этой секции полей. В данном случае группировка идет по полям Период (ДатаНачала) и ДатаОкончанияДействия (ДатаКонца).
Если в регистре по контрагенту зафиксировано две пересекающиеся записи с разными датами начала и окончания (например, одна запись с 29.06.2020 по 30.06.2020 на сумму 40 000, а вторая — с 30.06.2020 по 02.07.2020 на сумму 57 000), то СУБД видит две уникальные пары дат:
Поскольку эти пары различны, они формируют две отдельные группы. Внутри каждой группы функция МАКСИМУМ(Сумма) вычисляется независимо. Для первой группы максимум составит 40 000, для второй — 57 000. В итоге на выходе мы получаем обе строки, хотя ожидали получить только одну (строку с максимальной суммой 57 000 и ее датами).
Если же мы уберем даты из группировки и оставим только поле Контрагент, запрос отработает математически верно и вернет одну строку со значением 57 000. Но при этом мы безвозвратно теряем информацию о датах начала и окончания действия этой суммы, так как не можем просто так вывести их без группировки.
Для того чтобы гарантированно получить одну строку с максимальной суммой и соответствующими ей датами, необходимо разделить задачу на логические этапы. Это стандартный и наиболее производительный паттерн в платформе 1С. Реализуем его с помощью пакета запросов и временных таблиц.
Шаг 1. Нахождение максимальной суммы. Сначала мы выберем максимальное значение суммы для контрагента за указанный период во временную таблицу. На этом этапе группировка по датам не производится.
Шаг 2. Получение дат для этой максимальной суммы. Мы соединяем исходную таблицу регистра с нашей временной таблицей по полям Контрагент и Сумма. Из полученного результата мы извлекаем связанные даты начала и окончания.
Посмотрим на пример реализации такого пакета запросов:
ВЫБРАТЬ
МАКСИМУМ(ЛадСогласСуммаСрезПоследних.Сумма) КАК МаксСумма,
ЛадСогласСуммаСрезПоследних.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_МаксСумма
ИЗ
РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
СГРУППИРОВАТЬ ПО
ЛадСогласСуммаСрезПоследних.Контрагент
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЛадСогласСуммаСрезПоследних.Период КАК ДатаНачала,
ЛадСогласСуммаСрезПоследних.ДатаОкончанияДействия КАК ДатаКонца,
ВТ_МаксСумма.МаксСумма КАК СогласСумма
ИЗ
ВТ_МаксСумма КАК ВТ_МаксСумма
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
ПО ВТ_МаксСумма.Контрагент = ЛадСогласСуммаСрезПоследних.Контрагент
И ВТ_МаксСумма.МаксСумма = ЛадСогласСуммаСрезПоследних.Сумма
Этот способ работает стабильно, легко читается и позволяет СУБД построить оптимальный план выполнения запроса.
Проанализируем гипотетическую, но частую ситуацию: что произойдет, если в регистре окажутся две разные записи с абсолютно одинаковой максимальной суммой (например, две записи по 57 000 с разными периодами)?
В этом случае простое соединение по полю Сумма из первого решения вернет обе записи, и мы снова получим две строки на выходе. Если бизнес-логика требует получить строго одну строку (например, с самой поздней датой начала), нам понадобится трехэтапный пакет запросов.
Разберем этот алгоритм по шагам:
ВТ_МаксимальнаяСумма.ВТ_МаксимальнаяСумма по контрагенту и сумме, чтобы найти максимальную дату начала (поле Период) для этой суммы. Результат помещаем в ВТ_МаксимальныйПериод.ВТ_МаксимальныйПериод с исходным регистром по контрагенту, сумме и периоду. Это позволяет вытащить точную дату окончания действия, соответствующую этой уникальной строке.Посмотрим на пример реализации этого продвинутого алгоритма:
ВЫБРАТЬ
ЛадСогласСуммаСрезПоследних.Контрагент КАК Контрагент,
МАКСИМУМ(ЛадСогласСуммаСрезПоследних.Сумма) КАК МаксСумма
ПОМЕСТИТЬ ВТ_Шаг1_МаксСумма
ИЗ
РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
СГРУППИРОВАТЬ ПО
ЛадСогласСуммаСрезПоследних.Контрагент
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Шаг1_МаксСумма.Контрагент КАК Контрагент,
ВТ_Шаг1_МаксСумма.МаксСумма КАК МаксСумма,
МАКСИМУМ(ЛадСогласСуммаСрезПоследних.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_Шаг2_МаксПериод
ИЗ
ВТ_Шаг1_МаксСумма КАК ВТ_Шаг1_МаксСумма
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
ПО ВТ_Шаг1_МаксСумма.Контрагент = ЛадСогласСуммаСрезПоследних.Контрагент
И ВТ_Шаг1_МаксСумма.МаксСумма = Swapped_1.Сумма // Где Swapped_1 - это алиас
И ВТ_Шаг1_МаксСумма.МаксСумма = ЛадСогласСуммаСрезПоследних.Сумма
СГРУППИРОВАТЬ ПО
ВТ_Шаг1_МаксСумма.Контрагент,
ВТ_Шаг1_МаксСумма.МаксСумма
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЛадСогласСуммаСрезПоследних.Период КАК ДатаНачала,
ЛадСогласСуммаСрезПоследних.ДатаОкончанияДействия КАК ДатаКонца,
ВТ_Шаг2_МаксПериод.МаксСумма КАК СогласСумма
ИЗ
ВТ_Шаг2_МаксПериод КАК ВТ_Шаг2_МаксПериод
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЛадСогласСумма.СрезПоследних(
&ДатаНачала,
Контрагент = &Контрагент
И (&ДатаОкончания <= ДатаОкончанияДействия
ИЛИ ДатаОкончанияДействия = ДАТАВРЕМЯ(1, 1, 1))
) КАК ЛадСогласСуммаСрезПоследних
ПО ВТ_Шаг2_МаксПериод.Контрагент = ЛадСогласСуммаСрезПоследних.Контрагент
И ВТ_Шаг2_МаксПериод.МаксСумма = ЛадСогласСуммаСрезПоследних.Сумма
И ВТ_Шаг2_МаксПериод.МаксПериод = ЛадСогласСуммаСрезПоследних.Период
Этот подход гарантирует получение ровно одной строки на каждого контрагента независимо от того, насколько сложными и пересекающимися являются исходные данные в регистре сведений.
Проанализируем структуру регистра, описанную в сообщениях форума. Автор указывает, что в регистре сведений ЛадСогласСумма поле ДатаОкончанияДействия является измерением. С точки зрения стандартов разработки фирмы «1С», такое проектирование является грубой архитектурной ошибкой.
Для СУБД уникальность записи в периодическом регистре сведений определяется набором измерений плюс периодом. Если мы делаем дату окончания измерением, то для одного контрагента на одну и ту же дату начала мы можем записать неограниченное количество строк, просто указав разные даты окончания:
В такой структуре виртуальная таблица СрезПоследних перестает работать корректно. Вместо того чтобы выдать одну актуальную запись на конкретную дату, срез последних возвращает все эти строки одновременно, поскольку комбинации измерений уникальны. Из-за этого разработчикам приходится писать тяжелые, непроизводительные запросы с множественными вложенными соединениями.
Правильное проектное решение:
ДатаОкончанияДействия ресурсом регистра сведений, а не измерением.Контрагент.При такой структуре на одну дату начала (поле Период) для контрагента физически не сможет существовать более одной записи. Виртуальная таблица СрезПоследних на любую дату вернет ровно одну строку, в которой будут содержаться и актуальная сумма, и соответствующая ей дата окончания действия. Потребность во всех сложных группировках и вложенных запросах моментально исчезнет.
В сообщении 2 темы предлагалось альтернативное решение через использование вложенного запроса в секции ЛЕВОЕ СОЕДИНЕНИЕ:
ИЗ (ВЫБРАТЬ МАКСИМУМ(...) ...) КАК ПоискМаксимума
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений... ПО ЛадСогласСуммаСрезПоследних.Сумма = ПоискМаксимума.МаксСумма
Проанализируем, почему этот вариант нежелательно использовать в высоконагруженных системах. СУБД не может проиндексировать результат вложенного запроса, выполняемого «на лету» в секции ИЗ. Более того, соединение выполняется по полю Сумма. Поскольку сумма — это числовой ресурс, по нему в СУБД отсутствуют индексы.
На таблицах большого объема такое соединение приведет к полному сканированию данных (операция Table Scan или Index Scan), что вызовет серьезные просадки производительности и блокировки базы данных. Использование временных таблиц в пакете запросов (как показано в Решении 1 и Решении 2) позволяет платформе 1С оптимизировать работу СУБД, а при необходимости мы можем явно проиндексировать временную таблицу по связующим полям с помощью конструкции ИНДЕКСИРОВАТЬ ПО.
В сообщении 24 автор столкнулся с проблемой: если регистр пуст, то запрос все равно возвращает одну строку, в которой все поля равны значением NULL. Выясним причину этого поведения.
Это базовая спецификация языка SQL. Если в запросе используются агрегатные функции (такие как МАКСИМУМ, МИНИМУМ, СУММА) и при этом отсутствует секция СГРУППИРОВАТЬ ПО, СУБД гарантированно возвращает ровно одну строку. Если исходная выборка пуста, то результатом агрегации становится значение NULL.
Чтобы избежать ошибок при обработке результата в коде 1С, всегда оборачивайте агрегатные функции в функцию ЕСТЬNULL. Например:
ВЫБРАТЬ
ЕСТЬNULL(МАКСИМУМ(ЛадСогласСуммаСрезПоследних.Сумма), 0) КАК МаксСумма
Если же в запросе присутствует секция СГРУППИРОВАТЬ ПО, и выборка оказывается пустой, то запрос вернет действительно пустой результат (0 строк), что избавляет от необходимости обрабатывать нежелательные значения NULL.
В крупных типовых решениях от фирмы «1С» (таких как «1С:ERP Управление предприятием» или «1С:Зарплата и управление персоналом») для работы с периодическими интервалами часто применяется концепция интервальных регистров.
Вместо того чтобы динамически вычислять наложения дат в тяжелых запросах к периодическому регистру, система автоматически поддерживает в актуальном состоянии специальный вспомогательный (непериодический) регистр сведений. При записи документа система производит расчет и записывает данные в виде непрерывных, непересекающихся интервалов, где даты начала и окончания зафиксированы в измерениях. Запросы к таким регистрам выполняются мгновенно, так как они сводятся к простому поиску по индексу вхождения даты в интервал.