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