В процессе разработки отчетов или написания выборок в системе «1С:Предприятие» перед разработчиками часто встает задача: получить список контрагентов, по которым велись активные продажи за определенный период (например, за последний год). На первый взгляд задача кажется тривиальной, однако при ее реализации легко допустить критические ошибки, которые скажутся либо на корректности данных, либо на производительности всей системы.
В этой статье мы подробно проанализируем типичный запрос для решения этой задачи, разберем допущенные в нем ошибки (как логические, так и архитектурные), рассмотрим правильные методы работы с датами и покажем, как написать этот запрос в соответствии с лучшими практиками и стандартами разработки фирмы «1С».
---
Рассмотрим ситуацию: разработчику необходимо получить список контрагентов, по которым были продажи за последний год (начиная с 18.09.2019 по текущую дату). Первоначальный запрос выглядел следующим образом:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Контрагенты.Код,
Контрагенты.Наименование КАК Контрагент
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
ПО РеализацияТоваровУслуг.Контрагент = Контрагенты.Ссылка
ГДЕ
РеализацияТоваровУслуг.Дата >= ДАТАВРЕМЯ(2018, 9, 18)
СГРУППИРОВАТЬ ПО
Контрагенты.Наименование,
Контрагенты.Код
УПОРЯДОЧИТЬ ПО
Контрагент
При выполнении этого запроса пользователь столкнулся с тем, что база выдала 6660 контрагентов, хотя часом ранее при аналогичном условии было получено всего 175. Выясним, почему возникло такое расхождение.
Главная логическая ошибка кроется в жестко прописанной дате. В словесной постановке задачи автор указывает дату начала периода — 18.09.2019. Однако в самом тексте запроса в параметре ДАТАВРЕМЯ указан 2018 год (2018, 9, 18). Таким образом, запрос захватил продажи не за один год, а за два. За счет этого в выборку попало огромное число старых или разовых контрагентов, что и привело к увеличению результата с 175 до 6660 записей.
---
Проанализируем структуру исходного запроса. В нем присутствует явная избыточность, которая негативно сказывается на читаемости кода и создает лишнюю нагрузку на СУБД:
РАЗЛИЧНЫЕ (которое транслируется в SQL-конструкцию DISTINCT) совместно с оператором СГРУППИРОВАТЬ ПО (транслируется в GROUP BY) абсолютно бессмысленно, если в запросе отсутствуют агрегатные функции (такие как СУММА, КОЛИЧЕСТВО или СРЕДНЕЕ). Обе эти конструкции служат для сворачивания дублей. Для получения уникального списка достаточно использовать только ключевое слово РАЗЛИЧНЫЕ. Это значительно упростит СУБД построение плана выполнения запроса.ЛЕВОЕ СОЕДИНЕНИЕ таблицы документов с таблицей справочника контрагентов. В типовых конфигурациях 1С реквизит Контрагент в документе реализации является обязательным для заполнения. При использовании левого соединения СУБД вынуждена резервировать ресурсы под возможные значения NULL со стороны справочника. В данном случае гораздо эффективнее использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ или, что еще лучше, получать данные непосредственно через точку от объектного реквизита (дереференсинг), если нам не требуются специфические оптимизации.Упрощенный вариант исходного запроса без лишних соединений и группировок будет выглядеть так:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РеализацияТоваровУслуг.Контрагент КАК Контрагент
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
РеализацияТоваровУслуг.Дата >= &НачалоПериода
---
Несмотря на то, что упрощенный запрос вернет корректный список контрагентов, его использование в реальной рабочей базе данных является грубым нарушением стандартов разработки 1С. Рассмотрим, почему обращение к таблице Документ.РеализацияТоваровУслуг напрямую — это антипаттерн.
Физическая таблица документов в СУБД содержит огромное количество тяжелых данных: ссылки на шапку, многочисленные реквизиты, а также связи с табличными частями. Когда мы накладываем фильтр по дате на таблицу документов, СУБД выполняет сканирование огромных массивов данных. На крупных базах с высокой интенсивностью документооборота такой запрос приведет к:
Как правильно решать эту задачу? Для получения любой аналитики по продажам, взаиморасчетам или остаткам необходимо использовать предназначенные для этого регистры (накопления или бухгалтерии) и их виртуальные таблицы оборотов. Виртуальные таблицы используют заранее рассчитанные и оптимизированные итоги, благодаря чему выборка выполняется мгновенно и без блокировок транзакционных таблиц документов.
---
В зависимости от архитектуры используемой вами конфигурации, правильные запросы для получения активных контрагентов будут выглядеть по-разному. Рассмотрим три основных варианта.
В этих решениях для учета продаж применяется регистр накопления Продажи. Запрос к его виртуальной таблице оборотов выглядит лаконично и работает максимально быстро:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПродажиОбороты.Контрагент КАК Контрагент
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты
УПОРЯДОЧИТЬ ПО
Контрагент.Наименование
В современных конфигурациях используется концепция разделения партнеров (управленческий учет) и контрагентов (регламентированный учет). Информация о продажах аккумулируется в регистре ВыручкаИСебестоимостьПродаж. Запрос составляется следующим образом:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВыручкаОбороты.АналитикаУчетаПоПартнерам.Контрагент КАК Контрагент
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ВыручкаОбороты
ГДЕ
ВыручкаОбороты.АналитикаУчетаПоПартнерам.Контрагент <> ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)
Если задача решается в бухгалтерской конфигурации, где регистры накопления продаж могут не вестись, оптимальным решением будет обращение к оборотам регистра бухгалтерии (например, по субсчетам счета 90.01 «Выручка»):
ВЫБРАТЬ РАЗЛИЧНЫЕ
ХозрасчетныйОбороты.Субконто1 КАК Контрагент
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачалоПериода, &КонецПериода, , Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Продажи), , , , ) КАК ХозрасчетныйОбороты
---
Вернемся к вопросу динамического определения периода. Пользователь спросил, как сделать так, чтобы запрос всегда выбирал данные ровно за последний год, не требуя ручного ввода конкретных дат при каждом запуске.
В платформе 1С существует встроенная функция языка запросов ДОБАВИТЬКДАТЕ, синтаксис которой выглядит следующим образом:
ДОБАВИТЬКДАТЕ(ИсходнаяДата, ТипУвеличения, Величина)
Где в качестве типа увеличения могут выступать ключевые слова: ДЕНЬ, МЕСЯЦ, КВАРТАЛ, ГОД и др.
Если бы мы хотели вычислить дату начала прямо в запросе, то условие выглядело бы так:
РеализацияТоваровУслуг.Дата >= ДОБАВИТЬКДАТЕ(&КонецПериода, ГОД, -1)
Однако передавать логику расчета дат внутрь самого текста запроса не рекомендуется по следующим причинам:
ГДЕ в ряде случаев может помешать СУБД эффективно использовать индексы таблиц.Правильным стандартом разработки является вычисление граничных дат во внешнем коде на встроенном языке 1С с последующей передачей готовых значений в параметры запроса.
Разберем по шагам пример на встроенном языке:
// 1. Создаем объект Запрос
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗЛИЧНЫЕ
| ПродажиОбороты.Контрагент КАК Контрагент
|ИЗ
| РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты";
// 2. Рассчитываем даты на встроенном языке
КонецПериода = ТекущаяДата(); // Текущий момент времени
НачалоПериода = ДобавитьМесяц(КонецПериода, -12); // Ровно один год назад
// 3. Передаем вычисленные значения в параметры запроса
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
// 4. Выполняем запрос
РезультатЗапроса = Запрос.Выполнить();
ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();
Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
// Обработка полученных контрагентов
КонецЦикла;
Если вы разрабатываете отчет с использованием Системы компоновки данных (СКД), высчитывать даты в коде вручную не нужно. В СКД достаточно добавить стандартные параметры НачалоПериода и КонецПериода, связать их с механизмом доступных периодов, и пользователи смогут интерактивно выбирать относительные интервалы (например, «Прошлый год», «Этот год», «Последние 12 месяцев») прямо в пользовательских настройках отчета.
---
Проанализировав проблему, мы выяснили, что исходная путаница в результатах выборки (175 против 6660) возникла из-за простой опечатки в указании года в условии запроса. Однако, помимо исправления даты, мы провели важную оптимизацию:
РАЗЛИЧНЫЕ и СГРУППИРОВАТЬ ПО, оставив только РАЗЛИЧНЫЕ.ДобавитьМесяц для передачи чистых параметров в СУБД.Применяя данные подходы в своей практике, вы обеспечите высокую скорость работы ваших отчетов и стабильное функционирование всей информационной системы 1С.