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