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