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