При разработке отчетов на платформе 1С:Предприятие программисты часто сталкиваются с классической архитектурной задачей: необходимо вывести в одном отчете данные о продажах (в разрезе организаций) и данные о поступлении (приходе) товаров (в разрезе конкретных складов). Сложность заключается в том, что в типовых конфигурациях (особенно исторического поколения, таких как «Управление торговлей 10.3», «Управление производственным предприятием 1.3») эти данные распределены по разным регистрам накопления, не имеющим сквозной аналитики.
Давайте подробно разберем, почему возникает эта проблема, проанализируем типичные ошибки при составлении запроса, рассмотрим предложенные на форуме варианты и разработаем максимально эффективное и быстрое решение в соответствии со стандартами разработки 1С.
Рассмотрим структуру типовых регистров, которые обычно используются для решения этой задачи:
Организация, Номенклатура, Контрагент и т.д. Важно: в этом регистре, как правило, нет измерения «Склад».Номенклатура, Склад, ХарактеристикаНоменклатуры. Важно: в этом регистре нет измерения «Организация».Когда мы пытаемся связать эти две таблицы простым левым соединением по полю Номенклатура, СУБД выполняет декартово произведение для каждой пары записей, где совпадает номенклатура. Выясним причину на простом примере:
Если связать таблицы только по номенклатуре, то приход в количестве 200 метров привяжется и к первой, и во второй организации. В результате в отчете мы увидим общую сумму прихода 400 метров, что является грубейшей ошибкой и искажением управленческой отчетности.
Рассмотрим по шагам, как можно решить эту проблему, двигаясь от простого (но медленного) варианта к профессиональному и быстродействующему.
Если в вашей конфигурации справочник Склады имеет реквизит Организация (владелец или основное юрлицо, за которым закреплен склад), мы можем использовать его в качестве дополнительного условия связи. На это решение натолкнули участники обсуждения в сообщениях 6 и 17.
Давайте проанализируем итоговый запрос, который учитывает группировку данных и связь по организации склада:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ПродажиОбороты.Организация КАК Организация,
ПродажиОбороты.Номенклатура КАК Номенклатура,
СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот,
СУММА(ПродажиОбороты.СтоимостьОборот) КАК СтоимостьОборот,
СУММА(ЕСТЬNULL(ТоварыНаСкладахОбороты.КоличествоПриход, 0)) КАК КоличествоПриход
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНач, &ДатаКон, , ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты(&ДатаНач, &ДатаКон, , Склад В (&Склад)) КАК ТоварыНаСкладахОбороты
ПО ПродажиОбороты.Номенклатура = ТоварыНаСкладахОбороты.Номенклатура
И ПродажиОбороты.Организация = ТоварыНаСкладахОбороты.Склад.Организация
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура,
ПродажиОбороты.Организация
Важное замечание по оптимизации: обратите внимание, что вместо виртуальной таблицы ОстаткиИОбороты мы использовали таблицу Обороты (как справедливо заметил участник в сообщении 22). Таблица ОстаткиИОбороты является крайне «тяжелой» для СУБД, так как рассчитывает остатки на каждую контрольную точку периода. Если нам нужен только приход, использование Обороты существенно ускорит выполнение запроса.
ПродажиОбороты.Организация = ТоварыНаСкладахОбороты.Склад.Организация) заставляет СУБД выполнять неявное соединение таблиц (Implicit Join) прямо во время связи. На базах данных большого объема этот запрос будет вызывать серьезные задержки и неоптимальную работу индексов.Если на предприятии используется партионный учет, то лучшим решением будет отказаться от связки регистров «Продажи» и «Товары на складах». Вместо этого мы обратимся к регистру «Партии товаров на складах». Этот регистр уникален тем, что содержит в себе абсолютно все необходимые нам аналитические разрезы: и организацию, и склад, и номенклатуру.
Рассмотрим пример запроса, получающего данные из одной таблицы:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ПартииОбороты.Организация КАК Организация,
ПартииОбороты.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР
КОГДА ПартииОбороты.КодОперации В (ЗНАЧЕНИЕ(КодОперации.Продажа), ЗНАЧЕНИЕ(КодОперации.Реализация))
ТОГДА ПартииОбороты.КоличествоРасход
ИНАЧЕ 0
КОНЕЦ) КАК КоличествоОборот,
СУММА(ПартииОбороты.КоличествоПриход) КАК КоличествоПриход
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Обороты(&ДатаНач, &ДатаКон, , Склад В (&Склад)) КАК ПартииОбороты
СГРУППИРОВАТЬ ПО
ПартииОбороты.Организация,
ПартииОбороты.Номенклатура
Такой подход полностью исключает необходимость каких-либо соединений. Мы получаем абсолютно точные данные по приходам и расходам в рамках конкретной организации и конкретного склада за один простой и максимально быстрый запрос.
Что делать, если учет партий не ведется или данные в нем неактуальны? В этом случае нам необходимо связать продажи и складские движения. Сделать это эффективно можно только путем извлечения информации о складе из документов-регистраторов продаж.
Давайте реализуем этот алгоритм с помощью временных таблиц. Это позволит нам избежать обращения через точку в условиях связи и обеспечит максимальное быстродействие.
Шаг 1. Получаем продажи с определением склада из регистратора.
Мы выберем данные о продажах и попробуем определить склад, обратившись к документам движения. Для оптимизации используем конструкцию ВЫРАЗИТЬ, чтобы СУБД знала точный тип документа-регистратора.
ВЫБРАТЬ РАЗРЕШЕННЫЕ
Продажи.Организация КАК Организация,
Продажи.Номенклатура КАК Номенклатура,
ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Склад КАК Склад,
Продажи.Количество КАК КоличествоОборот,
Продажи.Стоимость КАК СтоимостьОборот
ПОМЕСТИТЬ ВТ_ПродажиСоСкладом
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &ДатаНач И &ДатаКон
И Продажи.Номенклатура = &Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ПродажиСоСкладом.Организация КАК Организация,
ВТ_ПродажиСоСкладом.Номенклатура КАК Номенклатура,
ВТ_ПродажиСоСкладом.Склад КАК Склад,
СУММА(ВТ_ПродажиСоСкладом.КоличествоОборот) КАК КоличествоОборот,
СУММА(ВТ_ПродажиСоСкладом.СтоимостьОборот) КАК СтоимостьОборот
ПОМЕСТИТЬ ВТ_СгруппированныеПродажи
ИЗ
ВТ_ПродажиСоСкладом КАК ВТ_ПродажиСоСкладом
ГДЕ
ВТ_ПродажиСоСкладом.Склад В(&Склад)
СГРУППИРОВАТЬ ПО
ВТ_ПродажиСоСкладом.Организация,
ВТ_ПродажиСоСкладом.Номенклатура,
ВТ_ПродажиСоСкладом.Склад
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Склад
;
Шаг 2. Получаем обороты прихода по складу во временную таблицу.
Выберем чистые приходы по складу из таблицы оборотов складского регистра.
ВЫБРАТЬ
ТоварыНаСкладахОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОбороты.Склад КАК Склад,
СУММА(ТоварыНаСкладахОбороты.КоличествоПриход) КАК КоличествоПриход
ПОМЕСТИТЬ ВТ_Приходы
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&ДатаНач, &ДатаКон, , Склад В (&Склад)) КАК ТоварыНаСкладахОбороты
ГДЕ
ТоварыНаСкладахОбороты.Номенклатура = &Номенклатура
СГРУППИРОВАТЬ ПО
ТоварыНаСкладахОбороты.Номенклатура,
ТоварыНаСкладахОбороты.Склад
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Склад
;
Шаг 3. Финальное объединение таблиц по проиндексированным полям.
Теперь мы можем объединить полученные временные таблицы. Так как обе таблицы содержат разрезы Номенклатура и Склад, связь будет максимально точной и быстрой, без образования ложных дублей строк.
ВЫБРАТЬ
ВТ_СгруппированныеПродажи.Организация КАК Организация,
ВТ_СгруппированныеПродажи.Номенклатура КАК Номенклатура,
ВТ_СгруппированныеПродажи.КоличествоОборот КАК КоличествоОборот,
ВТ_СгруппированныеПродажи.СтоимостьОборот КАК СтоимостьОборот,
ЕСТЬNULL(ВТ_Приходы.КоличествоПриход, 0) КАК КоличествоПриход
ИЗ
ВТ_СгруппированныеПродажи КАК ВТ_СгруппированныеПродажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Приходы КАК ВТ_Приходы
ПО ВТ_СгруппированныеПродажи.Номенклатура = ВТ_Приходы.Номенклатура
И ВТ_СгруппированныеПродажи.Склад = ВТ_Приходы.Склад
Мы детально проанализировали проблему дублирования записей при связи многомерных складских таблиц и таблиц продаж. В ходе совместной работы мы пришли к следующим выводам:
Склад.Организация решает проблему только в случае жесткой привязки складов к юрлицам и замедляет работу СУБД.ВЫРАЗИТЬ.