При разработке отчетов и аналитических выборок в системе 1С:Предприятие программисты часто сталкиваются с задачей получения первой или последней продажи по каждому контрагенту за определенный период. На первый взгляд задача кажется простой, однако попытки решить ее "в лоб" с помощью одного простого запроса с группировкой приводят к серьезным ошибкам в данных и критическому снижению производительности базы данных. В этой статье мы подробно разберем, какие типичные ошибки совершают разработчики, почему они возникают и как построить оптимальный, быстродействующий запрос с использованием временных таблиц.
Рассмотрим классический некорректный запрос, который часто пытаются использовать начинающие разработчики:
ВЫБРАТЬ
РасчетыСКонтрагентами.Регистратор.Ссылка,
МАКСИМУМ(РасчетыСКонтрагентами.Регистратор.Дата) КАК РегистраторДата,
РасчетыСКонтрагентами.Регистратор.Организация.Наименование,
РасчетыСКонтрагентами.Регистратор.Контрагент.Наименование
ИЗ
РегистрНакопления.РасчетыСКонтрагентами КАК РасчетыСКонтрагентами
ГДЕ
РасчетыСКонтрагентами.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
СГРУППИРОВАТЬ ПО
РасчетыСКонтрагентами.Регистратор.Ссылка,
РасчетыСКонтрагентами.Регистратор.Организация.Наименование,
РасчетыСКонтрагентами.Регистратор.Контрагент.Наименование
Проанализируем, почему этот запрос работает неправильно и не решает поставленную задачу:
СГРУППИРОВАТЬ ПО включено уникальное поле Регистратор.Ссылка. Поскольку ссылка на каждый документ уникальна, группировка будет происходить по каждому конкретному документу отдельно. Функция МАКСИМУМ для даты в таком случае просто вернет дату этого же документа, а запрос выведет абсолютно все записи регистра, соответствующие условию, вместо одной последней.Регистратор в регистрах накопления 1С всегда имеет составной тип данных, так как регистраторами могут выступать десятки различных документов. Обращение к реквизитам через точку (например, Регистратор.Организация) заставляет платформу 1С генерировать SQL-запрос с неявным использованием оператора LEFT JOIN ко всем таблицам документов, которые могут делать движения по этому регистру. Если в конфигурации 50 возможных регистраторов, СУБД выполнит 50 скрытых соединений, что приведет к катастрофическому падению производительности на реальной базе данных..Наименование для справочников внутри запроса создает лишнее соединение с таблицей справочника. В 1С представление ссылки формируется автоматически при выводе данных, поэтому получать наименование строкой в запросе не нужно. К тому же, измерения Организация и Контрагент уже присутствуют в самом регистре.Другая частая ошибка возникает при попытке получить сумму первой или последней продажи. Разработчики пытаются использовать конструкцию СУММА(РасчетыСКонтрагентами.Сделка.СуммаДокумента) с группировкой по контрагенту. Выясним причину, почему это приводит к неверным результатам:
СУММА сложит показатели всех документов, а не выведет сумму конкретной первой продажи.Чтобы получить точные данные без дублирования сумм и избыточной нагрузки на сервер СУБД, разберем процесс построения оптимального запроса по шагам. Мы будем использовать механизм временных таблиц (ВТ).
Шаг 1: Получение минимальной даты движения для каждого контрагента.
Сначала мы определим самую раннюю дату продажи для каждого контрагента и организации за выбранный интервал времени. Для этого обратимся к реальным измерениям регистра, избегая обращения через Регистратор. Результат сохраним во временную таблицу ВТ_МинимальныеДаты.
ВЫБРАТЬ
РасчетыСКонтрагентами.Организация КАК Организация,
РасчетыСКонтрагентами.Контрагент КАК Контрагент,
МИНИМУМ(РасчетыСКонтрагентами.Период) КАК МинимальныйПериод
ПОМЕСТИТЬ ВТ_МинимальныеДаты
ИЗ
РегистрНакопления.РасчетыСКонтрагентами КАК РасчетыСКонтрагентами
ГДЕ
РасчетыСКонтрагентами.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
И РасчетыСКонтрагентами.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
СГРУППИРОВАТЬ ПО
РасчетыСКонтрагентами.Организация,
РасчетыСКонтрагентами.Контрагент
;
Шаг 2: Определение конкретного документа продажи.
На этом шаге мы сопоставим полученную минимальную дату с реальным документом-регистратором. Поскольку теоретически в одну и ту же секунду может быть проведено несколько документов по одному контрагенту, мы применим функцию МИНИМУМ(Регистратор) для обеспечения уникальности ссылки. Результат запишем во временную таблицу ВТ_ПервыеДокументы.
ВЫБРАТЬ
ВТ_МинимальныеДаты.Организация КАК Организация,
ВТ_МинимальныеДаты.Контрагент КАК Контрагент,
МИНИМУМ(РасчетыСКонтрагентами.Регистратор) КАК Регистратор
ПОМЕСТИТЬ ВТ_ПервыеДокументы
ИЗ
ВТ_МинимальныеДаты КАК ВТ_МинимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКонтрагентами КАК РасчетыСКонтрагентами
ПО ВТ_МинимальныеДаты.Организация = РасчетыСКонтрагентами.Организация
И ВТ_МинимальныеДаты.Контрагент = РасчетыСКонтрагентами.Контрагент
И ВТ_МинимальныеДаты.МинимальныйПериод = РасчетыСКонтрагентами.Период
ГДЕ
РасчетыСКонтрагентами.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
СГРУППИРОВАТЬ ПО
ВТ_МинимальныеДаты.Организация,
ВТ_МинимальныеДаты.Контрагент
;
Шаг 3: Получение точных реквизитов и суммы документа.
Имея точную ссылку на уникальный документ-регистратор для каждого контрагента, мы можем соединить временную таблицу напрямую с физической таблицей документа Документ.РеализацияТоваровУслуг. Это позволит извлечь точную сумму продажи СуммаДокумента напрямую из реквизитов документа без искажений и группировок.
ВЫБРАТЬ
ВТ_ПервыеДокументы.Организация КАК Организация,
ВТ_ПервыеДокументы.Контрагент КАК Контрагент,
ВТ_ПервыеДокументы.Регистратор КАК ДокументПродажи,
Реализация.Дата КАК ДатаДокумента,
Реализация.СуммаДокумента КАК СуммаПервойПродажи
ИЗ
ВТ_ПервыеДокументы КАК ВТ_ПервыеДокументы
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
ПО ВТ_ПервыеДокументы.Регистратор = Реализация.Ссылка
Иногда бизнес-задача требует вывести только абсолютно новых контрагентов, то есть исключить тех покупателей, у которых уже были продажи в прошлых периодах (до даты начала анализируемого интервала). Рассмотрим, как реализовать это условие наиболее производительным способом с помощью левого соединения.
Добавим в наш пакет запросов предварительную временную таблицу, которая соберет всех клиентов, совершавших покупки ранее:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РасчетыСКонтрагентами.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_СтарыеКлиенты
ИЗ
РегистрНакопления.РасчетыСКонтрагентами КАК РасчетыСКонтрагентами
ГДЕ
РасчетыСКонтрагентами.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
И РасчетыСКонтрагентами.Период < &ДатаНачала
;
Теперь на первом шаге нашего основного алгоритма мы просто исключим этих контрагентов, используя левое соединение и проверку на значение NULL в секции ГДЕ:
ВЫБРАТЬ
РасчетыСКонтрагентами.Организация КАК Организация,
РасчетыСКонтрагентами.Контрагент КАК Контрагент,
МИНИМУМ(РасчетыСКонтрагентами.Период) КАК МинимальныйПериод
ПОМЕСТИТЬ ВТ_МинимальныеДаты
ИЗ
РегистрНакопления.РасчетыСКонтрагентами КАК РасчетыСКонтрагентами
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СтарыеКлиенты КАК ВТ_СтарыеКлиенты
ПО РасчетыСКонтрагентами.Контрагент = ВТ_СтарыеКлиенты.Контрагент
ГДЕ
РасчетыСКонтрагентами.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
И РасчетыСКонтрагентами.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И ВТ_СтарыеКлиенты.Контрагент ЕСТЬ NULL
СГРУППИРОВАТЬ ПО
РасчетыСКонтрагентами.Организация,
РасчетыСКонтрагентами.Контрагент
Этот подход гарантирует высокую скорость выполнения даже на базах данных с миллионами записей, так как СУБД будет использовать индексы по полям Контрагент и Период, минимизируя нагрузку на сервер.