При решении задач складского и торгового учета разработчики часто сталкиваются с необходимостью построить единый аналитический отчет по контрагентам. Типичная задача: вывести список всех партнеров (как поставщиков, так и покупателей) и подсчитать для каждого из них суммарное количество различных наименований товаров, с которыми они работали. При этом партнер может одновременно выступать и как поставщик, и как покупатель, а одни и те же позиции могут встречаться в разных документах неоднократно.
Рассмотрим частые ошибки, возникающие при попытке решить эту задачу "в лоб", выясним причины некорректного поведения запроса и подробно разберем правильные варианты реализации на платформе 1С:Предприятие.
Проанализируем структуру ошибочного запроса, в котором таблицы документов перечисляются через запятую в секции ИЗ:
ИЗ (например, Документ.ПоступлениеТоваров.Товары, Документ.РеализацияТоваров.Товары) приводит к полному перемножению строк поступления на строки реализации. В результате СУБД формирует гигантский объем строк, а функция КОЛИЧЕСТВО выдает многократно завышенные и недостоверные значения.Поставщик и Покупатель попадают в разные колонки выборки и группировки. В итоговом отчете контрагент должен выступать единым аналитическим разрезом (измерением), независимо от роли в конкретной сделке.КОЛИЧЕСТВО(Номенклатура) без квалификатора РАЗЛИЧНЫЕ или без предварительного схлопывания дублей приводит к подсчету физического числа строк в накладных. Если контрагент приобрел один и тот же товар в десяти разных накладных, он будет ошибочно посчитан 10 раз вместо одного.Разберем наиболее лаконичный и производительный подход. Чтобы получить уникальные пары Контрагент — Номенклатура, объединим данные поступлений и реализаций с помощью оператора ОБЪЕДИНИТЬ (без ключевого слова ВСЕ). Оператор ОБЪЕДИНИТЬ на уровне СУБД автоматически выполняет операцию DISTINCT и схлопывает абсолютно одинаковые строки.
Если один и тот же контрагент поставлял товар «Карандаш» и покупал товар «Карандаш», во вложенном запросе останется ровно одна запись. После этого во внешнем запросе останется сгруппировать результат по контрагенту и применить агрегатную функцию КОЛИЧЕСТВО.
Посмотрим на пример реализации:
ВЫБРАТЬ
ВложенныйЗапрос.Контрагент КАК Контрагент,
КОЛИЧЕСТВО(ВложенныйЗапрос.Номенклатура) КАК КоличествоНаименований
ИЗ
(ВЫБРАТЬ
ПоступлениеТовары.Ссылка.Поставщик КАК Контрагент,
ПоступлениеТовары.Номенклатура КАК Номенклатура
ИЗ
Документ.ПоступлениеТоваров.Товары КАК ПоступлениеТовары
ГДЕ
ПоступлениеТовары.Ссылка.Проведен = ИСТИНА
ОБЪЕДИНИТЬ
ВЫБРАТЬ
РеализацияТовары.Ссылка.Покупатель КАК Контрагент,
РеализацияТовары.Номенклатура КАК Номенклатура
ИЗ
Документ.РеализацияТоваров.Товары КАК РеализацияТовары
ГДЕ
РеализацияТовары.Ссылка.Проведен = ИСТИНА) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Контрагент
УПОРЯДОЧИТЬ ПО
Контрагент
Рассмотрим альтернативную постановку задачи: требуется посчитать сумму уникальных позиций закупки и уникальных позиций продажи по отдельности. Например, если контрагент поставил 2 уникальных товара и купил 3 уникальных товара (даже если среди них есть одинаковые позиции), в итоговом результате должно получиться 5.
Для этого в каждой ветке запроса посчитаем КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Номенклатура), объединим результаты оператором ОБЪЕДИНИТЬ ВСЕ и выполним суммирование во внешнем запросе:
ВЫБРАТЬ
Т.Контрагент КАК Контрагент,
СУММА(Т.КоличествоНаименований) КАК КоличествоНаименований
ИЗ
(ВЫБРАТЬ
ПоступлениеТовары.Ссылка.Поставщик КАК Контрагент,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПоступлениеТовары.Номенклатура) КАК КоличествоНаименований
ИЗ
Документ.ПоступлениеТоваров.Товары КАК ПоступлениеТовары
ГДЕ
ПоступлениеТовары.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ПоступлениеТовары.Ссылка.Поставщик
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РеализацияТовары.Ссылка.Покупатель КАК Контрагент,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РеализацияТовары.Номенклатура) КАК КоличествоНаименований
ИЗ
Документ.РеализацияТоваров.Товары КАК РеализацияТовары
ГДЕ
РеализацияТовары.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
РеализацияТовары.Ссылка.Покупатель) КАК Т
СГРУППИРОВАТЬ ПО
Т.Контрагент
УПОРЯДОЧИТЬ ПО
Контрагент
ПоступлениеТовары.Ссылка.Поставщик автоматически преобразуется платформой 1С в неявное левое соединение с таблицей документа. Это исключает необходимость вручную прописывать секции ЛЕВОЕ СОЕДИНЕНИЕ для извлечения реквизитов шапки и делает текст запроса более компактным и читаемым.Ссылка.Проведен = ИСТИНА, чтобы черновики и помеченные на удаление документы не искажали итоговую статистику.Закупки и ВыручкаИСебестоимостьПродаж).