Разработка сложных отчетов в системе 1С:Предприятие часто сопровождается трудностями, связанными с получением корректных количественно-суммовых показателей. Две наиболее распространенные проблемы, с которыми сталкиваются как начинающие, так и опытные разработчики — это "двоение" (дублирование) строк при соединении таблиц разной детальности и пропуски дат при выводе остатков за период. В этой статье мы подробно проанализируем обе проблемы на практическом примере, выясним причины их возникновения и разберем проверенные архитектурные решения.
Рассмотрим ситуацию, когда при компоновке запроса по остаткам резервов, партий и продажам в итоговой таблице начинают дублироваться строки номенклатуры, а результирующие суммы искажаются (одни уходят в минус, другие многократно увеличиваются). Выясним причину этой проблемы, обратившись к исходному коду соединения временных таблиц:
ПОЛНОЕ СОЕДИНЕНИЕ Вт2 КАК Вт2
ПО РезервыОстатки.Номенклатура = Вт2.Номенклатура
Для того чтобы понять, почему здесь происходит логическая ошибка, проанализируем состав и уровень группировки временной таблицы Вт2. Данная таблица выбирает остатки из регистра накопления ПартииТоваров.Остатки в разрезе полей Номенклатура и СерияНоменклатуры:
ВЫБРАТЬ
ПартииТоваровОстатки.Номенклатура КАК Номенклатура,
ПартииТоваровОстатки.СерияНоменклатуры КАК СерияНоменклатуры,
ПартииТоваровОстатки.КоличествоОстаток КАК КоличествоОстаток,
ПартииТоваровОстатки.СтоимостьОстаток КАК СтоимостьОстаток
ПОМЕСТИТЬ Вт2
ИЗ
РегистрНакопления.ПартииТоваров.Остатки(...) КАК ПартииТоваровОстатки
...
СГРУППИРОВАТЬ ПО
ПартииТоваровОстатки.Номенклатура,
ПартииТоваровОстатки.СерияНоменклатуры,
ПартииТоваровОстатки.КоличествоОстаток,
ПартииТоваровОстатки.СтоимостьОстаток
Здесь кроется ключевое несоответствие уровней детализации (разрезов учета) двух соединяемых таблиц:
Вт2 содержит информацию, детализированную до конкретной серии товара. Если по одной позиции Номенклатура на складе числится 3 разные серии, то в этой таблице для данного товара будет ровно 3 строки.РезервыОстатки (или Вт4) содержит данные по резервам, где учет по сериям может не вестись вовсе, либо данные не сопоставлены по сериям на этапе соединения.ПОЛНОЕ СОЕДИНЕНИЕ (или ЛЕВОЕ СОЕДИНЕНИЕ) по условию связи исключительно по одному полю ПО РезервыОстатки.Номенклатура = Вт2.Номенклатура СУБД выполняет частичное декартово произведение. Каждая строка из таблицы резервов связывается с каждой строкой из таблицы партий для этой номенклатуры. В результате одна строка резерва размножается на 3 строки в итоговой выборке.Это приводит к фатальному искажению математических расчетов. Посмотрим на расчетное поле стоимости резерва:
СУММА(РезервыОстатки.КоличествоКонечныйОстаток * Вт2.СтоимостьОстаток / Вт2.КоличествоОстаток) КАК СуммаРезервов
Поскольку строка резерва продублировалась трижды, СУБД на каждую из трех строк наложит полный объем зарезервированного количества КоличествоКонечныйОстаток, умножит его на себестоимость конкретной серии и сложит эти результаты при группировке. Итоговая сумма резерва окажется колоссально завышенной, а складские остатки распределятся некорректно.
Чтобы исключить хаотичное размножение строк, необходимо привести обе таблицы к единой "плоскости" (одинаковому уровню детализации) еще до момента их физического связывания.
Способ А. Расчет средневзвешенной цены (сворачивание по номенклатуре)
Если в отчете не требуется выводить серии номенклатуры, а нужно лишь получить общую стоимость резервов по средней себестоимости, мы должны предварительно сгруппировать таблицу партий Вт2 строго по полю Номенклатура, избавившись от детального поля СерияНоменклатуры:
ВЫБРАТЬ
ПартииТоваровОстатки.Номенклатура КАК Номенклатура,
СУММА(ПартииТоваровОстатки.КоличествоОстаток) КАК КоличествоОстаток,
СУММА(ПартииТоваровОстатки.СтоимостьОстаток) КАК СтоимостьОстаток
ПОМЕСТИТЬ Вт2_Свернутая
ИЗ
РегистрНакопления.ПартииТоваров.Остатки(...) КАК ПартииТоваровОстатки
СГРУППИРОВАТЬ ПО
ПартииТоваровОстатки.Номенклатура
После такого сворачивания связь между таблицами станет строго «один-к-одному» (одна номенклатура в резервах связывается ровно с одной строкой агрегированных партий), и двоение полностью исчезнет.
Способ Б. Связь по полному ключу аналитики
Если серии номенклатуры принципиально важны для отчета и резервы также ведутся в разрезе серий, необходимо обязательно добавить поле СерияНоменклатуры в таблицу резервов и дополнить условие связи в предложении ПО всеми ключевыми полями аналитики:
ПОЛНОЕ СОЕДИНЕНИЕ Вт2 КАК Вт2
ПО РезервыОстатки.Номенклатура = Вт2.Номенклатура
И РезервыОстатки.СерияНоменклатуры = Вт2.СерияНоменклатуры
Способ В. Отказ от соединений в пользу объединения (Best Practice в 1С)
В крупноблочных запросах с большим объемом данных использование соединений (особенно ПОЛНОГО СОЕДИНЕНИЯ) крайне негативно сказывается на производительности СУБД. Профессиональным стандартом разработки на платформе 1С является использование конструкции ОБЪЕДИНИТЬ ВСЕ с последующей группировкой результирующего пакета. Посмотрим, как переписать структуру такого запроса:
ВЫБРАТЬ
Резервы.Номенклатура КАК Номенклатура,
Резервы.КоличествоОстаток КАК КоличествоРезерв,
0 КАК СтоимостьПартии,
0 КАК КоличествоПартии
ПОМЕСТИТЬ ВТ_Объединенная
ИЗ
РегистрНакопления.Резервы.Остатки(...) КАК Резервы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Партии.Номенклатура,
0,
Партии.СтоимостьОстаток,
Партии.КоличествоОстаток
ИЗ
РегистрНакопления.ПартииТоваров.Остатки(...) КАК Партии
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Объединенная.Номенклатура КАК Номенклатура,
СУММА(ВТ_Объединенная.КоличествоРезерв) КАК КоличествоРезерв,
СУММА(ВТ_Объединенная.СтоимостьПартии) КАК СтоимостьПартии,
СУММА(ВТ_Объединенная.КоличествоПартии) КАК КоличествоПартии
ИЗ
ВТ_Объединенная КАК ВТ_Объединенная
СГРУППИРОВАТЬ ПО
ВТ_Объединенная.Номенклатура
Такой подход полностью исключает риск декартова произведения строк, работает значительно быстрее на уровне СУБД (так как отсутствуют тяжелые операции сканирования индексов при соединении) и гораздо проще поддается оптимизации.
При расчете себестоимости единицы товара по формуле СтоимостьОстаток / КоличествоОстаток всегда существует риск аварийного завершения работы СУБД из-за деления на ноль. В реальной базе данных возможны ситуации, когда количественный остаток списался в ноль, а копеечная стоимость из-за погрешностей округления осталась на остатках, либо СУБД вернула значение NULL при левом соединении.
Чтобы обезопасить расчеты, обязательно оборачивайте математические операции в условные конструкции ВЫБОР:
ВЫБОР
КОГДА ЕСТЬNULL(Вт2.КоличествоОстаток, 0) = 0
ТОГДА 0
ИНАЧЕ РезервыОстатки.КоличествоКонечныйОстаток * ЕСТЬNULL(Вт2.СтоимостьОстаток, 0) / Вт2.КоличествоОстаток
КОНЕЦ КАК СуммаРезервов
Этот простой шаг гарантирует стабильность работы ваших отчетов независимо от корректности данных в регистрах учета.
В исходном запросе автора для получения конечного остатка резервов использовалась виртуальная таблица ОстаткиИОбороты:
РегистрНакопления.Резервы.ОстаткиИОбороты(&ДатаНачало, &ДатаКонец, День, , ) КАК РезервыОстатки
Это классическая архитектурная ошибка. Виртуальная таблица ОстаткиИОбороты является наиболее тяжелой для СУБД, так как под капотом она выполняет сложные расчеты по сопоставлению начальных остатков, приходов, расходов и конечных остатков. Использовать ее для получения простого среза остатков на конкретную дату категорически не рекомендуется.
Для получения актуальных остатков всегда следует использовать специализированную виртуальную таблицу Остатки. Она работает на порядок быстрее, поскольку считывает готовые рассчитанные итоги из таблиц остатков СУБД:
РегистрНакопления.Резервы.Остатки(&ДатаКонец, ) КАК РезервыОстатки
---
Вторая частая проблема при построении отчетов по динамике остатков заключается в том, что виртуальная таблица выдает информацию только на те даты, когда в системе происходили реальные движения (обороты) по аналитике. Если по товару было движение 1-го числа, а следующее произошло только 10-го числа, стандартный запрос вернет данные только по этим двум датам. Однако бизнес-пользователям необходимо видеть актуальный остаток на каждый день периода (например, чтобы знать, что со 2-го по 9-е число товар физически находился на складе в резерве).
Проанализируем три основных метода реализации данной задачи в системе 1С.
Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), решать эту задачу "чистым" запросом не рекомендуется. СКД содержит мощный встроенный функционал автоматического дополнения периодов, который работает максимально эффективно.
Разберем по шагам, как настроить дополнение дат в СКД:
Период или Дата), в колонке «Роль» нажмите кнопку настройки.День) и выберите родительское поле (если используется иерархия периодов).Период и откройте вкладку «Системные поля» (или свойства этой группировки).&ДатаНачало и &ДатаКонец).При такой настройке СКД сама сгенерирует недостающие даты и автоматически "протянет" (продублирует) значения остатков на все пустые дни, когда движений по регистру не было.
Если отчет строится без использования СКД (например, результат нужно выгрузить в таблицу значений в программном коде), задачу приходится решать на уровне запроса СУБД. Для этого используется метод связи с непрерывным календарем и расчет нарастающего итога.
Проанализируем структуру такого запроса по шагам:
Шаг 1. Получение непрерывного списка дат
Источником непрерывных дат служит регистр сведений ДанныеПроизводственногоКалендаря:
ВЫБРАТЬ
Календарь.Дата КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.ДанныеПроизводственногоКалендаря КАК Календарь
ГДЕ
Календарь.Дата МЕЖДУ &ДатаНачало И &ДатаКонец
Шаг 2. Создание координатной сетки (Декартово произведение дат и товаров)
Чтобы гарантировать наличие строки для каждого товара на каждый день периода, мы соединяем уникальный список товаров с календарем без условий соединения (или по условию ИСТИНА):
ВЫБРАТЬ
Товары.Ссылка КАК Номенклатура,
ВТ_Календарь.Период КАК Период
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
Справочник.Номенклатура КАК Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Календарь КАК ВТ_Календарь
ПО ИСТИНА
Шаг 3. Соединение сетки с оборотами и расчет нарастающего итога
Теперь мы связываем каждую точку нашей координатной сетки [Товар + Дата] со всеми движениями, которые произошли с начала времен до этой конкретной даты включительно:
ВЫБРАТЬ
Сетка.Номенклатура КАК Номенклатура,
Сетка.Период КАК Период,
СУММА(ЕСТЬNULL(Движения.СуммаОборот, 0)) КАК ОстатокНаДату
ИЗ
ВТ_Сетка КАК Сетка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(, &ДатаКонец, День, ) КАК Движения
ПО Сетка.Номенклатура = Движения.Номенклатура
И Движения.Период <= Сетка.Период
СГРУППИРОВАТЬ ПО
Сетка.Номенклатура,
Сетка.Период
Важное предупреждение: Данный метод является крайне ресурсоемким для СУБД. При больших объемах справочника номенклатуры и длинных периодах (например, год) размер таблицы ВТ_Сетка может превысить миллионы строк, что приведет к перегрузке памяти и зависанию сервера. Используйте этот метод с осторожностью, ограничивая выборку товаров жесткими фильтрами.
Если данные требуются внутри программного алгоритма (например, для проведения документов или сложных расчетов в модуле менеджера), наиболее производительным решением будет программный обход выборки в коде на сервере.
Рассмотрим алгоритм реализации:
ТаблицуЗначений.ТаблицуЗначений с колонками: Дата, Номенклатура, Остаток.&ДатаНачало до &ДатаКонец с шагом в один день (86400 секунд).Этот способ работает в десятки раз быстрее сложных SQL-запросов с нарастающим итогом, так как линейная обработка массива в оперативной памяти сервера приложений 1С минимизирует количество тяжелых дисковых операций чтения-записи СУБД.