При разработке отчетов в системе 1С:Предприятие часто возникает классическая задача: необходимо вывести только последнее событие по объекту за определенный период (например, за месяц), избегая дублирования строк. Подобная проблема возникает, если у одного объекта (в нашем случае — животного) в течение месяца происходит несколько однотипных событий, а в отчет должна попадать только актуальная информация на конец периода.
В этой статье мы подробно разберем, почему простой запрос с функцией МАКСИМУМ не решает задачу напрямую, как правильно составить пакетный запрос с временными таблицами, исправим синтаксические ошибки популярных решений с форумов, а также рассмотрим важные архитектурные нюансы оптимизации СУБД.
Проанализируем сначала некорректную попытку решения задачи «в лоб». Автор темы попытался использовать следующий запрос:
ВЫБРАТЬ
СобытияПоВоспроизводству.Животное,
СобытияПоВоспроизводству.РезультатОсмотра,
СобытияПоВоспроизводству.ЭтапЦиклаВоспроизводства,
МАКСИМУМ(СобытияПоВоспроизводству.Период) КАК Дата
ИЗ
РегистрНакопления.СобытияПоВоспроизводству КАК СобытияПоВоспроизводству
Этот запрос содержит сразу несколько критических ошибок:
МАКСИМУМ(СобытияПоВоспроизводству.Период). По правилам языка запросов 1С (и стандартов SQL), если в выборке присутствует хотя бы одно агрегатное поле, все остальные неагрегатные поля должны быть перечислены в секции СГРУППИРОВАТЬ ПО. Без этого запрос попросту выдаст синтаксическую ошибку и не запустится.Животное, РезультатОсмотра и ЭтапЦиклаВоспроизводства, мы не избавимся от дублирования. Платформа сгруппирует данные по уникальным комбинациям всех этих трех полей. Если в одном месяце у животного было два осмотра с разными результатами, мы все равно получим две строки в отчете, просто для каждой строки будет показана ее максимальная дата.Выясним причину: чтобы получить срез последних данных по нечисловым реквизитам (состояниям), нельзя выполнять агрегацию в одной таблице вместе с этими реквизитами. Нам необходимо сначала вычислить точные даты последних событий, а затем «вытащить» соответствующие им строки.
Рассмотрим правильный подход к решению этой задачи. Мы разобьем выполнение запроса на два последовательных шага:
Животное и Период. Таким образом, мы отсечем все промежуточные события и оставим только финальные.Ниже представлен исправленный и оптимизированный вариант запроса:
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(СобытияПоВоспроизводству.Период, МЕСЯЦ) КАК ПериодМесяц,
СобытияПоВоспроизводству.Животное КАК Животное,
МАКСИМУМ(СобытияПоВоспроизводству.Период) КАК ПоследняяДата
ПОМЕСТИТЬ ВТ_ПоследниеДаты
ИЗ
РегистрНакопления.СобытияПоВоспроизводству КАК СобытияПоВоспроизводству
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(СобытияПоВоспроизводству.Период, МЕСЯЦ),
СобытияПоВоспроизводству.Животное
ИНДЕКСИРОВАТЬ ПО
Животное,
ПоследняяДата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ПоследниеДаты.ПериодМесяц КАК ПериодМесяц,
СобытияПоВоспроизводству.Животное КАК Животное,
СобытияПоВоспроизводству.РезультатОсмотра КАК РезультатОсмотра,
СобытияПоВоспроизводству.ЭтапЦиклаВоспроизводства КАК ЭтапЦиклаВоспроизводства,
СобытияПоВоспроизводству.Период КАК ТочнаяДатаСобытия
ИЗ
РегистрНакопления.СобытияПоВоспроизводству КАК СобытияПоВоспроизводству
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ПоследниеДаты КАК ВТ_ПоследниеДаты
ПО СобытияПоВоспроизводству.Животное = ВТ_ПоследниеДаты.Животное
И СобытияПоВоспроизводству.Период = ВТ_ПоследниеДаты.ПоследняяДата
Важное исправление: Обратите внимание, что в первом запросе пакета после поля СобытияПоВоспроизводству.Животное КАК Животное обязательно должна стоять запятая перед вызовом функции МАКСИМУМ. В исходных сообщениях на форумах часто допускают эту опечатку, из-за чего код не проходит компиляцию.
В ходе обсуждения на форуме возник спор об использовании конструкции ИНДЕКСИРОВАТЬ ПО для временных таблиц. Давайте разберем этот технологический нюанс подробнее.
Индексирование временных таблиц в 1С создает физический индекс в СУБД (MS SQL Server, PostgreSQL и др.) по указанным полям. Это ускоряет операцию ВНУТРЕННЕЕ СОЕДИНЕНИЕ на втором шаге. Однако создание индекса само по себе требует времени и ресурсов сервера.
Рекомендации по стандартам разработки 1С (ИТС):
ВТ_ПоследниеДаты гарантированно содержит небольшое количество записей (например, до 100-1000 строк), то затраты на создание индекса превысят выгоду от его использования. В этом случае индексацию лучше опустить.Животное и ПоследняяДата строго обязательно.Разберем тонкую логическую проблему, о которой мало кто задумывается на этапе проектирования. В 1С минимальная дискретность времени в стандартном поле Период составляет 1 секунду.
Представим ситуацию: в результате программного импорта данных, проведения группового документа или сбоя обмена данными по одному и тому же животному в одну и ту же секунду (например, 15.10.2023 14:05:00) были записаны два разных события:
Функция МАКСИМУМ(Период) вернет дату 15.10.2023 14:05:00. Но при внутреннем соединении по условию Период = ПоследняяДата СУБД найдет обе эти записи! В результате в отчете снова появится дублирование данных для этого месяца.
Как гарантировать 100% уникальность?
Для полного исключения дублей при совпадении времени необходимо использовать уникальный идентификатор записи. Для регистров, подчиненных регистратору, уникальным ключом является связка полей Регистратор и НомерСтроки. Если существует вероятность коллизии секунд, во временную таблицу нужно помещать максимальную дату, а в случае равенства дат — выбирать запись с максимальным номером строки или максимальной ссылкой на регистратор.
Обратим внимание на архитектуру метаданных. В исходном вопросе фигурирует РегистрНакопления.СобытияПоВоспроизводству. Однако сущности вроде «Результат осмотра» или «Этап цикла» представляют собой качественные состояния объекта, а не накапливаемые числовые показатели (обороты или остатки).
Использование регистра накопления для хранения истории состояний — это грубая архитектурная ошибка. Для этих целей в 1С предназначен периодический Регистр сведений.
Если перевести эти данные в периодический регистр сведений СобытияПоВоспроизводству, то решение задачи значительно упростится. Платформа 1С автоматически создает виртуальную таблицу СрезПоследних, которая оптимизирована под подобные задачи на уровне СУБД. Наш запрос сократится до лаконичного кода:
ВЫБРАТЬ
СрезПоследних.Животное КАК Животное,
СрезПоследних.РезультатОсмотра КАК РезультатОсмотра,
СрезПоследних.ЭтапЦиклаВоспроизводства КАК ЭтапЦиклаВоспроизводства,
СрезПоследних.Период КАК Период
ИЗ
РегистрСведений.СобытияПоВоспроизводству.СрезПоследних(&КонецПериода, ) КАК СрезПоследних
Такой запрос работает в разы быстрее, код становится легко читаемым, а вероятность ошибок при написании сводится к минимуму.
Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), вам не обязательно писать сложные пакетные запросы вручную. СКД умеет самостоятельно рассчитывать срез последних на каждую дату с помощью связей наборов данных.
Разберем, как это настроить по шагам:
Животное.Платформа 1С при генерации отчета самостоятельно преобразует эти связи в максимально эффективный SQL-запрос с учетом специфики используемой СУБД.
Резюмируя разбор проблемы, дадим несколько практических советов:
СрезПоследних.НАЧАЛОПЕРИОДА.