Группировка в запросе. Группировка в запросе Как осуществляется группировка

Вопрос Обход запроса по группировкам, как собираются данные в запросе с конструкцией ИТОГИ ПО. Предположим, требуется организовать цикл, в котором должен формироваться массив заказов, в разрезе Контрагентов и Адреса доставки. Как это сделать?
Ответ Пишем запрос:

1С (Код)

Запрос.Текст = "ВЫБРАТЬ // Адрес доставки заключаем в "ВЫРАЗИТЬ", т.к. в метаданных Адрес Доставки - строка неограниченной длины и если ее не преобразовать в строку ограниченной длины, //то ее будет невозможно использовать в конструкции "ИТОГИ ПО" // и мы получим сообщение об ошибке вида "Недопустимое поле для группировки" |ИЗ |ГДЕ | усЗаказНаОтгрузку.Проведен | |УПОРЯДОЧИТЬ ПО |ИТОГИ ПО | Контрагент, | АдресДоставки";

Запрос< spanclass="k" > . Текст< spanclass="k" > =

< spanclass="s" > "ВЫБРАТЬ

| усЗаказНаОтгрузку.Ссылка КАК Ссылка,

| усСтатусДокументов.Статус КАК Статус,

| усЗаказНаОтгрузку.Контрагент КАК Контрагент,

// Адрес доставки заключаем в " ВЫРАЗИТЬ", т.к. в метаданных Адрес Доставки - строка неограниченной длины и если ее не преобразовать в строку ограниченной длины,

//то ее будет невозможно использовать в конструкции " ИТОГИ ПО"

// и мы получим сообщение об ошибке вида " Недопустимоеполедля группировки "

| ВЫРАЗИТЬ(усЗаказНаОтгрузку.АдресДоставки КАК СТРОКА(350)) КАК АдресДоставки

|ИЗ

| РегистрСведений.усСтатусДокументов КАК усСтатусДокументов

| ЛЕВОЕ СОЕДИНЕНИЕ Документ.усЗаказНаОтгрузку КАК усЗаказНаОтгрузку

| ПО усСтатусДокументов.Документ = усЗаказНаОтгрузку.Ссылка

|ГДЕ

| усЗаказНаОтгрузку.Проведен

|УПОРЯДОЧИТЬ ПО

| усСтатусДокументов.Статус.Порядок

|ИТОГИ ПО

| Контрагент,

| АдресДоставки" < spanclass="k" > ;

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

Контрагент 1

АдресДоставки1

Контрагент 1

АдресДоставки2

Контрагент2

АдресДоставки3

Теперь нам нужно получить три массива:
— заказ 1, заказ 2, заказ 3
— заказ 4, заказ 5
— заказ 6
Для получения этих массивов требуется организовать обход приведенного выше запроса «По группировкам с иерархией»:

1С (Код)

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

СпособВыборки < spanclass="k" > = ОбходРезультатаЗапроса< spanclass="k" > . ПоГруппировкамСИерархией< spanclass="k" > ;

Результат< spanclass="k" > = Запрос< spanclass="k" > . < spanclass="k" > Выполнить < spanclass="k" > ( < spanclass="k" > ) < spanclass="k" > ;

ВыборкаПервогоУровня< spanclass="k" > = Результат< spanclass="k" > . Выбрать< spanclass="k" > ( СпособВыборки< spanclass="k" > ) < spanclass="k" > ;

< spanclass="k" > Пока ВыборкаПервогоУровня< spanclass="k" > . Следующий< spanclass="k" > ( < spanclass="k" > ) < spanclass="k" > Цикл < spanclass="c" > // внутри каждого уровня иерархии заказы упорядочены: "Создан", "В работу", "Ведутся работы", "Готов к отгрузке" (именно в таком порядке, порядок установлен в перечислении)

ВыборкаВторогоУровня< spanclass="k" > = ВыборкаПервогоУровня< spanclass="k" > . Выбрать< spanclass="k" > ( СпособВыборки< spanclass="k" > ) < spanclass="k" > ;

мсвЗаказов< spanclass="k" > . Очистить< spanclass="k" > ( < spanclass="k" > ) < spanclass="k" > ; < spanclass="c" > // очистили перед добавлением первого в список

< spanclass="k" > Пока ВыборкаВторогоУровня< spanclass="k" > . Следующий< spanclass="k" > ( < spanclass="k" > ) < spanclass="k" > Цикл < spanclass="c" > // внутри каждого уровня иерархии заказы упорядочены: "Создан", "В работу", "Ведутся работы", "Готов к отгрузке" (именно в таком порядке, порядок установлен в перечислении)

ВыборкаТретьегоУровня< spanclass="k" > = ВыборкаВторогоУровня< spanclass="k" > . Выбрать< spanclass="k" > ( ОбходРезультатаЗапроса< spanclass="k" > . Прямой< spanclass="k" > ) < spanclass="k" > ; < spanclass="c" > // параметр можно не передавать, по умолчанию способ выборки прямой

< spanclass="k" > Пока ВыборкаТретьегоУровня< spanclass="k" > . Следующий< spanclass="k" > ( < spanclass="k" > ) < spanclass="k" > Цикл < spanclass="c" > // внутри каждого уровня иерархии заказы упорядочены: "Создан", "В работу", "Ведутся работы", "Готов к отгрузке" (именно в таком порядке, порядок установлен в перечислении)

мсвЗаказов< spanclass="k" > . Добавить< spanclass="k" > ( ВыборкаТретьегоУровня< spanclass="k" > . Ссылка< spanclass="k" > ) < spanclass="k" > ;

< spanclass="k" > КонецЦикла < spanclass="k" > ;

СформироватьОтборПоЗаказу< spanclass="k" > ( мсвЗаказов< spanclass="k" > ) < spanclass="k" > ; < spanclass="c" > // тут мы по очереди получим в цикле требуемые массивы: [заказ 1, заказ 2, заказ 3], затем [заказ 4, заказ 5] и [заказ 6]

< spanclass="k" > КонецЦикла < spanclass="k" > ;

Примечание. Конструкция «УПОРЯДОЧИТЬ ПО усСтатусДокументов.Статус.Порядок» будет срабатывать внутри каждого массива, т.е. заказы будут упорядочены по статусу в каждом из полученных массивов.

Справка по расчету итогов запроса из описания в справке Конфигуратора

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

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

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

<Описание итогов>

<Итоги> [<Список агрегатных функций>]

<Итоги>

ИТОГИ [<Список итоговых_полей>] ПО [ОБЩИЕ] <Список контрольных точек>

<Список итоговых_полей>

<Итоговое_поле> [,<Список_итоговых_полей> [, …]]

<Итоговое_поле>

<Агрегатная_функция> | <Выражение> [[КАК] <Псевдоним_поля>]

<Список контрольных точек>

<Контрольная точка> [, <Контрольная точка> [, …]]

<Контрольная точка>

<Выражение> [[ТОЛЬКО] ИЕРАРХИЯ] | [ПЕРИОДАМИ(Секунда | Минута | Час | День | Неделя | Месяц | Квартал | Год | Декада | Полугодие
[,<Литерал типа DATE> | <Идентификатор параметра>]
[,<Литерал типа DATE> | <Идентификатор параметра>])] [[КАК] Псевдоним поля]

Описание итогов начинается с обязательного ключевого слова ИТОГИ.

<Список агрегатных функций> содержит перечень агрегатных функций, которые необходимо рассчитывать в итогах. Агрегатные функции рассматриваются в .

Помимо общих итогов, можно задать расчет итогов по контрольным точкам. Для этого после обязательного ключевого слова ПО необходимо указать <Список контрольных точек>. Каждая <Контрольная точка> содержит <Выражение>, вычисляемое при выполнении запроса. По каждой комбинации значений этих выражений будут рассчитаны и добавлены в результат запроса итоговые строки.

Если контрольная точка является полем — субконто регистра бухгалтерии, то при получении итогов по этой контрольной точке будут игнорироваться значения NULL.

Агрегатные функции языка запросов

В языке запросов предусмотрены агрегатные функции, которые используются при группировке результатов запроса и при подсчете итогов. Агрегатные функции предназначены для обобщения значений указанного параметра. Определены следующие агрегатные функции:

<Агрегатная функция>

СУММА (<Выражение>) |
СРЕДНЕЕ (<Выражение>) |
МИНИМУМ (<Выражение>) |
МАКСИМУМ (<Выражение>) |
КОЛИЧЕСТВО ([РАЗЛИЧНЫЕ] <Выражение> | *)

Пример:

Выбрать
Накладная.Номенклатура.Наименование,
Сумма (Накладная.Сумма) Как Сумма,
Среднее (Накладная.Сумма) Как Среднее,
Максимум (Накладная.Сумма) Как Максимум,
Минимум (Накладная.Сумма) Как Минимум,
Количество (Накладная.Сумма) Как Колич

Из

Сгруппировать По
Накладная.Номенклатура

Итоги Общие

Результат запроса:

Наименование Сумма Среднее Максимум Минимум Колич
265 955,45 12 511,12 40 000,23 555 8
Брюки детские 28 500,22 9 500,07 15 000 3 000 3
Рубашка «Ковбойка» 24 000 8 000 16 000 4 000 3
Джинсы женские 63 555 6 355,5 30 000 555 10
Свитер детский 6 400 6 400 6 400 6 400 1
Раковина «Лилия» 60 000,23 30 000,115 40 000,23 20 000 2
Мойдодыр «Аквариум» 65 500 21 833,33 40 000 8 000 3
Смеситель «Ультра» 15 000 15 000 15 000 15 000 1
Кухонный комбайн Крупс 3 000 3 000 3 000 3 000 1

Агрегатные функции могут использоваться в списке полей выборки, предложениях ИМЕЮЩИЕ, ИТОГИ, УПОРЯДОЧИТЬ ПО.

Агрегатная функция СУММА

Функция вычисляет арифметическую сумму всех попавших в выборку значений поля.

В качестве параметра функции можно указывать только поля, содержащие числовое значение.

Если поле не может содержать числовых значений, то применение функции СУММА к такому полю вызовет ошибку. Если поле может содержать числовые значения (имеет составной тип данных), то данная функция может быть применена к такому полю. Но если среди значений поля в выборке встретится нечисловое значение (помимо значений NULL), это вызовет ошибку.

Агрегатная функция СРЕДНЕЕ

Функция вычисляет среднее значение всех попавших в выборку значений поля.

В качестве параметра функции можно указывать только ссылки на поля, содержащие числовое значение.

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

Агрегатная функция МИНИМУМ

Функция вычисляет минимальное значение из всех попавших в выборку значений поля.

При определении минимального значения применяются правила сравнения значений, описанные в .

Агрегатная функция МАКСИМУМ

Функция вычисляет максимальное значение из всех попавших в выборку значений поля.

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

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

Агрегатная функция КОЛИЧЕСТВО

Функция подсчитывает количество значений параметра, попавших в выборку. В отличие от других агрегатных функций функция КОЛИЧЕСТВО допускает три способа использования.

Во-первых, функция позволяет подсчитать количество значений указанного поля, не равных NULL.
Во-вторых, функция позволяет подсчитать количество различных значений указанного поля, не равных NULL. Для этого перед спецификацией поля надо указать ключевое слово РАЗЛИЧНЫЕ.
В-третьих, функция позволяет подсчитать количество строк в результате запроса. Для этого в качестве параметра функции надо указать звездочку «*».

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

Пример:

ВЫБРАТЬ
Количество (*) Как Всего,
Количество (Различные Накладная.Номенклатура) Как Разные

Из
Документ.РасходнаяНакладная.Состав Как Накладная

Результат запроса:

Всего Разные
24 8

Расчет общих итогов

Для расчета итогов по всей таблице в предложении ИТОГИ следует указать слово ОБЩИЕ. В этом случае будут вычислены значения агрегатных функций для всех записей таблицы.

Пример:

ИТОГИ СУММА(Количество) По ОБЩИЕ

Результат запроса:

Товар Количество Номер Получатель
238
Кран 10 РНк-1 Урюпинскторг
Кран 8 РНк-3 Маг. «Хозяин»
Кран 44 РНк-4 Маг. «Гигант»
Кран 22 РНк-5 Урюпинскторг
Вантус 5 РНк-1 Урюпинскторг
Вантус 1 РНк-3 Маг. «Хозяин»
Вантус 14 РНк-4 Маг. «Гигант»
Стол 1 РНк-1 Урюпинскторг
Стол 15 РНк-2 Маг. «Мебель»
Стол 10 РНк-4 Маг. «Гигант»
Стул 55 РНк-2 Маг. «Мебель»
Стул 5 РНк-3 Маг. «Хозяин»
Стул 32 РНк-4 Маг. «Гигант»
Стул 16 РНк-5 Урюпинскторг

Логические выражения в языке запросов

В языке запросов в операциях выбора и в условиях отборов используются логические выражения:

<Логическое выражение>

<Выражение> |
<Выражение> <Операция сравнения> <Выражение> |
<Выражение> [НЕ] В [ИЕРАРХИИ] (<Список значений> | <Массив значений>) |
<Выражение> [НЕ] В [ИЕРАРХИИ] (<Описание запроса>) |
<Выражение> [НЕ] МЕЖДУ <Выражение> И <Выражение> |
<Выражение> ЕСТЬ [НЕ] NULL |
<Выражение> ССЫЛКА <Имя таблицы> |
<Выражение> [НЕ] ПОДОБНО <Литерал типа СТРОКА>
[СПЕЦСИМВОЛ <Литерал типа СТРОКА>]

<Операция сравнения>

> | < | = | >= | <= | <>

<Список значений>

<Выражение>[, <Выражение> [, …]]

Логическим выражением может быть:

При сравнении значений используются правила сравнения значений, описанные ниже.

Правила сравнения значений

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

сравнения значений в операторах сравнения;
определения максимального и минимального значений в агрегатных функциях МИНИМУМ и МАКСИМУМ;
упорядочивания записей результата запроса в соответствии с порядком, заданным в предложении УПОРЯДОЧИТЬ ПО.

Если типы значений отличаются друг от друга, то отношения между значениями определяются на основании приоритета типов:

тип NULL (самый низший);
тип Булево;
тип Число;
тип Дата;
тип Строка;
ссылочные типы.

Отношения между различными ссылочными типами определяются на основе внутренних ссылочных номеров таблиц, соответствующих тому или иному типу.

Если типы данных совпадают, то производится сравнение значений по следующим правилам:

у типа Булево значение ИСТИНА больше значения ЛОЖЬ;
у типа Число обычные правила сравнения для чисел;
у типа Дата более ранние даты меньше более поздних;
у типа Строка сравнения производится в соответствии с установленными национальными особенностями базы данных без учета концевых пробелов;
ссылочные типы сравниваются на основе своих значений (номера записи и т. п.);
не допускается сравнение полей неограниченной длины (строки неограниченной длины, ХранилищеЗначения, поле ТипЗначения из таблицы планов видов характеристик).

Важно! Любая операция сравнения двух значений, в которой участвует хотя бы одно значение NULL, дает результат, аналогичный значению ЛОЖЬ.

Оператор проверки совпадения значения

Форма оператора В для проверки совпадения с одним из перечисленных

Оператор В позволяет проверить, совпадает ли значение выражения, указанного справа от него, с одним из значений, описанных слева. Если совпадает хотя бы с одним – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное. Сравнение значений производится по правилам, описанным в .

Пример:

Выбрать

Где
Справочник.Номенклатура.Родитель.Наименование
В («Бытовая техника», «Оргтехника»)

Форма оператора В для проверки принадлежности по иерархии

Для справочников проверка может осуществляться и на принадлежность по иерархии. Результатом оператора В ИЕРАРХИИ будет ИСТИНА, если значение выражения слева является ссылкой на элемент справочника и входит во множество значений справа или иерархически принадлежит какой-нибудь группе, содержащейся в этом множестве:

Пример:

// В качестве параметра Группа в запрос передается ссылка
// на какую-либо группу справочника Номенклатура.

Выбрать
Справочник.Номенклатура.Наименование

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

Пример:

Выбрать
Справочник.Номенклатура.Наименование

Форма оператора В для проверки совпадения значения с одним из результата запроса

Примером применения данного оператора может послужить следующее:

Пример:

ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка В
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ

)

Результат запроса:

Для получения противоположного результата, то есть, если нужно определить, что значение не совпадает ни с одним из результата запроса, запрос выглядит следующим образом:

Пример:

// Выбрать названия товаров, которые присутствовали в расходных накладных
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка НЕ В
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ
Документ.РасхНакл.Состав КАК РасхНаклСостав
)

Результат запроса:

Заметим, что из запроса операции В возможно обращение к полям таблиц, которые встречались во внешнем запросе до появления операции.

Пример:

// Выбрать названия товаров, которые присутствовали в расходных накладных
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.Ссылка В
ВЫБРАТЬ
РасхНаклСостав.Товар
ИЗ
Документ.РасхНакл.Состав КАК РасхНаклСостав
ГДЕ
РасхНаклСостав.Товар = Товары.Ссылка
)

Результат запроса:

Оператор проверки вхождения значения в диапазон

Оператор МЕЖДУ позволяет проверить, входит ли значение выражения, указанного справа от него, в диапазон, указанный слева (вместе с границами диапазона). Если входит – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное. Сравнение значений производится по правилам, описанным в .

Пример:

Выбрать

Где
Справочник.Номенклатура.ЗакупочнаяЦена МЕЖДУ 100 И 1000

Оператор проверки значения на NULL

Оператор ЕСТЬ NULL позволяет проверить значение выражения слева от него на NULL. Если значение равно NULL – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

Пример:

Выбрать
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЗакупочнаяЦена
Где
Справочник.Номенклатура.ЗакупочнаяЦена Есть NULL

Оператор проверки ссылочного значения

Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного справа от него, ссылкой на таблицу, указанную слева. Если да – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Разыменование таблиц описано в параграфе «Разыменование полей».

Пример:

Выбрать
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЕдиницаИзмерения
Где
Справочник.Номенклатура.ЕдиницаИзмерения Ссылка Справочник.ЕдиницыИзмерения

Оператор проверки строки на подобие шаблону

Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип строка. Если значение выражения удовлетворяет шаблону – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ.

Следующие символы в строке шаблона являются служебными и имеют смысл, отличный от символа строки:

% (процент): последовательность, содержащая любое количество произвольных символов
_ (подчеркивание): один произвольный символ
[…] (в квадратных скобках один или несколько символов): любой одиночный символ из перечисленных внутри квадратных скобок
В перечислении могут встречаться диапазоны, например a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона.
[^…] (в квадратных скобках значок отрицания, за которым следует один или несколько символов): любой одиночный символ, кроме тех, которые перечислены следом за значком отрицания

Любой другой символ означает сам себя и не несет никакой дополнительной нагрузки.

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

Например, шаблон “%АБВ[абвг]_абв%” СПЕЦСИМВОЛ “” означает подстроку, состоящую из последовательности символов:
буквы А; буквы Б; буквы В; одной цифры; одной из букв а, б, в или г; символа подчеркивания; буквы а; буквы б; буквы в.

Причем перед этой последовательностью может располагаться произвольный набор символов.

Итоги по иерархии

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

Пример:

ВЫБРАТЬ Док.Товар, Док.Количество, Док.Ссылка.Номер, Док.Ссылка.Получатель
ИЗ Документ.РасхНакл.Состав Как Док
УПОРЯДОЧИТЬ ПО Док.Товар
ИТОГИ СУММА(Количество) ПО Док.Товар ИЕРАРХИЯ

Результат запроса:

Товар Количество Номер Получатель
Сантехника 104
Кран 84
Кран 10 РНк-1 Урюпинскторг
Кран 8 РНк-3 Маг. “Хозяин”
Кран 44 РНк-4 Маг. “Гигант”
Кран 22 РНк-5 Урюпинскторг
Вантус 20
Вантус 5 РНк-1 Урюпинскторг
Вантус 1 РНк-3 Маг. “Хозяин”
Вантус 14 РНк-4 Маг. “Гигант”
Мебель 134
Стол 26
Стол 1 РНк-1 Урюпинскторг
Стол 15 РНк-2 Маг. “Мебель”
Стол 10 РНк-4 Маг. “Гигант”
Стул 108
Стул 55 РНк-2 Маг. “Мебель”
Стул 5 РНк-3 Маг. “Хозяин”
Стул 32 РНк-4 Маг. “Гигант”
Стул 16 РНк-5 Урюпинскторг

Пример:

ВЫБРАТЬ Док.Товар, Док.Количество, Док.Ссылка.Номер, Док.Ссылка.Получатель
ИЗ Документ.РасхНакл.Состав Док
УПОРЯДОЧИТЬ ПО Док.Товар
ИТОГИ СУММА(Количество) ПО Док.Товар ТОЛЬКО ИЕРАРХИЯ

Результат запроса:

Товар Количество Номер Получатель
Сантехника 104
Кран 10 РНк-1 Урюпинскторг
Кран 8 РНк-3 Маг. “Хозяин”
Кран 44 РНк-4 Маг. “Гигант”
Кран 22 РНк-5 Урюпинскторг
Вантус 5 РНк-1 Урюпинскторг
Вантус 1 РНк-3 Маг. “Хозяин”
Вантус 14 РНк-4 Маг. “Гигант”
Мебель 134
Стол 1 РНк-1 Урюпинскторг
Стол 15 РНк-2 Маг. “Мебель”
Стол 10 РНк-4 Маг. “Гигант”
Стул 55 РНк-2 Маг. “Мебель”
Стул 5 РНк-3 Маг. “Хозяин”
Стул 32 РНк-4 Маг. “Гигант”
Стул 16 РНк-5 Урюпинскторг

Posted in , Tagged #

Предположим, что на наших складах имеется такой товар:

Секция группировки объявляется ключевым словом СГРУППИРОВАТЬ ПО . Для чего нужна группировка в запросе? Совершенно верно, для объединения в группу одинаковых полей таблицы и получения суммарных результатов по остальным. Группировка сворачивает одинаковые поля запроса в одно, уменьшая тем самым количество результирующих записей. Сразу оговорюсь, если в запросе применяется группировка, то все поля должны быть разбиты на две категории: те по которым группируем и те которые группируются. Поясню на примере, допустим мы хотим узнать сколько у нас товара вообще, без учета складов, тогда мы напишем следующий код запроса:

Товар в данном случае - это поле по которому осуществляется группировка, а Количество - группируемое поле. СУММА - это агрегатная функция, она указывает запросу что все данные по количеству одинаковых товаров нужно просуммировать. Результатом выполнения этого запроса будет таблица вида:

Агрегатные функции

К группируемым полям должны быть обязательно применена агрегатная функция, это необязательно СУММА, а также МАКСИМУМ, МИНИМУМ, СРЕДНЕЕ, КОЛИЧЕСТВО, КОЛИЧЕСТВО РАЗЛИЧНЫХ. Рассмотрим более подробно действие каждой из них.

СУММА - применяется только для числовых полей, складывает переданные ей числа. Ее результат приведен на рисунке выше.

СРЕДНЕЕ - применяется только для числовых полей, вычисляет среднее - сумма переданных параметров / количество параметров:

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

Т.е. имеем опять одно группировочное поле Товар и два группируемых поля: Склад и Количество. Результат выполнения этого запроса будет выглядеть следующим образом:

МИНИМУМ - может использоваться для любых полей, получает минимальный из переданных параметров:

КОЛИЧЕСТВО - может использоваться для любых полей, получает количество переданных параметров:

КОЛИЧЕСТВО РАЗЛИЧНЫХ - может использоваться для любых полей, получает количество различных параметров. Т.е. если функции передать параметры (1,1,2,3,3,3,4,4,4,4,4,5), то она вернет 5 . Функция КОЛИЧЕСТВО вернула бы 12. Запрос с использованием функции КОЛИЧЕСТВО РАЗЛИЧНЫХ будет выглядеть так:

Результат:

Пусть мы группируем по двум полям: Товар и Склад:

для различных агрегатных функций результат будет следующим:

Подведем итоги:

Если в запросе используется группировка, то все поля должны делиться на группируемые (которые будут "свернуты") и группировочные (по которым осуществляется группировка - "сворачивание"). К группируемым полям должна быть применена одна из агрегатных функций, причем такие функции как СУММА и СРЕДНЕЕ могут быть применены только к числовым полям.

Язык запросов является одним из основополагающих механизмов 1С 8.3 для разработчиков. При помощи запросов можно быстро получить любые данные, хранящиеся в базе. Его синтаксис очень похож на SQL, но есть и отличия.

Основные достоинства языка запросов 1С 8.3 (8.2) перед SQL:

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

Недостатки языка запросов в 1С:

в отличие от SQL, в 1С запросы не позволяют изменять данные;
отсутствие хранимых процедур;
невозможность преобразования строки в число.

Рассмотрим наш мини учебник по основным конструкциям языка запросов 1С.

В связи с тем, что запросы в 1С позволяют лишь получать данные, любой запрос должен начинаться со слова «ВЫБРАТЬ». После этой команды указываются поля, данные из которых нужно получить. Если указать «*», то будут выбраны все доступные поля. Место, откуда будут выбираться данные (документы, регистры, справочники и прочее) указывается после слова «ИЗ».

В рассмотренном ниже примере выбираются наименования всей номенклатуры из справочника «Номенклатура». После слова «КАК» указываются псевдонимы (имена) для таблиц и полей.

ВЫБРАТЬ
Номенклатура.Наименование КАК НаименованиеНоменклатуры
ИЗ
Справочник.Номенклатура КАК Номенклатура

Рядом с командой «ВЫБРАТЬ» можно указать ключевые слова:

РАЗЛИЧНЫЕ . Запрос будет отбирать только отличающиеся хотя бы по одному полю строки (без дублей).
ПЕРВЫЕ n , где n – количество строк с начала результата, которые необходимо отобрать. Чаще всего такая конструкция используется совместно с сортировкой (УПОРЯДОЧИТЬ ПО). Например, когда нужно отобрать определенное количество последних по дате документов.
РАЗРЕШЕННЫЕ . Данная конструкция позволяет выбирать из базы только те записи, которые доступны текущему пользователю. Баз использования этого ключевого слова пользователю будет выведено сообщение об ошибке при попытке обращения запроса к тем записям, доступа к которым у него нет.

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

ДЛЯ ИЗМЕНЕНИЯ

Это предложение блокирует данные для исключения взаимных конфликтов. Заблокированные данные не будут считываться из другого соединения до окончания транзакции. В данном предложении можно указывать конкретные таблицы, которые нужно заблокировать. В противном случае будут заблокированы все. Конструкция актуальна лишь для режима автоматических блокировок.

Чаще всего предложение «ДЛЯ ИЗМЕНЕНИЯ» используется при получении остатков. Ведь при одновременной работе нескольких пользователей в программе, пока один получает остатки, другой может их изменить. В таком случае полученный остаток будет уже не верен. Если же заблокировать данные этим предложением, то пока первый сотрудник не получит корректный остаток и не совершит с ним все необходимые манипуляции, второй сотрудник будет вынужден ждать.

ВЫБРАТЬ
Взаиморасчеты.Сотрудник,
Взаиморасчеты.СуммаВзаиморасчетовОстаток
ИЗ
РегистрНакопления.ВзаиморасчетыССотрудниками.Остатки КАК Взаиморасчеты
ДЛЯ ИЗМЕНЕНИЯ

ГДЕ (WHERE)

Конструкция необходима для наложения какого-либо отбора на выгружаемые данные. В некоторых случая получения данных из регистров разумнее прописывать условия отборов в параметрах виртуальных таблиц. При использовании «ГДЕ», сначала получаются все записи, и только потом применяется отбор, что значительно замедляет выполнение запроса.

Ниже приведен пример запроса получения контактных лиц с определенной должностью. Параметр отбора имеет формат: &ИмяПараметра (имя параметра произвольное).

ВЫБОР (CASE)

Конструкция позволяет указывать условия непосредственно в теле запроса.

В приведенном ниже примере «ДополнительноеПоле» будет содержать текст в зависимости от того проведен документ или нет:

ВЫБРАТЬ
ПоступлениеТиУ.Ссылка,
ВЫБОР
КОГДА ПоступлениеТиУ.Проведен
ТОГДА «Документ проведен!»
ИНАЧЕ «Документ не проведен…»
КОНЕЦ КАК ДополнительноеПоле
ИЗ
Документ.ПоступлениеТоваровУслуг КАК ПоступлениеТиУ

СОЕДИНЕНИЕ (JOIN)

Соединения связывают две таблицы по определенному условию связи.

ЛЕВОЕ/ПРАВОЕ СОЕДИНЕНИЕ

Суть ЛЕВОГО соединения заключается в том, что полностью берется первая указанная таблица и к ней по условию связи привязывается вторая. Если записей, соответствующих первой таблице во второй не нашлось, то в качестве их значений подставляется NULL. Проще говоря, главной является первая указанная таблица и к её данным уже подставляются данные второй таблицы (если они есть).

Например, необходимо получить номенклатурные позиции из документов «Поступление товаров и услуг» и цены из регистра сведений «Цены номенклатуры». В данном случае, если цена у какой-либо позиции не найдена, вместо нее подставиться NULL. Из документа все позиции будут выбраны вне зависимости от того, есть ли на них цена или нет.

ВЫБРАТЬ
ПоступлениеТиУ.Номенклатура,
Цены.Цена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТиУ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО ПоступлениеТиУ.Номенклатура = Цены.Номенклатура

В ПРАВОМ все в точности да наоборот.

ПОЛНОЕ СОЕДИНЕНИЕ

Данный вид соединения отличается от предыдущих тем, что в результате будут возвращены все записи как первой таблицы, так и второй. Если по заданному условию связи в первой или второй таблице не найдено записей, вместо них будет возвращено значение NULL.

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Отличием ВНУТРЕННЕГО соединения от ПОЛНОГО является то, что если хотя бы в одной из таблиц не найдена запись, то запрос не выведет ее вообще. В результате будут выбраны только те номенклатурные позиции из документа «Поступление товаров и услуг», для которых в регистре сведений «Цены номенклатуры» есть записи, если в предыдущем примере заменить «ПОЛНОЕ» на «ВНУТРЕННЕЕ».

СГРУППИРОВАТЬ ПО (GROUP BY)

Группировка в запросах 1С позволяет сворачивать строки таблицы (группировочные поля) по определенному общему признаку (группируемым полям). Группировочные поля могут выводиться только с применением агрегатных функций.

Результатом следующего запроса будет список видов номенклатуры с максимальными ценами по ним.

ВЫБРАТЬ
,
МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ

СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры

ИТОГИ

В отличие от группировки при использовании итогов выводятся все записи и уже к ним добавляются итоговые строки. Группировка выводит лишь обобщенные записи.

Итоги можно подводить по всей таблице целиком (с использованием ключевого слова «ОБЩИЕ»), по нескольким полям, по полям с иерархической структурой (ключевые слова «ИЕРАРХИЯ», «ТОЛЬКО ИЕРАРХИЯ»). При подведении итогов не обязательно использовать агрегатные функции.

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

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

ИМЕЮЩИЕ (HAVING)

Данный оператор схож с оператором «ГДЕ», но используется только для агрегатных функций. Остальные поля, кроме используемых этим оператором, должны быть сгруппированы. Оператор «ГДЕ» не применим для агрегатных функций.

В рассмотренном ниже примере отбираются максимальные цены номенклатуры, если они превышают 1000, сгруппированные по виду номенклатуры.

ВЫБРАТЬ

МАКСИМУМ(Цены.Цена) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура.ВидНоменклатуры
ИМЕЮЩИЕ
МАКСИМУМ(Цены.Цена) > 1000

УПОРЯДОЧИТЬ ПО

Оператор «УПОРЯДОЧИТЬ ПО» сортирует результат запроса. Для того, чтобы гарантированно выводить записи в постоянном порядке, используется АВТОУПОРЯДОЧИВАНИЕ. Примитивные типы сортируются по обычным правилам. Ссылочные типы сортируются по GUID.

Пример получения списка сотрудников, отсортированного по наименованию:

ВЫБРАТЬ
Сотрудники.Наименование КАК Наименование
ИЗ
Справочник.Сотрудники КАК Сотрудники
УПОРЯДОЧИТЬ ПО
Наименование
АВТОУПОРЯДОЧИВАНИЕ

Прочие конструкции языка запросов 1С

ОБЪЕДИНИТЬ – результаты двух запросов в один.
ОБЪЕДИНИТЬ ВСЕ – аналог ОБЪЕДИНИТЬ, но без группировки одинаковых строк.
ПУСТАЯ ТАБЛИЦА – иногда используется при объединении запросов для указания пустой вложенной таблицы.
ПОМЕСТИТЬ – создает временную таблицу для оптимизации сложных запросов 1С. Такие запросы называются пакетными.

Функции языка запросов

ПОДСТРОКА обрезает строку с определенной позиции на указанное количество символов.
ГОД…СЕКУНДА позволяют получить выбранное значение числового типа. Входным параметром является дата.
НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА используются при работе с датами. В качестве дополнительного параметра указывается тип периода (ДЕНЬ, МЕСЯЦ, ГОД и т. п.).
ДОБАВИТЬКДАТЕ позволяет прибавить или отнять от даты указанное время определенного типа (СЕКУНДА, МИНУТА, ДЕНЬ и т. п.).
РАЗНОСТЬДАТ определяет разницу между двумя датами с указанием типа выходного значения (ДЕНЬ, ГОД, МЕСЯЦ и т. п.).
ЕСТЬNULL заменяет отсутствующее значение на указанное выражение.
ПРЕДСТАВЛЕНИЕ и ПРЕДСТАВЛЕНИЕССЫЛКИ получают строковое представление указанного поля. Применяются для любых значений и только ссылочных соответственно.
ТИП, ТИПЗНАЧЕНИЯ используются для определения типа входного параметра.
ССЫЛКА является логическим оператором сравнения для типа значения реквизита.
ВЫРАЗИТЬ используется для преобразования значения к нужному типу.
ДАТАВРЕМЯ получает значение типа «Дата» из числовых значений (Год, Месяц, День, Час, Минута, Секунда).
ЗНАЧЕНИЕ в запросе 1С используется для указания предопределенных значений — справочников, перечислений, планов видов характеристик. Пример использования: «Где ЮрФизЛицо = Значение(Перечисление.ЮрФизЛица.ФизЛицо) «.

Конструктор запросов

Для создания запросов с 1С есть очень удобный встроенный механизм – конструктор запросов. Он содержит следующие основные вкладки:

«Таблицы и поля» — содержит поля, которые необходимо выбрать и их источники.
«Связи» — описывает условий для конструкции СОЕДИНЕНИЕ.
«Группировка» — содержит описание конструкций группировок и суммируемых полей по ним.
«Условия» — отвечает за отборы данных в запросе.
«Дополнительно» — дополнительные параметры запроса, такие как ключевые слова команды «ВЫБРАТЬ» и пр.
«Объединения/Псевдонимы» — указываются возможности объединения таблиц и задаются псевдонимы (конструкция «КАК»).
«Порядок» — отвечает за сортировку результата запросов.
«Итоги» — аналогична вкладке «Группировка», но применяется для конструкции «ИТОГИ».

Текст самого запроса можно просмотреть, нажав в левом нижнем углу на кнопку «Запрос». В данной форме его можно откорректировать вручную или скопировать.

Консоль запросов

Для быстрого просмотра результата запроса в режиме «Предприятие», либо отладки сложных запросов используется . В ней пишется текст запроса, устанавливаются параметры, и показывается его результат.

Скачать консоль запросов можно на диске ИТС, либо по .

В этой статье рассмотрим группировку в запросах 1С 8 и
агрегатные функции с помощью которых эта группировка и делается.

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

А мы хотим из этой таблицы получить другую, где для каждого товара будет его общее
количество на всех складах:

Здесь то нам и поможет группировка.

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

ВЫБРАТЬ "Центральный" КАК Склад, "Карандаш" КАК Товар, 45 КАК Количество ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Центральный", "Ручка", 30 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Карандаш", 15 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Ручка", 25

А теперь поместим нашу исходную таблицу во временную и выберем из нее данные с
группировкой по полю Товар

ВЫБРАТЬ "Центральный" КАК Склад, "Карандаш" КАК Товар, 45 КАК Количество ПОМЕСТИТЬ ВТ_Количество ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Центральный", "Ручка", 30 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Карандаш", 15 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Ручка", 25 ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ВТ_Количество.Товар, СУММА(ВТ_Количество.Количество) КАК Количество ИЗ ВТ_Количество КАК ВТ_Количество СГРУППИРОВАТЬ ПО ВТ_Количество.Товар

Жирным выделен код, который непосредственно учавствует в группировке.
СУММА() - это агрегатная функция.
СГРУППИРОВАТЬ ПО - это оператор после которого идет список
полей через запятую по которым производится группировка. По сути здесь должны быть
перечислены все поля из раздела ВЫБРАТЬ за исключением тех к которым
применены агрегатные функции. В противном случае выполнение запроса завершится с
ошибкой.

Таким образом группировка как бы схлопывает таблицу по полям идущим после
СГРУППИРОВАТЬ ПО . К остальным полям применяется та или иная агрегатная функция.
Применительно к нашему примеру, до группировки записей с товарами Ручка
и Карандаш у нас по две штуки, а после группировки — по одной.
При работе с таблицами значений аналогичную функцию выполняет метод Свернуть() ,
за тем исключением, что там можно получать только сумму по полям не входящим в группировку.
В запросах же можно использовать несколько агрегатных функций. Давайте их перечислим
и кратко рассмотрим.

СУММА() - суммирует значения поля;
МИНИМУМ() - выбирает из всех значений поля минимальное;
МАКСИМУМ() - выбирает из всех значений поля максимальное;
СРЕДНЕЕ() - рассчитывает среднее значение (Сумма/Количество);
КОЛИЧЕСТВО() - выводит количество записей;
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) - выводит количество записей у которых различаются значения;
Продемонстрируем их работу на нашем примере. Добавим в наш запрос другие агрегатные
функции кроме СУММЫ .

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

В результате выполнения запроса получим следующую таблицу:

Здесь мы в поле Количество получили общее количество записей в таблице,
а в поле КоличествоРазличные - количество записей, у которых в поле
Товар значения различаются. Применительно к нашей таблице это Карандаш
и Ручка .

Как видно из последнего примера, если ко всем полям в запросе применяются
агрегатные функции, то использовать СГРУППИРОВАТЬ ПО не нужно.

В прошлом уроке была рассмотрена фильтрация результатов запроса. С помощью предложения ГДЕ задавали условия отбора из исходных таблиц, чтобы в запросе обрабатывались только те записи, для которых условие истинно. Причем условие отбора могло быть как простым, так и достаточно сложным логическим выражением. Цель такой обработки - максимально ограничить объем получаемых в результирующем наборе данных. Что дальше делать с тем множеством данных, которые ограничить не удалось, поскольку они по всем параметрам нужны?

1. Зачем группируют данные?

Возможность располагать всеми данными в системе, несомненно, - залог эффективного управления. Однако, как мы уже убедились, все данные всех таблиц базы данных пользователям не нужны. Да и нужные данные существуют не просто так, а для чего-то.

Но даже вполне корректно в этих целях отобранные данные анализировать сложно: их будет слишком много. Все данные контролировать тоже нереально, а управлять ими - просто невозможно. Недаром говорят, что суть управленческого учета сводится к проблеме определенного агрегирования (группирования) информации.

Первая причина группировки - уменьшить количество информации , сведя ее в однородные группы. Например, суммы продаж и покупок сводятся в группы, которыми служат бухгалтерские счета. Затем по этим уже сводным показателям формируются более крупные показатели - балансовые группы, различные отчетные показатели. Однако меньше, чем счетом, уже не оперируют.

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

Вторая причина - нормирование показателей . Стандартные показатели (рентабельность, оборачиваемость и т. п.) можно измерять (например, в количественном выражении) и сравнивать с ними.

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

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

2. Что такое группировка?

Группировка - это процесс объединения в логическом порядке столбцов с повторяющимися значениями. На рис. 1 представлен фрагмент таблицы товаров, которая может быть сгруппирована по любому полю. Например, по цвету корпуса - рядом окажутся приборы в корпусах одного цвета, или страна-производитель - тогда мы получим другую группировку, где рядом окажутся товары одной страны. На рисунке голубым цветом выделены товары в серебристых корпусах; желтым - товары производства Тайваня. Разумеется, это различные группировки и они могут и не пересекаться. Нужного нам товара производства Тайваня может и не быть в серебристом корпусе.

3. Как осуществляется группировка?

Группировка данных - это понятный и технически достаточно простой процесс.

Исходные данные в запросе могут быть сгруппированы по одинаковым значениям полей, и к ним можно применить различные агрегатные функции. Группируются записи, которые содержат одинаковые значения группировочного поля. Допустим, поле Родитель - и будет сгруппирована вся посуда, алкоголь и т. д., поле Цвет - и сгруппировано все проданное по цветам и т. п.).

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

Для иллюстрации этого примера на рисунке 1 можно применить функцию КОЛИЧЕСТВО (Цветкорпуса) . Строки в результате запроса будут содержать результаты вычисления указанных агрегатных функций (СУММА, КОЛИЧЕСТВО, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ) , рассчитанные (сгруппированные) по данным исходных столбцов.

Агрегатные функции указывают в списке полей выборки в предложениях СГРУППИРОВАТЬ ПО , при этом обязательно нужно указать список полей, по которым необходимо проводить группировку. Если совсем не указать группировку, по умолчанию будет одно число (для текстовых полей - количество, для числовых - сумма всех записей).

4. Место в запросе предложения СГРУППИРОВАТЬ ПО

Чтобы создавать запросы, стоит знать, куда попадают визуально указанные вами параметры при трансляции их конструктором. Вот место предложения СГРУППИРОВАТЬ ПО среди других предложений запроса.

Как видите, оно следует после предложения ГДЕ и перед предложением УПОРЯДОЧИТЬ (если таковые имеются в запросе).

5. Пример создания запроса с группировкой с помощью конструктора запросов «1С:Предприятия 8.0»

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

Воспользуемся запросом, который мы уже создали. Ниже приведен его текст (блок в зеленой рамке), который мы уже написали, и изменять его не будем. Копию предыдущего запроса можно сделать в окне Сервис - Консоль Отчетов , пользуясь инструментами верхней панели, и вызвать на редактирование с помощью кнопки F2 .

Конструктор запросов вызывается с помощью кнопки на нижней панели инструментов окна редактирования запроса.

Еще раз вернемся к той части текста, которую мы уже создали. Мы выбрали поля с названием проданной номенклатуры, сумму продажи (назвали ее выручкой). Воспользовались возможностью получить сразу результат вычисления полей, содержащих выручку и себестоимость (назвали эту разность прибылью). Все это сделано с помощью закладок Таблицы и поля , Псевдонимы конструктора запросов «1С:Предприятия». Выбор полей сделан из таблиц справочника номенклатура и регистра накопления продажи компании.

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

Теперь мы можем приступать к группировке. Для этого используются закладки Группировки и Итоги .

На закладке Группировки мы выбираем поле, по которому будем группировать, - по полю Родитель (рис. 2 ). Остальные поля (текст запроса в голубой рамке) с представлением элементов поставит конструктор. И ниже в Суммируемом поле (рис. 3 ) мы поставим агрегатные функции, значения которых нам нужны - сумму выручки по группе и среднюю прибыль по продажам.

Что здесь важно? То, что группировать можно только по полям, которые указаны в списке выборки. Это вполне логично: если значения поля не выбраны, они неизвестны, и нельзя осуществить группировку. Если же мы хотим наложить впоследствии условие на значение группировочного поля предложением ИМЕЮЩИЕ (например, посчитали среднюю оборачиваемость и выбрали только те группы, по которым этот показатель ниже нормы), обязательно должны быть группировки по таким полям. Это условие необходимо соблюдать, даже если не нужно видеть эти поля в итогах.

Это тоже понятно: сначала нужно сгруппировать и рассчитать, а только потом можно увидеть, отвечает ли результат каким-то условиям. Сообщения о таких ошибках вы получите при проверке запроса, но лучше сразу иметь это в виду.

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

Далее указываются агрегатные функции, значения которых нужно получить, - а мы хотим знать сумму выручки по группе и среднюю прибыль (рис. 5 ). Как параметры этой закладки преобразованы в текст запроса, видно в желтом блоке текста запроса.

На закладке Порядок мы указали сортировку по полю Выручка по убыванию. Обычно, когда нас интересуют какие-то показатели, более удачной бывает сортировка по числовым полям. Если этого не сделать, будет принята сортировка первой из объединенных таблиц (а это таблица справочника «Номенклатура»). Наименования товаров будут отсортированы по алфавиту.

Везде в итогах, группировках и сортировках, если мы квалифицировали столбцы (дали им псевдонимы - товар, выручка, прибыль), можно использовать квалифицированные имена. Теперь можно нажать кнопку ОК в конструкторе - будет выполнена синтаксическая проверка, и если все нормально, можно выполнить запрос.

Если мы хотим наложить условие на группы, нужно наложить условия на значения агрегатных функций группировочных полей с помощью предложения ИМЕЮЩИЕ (следует непосредственно за предложением СГРУППИРОВАТЬ ). Например, ИМЕЮЩИЕ СРЕДНЕЕ (Прибыль) > 500 - и в наборе не будет ни одной группы, в которой средняя по группе прибыль не достигла 500,01 грн.

На рисунке 6 представлено то, что у нас получилось в итоге (фрагмент получившейся таблицы, сделанной по данным демонстрационной версии конфигурации «Управление торговлей для Украины 8.0» ред. 2.1, чтобы можно было воспроизвести результаты).

Запрос такой сложности уже вполне подходит для работы. Более того, если добавить сюда еще одно поле - рентабельность товарных запасов (прибыль, разделенная на среднедневной остаток товаров за период), в таблицу будут включены основные показатели, с которыми работают менеджеры по управлению запасами. Для этого в запрос нужно добавить еще таблицу «ОстаткиТоваровКомпании», соединить таблицы и снова пройти все закладки.

ВЫБРАТЬ <поля1,поля2>

ИЗ<таблица1,таблица2>

ГДЕ<условия>

СГРУППИРОВАТЬ ПО <поля1,поля2>

УПОРЯДОЧИТЬ ПО <поля1, поля2>

ИТОГИ <фукция1,функция2>

ПО <поле1,поля2>

СпрНоменклатура.Предста-вление,

ПродажиКомпанииОбороты. СуммаПродажиОборот

КАК Выручка,

ПродажиКомпанииОборо-ты.СуммаПродажиОборот ПродажиКомпанииОбороты. СебестоимостьОборот КАК Прибыль

ИЗ Справочник. Номенклатура

КАК СпрНоменклатура

ВНУТРЕННЕЕ СОЕДИНЕНИЕРе-гистрНакопления.ПродажиКомпании.Обороты(&ДатаНа-чала, &ДатаКонца,)

КАК ПродажиКомпании Обороты

ПО ПродажиКомпанииОбо-роты.Номенклатура = СпрНоменклатура.

СГРУППИРОВАТЬ ПО

СпрНоменклатура.Родитель,

СпрНоменклатура.Представ-ление

УПОРЯДОЧИТЬ ПО