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