Работа с итогами в запросе

vladimir_putin

Здравствуйте! Сегодня разберем интересную тему — работа с итогами.

Я не безграмотный …

Я — творческая личность в области орфографии…laughlaughlaugh

Посвящается всем грамотеям вроде меня. Букв будет много и ошибок, естественно, тоже. Строго не судить! Начнемenlightened.

Немного теории

Представим, что у нас есть следующие исходные данные:

Продавец Касса Сумма
Петров Касса1 100
Петров Касса2 200
Петров Касса1 150
Скворцов Касса2 300
Скворцов Касса2 50
Сидоров Касса3 400

Как быть, если нам потребуется получить сгруппированные данные по полю Продавец и Касса? Нужно просто применить группировкуsmiley:

Продавец Касса Сумма
Петров Касса1 250
Петров Касса2 200
Скворцов Касса2 350
Сидоров Касса3 400

В результате группировки количество записей уменьшилось, что и логично.

Как быть, если нам потребуется получить общий итоги по каждому продавцу, а потом детализацию по кассам? Не понял! Это как? Хм…frown Вот так:

Продавец Касса Сумма
Петров NULL 450
Петров Касса1 100
Петров Касса2 200
Петров Касса2 150
Скворцов NULL 350
Скворцов Касса2 300
Скворцов Касса2 50
Сидоров NULL 400
Сидоров Касса3 400

Данный результат называется итогами, настроенными по полю Продавец. Именно в таком варианте будет выглядеть линейная таблица с итогами.

Как видим, количество записей увеличилось — к исходным данным добавились итоговые (результирующие) записи.

Можно ли на исходные данные одновременно применить группировку и итоги? Конечно, можно! Результат будет таким:

Продавец Касса Сумма
Петров NULL 450
Петров Касса1 100
Петров Касса2 350
Скворцов NULL 350
Скворцов Касса2 350
Сидоров NULL 400
Сидоров Касса3 400

Таким образом, мы получили данные с тремя итоговыми записями и с четырьмя детальными записями.

Надеюсь, Вы поняли отличие итогов от группировки! Теперь перейдем к практике.

Ключевое слово ИТОГИ

Задача 1: Настроить итоги для поля Номенклатура табличной части Товары документа РеализацияТоваровУслуг.

Откроем консоль запросов и в конструкторе запроса и определим источник данных и выходные поля.

zapros_8_1

Убедимся, что есть хоть какие-то данные, выполнив запрос.

zapros_8_2

Отобразился обычный линейный список. Поехали дальшеlaugh. Теперь настроим итоги по полю Номенклатура. Для этого в конструкторе запроса существует закладка Итоги, где указываются группировочные поля и итоговые поля. В группировочных полях указываются поля, по которым будут получаться итоги, а в итоговых полях указываются поля по которым будет происходить суммирование данных.

zapros_8_3

Теперь посмотрим, какой получится результат (для наглядности способ выгрузки поменяем на Дерево).

zapros_8_4

Надеюсь, здесь все понятно? Как нет? А сейчас?

zapros_8_5

Проще уже некуда!

А теперь посмотрим на получившийся текст запроса:

ВЫБРАТЬ
    РеализацияТоваровУслугТовары.Номенклатура КАК Номенклатура,
    РеализацияТоваровУслугТовары.Количество,
    РеализацияТоваровУслугТовары.Сумма КАК Сумма
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары
ИТОГИ
    СУММА(Сумма)
ПО
    Номенклатура

Как видим, в текст запроса были добавлены ключевые слова ИТОГИ и ПО. После ключевого слова ИТОГИ следует список итоговых полей. Если итоги считаются для нескольких полей, то поля перечисляются через запятую. В качестве итоговых полей обычно выступает результат агрегатных функций. Затем следует ключевое слово ПО, после которого перечисляется список полей, для которых считаются итоги — контрольные точки.

Итоговые поля могут рассчитываться необязательно для всех выходных полей. Кстати, можете убедиться в этом, удалив строчку СУММА(Сумма).

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

— Поиграй со мной, попросил Язык Запросовsurprise.

Задача 2: Убедиться, что одновременно можно использовать итоги и группировки.

Убедиться в этом легко — одновременно в консоли запросов настроить группировку в секции Группировка и настроить итоги в секции Итоги. Надеюсь, с этим Вы сами справитесь.

Хочу лишь обратить внимание на два момента:

1) Если итоги используются совместно с группировкой и для итогов не указан список агрегатных функций, то итоговые поля будут ВСЕ РАВНО формироваться автоматически из агрегатных полей списка выборки группировки. Как-то слишком сложно объяснилlaugh.

Корочеlaugh, в данном тексте запроса не обязательно писать СУММА(Количество)

ВЫБРАТЬ
    РеализацияТоваровУслугТовары.Номенклатура КАК Номенклатура,
    СУММА(РеализацияТоваровУслугТовары.Количество) КАК Количество
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары

СГРУППИРОВАТЬ ПО
    РеализацияТоваровУслугТовары.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура

2) Если запрос содержит объединение, то агрегатные функции для итогов будут браться из первого запроса.

Итоги для иерархических справочников

Если контрольная точка является ссылкой на справочник, то возможен расчет итогов по иерархии справочника. Для этого после контрольной точки нужно указать ключевое слово ИЕРАРХИЯ.

Например, есть текст запроса:

ВЫБРАТЬ
    ВТ_Пример.Номенклатура КАК Номенклатура,
    ВТ_Пример.Количество КАК Количество
ИЗ
    ВТ_Пример КАК ВТ_Пример
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура

который возвращает следующие данные:

zapros_8_6

Поле Номенклатура имеет тип СправочникСсылка.Номенклатура со следующими группами:

zapros_8_7

В случае, если в запросе указать ключевое слово ИЕРАРХИЯ

ПО
    Номенклатура ИЕРАРХИЯ КАК ИерархияНоменклатуры

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

zapros_8_8

В случае, если требуется рассчитать ТОЛЬКО ИТОГИ значений по иерархии, не рассчитывая итоги в контрольных точках, то перед ключевым словом ИЕРАРХИЯ нужно указать слово ТОЛЬКО:

ПО
    Номенклатура ТОЛЬКО ИЕРАРХИЯ КАК ТолькоИерархияНоменклатуры

Задача 3: Самостоятельно познакомиться с флагом Общие итоги.

zapros_8_9

Ничего сложного нет. Просто установите данный флаг и посмотрите результат выполнения запроса, а также обратите внимание на то, как изменился текст запроса.

Обход результата запроса с итогами

Задача 4: Разобраться, как реализован обход результата запроса с итогамиlaugh.

Разберем данный механизм на достаточно интересной реальной задаче, которую мне когда-то приходилось решать. Необходимо реализовать отчет в виде кросс-таблицы без использования СКД.

Для учебных целей пусть отчет должен выглядеть так:

Фрукт Контрагент1 Контрагент2 КонтрагентN
Яблоко 100 200 300
Банан 60 20   50
Апельсин 30 45   80

Данные берутся из оборотного регистра накопления ЗакупкаФруктов за определенный период. В колонке Фрукт отображаются все фрукты, которые были куплены у Контрагентов, а Контрагенты должны отображаться в колонках отчета. На пересечении колонок и строк выводятся данные — количество купленных фруктов неважно в чем (коробки, штуки).

Как Вы, наверно, догадались, количество колонок — динамическое.

Например, данные в оборотном регистре такие:

Фрукт Контрагент КоличествоОборот
Яблоко Пятачок 100
Яблоко Ярмарка 200
Яблоко Три Тополя 300
Банан Пятачок 60
Апельчин Ярмарка 80

Идея:

1) Нарисовать макет табличного документа.

zapros_8_10

Думаю, здесь все понятно. Простейший макет с четырьями областями: ОбластьШапкаФрукт, ОбластьШапкаКонтрагент, ОбластьСтрокаФрукт, ОбластьСтрокаКоличество.

2) Написать сам текст запроса:

ВЫБРАТЬ

    ЗакупкаФруктовОбороты.Контрагент КАК Контрагент,
    ЗакупкаФруктовОбороты.Фрукт КАК Фрукт ,
    ЗакупкаФруктовОбороты.КоличествоОборот КАК КоличествоОборот
ИЗ
    РегистрНакопления.ЗакупкаФруктов.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ЗакупкаФруктовОбороты
ИТОГИ
    СУММА(КоличествоОборот)
ПО
    Контрагент,
    Фрукт

Данный запрос получит сначала итоги по Контрагенту, далее будут получены итоги по Фрукту, а детализация по КоличествоОборот.

Будет выглядеть примерно так (надеюсь, нигде не ошибсяlaugh):

Итоги по полю Контрагент:

zapros_8_11

Итоги по полю Фрукт:

zapros_8_12

Детализация по КоличествоОборот:

zapros_8_13

А теперь начинается самое интересноеcool.

3) Заполнить макет табличного документа.

Возможно, Вы уже догадались для чего были подсчитаны итоги. Правильно! Для того, чтобы было чем заполнять макет табличного документа.

Получить всех контрагентов можно путем обхода итогов по полю Контрагент:

РезультатЗапроса = Запрос.Выполнить();
ВыборкаКонтрагент = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Контрагент");
Пока
ВыборкаКонтрагент.Следующий() Цикл
   
ОбластьШапкаКонтрагент.Параметры.Заполнить(ВыборкаКонтрагент);
   
ТабличныйДокумент.Присоединить(ОбластьШапкаКонтрагент);
КонецЦикла;

Таким способом будет заполнена шапка таблицы контрагентами: Пятачок, Ярмарка, Три Тополя.

Хочу обратить внимание на параметры ОбходРезультатаЗапроса.ПоГруппировкам и Контрагент. Указывая данные параметры мы системе говорим, что нужно выбрать итоговые записи по Контрагенту. Если данные параметры не указывать, то будет обычная линейная выборка.

Далее, необходимо построчно вывести фрукт и его количество закупки у контрагента, указанного в шапке отчета.

Выборка по фруктам:

ВыборкаФрукт = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Фрукт");

Далее начинаем обходить выборку из результата запроса:

ВыборкаФрукт = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Фрукт");
Пока
ВыборкаФрукт.Следующий() Цикл
   
ОбластьСтрокаФрукт.Параметры.Заполнить(ВыборкаФрукт);
   
ТабличныйДокумент.Вывести(ОбластьСтрокаФрукт);
КонецЦикла;

Отлично! Осталось только придумать, как заполнить остальные ячейки!

Здесь есть небольшая хитрость. У типа ВыборкаИЗРезультатаЗапроса есть метод Выбрать(), где в качестве параметра можно указать также группировку, в нашем случае это будет "Контрагент".

Объясняю на простом языкеlaugh.

В ВыборкаФрукт сколько у нас будет записей? Правильно, три:

zapros_8_15

Например, текущая позиция первая запись (NULL, Яблоко, 600), у которой тип ВыборкаИЗРезультатаЗапроса. Если мы текущую выборку обойдем по группе Контрагент, то получим как раз требуемый результат:

ВыборкаКонтрагент = ВыборкаФрукт.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Контрагент", "ВСЕ");
Пока
ВыборкаКонтрагент.Следующий() Цикл
   
ОбластьСтрокаКоличество.Параметры.Заполнить(ВыборкаКонтрагент);
   
ТабличныйДокумент.Присоединить(ОбластьСтрокаКоличество);
КонецЦикла;

Обратите на параметр "ВСЕ". Указывая данный параметр мы системе говорим обойти выборку по всем трем контрагентам, именно поэтому, в ВыборкаКонтрагент будет 3 записи (т.к. всего три контрагента):

zapros_8_12

Если бы текущая позиция была бы 3 запись (NULL, Апельсин, 80), то в ВыборкаКонтрагент все равно будет 3 записи:

zapros_8_13

Обратите внимание на записи со значением NULL в ячейках (значит по таким  контрагентам не было закупки фруктов).

Если не указать параметр "ВСЕ", то в ВыборкаКонтрагент была бы только 1 запись:

zapros_8_14

Вообщем, указывая параметр "ВСЕ" мы обходим последовательно всех контрагентов и последовательно заполняем ячейки табличного документа.

ВыборкаФрукт = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Фрукт");
Пока
ВыборкаФрукт.Следующий() Цикл
   
ОбластьСтрокаФрукт.Параметры.Заполнить(ВыборкаФрукт);
   
ТабличныйДокумент.Вывести(ОбластьСтрокаФрукт);
   
ВыборкаКонтрагент = ВыборкаФрукт.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Контрагент", "ВСЕ");
    Пока
ВыборкаКонтрагент.Следующий() Цикл
       
ОбластьСтрокаКонтрагент.Параметры.Заполнить(ВыборкаКонтрагент);
       
ТабличныйДокумент.Присоединить(ОбластьСтрокаКонтрагент);
    КонецЦикла;
КонецЦикла;

Про присоединение областей — без комментариев т.к. все очевидно.

Надеюсь, Вам понравилась статья. Если у вас есть вопросы то, пожалуйста, не стесняйтесь спрашивайтеlaugh.

А я пошел дальше смотреть футбол матч Лиги Чемпионов Ростов — Атлетикоlaughlaughlaugh.

Понравилось? Не забудь поддержать сайт!

Комментарии 5

  • Спасибо за ещё одну интересную статью.

    А можно пример работы с запросами, в объекте конфигурации "обработка"?

    К примеру нужно создать запрос, который выводил на форму обработки табличную часть с колонками: контрагент, склад, сумма. 

    И считал общую сумму и выводя итог на форму обработки в виде поля 

    Я начинающий программист, в интернете не нашел подобных, наверное простых примеров для матерых программистов, может здесь помогут-)

    P.S. Болеем за Ростов-)

    • Эх… Проиграл Ростов…

      Спасибо за отзыв.

      По поводу Вашего примера.

      Значит так, в конфигурации есть какая-то обработка «Пример». У неё есть табличная часть «Информация» с колонками «Контрагент», «Склад» и «Сумма».

      Создаем форму, в которой размещаем нашу табличную часть.

      На форме есть кнопка «Заполнить». По кнопке «Заполнить» выполняется какой-то запрос, который заполняет табличную часть?

      Итоги по колонке «Сумма» Вы хотите видеть именно в отдельном поле или подвале табличной части?

       

      • Есть конфигурация в которой есть документ "продажа товаров"

        Нужно сделать 

        1. Сделать запрос за период по документу: продажа товара;

        1.1 Вынести на форму обработки табличную часть с колонками: контрагент, склад, сумма;

        1.2 посчитать общую сумму и вынести итог на форму обработки в виде поля;

        Вот так дословно звучит задача…

        Но вот как это сделать именно в "Обработки" не знаю. Поэтому может у Вас есть, подробный пример подобной задачи-)

        Извиняюсь, если не грамматно объясняю…

         

        • А, ну тогда в форме обработки нужно разместить ещё два поля: ДатаНачала и ДатаОкончания.

          Это нужно сделать в обычной форме или в управляемой? Отвечу на ваш вопрос в виде отдельной статьи ближе к выходным.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *