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