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

19.01.10

Разработка - Запросы

Рассмотрен алгоритм расчета итогов по группам справочника при помощи рекурсивных запросов.

            Часто заказчики хотят, чтобы быстрый отчет на прямых запросах выводил итоги по группам справочников. Например, посчитать сумму проданного товара за период с группировкой по номенклатуре. Да еще и по контрагенту иногда. И обязательно с итогами по группам. И обязательно результаты должны быть выведены в том порядке, как это обычно делает 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 – это группа). Ссылку на текущую группу и ссылку на ее родителя. Сначала при помощи рекурсивного поиска родителей надо получить примерно такую таблицу:

 

FullID

ParentID

IsFolder

Tov_Dlya_Itogov

lvl

tov

Summa

2   24901 /

   27J01

2

24901

1

24901

70341.00

1   27J01 /2   24901 /

   27I01

1

   27J01

2

24901

70341.00

1   27I01 /1   27J01 /2   24901 /

46

1

   27I01

3

24901

70341.00

1    46  /1   27I01 /1   27J01 /2   24901 /

0

1

46

4

24901

70341.00

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

FullID

ParentID

IsFolder

Tov_Dlya_Itogov

lvl

tov

Summa

1    46  /1   27I01 /1   27J01 /2   24901 /

   27J01

2

24901

1

24901

70341.00

1    46  /1   27I01 /1   27J01 /2   24901 /

   27I01

1

   27J01

2

24901

70341.00

1    46  /1   27I01 /1   27J01 /2   24901 /

46

1

   27I01

3

24901

70341.00

1    46  /1   27I01 /1   27J01 /2   24901 /

0

1

46

4

24901

70341.00

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

FullID

ParentID

IsFolder

Tov_Dlya_Itogov

lvl

tov

Summa

1    46  /1   27I01 /1   27J01 /2   24901 /

   27J01

2

24901

1

24901

70341.00

1    46   /1   27I01 /1   27J01 /

   27I01

1

   27J01

2

24901

70341.00

1    46   /1   27I01 /

46

1

   27I01

3

24901

70341.00

1    46   /

0

1

46

4

24901

70341.00

Остается только сгруппировать по полю 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 элементов записывать наименования. Стоит только обратить внимание на тип данных, в котором хранится FullIDvarchar(255), его может не хватить на полные наименования. И процедуру вырезания подстроки стоит пересмотреть, т.к. она рассчитана на фиксированную длину поля ID.

 

См. также

Микро консоль для запросов к MySQL базе из 1С: Предприятие 7.7

Инструментарий разработчика Запросы Платформа 1С v7.7 Конфигурации 1cv7 Абонемент ($m)

Обработка, позволяющая выполнять запросы к базе, лежащей в MySQL, для 1С:Предприятие 7.7.

10.08.2022    2936    4    crocolo    0    

2

.Net в 1С. Асинхронные HTTP запросы, отправка Post нескольких файлов multipart/form-data, сжатие трафика с использованием gzip, deflate, удобный парсинг сайтов и т.д.

Запросы Платформа 1С v7.7 Платформа 1С v8.3 Бесплатно (free)

Очень часто нужно при работе с HTTP сервисами или сайтами использовать Асинхронные HTTP запросы, отправку на сервер нескольких файлов, использование сжатия трафика. Эта статья про то, как этого легко добиться.

09.03.2016    37669    Serginio    22    

44

Универсальный способ сравнения таблиц

Универсальные функции Запросы Платформа 1С v7.7 Платформа 1С v8.3 Бесплатно (free)

На эту тему уже есть статьи, но этот способ нигде не описан. Хотя я его использую с тех пор, как занимаюсь программированием. Его преимущество в простоте и универсальности: можно применять на 1С, SQL, а также в любом другом языке программирования.

05.07.2015    21652    json    3    

22

Пример получения остатков по складу по запросу по почте из программы 1С 7.7.

Запросы Платформа 1С v7.7 Конфигурации 1cv7 Абонемент ($m)

Пример получения остатков по складу по запросу по почте из программы 1С 7.7. Для получения остатков необходимо пользователю с любого почтовика (с любого "мыла") отправить текст сообщения GiveMyStockBalance_ForAnalize на почтовый адрес определенный в Константа.СерверПолучения. Программа выдаст остатки (можно переписать функцию для выдачи любых данных) в формате xls на почту указанную в константе Константа.СерверОтправки. Программа может быть полезна в тех организациях где трудно или невозможно осуществить прямой доступ к 1С сотрудников для просмотра необходимых данных. Также можно организовать некий почтамт - запрос для клиентов - при посылке определенного логина клиентом на его почту будет автоматически выслана информация, например, акт сверки с клиентов, или процент выполнения его заказа и т.д.

3 стартмани

25.03.2014    25149    5    protexprotex    3    

5

Реализация условий в прямых запросах к журналу расчета.

Зарплата Запросы Расчет 7.7 1С:Зарплата и кадры 7.7 Абонемент ($m)

Обзор методов реализации условий и функций с условиями в обращениях к журналу расчетов при помощи прямого запроса.

1 стартмани

06.12.2012    14086    nicotin    5    

9

Универсальный отчет - Конструктор запросов для 1С 7.7

Запросы Платформа 1С v7.7 Оперативный учет 7.7 Бухгалтерский учет 7.7 Расчет 7.7 Конфигурации 1cv7 Абонемент ($m)

Универсальный отчет для конфигураций на платформе 1С 7.7. Умеет обращатся к справочникам, документам, регистрам и журналам расчетов. Удобен для быстрого получения каких либо данных, которые можно получить посредствам запроса. Предусмотренна возможность сохранения настроек.

1 стартмани

14.04.2012    33862    303    set2333    16    

11
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Ёпрст 1063 19.01.10 10:56 Сейчас в теме
2. aaa 19.01.10 20:26 Сейчас в теме
тоже почитаю на досуге, но я группирую в индексированной ТЗ и не парюсь. Кода минимум, запросы прозрачные, без лишних наворотов
По группам двух справочников вряд ли вообще целесообразно группировать. Отчет будет неудобный для реального использования.
3. alon 171 01.11.12 21:21 Сейчас в теме
Полезная статья.
ИТ группирует только справочники. А вот счета к примеру не сможет.
4. newbas 533 01.07.13 15:50 Сейчас в теме
Попробовал - работает и довольно быстро.
По группам 2-х справочников точно нет необходимости, а вот по группам и элементам - например
"Номенклатура, Склад". Пример бы...
Оставьте свое сообщение