Когда мы работаем с данными в 1С, нам часто требуется получить не просто сумму за период, а накопительный итог — сумму, которая увеличивается с каждым последующим днем или строкой. Это полезно для анализа трендов, расчёта остатков или получения текущих оборотов. В стандартном языке запросов 1С нет прямой поддержки оконных функций SQL, таких как SUM() OVER(ORDER BY ...), которые значительно упрощают эту задачу. Однако существуют эффективные способы для решения данной проблемы. Рассмотрим их подробнее.
Один из наиболее распространенных и фундаментальных методов для получения накопительных итогов в 1С — это использование левого соединения таблицы с самой собой. Давайте разберем его принцип.
Принцип работы:
Мы соединяем нашу таблицу данных (источник) с той же самой таблицей по условию, что дата из основной таблицы будет больше или равна дате из присоединенной таблицы. Затем, для каждой строки основной таблицы, мы суммируем значения из присоединенной таблицы, которые соответствуют этому условию. Это позволяет нам "накапливать" сумму по мере продвижения по датам.
ВЫБРАТЬ
Т1.Дата КАК Дата,
СУММА(Т2.ФактическийСдельныйФОТ) КАК НакопительнаяСумма
ИЗ
ВТ_ТрудозатратыПоСотрудникам КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТрудозатратыПоСотрудникам КАК Т2
ПО Т1.Дата >= Т2.Дата
ГДЕ
Т1.Дата >= &НачалоПериода
И Т1.Дата <= &КонецПериода
СГРУППИРОВАТЬ ПО
Т1.Дата
УПОРЯДОЧИТЬ ПО
Т1.Дата
В этом примере:
ВТ_ТрудозатратыПоСотрудникам, но вы можете использовать любую другую таблицу или регистр, где хранятся ваши данные.Т1 — это наша основная таблица, строки которой мы хотим получить с накопительным итогом.Т2 — это та же самая таблица, которую мы присоединяем к Т1.ПО Т1.Дата >= Т2.Дата обеспечивает, что для каждой даты из Т1 будут присоединены все строки из Т2, у которых дата меньше или равна текущей дате Т1.Дата.СУММА(Т2.ФактическийСдельныйФОТ) затем просуммирует значения из присоединенных строк Т2 для каждой даты Т1.Дата, таким образом формируя накопительный итог.СГРУППИРОВАТЬ ПО Т1.Дата агрегирует данные для каждой уникальной даты.УПОРЯДОЧИТЬ ПО Т1.Дата гарантирует, что результат будет отсортирован по датам, что логично для накопительного итога.Важный аспект: Производительность этого метода может снижаться на больших объемах данных. Для улучшения скорости выполнения запроса крайне важно, чтобы поле Дата в вашей исходной таблице было проиндексировано. Правильная индексация значительно ускоряет операции соединения и отбора.
Хотя язык запросов 1С не поддерживает оконные функции SQL напрямую, Система Компоновки Данных (СКД), которая является мощным инструментом для построения отчетов в 1С, предоставляет свои механизмы для работы с ресурсами, которые могут эмулировать функциональность оконных функций.
В СКД мы можем настроить вычисляемые поля или ресурсы, используя встроенные функции. Например, для получения накопительной суммы можно использовать вычисляемое поле с выражением, которое обращается к предыдущим значениям или выполняет агрегацию в рамках определенной группировки.
Существует активная дискуссия среди разработчиков, выполняются ли эти функции СКД непосредственно на уровне SQL-сервера или рассчитываются средствами самой платформы 1С уже после получения данных. Для некоторых простых агрегаций и вычисляемых полей СКД может генерировать более сложный SQL-запрос, пытаясь выполнить часть вычислений на стороне сервера. Однако для более сложных "оконных" функций, таких как ROW_NUMBER() или SUM() OVER(...), СКД часто реализует их средствами платформы. Это означает, что данные сначала выгружаются из базы, а затем 1С обрабатывает их для формирования накопительных итогов.
Для того чтобы точно узнать, какой SQL-запрос генерируется СКД и как именно выполняются вычисления, мы можем использовать технологический журнал 1С или профилировщик SQL-сервера. Это позволит нам проанализировать план выполнения запроса и оценить его производительность.
Если производительность запроса критична, объем данных очень велик, и стандартные средства 1С (включая СКД) не обеспечивают необходимой скорости, мы можем рассмотреть вариант использования прямых запросов к SQL-серверу. Этот подход позволяет нам напрямую использовать все мощные возможности SQL, включая оконные функции.
Оконные функции, такие как SUM() OVER(ORDER BY ...), специально разработаны для выполнения агрегаций по определенному "окну" строк без использования самосоединений, что делает их очень эффективными.
SELECT
Т.Дата,
SUM(Т.ФактическийСдельныйФОТ) OVER (ORDER BY Т.Дата ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS НакопительнаяСумма
FROM
ВТ_ТрудозатратыПоСотрудникам AS Т
WHERE
Т.Дата >= @НачалоПериода
AND Т.Дата <= @КонецПериода
ORDER BY
Т.Дата;
В данном примере SQL-запроса:
SUM(Т.ФактическийСдельныйФОТ) OVER (...) — это оконная функция, которая вычисляет сумму.ORDER BY Т.Дата определяет порядок строк в окне.ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW указывает, что окно начинается с самой первой строки (в рамках текущей партиции, если бы она была) и заканчивается текущей строкой. Это создает накопительный итог.Преимущества прямых запросов:
Риски и ограничения:
ADO или внешние компоненты), что может усложнить разработку и поддержку.Этот подход целесообразен в исключительных случаях, когда стандартные методы 1С не справляются с нагрузкой, и когда мы готовы взять на себя риски, связанные с прямой работой с СУБД.
Помимо вышеописанных основных методов, существуют и другие способы, которые могут быть применены в зависимости от конкретной задачи и объема данных.
Если объем данных, которые нам нужно обработать, относительно небольшой, мы можем сначала выбрать все необходимые данные обычным запросом, а затем выполнить накопительное суммирование уже в коде 1С. Этот метод прост в реализации и не требует сложных запросов.
// Пример кода 1С
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| Т.Дата КАК Дата,
| СУММА(Т.ФактическийСдельныйФОТ) КАК ФактическийСдельныйФОТ
|ИЗ
| ВТ_ТрудозатратыПоСотрудникам КАК Т
|ГДЕ
| Т.Дата >= &НачалоПериода
| И Т.Дата <= &КонецПериода
|СГРУППИРОВАТЬ ПО
| Т.Дата
|УПОРЯДОЧИТЬ ПО
| Т.Дата";
Запрос.УстановитьПараметр("НачалоПериода", НачалоДня(НачалоПериода));
Запрос.УстановитьПараметр("КонецПериода", КонецДня(КонецПериода));
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
НакопительнаяСумма = 0;
Для Каждого СтрокаТЗ Из РезультатЗапроса Цикл
НакопительнаяСумма = НакопительнаяСумма + СтрокаТЗ.ФактическийСдельныйФОТ;
СтрокаТЗ.Вставить("Накопительная", НакопительнаяСумма); // Добавляем новую колонку с накопительной суммой
КонецЦикла;
// РезультатЗапроса теперь содержит колонку "Накопительная"
Преимущество: Простота реализации.
Недостаток: Неэффективно для больших объемов данных, так как все вычисления выполняются на стороне клиента или сервера приложений 1С, что может привести к замедлению работы и потреблению значительных ресурсов памяти.
В парадигме 1С для задач, связанных с остатками и оборотами, наиболее правильным и эффективным подходом является использование регистров накопления. Если ваша задача позволяет, то корректное проектирование и использование регистров накопления может обеспечить высокую производительность при получении накопительных итогов.
Регистры накопления по своей сути предназначены для хранения агрегированных данных и позволяют получать остатки и обороты за период без сложных запросов или циклов, используя встроенные механизмы платформы, такие как методы ПолучитьОстатки() и ПолучитьОбороты().
Преимущество: Максимальная производительность для задач остатков/оборотов, архитектурно правильный подход в 1С.
Недостаток: Требует предварительного проектирования и ведения данных в регистре.
В сложных запросах для улучшения читаемости и, в некоторых случаях, для оптимизации производительности, мы можем использовать временные таблицы. Это позволяет нам разбить сложный расчет на несколько шагов. Например, сначала мы можем получить базовые агрегированные данные во временную таблицу, а затем уже к ней применять метод левого соединения для получения накопительной суммы.
ВЫБРАТЬ
Т.Дата КАК Дата,
СУММА(Т.ФактическийСдельныйФОТ) КАК ФактическийСдельныйФОТ
ПОМЕСТИТЬ ВТ_ДневныеИтоги
ИЗ
ВТ_ТрудозатратыПоСотрудникам КАК Т
ГДЕ
Т.Дата >= &НачалоПериода
И Т.Дата <= &КонецПериода
СГРУППИРОВАТЬ ПО
Т.Дата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Дата КАК Дата,
СУММА(Т2.ФактическийСдельныйФОТ) КАК НакопительнаяСумма
ИЗ
ВТ_ДневныеИтоги КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДневныеИтоги КАК Т2
ПО Т1.Дата >= Т2.Дата
СГРУППИРОВАТЬ ПО
Т1.Дата
УПОРЯДОЧИТЬ ПО
Т1.Дата
Преимущество: Улучшает читаемость сложных запросов, может оптимизировать выполнение за счет предварительной агрегации и уменьшения объема данных для последующих соединений.
Недостаток: Увеличивает количество шагов в запросе.
Выбор метода зависит от ваших требований к производительности, объему данных и готовности использовать более сложные или специфичные для 1С механизмы. Для большинства случаев метод левого соединения является рабочим и относительно простым в реализации. Для максимальной производительности при больших объемах данных и при возможности использования прямого SQL, оконные функции являются оптимальным решением. В рамках 1С архитектурно правильным и производительным методом для учета остатков и оборотов остаются регистры накопления.