1с 8 максимум в запросе. Агрегатные функции системы компоновки данных (7 видео)

1с 8 максимум в запросе. Агрегатные функции системы компоновки данных — Правник

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

Войдите на сайт как ученик

Войдите как ученик, чтобы получить доступ к материалам школы

Содержание

Язык запросов 1С 8.3 для начинающих программистов: группировка
Группировка в запросах
Агрегатная функция СУММА
Агрегатная функция СРЕДНЕЕ
Агрегатная функция МИНИМУМ
Агрегатная функция МАКСИМУМ
Агрегатная функция КОЛИЧЕСТВО
Агрегатная функция КОЛИЧЕСТВО РАЗЛИЧНЫЕ
Группировка без группируемых полей и агрегатных функций
Группировка без группировочных полей
Группировка по нескольким полям
Пройдите тест
Выражения в языке запросов. Агрегатные функции системы компоновки данных 1с выбрать максимальное значение в запросе
Задача 1
Задача 2
Задача 3
Функции языка запросов 1с
Онлайн-школа программирования в 1С
Выражения в языке запросов. Агрегатные функции системы компоновки данных 1с выбрать максимальное значение в запросе
Запросы 1С 8.3 и 8.2 от А до Я
Структура запросов
Общие конструкции
🔥 Видео

Язык запросов 1С 8.3 для начинающих программистов: группировка

Автор уроков и преподаватель школы: Владимир Милькин

Группировка в запросах

Давайте запросим из таблицы Справочник.Еда следующие реквизиты: Наименование, Цвет и Калорийность:

ВЫБРАТЬ Наименование, Цвет, КалорийностьИЗ Справочник.Еда

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

Алгоритм для жёлтого цвета будет такой:

Находим все строчки у которых в поле Цвет стоит Жёлтый.
Это будут строчки №1, 6, 8 и 9.
Суммируем поле Калорийность для каждой из этих строк: 89 + 31 + 340 + 536
Получаем, что для жёлтого цвета суммарная калорийность равна 996.

И так для каждого цвета.

Описанный выше процесс называется группировкой. Таким образом, группировка — это «схлопывание» (свёртка) строчек таблицы по определенному признаку.

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

Группировочные — это как раз те поля, по которым идёт свёртка. В нашем случае таким полем является Цвет.
Группируемые — это те поля, которые сворачиваются (схлопываются, объединяются). В нашем случае таким полем является Калорийность.

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

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

Это как раз случай, который мы разбирали. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Все строки группируются по группировочным полям (Цвет), а группируемые поля (Калорийность) суммируются:

ВЫБРАТЬ Цвет, СУММА(Калорийность)ИЗ Справочник.ЕдаСГРУППИРОВАТЬ ПО Цвет

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

В этом случае все строки группируются по группировочным полям (Цвет), а среди группируемых полей (Калорийность) находится среднее значение:

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

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

В этом случае все строки группируются по группировочным полям (Цвет), а среди группируемых полей (Калорийность) находится минимальное значение:

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

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

В этом случае все строки группируются по группировочным полям (Цвет), а среди группируемых полей (Калорийность) находится максимальное значение:

ВЫБРАТЬ Цвет, МАКСИМУМ(Калорийность)ИЗ Справочник.ЕдаСГРУППИРОВАТЬ ПО Цвет

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

В этом случае все строки группируются по группировочным полям (Цвет), а среди группируемых полей (Калорийность) находится их количество:

ВЫБРАТЬ Цвет, КОЛИЧЕСТВО(Калорийность)ИЗ Справочник.ЕдаСГРУППИРОВАТЬ ПО Цвет

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

В этом случае все строки группируются по группировочным полям (Цвет), а среди группируемых полей (Калорийность) находится количество элементов с различными значениями:

ВЫБРАТЬ Цвет, КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Калорийность)ИЗ Справочник.ЕдаСГРУППИРОВАТЬ ПО Цвет

Функция КОЛИЧЕСТВО РАЗЛИЧНЫЕ требует пояснения, потому что на выбранном примере её результат совпадает с функцией КОЛИЧЕСТВО. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Вот более показательный пример, который всё объясняет:

Группировка без группируемых полей и агрегатных функций

Использование агрегатных функций в запросе не требуется, если результатом запроса будут только группировочные поля:

К примеру, сделаем выборку всех вкусов, которые встречаются среди еды:

ВЫБРАТЬ ВкусИЗ Справочник.Еда

Как видите вкусы повторяются — давайте их сгруппируем:

ВЫБРАТЬ ВкусИЗ Справочник.ЕдаСГРУППИРОВАТЬ ПО Вкус

Группировка без группировочных полей

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

К примеру, получим результаты агрегатных функций применительно к полю Калорийность без группировочных полей (то есть по всей таблице):

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

Группировка по нескольким полям

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

Пройдите тест

Начать тест

9. Есть список чисел 1, 1, 2, 2, 3, 4, 5, 5, 6. После группировки он примет вид

1, 6

1, 2, 3, 4, 5, 6

1, 2, 3, 3, 4, 4, 5, 6, 6

1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6

а) Напишите запрос, который группирует цвета, встречающиеся среди еды:

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

https://www.youtube.com/watch?v=w6ezIwvy_RQ

Войдите на сайт как ученик

Авторизуйтесь, чтобы получить доступ ко всем материалам школы

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

yazyk-zaprosov-1s-8-3-dlya-nachinayushhix-programmistov-gruppiroa

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

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

Развернуть группировку после выполнения запроса и узнать, какие строки в нее вошли, после выполнения запроса невозможно (в отличие от применения Итогов).

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

Группировочные столбцы обозначаются в особой секции запроса, которая начинается с фразы СГРУППИРОВАТЬ ПО. Группируемые столбцы обозначаются через агрегатные функции в секции ВЫБРАТЬ.

Существует 6 видов агрегатных функций, применяемых при группировках:

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

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

Рассмотрим применение группировок в запросах 1С на примерах.

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

Задача 1

Узнать, какие поставщики поставляли товары.

Решение: Одним из вариантов решений может быть выбор единственного стобца Поставщики группировка по нему.

Запрос.

Текст=»ВЫБРАТЬПоставки.ПоставщикИЗПоставки КАК ПоставкиСГРУППИРОВАТЬ ПО

Поставки.Поставщик»

;

Задача 2

Узнать общее количество поставленного товара каждого вида.

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

Запрос.

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

Поставки.Товар»

;

Задача 3

Определить, сколько в среднем единиц товара поставляет каждый поставщик.

Решение: Необходимо выбрать два столбца: поставщик и количество, сгруппировать полученный результат по полю Поставщик, применив агрегатную функцию Среднее.

Запрос.

Текст=»ВЫБРАТЬПоставки.Поставщик,СРЕДНЕЕ (Поставки.Количество) КАК КоличествоИЗПоставки КАК ПоставкиСГРУППИРОВАТЬ ПО

Поставки.Поставщик»

;

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

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

https://www.youtube.com/watch?v=lGLJwiPW-8Q

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

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

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

1. ФункцияДАТАВРЕМЯ — данная функция создает константное поле с типом «Дата».

Синтаксис: ДАТАВРЕМЯ(,,,,,)

Пример использования:

2. Функция РАЗНОСТЬДАТ — возвращает разность двух дат в одном из измерений (год, месяц, день, час, минута, секунда). Измерение передается в параметре.

Синтаксис: РАЗНОСТЬДАТ(, , )

Пример использования:

Запрос.

Текст = «ВЫБРАТЬ | РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2015, 4, 17), ДАТАВРЕМЯ(2015, 2, 1), ДЕНЬ) | КАК КолвоДней»;

3. Функция ЗНАЧЕНИЕ — задает константное поле с предопределенной записью из базы данных, также можно получить пустую ссылку любого типа.

Синтаксис: ЗНАЧЕНИЕ()

Пример использования:

Запрос.

Текст = «ВЫБРАТЬ //предопределенный элемент | ЗНАЧЕНИЕ(Справочник.Валюты.Доллар) КАК Доллар, //пустая ссылка | ЗНАЧЕНИЕ(Документ.ПоступлениеТоваровУслуг.ПустаяСсылка) КАК Поступление, //знач. перечисления | ЗНАЧЕНИЕ(Перечисление.ЮрФизЛицо.ФизЛицо) КАК ФизЛицо, //предопределенный счет | ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Материалы) КАК Счет_10» ;

4. Функция ВЫБОР — перед нами аналог конструкции ЕСЛИ который используется в коде, только эта используется в запросах 1С.

Синтаксис: ВЫБОР КОГДА ТОГДА ИНАЧЕ КОНЕЦ

Пример использования:

Запрос.

Текст = //если сумма больше 7500, тогда должна быть скидка 300 рублей, //поэтому если условие срабатывает то функция //возвращает Сумма — 300 //в противном случае запрос вернет просто Сумма «ВЫБРАТЬ | ВЫБОР | КОГДА ТЧПоступления.Сумма > 7500 | ТОГДА ТЧПоступления.Сумма — 300 | ИНАЧЕ ТЧПоступления.Сумма | КОНЕЦ КАК СуммаСоСкидкой |ИЗ | Документ.ПоступлениеТоваровУслуг.Товары КАК ТЧПоступления»;

5. Функция ВЫРАЗИТЬ — позволяет выразить константное поле определенным типом.

Синтаксис: ВЫРАЗИТЬ(НазваниеПоля КАК НазваниеТипа)

Пример использования:

Запрос.

Закупки КАК Закупки»;

Еще есть вариант использования функции ВЫРАЗИТЬ в полях смешанных типах, где такие встречаются? Самый простой пример это «Регистратор» у любого регистра.

Так зачем нам может понадобиться уточнять тип в регистраторе? Давайте рассмотрим ситуацию когда мы из регистратора выбираем поле «Номер», из какой таблицы будет выбран номер? Правильный ответ из всех! Поэтому чтобы наш запрос работал быстро следует указывать явный тип с помощью функции ВЫРАЗИТЬ

Пример использования:

vyrazheniya-v-yazyke-zaprosov-agregatnye-funkcii-sistemy-komponoi.html

Видео:СКД. Разработка отчетов в 1ССкачать

Онлайн-школа программирования в 1С

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

Войдите на сайт как ученик

Войдите как ученик, чтобы получить доступ к материалам школы

Видео:Макет ресурсов - Применение системы компоновки данных в 1С:Предприятие 8 - 1С:Учебный центр №1Скачать

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

Существует 6 видов агрегатных функций, применяемых при группировках:

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

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

Рассмотрим применение группировок в запросах 1С на примерах.

Видео:Настройки отчетов в 1С:ЗУП 3.0 - Агрегатные функцииСкачать

Запросы 1С 8.3 и 8.2 от А до Я

Закажите бесплатный расчет стоимости вашей задачи по 1С!
Перезвоним за 10 минут! (в рабочие часы)

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

Язык запросов – самой простой и эффективный способ получения информации.

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

Структура запросов

Для получения данных достаточно использовать конструкции «ВЫБРАТЬ» (select) и «ИЗ» (from). Простейший запрос выглядит следующим образом:

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

https://www.youtube.com/watch?v=x_veVwxdezU

Где «*» означает выбор всех полей таблицы, а Справочники.Номенклатура – имя таблицы в базе данных.

Рассмотрим более сложный и общий пример:

ВЫБРАТЬ КАК ,Сумма() КАК ИЗ КАК СОЕДИНЕНИЕ КАК

ПО

ГДЕ

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

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

ИТОГИПО

В данном запросе мы выбираем данные полей «ИмяПоля1» и «ИмяПоля1» из таблиц «ИмяТаблицы1» и «ИмяТаблицы», присваиваем полям синонимы с помощью оператора «КАК», соединяем их по некому условию «УсловиеСоединениеТаблиц».

Из полученных данных мы отбираем только данные, соответствующие условию из «ГДЕ» «УсловиеОтбораДанных».Далее мы группируем запрос по полю «ИмяПоля1», при этом суммируя «ИмяПоля2».Создаём итоги по полю «ИмяПоля1» и итоговым полем «ИмяПоля2».

Последним действием мы сортируем запрос с помощью конструкции «УПОРЯДОЧИТЬ ПО».

Общие конструкции

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

ПЕРВЫЕ n

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

Пример:

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

Банки.Наименование

Запросом будет получено первых 100 записей справочника «Банки», упорядоченных по алфавиту.

РАЗРЕШЕННЫЕ

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

Если пользователь пытается с помощью запроса прочитать записи недоступные ему, он получит сообщение об ошибке. Чтобы этого избежать, следует использовать конструкцию «РАЗРЕШЕННЫЕ», т.е запрос будет читать только разрешенные ему записи.

Пример:

ВЫБРАТЬ РАЗРЕШЕННЫЕХранилищеДополнительнойИнформации.СсылкаИЗ

Справочник.ХранилищеДополнительнойИнформации

РАЗЛИЧНЫЕ

Использование «РАЗЛИЧНЫЕ» позволит исключить попадание строк-дублей в результат запроса 1С. Дублирование означает совпадение всех полей запроса.

Пример:

ВЫБРАТЬ ПЕРВЫЕ 100Банки.Наименование,Банки.Код КАК БИКИЗ

Справочник.Банки КАК Банки

ПустаяТаблица

Данная конструкция используется очень редко для объединения запросов. При объединении может возникнуть необходимость указать в одной из таблиц пустую вложенную таблицу. Для этого как раз подойдет оператор «ПустаяТаблица»

Пример из справки 1С 8:

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

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

ЕСТЬNULL

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

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

ПО НоменклатураСпр.Ссылка = РеализованныеТоварыКомитентовОстатки.Номенклатура

Можно использовать и по-другому. Например, если для каждой строки не известно, в какой таблице существует значение:

ЕСТЬNULL(СчетФактураПолученный.Дата, СчетФактураВыданный.Дата)

КАК

КАК – оператор, который позволяет нам присвоить имя (синоним) таблицы или полю. Пример использования мы видели выше.

ПРЕДСТАВЛЕНИЕ и ПРЕДСТАВЛЕНИЕССЫЛКИ

Данные конструкции очень похожи – они позволяют получить строковое представление нужного значения.

Единственное отличие в том, что ПРЕДСТАВЛЕНИЕ преобразует любые значения в строковый тип, а ПРЕДСТАВЛЕНИЕССЫЛКИ — только ссылочные.

ПРЕДСТАВЛЕНИЕССЫЛКИ рекомендуется применять в запросах системы компоновки данных для оптимизации, если, конечно, поле ссылочных данных не планируется использовать в отборах отчета на СКД.

Пример:

ВЫБРАТЬПредставление(Ссылка), //строка, например «Авансовый отчет №123 от 10.10.2015Представление(ПометкаУдаления) КАК ПометкаУдаленияТекст, //строка, «Да» или «Нет»ПредставлениеСсылки(ПометкаУдаления) КАК ПометкаУдаленияБулево //булево, Истина или ЛожьИЗ

Документ.АвансовыйОтчет

ВЫРАЗИТЬ

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

https://www.youtube.com/watch?v=-11o8ZjEEhA

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

ВЫРАЗИТЬ(ТаблицаЗатрат.Субконто1 КАК Справочник.СтатьиЗатрат).ВидДеятельностиДляНалоговогоУчетаЗатрат

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