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