Расчет итогов по группам справочника в прямом запросе.

            Часто заказчики хотят, чтобы быстрый отчет на прямых запросах выводил итоги по группам справочников. Например, посчитать сумму проданного товара за период с группировкой по номенклатуре. Да еще и по контрагенту иногда. И обязательно с итогами по группам. И обязательно результаты должны быть выведены в том порядке, как это обычно делает 1С. В данном примере, пожалуй, проще было бы отказаться от прямых запросов и реализовать его стандартными средствами. Но отчеты бывают всякие. Сложными и медленными для реализации стандартными средствами. В этой статье рассмотрю и опишу свой вариант алгоритма расчета итогов по группам.

            Хочу сразу заметить, что все запросы будут рассчитаны на MS SQL-Server 2005, т.к. он предоставляет больше функционала (будут использованы оконные функция, common table expression). Примеры будут приведены для стандартной ТиС с использованием 1С++.

            Итак. На входе имеем запрос, возвращающий сумму проданного за май 2009-ого года товара и ссылку на товар.

       select $t.Номенклатура tov,sum($t.Сумма) summa 

       from $ДокументСтроки.Реализация t (nolock)

       inner join _1sjourn jj (nolock) on jj.iddoc=t.iddoc

       where jj.closed&1=1

             and jj.date_time_iddoc between '20090501' and '20090531Z'

       group by $t.Номенклатура

            Нам необходимо посчитать итоги по группам. Это можно сделать и средствами 1С, обработав полученную таблицу значений. Например, можно создать иерархическую таблицу значений, каждая строка которой будет содержать ссылку на элемент справочника, сумму и ссылку на такую же таблицу значений, если это группа. Для всех товаров из выборки рассчитать родителей всех уровней и заполнить эту таблицу. Можно запросом (или методом «Выбратьэлементы()») пройтись по справочнику, занести его структуру в таблицу значений, а затем для каждой строки этой таблицы, если это группа, пробегать все строчки нашего запроса с использованием «ПринадлежитГруппе» и аккумулировать итоги. Возможны всякие другие варианты, в том числе использование классов 1С++. Но у всех них есть два минуса, которые мне не нравятся. Во-первых, это большое количество чтений таблицы справочника и вложенные циклы при расчете итогов. Во-вторых, лично мне приятнее и удобнее, чтобы максимум вычислений был сделан на сервере SQL, а 1С только вывела результаты.

            Как это можно сделать на SQL. Если бы речь шла о 2000-ом SQL сервере, то, скорее всего, это выглядело бы как множественное присоединение таблицы справочника для расчета родителей и далее какие-то операции по расчету итогов. В этом подходе меня не устраивает «множественное чтение», поэтому я его не рассматривал, оставил как резервный вариант. В 2005-ом сервере появились замечательные вещи, такие как оконные функции и общие табличные выражения (Common Table Expression или CTE). В частности, с помощью СТЕ возможна организация рекурсии. Рекурсивные запросы значительно быстрее, используют минимум чтений таблиц и более универсальные.

            Итак, алгоритм. Для каждого товара из первоначальной выборки рассчитаем всех родителей с накоплением полного кода из ID элементов справочников. Т.е. будем строить дерево групп в обратную сторону. Отсчет кол-ва уровней так же будем проводить в обратную сторону. Хоть это и может привести к тому, что одна группа будет иметь несколько уровней (смотря из каких глубин к ней пришли), это нам не помешает. Для каждой строки будем хранить ссылку на элемент справочника, из которого пришли в эту группу, значение суммы реализации этого товара. Накопленный полный ID. Причем, перед ID текущего элемента будем ставить «2», а переде ID группы – «1», чтобы потом можно было сделать сортировку по этому полю и получить порядок «как обычно в 1С». Признак группы (чтобы не делать лишних вычислений – в формате 1С. Т.е. 2 – это элемент. 1 – это группа). Ссылку на текущую группу и ссылку на ее родителя. Сначала при помощи рекурсивного поиска родителей надо получить примерно такую таблицу:

            Колонка lvl – номер шага рекурсии. В этом примере исходный товар, для которого построено дерево, имеет ID=24901. Как видно, полный ID для этого товара есть только в последней строке, для родителя самого верхнего уровня. Последующие действия не требуют рекурсии и выполняются после нее. Берем FullID из строки с максимальным уровнем для данного товара и записываем ее во все строки, для этого товара. Получится так:

Теперь, если для каждой строки вырезать часть из FullID, начиная с первого символа и заканчивая (включительно) ID текущей группы (поле Tov_Dlya_Itogov), то получим полный собственный ID для каждой из рассчитанных групп и для самого товара. Таблица примет следующий вид:

Остается только сгруппировать по полю Tov_Dlya_Itogov и отсортировать по FullID. Отчет готов.

            Перейдем к реализации. Для удобства запишем исходный запрос через СТЕ:

with main as

(

              select $t.Номенклатура tov,sum($t.Сумма) summa 

              from $ДокументСтроки.Реализация t (nolock)

              inner join _1sjourn jj (nolock) on jj.iddoc=t.iddoc

              where jj.closed&1=1

                    and jj.date_time_iddoc between '20090501' and '20090531Z'

              group by $t.Номенклатура

)

            Теперь рекурсия. Запуск рекурсии выглядит следующим образом:

       select

             cast('2'+tov+'/' as varchar(255)) FullID,parentid,2 isfolder

             ,tov tov_dlya_itogov,1 lvl

             ,tov,summa

       from main t

       inner join $Справочник.Номенклатура s (nolock) on s.id=t.tov

При запуске к исходной таблице присоединяется таблица справочника, заполняются служебные поля. Далее, тело рекурсии:

       select cast('1'+s.id+'/'+FullID as varchar(255))

             ,s.parentid, 1

             ,s.id,lvl+1

             ,tov,summa

       from cte

       inner join $Справочник.Номенклатура s (nolock) on s.id=cte.parentid

На каждом шаге наращивается FullID, инкриминируется уровень рекурсии, рассчитываются новые значения родителя для текущей группы. Добавив нерекурсивную часть запроса, запишем все вместе.

with main as

(

       select $t.Номенклатура tov,sum($t.Сумма) summa 

       from $ДокументСтроки.Реализация t (nolock)

       inner join _1sjourn jj (nolock) on jj.iddoc=t.iddoc

       where jj.closed&1=1

             and jj.date_time_iddoc between '20090501' and '20090531Z'

       group by $t.Номенклатура

)

, cte as

(

       select

             cast('2'+tov+'/' as varchar(255)) FullID,parentid,2 isfolder

             ,tov tov_dlya_itogov,1 lvl

             ,tov,summa

       from main t

       inner join $Справочник.Номенклатура s (nolock) on s.id=t.tov

       union all

       select cast('1'+s.id+'/'+FullID as varchar(255))

             ,s.parentid, 1

             ,s.id,lvl+1

             ,tov,summa

       from cte

       inner join $Справочник.Номенклатура s (nolock) on s.id=cte.parentid

)

select tov_dlya_itogov [Товар $Справочник.Номенклатура]

,personal_path

,isfolder ЭтоГруппа

,sum(summa) Сумма

from

(

       select *

             ,substring(common_path,1,

patindex('%'+tov_dlya_itogov+'%',common_path)+9) personal_path

       from

       (

             select *,max(case when m_lvl=lvl then FullID end)

over(partition by tov) common_path

             from

             (

                    select *, max(lvl) over(partition by tov) m_lvl

                    from cte

             ) a

       ) a

) a

group by tov_dlya_itogov,isfolder,personal_path

order by personal_path

Получается что-то типа шаблона для расчета итогов. Достаточно поменять запрос в Main и скорректировать название полей, кол-во рассчитываемых ресурсов и вид справочника, для групп которого производить расчет итогов. И все будет работать.

Если задача усложняется тем, что к группировке по номенклатуре добавляется группировка по контрагентам. Например, сначала Контрагент, потом Номенклатура. Тогда, помимо соответствующих изменений в запросе Main, нужно добавить в нерекурсивную часть разделение по ID клиента (over(partition by tov) заменить на over(partition by tov, ClientID)), добавить соответствующую группировку. Доработать рекурсивную часть, добавить сохранение на каждом шаге ID клиента. Нерекурсивную часть оформить как еще одно выражение СТЕ. Получится, расчет итогов по группам номенклатуры для каждого клиента будет сам являться исходным запросом для расчета итогов по группам контрагентов. Т.е. добавится еще одно рекурсивное выражение и своя нерекурсивная часть. Все по аналогии.

Для сортировки по наименованию можно вместо FullID собрать FullDescr. Вместо ID элементов записывать наименования. Стоит только обратить внимание на тип данных, в котором хранится FullID – varchar(255), его может не хватить на полные наименования. И процедуру вырезания подстроки стоит пересмотреть, т.к. она рассчитана на фиксированную длину поля ID.