Конструктор запросов 1С — обучение на примерах

Урок 5. Группировка в запросе 1с при помощи конструктора

Содержание статьи:

Таблицы и поля; Условия; Связи; Объединения / Псевдонимы; Группировка; Виртуальные таблицы.

Задача: Получить суммы поступлений за текущий год с разбивкой по месяцам. Для получения данных использовать проведенные документы Поступление товаров и услуг.

Новые вкладки: Группировка

Теоретическая часть урока №5

Группировка в запросе — это объединение нескольких строк выборки в одну, при этом используется два вида полей:

• Группировочные. Строки, в которых набор этих полей принимает одинаковые значения, сворачиваются одну строку;
• Группируемые. Для свернутых строк значения этих полей рассчитываются при помощи агрегатных функций:
  • Сумма. Значения поля суммируется по всем сгруппированным строкам;
  • Среднее. Рассчитывается среднее значение поля среди сгруппированных строк;
  • Максимум. Вычисляется максимальное значение поля среди сгруппированных строк;
  • Минимум. Вычисляется минимальное значение поля среди сгруппированных строк;
  • Количество. Вычисляется количество сгруппированных строк, вне зависимости от того к какому полю применяется данная агрегатная функция;
  • Количество различных. Вычисляется количество различных значений данного поля по всем сгруппированным строкам.

Пример 1. Пусть у нас есть таблица с двумя полями: Фрукт и Количество. Сгруппировать строки с одинаковыми фруктами и посчитать общее количество по каждому фрукту.

По условиям примера поле Фрукт — группировочное, а поле Количество — группируемое при помощи агрегатной функции Сумма.

После группировки наша таблица будет выглядеть так:

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

Таким образом у нас есть таблица с тремя полями Фрукт, Тара, Цена. Необходимо сгруппировать строки по полям Фрукт и Тара, а по полю Цена посчитать Максимум.

В данном примере у нас два группировочных поля и одно группируемое. После группировки наша таблица будет выглядеть так:

Теперь посмотрим, как реализован механизм группировки в конструкторе запросов 1с. В конструкторе перейдем на вкладку Группировка, она состоит из трех разделов:

• Поля (красная рамка). В данном разделе представлены все поля доступные для группировки. Это поля таблиц выбранных на вкладке Таблицы и поля. Их необходимо распределить между следующими двумя разделами;
• Группировочные поля (зеленая рамка). В данный раздел необходимо перетащить (мышью или при помощи кнопок) поля по которым будет производиться группировка;
• Группируемые поля (синяя рамка). В данный раздел необходимо перетащить (мышью или при помощи кнопок) поля, значения которых будут рассчитываться при помощи агрегатных функций. Данный раздел представляет из себя таблицу, в первой колонке которой находится группируемое поле, а во второй выбирается из списка агрегатная функция.

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

Практическая часть урока №5

Разберем решение задачи, приведенной в начале урока. Напомню условия:

Задача: Получить суммы поступлений за текущий год с разбивкой по месяцам. Для получения данных использовать проведенные документы Поступление товаров и услуг.

• Создадим новый запрос;
• Запустим конструктор запросов;
• Выберем таблицу ПоступлениеТоваровУслуг из ветки Документы;
• Из таблицы ПоступлениеТоваровУслуг выберем поля Дата и СуммаДокумента;
• Выделим поле Дата и нажмем кнопку Изменить текущий элемент,  чтобы открыть его в редакторе произвольных выражений;
  
• В разделе Функции языка запросов найдем ветку Функции работы с датами;
• Из данной ветки перетащим в раздел выражений функцию Месяц и заменим параметр Дата на выбранное нами поле с датой поступления;
  
• Нажмем кнопку ОК, в нижней части редактора произвольных выражений;
• Перейдем на вкладку Объединения / Псевдонимы и установим для поля с номером месяца псевдоним Месяц;
  
• Перейдем на вкладку Условия;
• В разделе Поля, раскроем ветку ПоступлениеТоваровУслуг при помощи кнопки «+»;
• Найдем реквизит Дата и перетащим его в раздел условия, выберем оператор сравнения Между и укажем параметры начала и окончания года, например НачалоГода и КонецГода;
• Из раздела Поля перетащим реквизит Проведен, поставим в строке с условием флаг Произвольное и сотрем лишнюю часть условия «= &Проведен»;
• Перейдем на вкладку Группировка;
• В раздел группировочных полей перетащим поле МЕСЯЦ(ПоступлениеТоваровУслуг.Дата), а в раздел группируемых полей — ПоступлениеТоваровУслуг.СуммаДокумента. По умолчанию установится агрегатная функция Сумма, в данном случае она нас вполне устраивает;
  
• Написание запроса завершено, нажмем кнопку ОК, чтобы закрыть конструктор запросов;

В итоге у нас получится запрос со следующим текстом:

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("НачалоГода", НачалоГода(ТекущаяДата()));
Запрос.УстановитьПараметр("ОкончаниеПериода", КонецГода(ТекущаяДата()));

Запрос.Текст = 
"ВЫБРАТЬ
|    МЕСЯЦ(ПоступлениеТоваровУслуг.Дата) КАК Месяц,
|    СУММА(ПоступлениеТоваровУслуг.СуммаДокумента) КАК СуммаДокумента
|ИЗ
|    Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТоваровУслуг
|ГДЕ
|    ПоступлениеТоваровУслуг.Дата МЕЖДУ &НачалоГода И &КонецГода
|    И ПоступлениеТоваровУслуг.Проведен
|
|СГРУППИРОВАТЬ ПО
|    МЕСЯЦ(ПоступлениеТоваровУслуг.Дата)";

Также прочтите статьи о языке запросов 1с 8:

• Язык запросов 1с — структура оператора Выбрать
• Программная работа с запросами 1с 8

<< Пред. страница   |   След.Страница >>