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