Разработка аналитических отчетов в системе 1С — одна из самых частых задач, с которыми сталкивается как начинающий, так и опытный программист. На первый взгляд, задача может показаться простой: собрать данные по закупкам, соединить их с продажами и наложить фильтр по свойствам номенклатуры. Однако при написании таких запросов легко допустить скрытые логические ошибки, которые приводят к искажению итоговых цифр (так называемому "задвоению" или "затроению" данных) и крайне низкой скорости работы отчета на реальных базах данных.
В этой статье мы подробно разберем практический кейс, проанализируем типичные ошибки начинающих разработчиков при работе с запросами, временными таблицами и виртуальными таблицами регистров накопления, а также напишем правильный, быстрый и оптимизированный запрос.
Перед нами стоит задача построить запрос, который анализирует закупки и продажи товаров за определенный период в разрезе классификации TOP GP (определенное свойство номенклатуры из регистра сведений). При этом в итоговую выборку должны попасть только те товары, по которым за указанный период было проведено более одного уникального документа поступления.
На выходе мы должны получить следующую структуру данных:
NULL).Давайте подробно рассмотрим, какие критические ошибки совершил разработчик в процессе обсуждения на форуме, почему его варианты кода выдавали неверный результат и как эти проблемы решить профессионально.
В первом варианте кода разработчик пытался наложить условие на количество документов поступления следующим образом:
ИМЕЮЩИЕ КОЛИЧЕСТВО(Закупки.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг) > 1
Затем, в сообщении 8, по совету коллег он заменил это на конструкцию:
СУММА(ВЫБОР КОГДА Закупки.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг ТОГДА 1 ИНАЧЕ 0 КОНЕЦ)
Выясним причину, почему оба этих варианта являются в корне неверными. Регистр накопления Закупки хранит физические движения документов. Если в одном документе ПоступлениеТоваровУслуг содержится несколько строк с одной и той же номенклатурой (например, товар поступил по разным ценам, с разными характеристиками или просто был разбит на несколько строк в табличной части), то при проведении этого документа в регистр запишется несколько движений с одним и тем же регистратором.
Функция КОЛИЧЕСТВО() или агрегирование через СУММА(ВЫБОР...) просто подсчитают количество записей (строк) в регистре по данной номенклатуре. В результате, если по товару был всего один документ поступления, но в нем этот товар дублировался в трех строках, система посчитает, что документов было 3, и ошибочно выведет товар в отчет. Это грубое нарушение бизнес-логики задачи.
Решение: Для корректного подсчета именно документов необходимо использовать ключевое слово РАЗЛИЧНЫЕ внутри агрегатной функции:
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Закупки.Регистратор)
Это заставит СУБД подсчитывать только уникальные идентификаторы ссылок документов-регистраторов, исключая повторения в рамках одного документа.
Рассмотрим вторую временную таблицу в запросе автора:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.КоличествоОборот КАК КоличествоПроданногоТовара
ПОМЕСТИТЬ Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&ПериодНачало, &ПериодОкончание, , ) КАК ПродажиОбороты
Проанализируем ситуацию подробнее. Виртуальная таблица Обороты без указания конкретных измерений в третьем параметре возвращает данные в разрезе всех измерений, существующих в регистре накопления Продажи. В типовых конфигурациях (например, "Управление торговлей 10.3") это могут быть измерения: Номенклатура, ХарактеристикаНоменклатуры, ЗаказПокупателя, ДоговорКонтрагента, Организация, Подразделение и другие.
Если один и тот же товар за указанный период продавался разным покупателям, с разных складов или по разным договорам, виртуальная таблица вернет множество строк для одной и той же номенклатуры. При последующем левом соединении по условию:
ПО Закупки.Номенклатура = Продажи.Номенклатура
произойдет классическое декартово произведение строк. Данные о закупках будут размножены столько раз, сколько уникальных строк продаж оказалось по этой номенклатуре. Это приведет к колоссальному искажению суммарных показателей закупок.
Решение: Данные во временной таблице продаж обязательно должны быть уникальными по ключевому полю связи — по номенклатуре. Нам необходимо принудительно свернуть (сгруппировать) продажи:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоПроданногоТовара
ПОМЕСТИТЬ Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&ПериодНачало, &ПериодОкончание, , ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура
В первом запросе автора связь со свойствами объектов реализована так:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО Закупки.Номенклатура = ЗначенияСвойствОбъектов.Объект
ГДЕ
ЗначенияСвойствОбъектов.Значение = &TOPGP
Регистр сведений ЗначенияСвойствОбъектов является классическим регистром "сущность-атрибут-значение". В нем хранятся значения для самых разных свойств одной и той же номенклатуры: цвет, размер, сезонность, ценовая группа, и в том числе — классификация "TOP GP".
Если мы накладываем условие только на поле Значение = &TOPGP, но не указываем конкретное свойство в поле Свойство, может возникнуть ситуация, когда у одного и того же товара значение параметра (например, строка "Да" или ссылка на элемент справочника) совпадет сразу для двух разных свойств (например, и для свойства "TOP GP", и для свойства "Новинка"). В этом случае товар соединится с регистром сведений дважды, что опять же приведет к удвоению строк в итоговой выборке.
Решение: В условиях соединения или в секции ГДЕ необходимо четко указывать как само значение, так и конкретный вид свойства:
ЗначенияСвойствОбъектов.Свойство = &СвойствоTOPGP
И ЗначенияСвойствОбъектов.Значение = &TOPGP
В финальном запросе разработчик выбирает поля:
Закупки.Номенклатура.Бренд КАК Бренд,
Закупки.Номенклатура.Код КАК Код
Обращение к реквизитам через точку от поля временной таблицы на этапе финального объединения заставляет платформу 1С генерировать неявные левые соединения (LEFT JOIN) с физической таблицей справочника Номенклатура. На больших объемах данных это существенно снижает производительность СУБД.
Решение: Гораздо правильнее получать все необходимые реквизиты номенклатуры (код, бренд, наименование) на самом первом этапе выборки данных во временную таблицу либо выполнять явное левое соединение со справочником в финальном пакете. Мы применим первый вариант, так как он наиболее производителен.
При использовании пакетов запросов хорошим тоном и важным требованием стандартов разработки 1С является индексация полей связи во временных таблицах. В коде автора временные таблицы Закупки и Продажи не проиндексированы по полю Номенклатура. При их соединении СУБД будет вынуждена делать полный перебор таблиц (Table Scan), что приведет к замедлению работы при росте базы данных.
Решение: Всегда добавляйте конструкцию ИНДЕКСИРОВАТЬ ПО для ключевых полей связи временных таблиц.
---
Разберем по шагам, как построить надежный, быстрый и абсолютно корректный запрос, который полностью удовлетворяет условиям задачи и не содержит вышеописанных ошибок.
Шаг 1. Получение данных по закупкам и фильтрация по уникальным документам
На первом этапе мы соберем данные по закупкам. Мы сразу выберем бренд, код и саму номенклатуру, чтобы избежать обращений "через точку" в дальнейшем. Мы свяжем данные с регистром сведений свойств, жестко ограничив выборку конкретным свойством "TOP GP". Также мы отфильтруем записи по типу регистратора (нам нужны только поступления) и наложим условие ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Закупки.Регистратор) > 1.
Шаг 2. Сбор и сворачивание данных по продажам
На втором этапе мы выберем данные о продажах из виртуальной таблицы оборотов, обязательно сгруппировав их по номенклатуре для исключения дублирования строк.
Шаг 3. Итоговое левое соединение и сортировка
На финальном этапе мы соединим закупки и продажи левым соединением. Таким образом, если продаж не было, поле количества продаж вернет NULL (что соответствует требованию "ЕСЛИ ПРОДАЖ НЕ БЫЛО, ТО = ПУСТО"). Также мы настроим сортировку по бренду от А до Я.
Посмотрим на готовый профессиональный вариант запроса:
// ШАГ 1: Собираем закупки с жестким фильтром по свойству и количеству уникальных поступлений
ВЫБРАТЬ
Закупки.Номенклатура КАК Номенклатура,
Закупки.Номенклатура.Бренд КАК Бренд,
Закупки.Номенклатура.Код КАК Код,
СУММА(Закупки.Количество) КАК КоличествоЗакупаемогоТовара,
ЗначенияСвойствОбъектов.Значение КАК TOPGP
ПОМЕСТИТЬ ВтЗакупки
ИЗ
РегистрНакопления.Закупки КАК Закупки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО Закупки.Номенклатура = ЗначенияСвойствОбъектов.Объект
ГДЕ
Закупки.Период МЕЖДУ &ПериодНачало И &ПериодОкончание
И Закупки.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
И ЗначенияСвойствОбъектов.Свойство = &СвойствоTOPGP
И ЗначенияСвойствОбъектов.Значение = &TOPGP
СГРУППИРОВАТЬ ПО
Закупки.Номенклатура,
Закупки.Номенклатура.Бренд,
Закупки.Номенклатура.Код,
ЗначенияСвойствОбъектов.Значение
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Закупки.Регистратор) > 1
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Собираем продажи, обязательно группируя их по номенклатуре для исключения дублей
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоПроданногоТовара
ПОМЕСТИТЬ ВтПродажи
ИЗ
РегистрНакопления.Продажи.Обороты(&ПериодНачало, &ПериодОкончание, , ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Соединяем данные и выводим итоговый результат
ВЫБРАТЬ
ВтЗакупки.Бренд КАК Бренд,
ВтЗакупки.Код КАК Код,
ВтЗакупки.Номенклатура КАК Номенклатура,
ВтЗакупки.КоличествоЗакупаемогоТовара КАК КоличествоЗакупаемогоТовара,
ВтПродажи.КоличествоПроданногоТовара КАК КоличествоПроданногоТовара,
ВтЗакупки.TOPGP КАК TOPGP
ИЗ
ВтЗакупки КАК ВтЗакупки
ЛЕВОЕ СОЕДИНЕНИЕ ВтПродажи КАК ВтПродажи
ПО ВтЗакупки.Номенклатура = ВтПродажи.Номенклатура
УПОРЯДОЧИТЬ ПО
Бренд
Проанализируем преимущества представленного нами решения перед кодом, который обсуждался на форуме:
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Закупки.Регистратор) гарантирует, что система посчитает именно уникальные физические документы поступления, а не количество строк в табличной части.ВтПродажи и строгому ограничению по конкретному свойству (ЗначенияСвойствОбъектов.Свойство = &СвойствоTOPGP) данные никогда не задвоятся при левом соединении. Показатели останутся абсолютно точными.Номенклатура. Это позволяет СУБД мгновенно соединять данные даже на базах с миллионами записей.Бренд и Код на этап формирования временной таблицы. Это исключает лишние скрытые соединения в SQL-запросе.УПОРЯДОЧИТЬ ПО Бренд, а отсутствие продаж корректно вернет пустое значение (NULL).Используйте этот шаблон проектирования аналитических запросов в своей работе. Понимание механизмов агрегации и соединения таблиц — это ключ к созданию быстрых и надежных отчетов в 1С!