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