При работе с системой 1С часто возникает необходимость формирования отчетов о продажах за различные периоды, причем с возможностью гибкой настройки этих периодов и отображения данных в иерархическом виде. Это может быть как отчет за несколько фиксированных интервалов (например, "этот месяц", "прошлый месяц", "год назад"), так и сравнение одних и тех же интервалов, но за разные годы (например, с 1 по 14 марта 2023 года и с 1 по 14 марта 2022 года). Давайте подробно рассмотрим, как лучше подойти к решению этой задачи, используя возможности 1С:Предприятия.
Основная сложность заключается в том, что количество сравниваемых периодов может быть неограниченным и задаваться пользователем в виде списка значений. Нам нужно не только получить данные за каждый из этих периодов, но и иметь возможность их сравнивать или отображать иерархически. Рассмотрим несколько подходов, которые помогут нам в этом.
Один из возможных вариантов решения задачи – это последовательное выполнение запросов для каждого из выбранных пользователем периодов, с сохранением результатов во временные таблицы и последующим их объединением или соединением. Этот подход может быть предпочтителен, если список периодов действительно произвольный и не всегда является непрерывным диапазоном дат.
Шаги реализации:
Создание первой временной таблицы для первого периода: Начнем с получения данных для первого периода. Результат этого запроса мы поместим во временную таблицу.
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Количество) КАК Количество,
СУММА(Продажи.Стоимость) КАК Стоимость
ПОМЕСТИТЬ ВТ_Период1
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода1 И &КонецПериода1
И Продажи.Активность = ИСТИНА
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура;
В этом запросе мы выбираем номенклатуру, суммарное количество и стоимость продаж за первый заданный интервал, используя параметры &НачалоПериода1 и &КонецПериода1. Результат сохраняем во временную таблицу ВТ_Период1.
Добавление данных для последующих периодов: Для каждого последующего периода мы можем выполнить аналогичный запрос и либо объединить его с предыдущими результатами, либо выполнить левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ), чтобы видеть продажи по каждому периоду в отдельных колонках.
Если мы хотим видеть данные по периодам в отдельных колонках, мы можем использовать конструкцию с левым соединением, как это было предложено в исходном сообщении, но с небольшими корректировками для агрегации:
ВЫБРАТЬ
ВТ_Период1.Номенклатура КАК Номенклатура,
ВТ_Период1.Количество КАК КоличествоПериод1,
ВТ_Период1.Стоимость КАК СтоимостьПериод1,
ЕСТЬNULL(ВТ_Период2.Количество, 0) КАК КоличествоПериод2,
ЕСТЬNULL(ВТ_Период2.Стоимость, 0) КАК СтоимостьПериод2
ИЗ
ВТ_Период1 КАК ВТ_Период1
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Количество) КАК Количество,
СУММА(Продажи.Стоимость) КАК Стоимость
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода2 И &КонецПериода2
И Продажи.Активность = ИСТИНА
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура) КАК ВТ_Период2
ПО ВТ_Период1.Номенклатура = ВТ_Период2.Номенклатура;
Таким образом, мы формируем отдельные временные таблицы для каждого периода, а затем соединяем их по номенклатуре. Это позволяет нам получить данные за разные периоды в разных колонках и, при необходимости, сравнивать их.
Для случая, когда количество периодов заранее неизвестно, мы будем вынуждены выполнять такие соединения в цикле, динамически генерируя текст запроса. Каждый шаг цикла будет добавлять новое левое соединение с очередной временной таблицей, содержащей данные за следующий период. Это достаточно "кошерный" (приемлемый) подход, как было отмечено в обсуждении, если он позволяет решить задачу.
Преимущества: Гибкость в обработке произвольных, несовпадающих периодов. Возможность сравнения данных в отдельных колонках.
Недостатки: Может снижать производительность при очень большом количестве периодов или данных, так как каждый запрос в цикле — это отдельное обращение к базе данных. Динамическое формирование текста запроса может быть сложным в отладке.
Этот подход особенно полезен, когда нам необходимо получить данные за каждый день, неделю, месяц или другой интервал в заданном диапазоне дат, даже если в некоторые из этих интервалов не было оборотов. Это позволяет "дополнить" результаты отчета недостающими периодами с нулевыми значениями, обеспечивая непрерывность временного ряда.
Шаги реализации:
Создание таблицы периодов: Мы можем динамически сгенерировать таблицу дат или периодов прямо в запросе. Для этого нам потребуется начальная и конечная даты общего диапазона. В 1С есть разные способы создания такой таблицы: можно использовать виртуальные таблицы, если они доступны в вашей конфигурации (например, некоторые конфигурации содержат служебные регистры или функции, позволяющие получать периоды), или вручную создать таблицу значений и передать ее в запрос, либо использовать пакетный запрос для генерации.
Предположим, мы хотим получить данные за каждый день между &НачалоОбщегоПериода и &КонецОбщегоПериода. Мы можем использовать конструкцию для генерации дат:
// Шаг 1: Генерируем таблицу всех дней в заданном диапазоне
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоОбщегоПериода, ДЕНЬ, НомерСтроки - 1) КАК ДатаПериода
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
(ВЫБРАТЬ
РАЗНОСТЬДАТ(&НачалоОбщегоПериода, &КонецОбщегоПериода, ДЕНЬ) + 1 КАК КоличествоДней
ИЗ
РегистрНакопления.Продажи КАК Продажи // Любая таблица для получения одной строки
ОГРАНИЧЕНИЕ 1) КАК ИсходныеДанные,
ПОСЛЕДОВАТЕЛЬНЫЕ_ДАТЫ_ТАБЛИЦЫ(&НачалоОбщегоПериода, &КонецОбщегоПериода) КАК ПоследовательныеДаты // Пример абстрактной функции
ГДЕ
НомерСтроки <= ИсходныеДанные.КоличествоДней;
На практике, генерация последовательности чисел или дат часто выполняется через служебные таблицы или программно в цикле с добавлением в ТаблицуЗначений, которая затем передается в запрос как параметр. В СКД эта задача часто решается использованием настроек "Периодами".
Если нам нужны произвольные, несовпадающие периоды (например, 1-14 марта 2023 и 1-14 марта 2022), мы можем создать таблицу значений программно, добавить в нее эти интервалы как отдельные строки (Начало, Конец, ИмяПериода), а затем передать в запрос как временную таблицу.
Соединение таблицы периодов с данными о продажах: После того как мы сгенерировали таблицу с нужными периодами, мы можем выполнить левое соединение этой таблицы с фактическими данными о продажах из регистра. Это позволит нам получить все периоды из нашей сгенерированной таблицы, даже те, по которым не было продаж.
ВЫБРАТЬ
ВТ_Периоды.ДатаПериода КАК Дата,
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Количество) КАК Количество,
СУММА(Продажи.Стоимость) КАК Стоимость
ИЗ
ВТ_Периоды КАК ВТ_Периоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи КАК Продажи
ПО ВТ_Периоды.ДатаПериода = НАЧАЛОПЕРИОДА(Продажи.Период, ДЕНЬ)
ГДЕ
Продажи.Активность = ИСТИНА
СГРУППИРОВАТЬ ПО
ВТ_Периоды.ДатаПериода,
Продажи.Номенклатура;
В этом примере мы соединяем сгенерированную таблицу ВТ_Периоды с регистром Продажи. Обратите внимание, что условие соединения должно соответствовать желаемой детализации периода (в данном случае, НАЧАЛОПЕРИОДА(Продажи.Период, ДЕНЬ)).
Работа с несовпадающими периодами (например, разные годы): Если требуется выборка за одни и те же дни, но за разные годы (например, с 1.03 по 14.03, но за 2023 и 2022 годы), нам потребуется более сложная таблица периодов. Мы можем создать временную таблицу, которая будет содержать два столбца: ИмяПериода (например, "2023 год" и "2022 год") и ГодСмещения (0 для текущего, -1 для прошлого и т.д.). Затем, при соединении с регистром, мы будем корректировать год даты продаж в условии соединения.
// Пример временной таблицы с периодами для сравнения годов
ВЫБРАТЬ
"ТекущийГод" КАК ИмяПериода,
0 КАК СмещениеГода
ПОМЕСТИТЬ ВТ_Интервалы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"ПредыдущийГод" КАК ИмяПериода,
-1 КАК СмещениеГода;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Интервалы.ИмяПериода,
Продажи.Номенклатура,
СУММА(Продажи.Количество) КАК Количество,
СУММА(Продажи.Стоимость) КАК Стоимость
ИЗ
РегистрНакопления.Продажи КАК Продажи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Интервалы КАК ВТ_Интервалы
ПО (ДЕНЬ(Продажи.Период) МЕЖДУ ДЕНЬ(&НачалоДняДиапазона) И ДЕНЬ(&КонецДняДиапазона))
И (МЕСЯЦ(Продажи.Период) МЕЖДУ МЕСЯЦ(&НачалоДняДиапазона) И МЕСЯЦ(&КонецДняДиапазона))
И (ГОД(Продажи.Период) = ГОД(&ТекущаяДата) + ВТ_Интервалы.СмещениеГода)
ГДЕ
Продажи.Активность = ИСТИНА
СГРУППИРОВАТЬ ПО
ВТ_Интервалы.ИмяПериода,
Продажи.Номенклатура;
В данном примере &НачалоДняДиапазона и &КонецДняДиапазона – это, например, 1.03 и 14.03 любого года, а &ТекущаяДата – текущий год, относительно которого мы считаем смещение.
Преимущества: Позволяет получать непрерывные ряды данных даже при отсутствии оборотов. Удобно для анализа динамики. Хорошо сочетается с СКД.
Недостатки: Генерация сложной таблицы периодов может требовать дополнительных усилий или специфических знаний. Для действительно произвольных и непересекающихся периодов может быть менее гибким, чем подход с циклами.
Для создания сложных отчетов, таких как отчеты о продажах за произвольные периоды с иерархией, наиболее мощным и гибким инструментом в 1С является Система Компоновки Данных (СКД). СКД позволяет настроить отчет таким образом, чтобы пользователь сам мог выбирать нужные периоды, их детализацию, а также строить иерархические группировки без изменения кода запроса.
Возможности СКД для решения задачи:
Настройка параметров периода:
Динамический период: В отчетах СКД существует возможность использования поля "Динамический период". Оно позволяет пользователю настраивать периодичность вывода данных (день, неделя, месяц, квартал, год) и сравнивать данные по подпериодам (например, "Текущий месяц" и "Прошлый месяц"). Это очень удобно для стандартных сравнительных отчетов.
Параметры СКД ДатаНачала и ДатаОкончания: Вы можете настроить параметры СКД, позволяющие пользователю выбирать произвольный интервал дат или использовать стандартные периоды (например, "Этот месяц", "Прошлый год"). СКД автоматически сформирует даты начала и конца стандартного периода с учетом времени (начальная дата 00:00:00, конечная 23:59:59), что избавляет от необходимости использовать функции НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА непосредственно в запросе.
Список периодов: Для неограниченного количества произвольных периодов вы можете создать в СКД параметр типа "Список значений" (или "Таблица значений") и передавать в него наборы дат/интервалов. Далее в запросе необходимо будет использовать ЛЕВОЕ СОЕДИНЕНИЕ с этим параметром-таблицей, аналогично подходу с генерируемой таблицей периодов.
Иерархические группировки:
Для создания иерархических отчетов в СКД достаточно добавить группировку по полю, которое имеет иерархическую структуру (например, "Номенклатура", "Контрагент", "Подразделение"). При этом можно выбрать тип иерархии:
СКД автоматически построит иерархию и рассчитает промежуточные итоги по группам, что является ключевым требованием к отчету.
Использование Менеджера временных таблиц (МВТ):
Для оптимизации сложных запросов в СКД, особенно если одни и те же выборки используются в разных наборах данных отчета, можно использовать Менеджер временных таблиц (МВТ). МВТ позволяет:
Это особенно актуально, когда нам нужно получить данные для нескольких периодов, каждый из которых требует сложной предварительной выборки. Сначала мы можем сформировать ВТ с продажами за Период 1, затем ВТ с продажами за Период 2 и так далее, а потом уже их соединять в финальном наборе данных.
Конструкция "Периодами" на закладке "Итоги":
Если требуется получать данные за каждый день/месяц выбранного периода, даже если в некоторые из них не было оборотов, используйте конструкцию "Периодами" на закладке "Итоги" в конструкторе запросов 1С. Это позволяет дополнять результат запроса недостающими периодами с нулевыми значениями, что очень удобно для построения графиков и анализа непрерывной динамики.
Преимущества СКД: Высокая гибкость и настраиваемость отчета без программирования. Оптимизация запросов через МВТ. Встроенные механизмы для работы с иерархией и динамическими периодами. Удобный пользовательский интерфейс для настройки отчета.
Недостатки: Требует понимания принципов работы СКД, что может быть сложнее для новичков.
Независимо от выбранного подхода, при работе с запросами и временными таблицами в 1С всегда следует учитывать вопросы производительности. Вот несколько важных рекомендаций:
Избегайте использования логического оператора ИЛИ в условиях отбора запроса: Если это возможно, старайтесь переформулировать запрос так, чтобы избежать ИЛИ, так как он может замедлять работу базы данных, особенно на больших объемах. Вместо Поле = Значение1 ИЛИ Поле = Значение2 лучше использовать Поле В (Значение1, Значение2). В более сложных случаях рассмотрите разбиение запроса на несколько отдельных и объединение результатов (ОБЪЕДИНИТЬ).
Индексирование временных таблиц (ВТ): Если вы создаете временные таблицы и затем используете их в соединениях или подзапросах, всегда индексируйте ключевые поля. Индексирование значительно повышает производительность выборки и соединения данных.
// Пример индексирования временной таблицы
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &Начало И &Конец
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура; // Индексируем поле, по которому будут происходить соединения
Ограничение полей во временных таблицах: Помещайте во временные таблицы только те поля, которые действительно будут использоваться в последующих запросах. Избегайте выборки всех полей (ВЫБРАТЬ *), если они не нужны, чтобы не увеличивать объем хранимых данных и не замедлять операции с ВТ.
Использование пакетных запросов: При выполнении нескольких запросов, связанных между собой (например, создание ВТ и последующее использование), всегда объединяйте их в один пакетный запрос. Это сокращает количество обращений к базе данных и улучшает общую производительность.
Вынесение виртуальных таблиц в отдельные ВТ: Если запрос с виртуальной таблицей (например, РегистрНакопления.Продажи.ОстаткиИОбороты(), РегистрНакопления.ТоварыНаСкладах.Остатки()) работает медленно, рекомендуется вынести обращение к ней в отдельный запрос с сохранением результатов во временной таблице. Это позволит СУБД один раз сформировать сложную таблицу, а затем работать уже с ее простым представлением.
Избегайте создания и удаления временных таблиц в цикле: Если это возможно, создайте временную таблицу один раз до начала цикла и наполняйте ее данными внутри цикла, а не создавайте и удаляйте на каждой итерации.
Заключение
Мы рассмотрели различные подходы к формированию отчета о продажах за разные периоды с иерархией в 1С. Выбор конкретного метода зависит от специфики задачи: нужен ли отчет для произвольных, несовпадающих интервалов, или для непрерывного ряда с нулевыми значениями, насколько важна производительность и требуется ли гибкая настройка отчета конечным пользователем. Для сложных, настраиваемых отчетов с иерархией и динамическими периодами наиболее оптимальным решением является использование Системы Компоновки Данных (СКД), которая предлагает мощные инструменты для решения этих задач, в том числе через настройки параметров, иерархические группировки и Менеджер временных таблиц для оптимизации.
Всегда помните об оптимизации запросов, чтобы обеспечить высокую производительность отчета, особенно при работе с большими объемами данных.