Количество различные запрос 1с. Агрегатные функции системы компоновки данных. Встроенные функции языка запросов

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

Программирование 1С состоит не только из написания программы. 1С это слиток действий пользователя и данных с которыми он работает.

Данные хранятся в базе данных. Запросы 1С – это способ доставать данные из базы данных для того, чтобы показать пользователю в форме или чтобы обработать их.

Основополагающая часть отчета – это запрос 1С. В случае отчета СКД – это большая часть отчета.

Сядьте. Вздохните. Успокойтесь. Сейчас я скажу Вам новость.

Чтобы программировать в 1С недостаточно знать язык программирования 1С. Нужно еще знать язык запросов 1С.

Язык запросов 1С – это совершенно отдельный язык, который позволяет указать какие данные нам нужно достать из базы данных.

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

Как используются Запросы 1С

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

Запросы 1С бывают:

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

Создание и выполнение запросов 1С

Запрос 1С – это собственно текст запроса на языке запросов 1С.
Текст можно написать ручками. То есть взять и написать (если знаешь этот язык).

Так как 1С продвигает концепцию визуального программирования, где многое или почти все можно сделать без написания кода ручками – есть специальный объект Конструктор запроса, который позволяет без знания языка запросов нарисовать текст запроса. Однако чудес не бывает – для этого нужно знать как работать с конструктором.

После того как готов текст запроса 1С – его нужно выполнить. Для этого есть объект в коде 1С Запрос(). Вот пример:

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

Результат можно выгрузить в обычную таблицу:
Выборка = Запрос.Выполнить().Выгрузить(); //Результат – таблица значений

Или просто обойти по строчкам.
Выборка = Запрос.Выполнить().Выбрать();
Пока Выборка.Следующий() Цикл
//Что-то делаем с результатами запроса
КонецЦикла;

Работа с запросами 1С

Основные принципы запросов 1С

Основные принципы построения запроса 1С –
ВЫБРАТЬ СписокПолей ИЗ НазваниеТаблицы ГДЕ Условия

Пример построения такого запроса 1С:

ВЫБРАТЬ
//список полей, которые надо выбрать
Ссылка,
Наименование,
Код
ИЗ
//наименование таблицы, откуда выбираем данные
//список таблиц – это список объектов в окне конфигуратора
Справочник.Номенклатура
ГДЕ
//указываем отбор
ВидТовара = &Услуга //отбор по внешнему значению
Или Услуга // «Услуга» реквизит типа Булево, отбор по значению Истина
УПОРЯДОЧИТЬ ПО
//Сортировка
Наименование

Список таблиц 1С

Названия таблиц Вы смотрите в окне конфигуратора. Только необходимо вместо «Справочники» писать «Справочник», например «Справочник.Номенклатура» или «Документ.РеализацияТоваровУслуг» или «РегистрНакопления.Продажи».

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

РегистрСведений.ИмяРегистра.СрезПоследних(&Дата) – запрос 1С из регистра сведений, если он является периодическим, на определенную дату

РегистрНакопления.ИмяРегистра.Остатки(&Дата) – запрос 1С из регистра остатков на определенную дату

РегистрНакопления.ИмяРегистра.Обороты(&ДатаНачала, &ДатаОкончания) – запрос 1С из регистра оборотов за период с даты начала по дату окончания.

Дополнительные принципы

Когда мы запрашиваем список каких то данных – работают основные принципы. Но мы можем запросить также цифры и запрос может нам их посчитать (сложить например).

ВЫБРАТЬ
//Количество(ИмяПоля) – считает количество
//Поле КАК ДругоеИмя – переименовывает поле
Количество(Ссылка) КАК КоличествоПроведенныхДокументов
ИЗ

ГДЕ
Проведен

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

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

Организация

Этот запрос 1С вернет нам количество документов по каждой организации (также говорят «в разрезе организаций»).

Посчитаем дополнительно с помощью запроса 1С сумму этих документов:

ВЫБРАТЬ
//просто поле документа
Организация,
//считаем количество

//считаем сумму

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

Этот запрос 1С вернет нам также сумму документов.

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

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

Кратко о дополнительных возможностях языка запросов 1С:

Соединение данных из нескольких таблиц
Вложенные запросы
Пакетный запрос
Создание собственных виртуальных таблиц
Запрос из таблицы значений
Использование встроенных функций получения значения и манипулирования значениями.

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

Чтобы не писать текст запроса руками – существует конструктор запросов 1С. Просто нажмите правой кнопкой мыши в любом месте модуля и выберите Конструктор запроса 1С.

Выберите в конструкторе запросов 1С нужную таблицу слева и перетащите правее.

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

На закладке Условия в конструкторе запросов 1С Вы можете выбрать нужные отборы таким же способом (перетащив поля, по которым Вы будете делать отбор). Не забудьте выбрать верное условие.

На закладке Порядок – указывается сортировка. На закладке Итоги – суммирование итогов.

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

<Разыменование поля> |

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

<Встроенная функция> |

<Операция выбора> |

<Приведение типа>[.<Разыменование поля>] |

<3начение> |

<Выражение> <Бинарная операция> <Выражение> |

<Унарная операция> <Выражение> |

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

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

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

Разыменование полей

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

[<Таблица>.]<Имя поля>[.<Имя поля>[...]]

<Имя таблицы> | <Псевдоним источника>

Разыменование поля начинается с имени таблицы, содержащей это поле. Если <Имя поля> уникально - существует только у одной из таблиц среди указанных в списке источников, <Таблица> может быть опущена.

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

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

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

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

СУММА (<Выражение >) |

СРЕДНЕЕ (<Выражение>) |

МИНИМУМ (<Выражение>) |

МАКСИМУМ (<Выражение>) |

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

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

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

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

Наименование Сумма Среднее Максимум Минимум Колич
265 955,45 12 511,12 40 000,23 555 8
Брюки детские 28 500,22 9 500,07 15000 3000 3
Рубашка «Ковбойка» 24000 8000 16000 4000 3
Джинсы женские 63555 6 355,5 30000 555 10
Свитер детский 6400 6400 6400 6400 1
Раковина «Лилия» 60 000,23 30000,115 40 000,23 20000 2
Мойдодыр «Аквариум» 65500 21 833,33 40000 8000 3
Смеситель «Ультра» 15000 15000 15000 15000 1
Кухонный комбайн Крупс 3000 3000 3000 3000 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В языке запросов определены встроенные функции, которые могут использоваться в выражениях в списке полей выборки (см. стр. 300) и в условии отбора в предложении ГДЕ (см. стр. 315). Определены следующие встроенные функции:

Данная функция предназначена для выделения подстроки из строки.

<Выражение>

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

<3начение>

Позиция символа, с которого начинается выделяемая из строки подстрока. Значение типа Число.

<3начение>

Длина выделяемой подстроки. Значение типа Число.

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

Выбрать
Справочних.Контрагенты.Наименование,
Подстрока(Справочник.Контрагенты.Наименование, 3, 5) Как Подстрока

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

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

Данная функция предназначена для вычисления номера квартала из з гачения типа Дата. Номер квартала находится в диапазоне 1-4.