Обход запроса по группировкам, как собираются данные в запросе с конструкцией ИТОГИ ПО — (1С: Программисту: Язык запросов) — 1c-h
 

Обход запроса по группировкам, как собираются данные в запросе с конструкцией ИТОГИ ПО — (1С: Программисту: Язык запросов)

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...

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

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

Контрагент 1

  • АдресДоставки1
  • заказ 1
  • заказ 2
  • заказ 3

Контрагент 1

  • АдресДоставки2
  • заказ 4
  • заказ 5

Контрагент2

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

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

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

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

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

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

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

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

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

<Итоги>

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

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

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

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

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

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

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

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

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

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

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

Ключевое слово ОБЩИЕ означает, что необходимо сформировать итоговую строку по всему результату запроса. Подробнее расчет общих итогов описан в Расчет общих итогов.

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

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

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

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

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

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

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

Пример:

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

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

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

Итоги Общие

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

НаименованиеСуммаСреднееМаксимумМинимум

Колич
265 955,4512 511,1240 000,23

 

5558
Брюки детские28 500,229 500,0715 0003 0003
Рубашка «Ковбойка»24 0008 00016 0004 0003
Джинсы женские63 5556 355,530 00055510
Свитер детский6 4006 4006 4006 4001
Раковина «Лилия»60 000,2330 000,11540 000,2320 0002
Мойдодыр «Аквариум»65 50021 833,3340 0008 0003
Смеситель «Ультра»15 00015 00015 00015 0001
Кухонный комбайн Крупс3 0003 000

 

3 000

 

3 000

 

1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример:

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

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

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

ВсегоРазные
248

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

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

Пример:

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

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

ТоварКоличествоНомерПолучатель
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 (самый низший);
  • тип Булево;
  • тип Число;
  • тип Дата;
  • тип Строка;
  • ссылочные типы.

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

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

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

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

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

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

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

Пример:

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

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

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

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

Пример:

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

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

Где
Справочник.Номенклатура.Ссылка В ИЕРАРХИИ (&Группа)

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

Пример:

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

Где
Справочник.Номенклатура.Ссылка В ИЕРАРХИИ
(
ВЫБРАТЬ
Справочник.Номенклатура.Ссылка
ГДЕ
Справочник.Номенклатура.Наименование = «Одежда»
)

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

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

Пример:

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

Результат запроса:
Товары
Кран
Вантус
Стол
Стул

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

Пример:

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

Результат запроса:
Товары
Сантехника
Мебель

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

Пример:

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

Результат запроса:
Товары
Кран
Вантус
Стол
Стул

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

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

Пример:

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

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

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

Пример:

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

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

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

Пример:

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

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

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

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

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

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

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

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

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

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

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

Пример:

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

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

ТоварКоличествоНомерПолучатель
Сантехника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Урюпинскторг
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...

Автор 1С

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

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

Next Post

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

Пт Дек 10 , 2010
Вопрос Как в 1С получить срез данных на даты из другого множества, т.е. для каждого элемента множества получить значение, действующее на конкретную дату из другого множества. Ответ Постановка задачи. Итак, есть множество в виде таблицы «Срез»: ДатаСреза ОбъектСреза 14.01.2010 1