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

Настройка итогов в схеме компоновки данных 1С

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

Доброго времени суток, уважаемые читатели блога SoftMaker.kz. В статье Система компоновки данных (СКД) уже было подробно рассказано о других важных составляющих СКД. А сегодня мы поговорим, как настроить итоги в схеме компоновки данных.

Определение ресурсов в схеме компоновки данных 1С

В компоновке данных настройка итогов выглядит несколько иначе, чем в запросах. Определим в системе компоновки данных набор данных «Запрос».

В самом запросе мы не настраиваем итоги, а переходим на закладку «Ресурсы» компоновки данных. На уровне схемы компоновки данных мы определяем ресурсы. Это те поля, которые нужно посчитать на уровне группировок. Нажмём на кнопку «>>» и система сама перенесёт все числовые поля и определит их в качестве ресурсов.

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

Итак, в схеме компоновки мы определяем только ресурсы, а сами группировки настраиваются на уровне варианта отчёта.

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

Создадим стандартную настройку компоновки данных.
Нажмём на кнопку «Открыть конструктор настроек».

Выберем тип отчета — список. Нажмём кнопку «Далее».

Выберем все поля и переместим поле контрагент на самую верхнюю позицию. Нажмём кнопку «Далее».

Выберем все поля и переместим поле контрагент на самую верхнюю позицию. Нажмём кнопку «OK».

Получилась следующая настройка:

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

Если открыть наш отчёт в режиме 1С:Предприятие и сформировать его, то мы увидим, что формируются итоговые данные на уровне группировок. Итоги по номенклатуре и по контрагентам.

Настройка ресурсов в схеме компоновки данных 1С

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

Здесь в поле «Выражение» мы можем написать и своё собственное выражение.

В поле «Выражение» мы можем обращаться и к функциям общих модулей.

Кроме того, можно указать в поле «Расчитывать по…» по каким группировкам необходимо рассчитывать ресурс. Если поле «Расчитывать по…» не заполнено, то итоговое значение ресурса будет считаться на всех уровнях группировки, которые определены в настройках варианта отчёта.

В нашем случае нужно заполнить поле «Расчитывать по…» ресурса «Количество», так как мы можем продавать товар с разными единицами измерения. Например: масло в литрах и колёса в штуках.

Не правда ли, нелогично будет складывать количество этих товаров? Поэтому нам нужно оставить суммирование количества на уровне номенклатуры, а на уровне контрагентов
суммирование уберём.

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

Возможности при описании ресурсов в схеме компоновки данных 1С

Давайте рассмотрим ряд неочевидных особенностей связанных с описанием ресурсов.

  • Каждый ресурс можно определить несколько раз. Но это имеет смысл лишь в том случае, еслиресурс будет рассчитываться по разным уровням группировок. Например, если количество, в одном случаесуммируется для номенклатуры, а по контрагентам получаем минимальное значение.

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

  • В поле «Выражение», кроме написания формулы, можно использовать специальную функцию компоновки данных которая называется «Вычислить». Эта функция позволяет вычислить какое-то итоговое значение по определённой формуле. Например, по каждому контрагенту необходимо знать процент закупки в натуральных единицах относительно общего объёма. Но как получить общий объём закупок по количеству? Для этого воспользуемся функцией «Вычислить» и запишем следующее выражение в поле «Выражение»:

Сумма(Количество)/Вычислить(«Сумма(Количество)», «ОбщийИтог»)*100

Как видно, все параметры функции «Вычислить» являются строковыми. Чтобы поле количество отображалось в отчёте красиво, настроим его на вкладке «Наборы данных». В строке количество найдём поле «Параметры редактирования». Откроем диалог, отыщем строку «Формат» и отредактируем в нём форматную строку, поставив на вкладке «Число», значение «Точность» равным двум.

Запустим отчёт и увидим результат вычисления процента закупки по контрагенту «АУПП КОС ТОО» относительно
общего объёма:

Скачайте бесплатную книгу по основам СКД в системе 1С 8

В конце статьи хочу посоветовать вам бесплатный курс по компоновке данных от Сотникова Анатолия. Это курс от опытного программиста. Он на отдельной базе покажет вам, как строить отчеты в СКД. Вам только нужно внимательно слушать и запоминать! Вы получите ответы на такие вопросы:

      
  • Как создать простой отчет в виде списка?
  •  

  • Для чего нужны колонки Поле, Путь и Заголовок на закладке «Поля»?
  •   

  • Какие существуют ограничения для полей компоновки?
  •  

  • Как правильно настраивать роли?
  •   

  • Какие существуют роли для полей компоновки?
  •   

  • Где найти закладку компоновка данных в запросе?
  •   

  • Как настраивать параметры в СКД?
  •  

  • Дальше еще интереснее…

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

Вот один из уроков о закладке компоновки данных в запросе:

Источник: http://www.softmaker.kz/skd/nastrojka-itogov-v-sxeme-komponovki-dannyx-1s.html

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

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

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

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

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

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

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

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

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

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

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

Задача 1

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

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

Запрос.

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

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

;

Задача 2

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

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

Запрос.

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

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

;

Задача 3

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

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

Запрос.

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

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

;

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

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

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

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

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

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

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

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

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

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

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

Запрос.

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

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

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

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

Запрос.

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

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

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

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

Запрос.

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

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

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

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

Запрос.

Текст = «ВЫБРАТЬ РАЗЛИЧНЫЕ | Продажи.Регистратор.Номер, | ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.Расходная | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.Расходная) | ИНАЧЕ ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.Реализация | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.Реализация) | КОНЕЦ | … | КОНЕЦ КАК Номер | ИЗ | РегистрНакопления.

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

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

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

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

Источник: https://www.ldk1.ru/vyrazheniya-v-yazyke-zaprosov-agregatnye-funkcii-sistemy-komponovki.html

Язык запросов 1С 8.3 и 8.2 — полное руководство

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

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

Язык запросов 1С — одно из основных отличий между версиями 7.7 и 8. Одним из важнейших пунктов в изучении 1С-программирования является язык запросов.  В 1С 8.3 запросы — самый мощный и эффективный инструмент получения данных. Язык запросов позволяет в удобном виде получать информацию из базы данных.

Сам синтаксис очень сильно напоминает классический T-SQL, за исключением того, что в 1С с помощью языка запросов можно только получать данные, используя конструкцию Выбрать(select). Язык поддерживает и более сложные конструкции, например, вложенные запросы (запрос в запросе).  Запросы в 1С 8 можно составлять как на кириллице, так и на латинице.

В статье я постараюсь рассказать про основные ключевые слова в языке запросов 1С:

  • выбрать
  • разрешенные
  • различные
  • выразить
  • первые
  • для изменения
  • значение
  • тип значения (и оператор ССЫЛКА)
  • выбор
  • сгруппировать по
  • имеющие
  • ЕСТЬNULL
  • Есть NULL
  • соединения — правые, левые, внутренние, полные.

А так же небольшие хитрости языка 1С, применяя которые вы сможете оптимально построить текст запроса.

Для отладки запросов в системе 1С 8.2 предусмотрен специальный инструмент — консоль запросов. Увидеть описание и скачать его можно по ссылке — консоль запросов 1C.

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

Рассмотрим самые важные и интересные операторы языка запросов 1С.

ВЫБРАТЬ (SELECT)

В языке запросов 1С предприятия 8 любой запрос начинается с ключевого слова ВЫБРАТЬ. В языке 1С нет конструкций UPDATE, DELETE, CREATE TABLE, INSERT, эти манипуляции производятся в объектной технике. Предназначение его — только чтение данных.

Например:

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

Справочник.Номенклатура КАК ТекущийСправочник

Запрос вернёт таблицу с наименованиями номенклатуры.

Рядом с конструкцией ВЫБРАТЬ можно встретить ключевые слова ДЛЯ ИЗМЕНЕНИЯ, РАЗРЕШЕННЫЕ, РАЗЛИЧНЫЕ, ПЕРВЫЕ

ДЛЯ ИЗМЕНЕНИЯ — блокирует записи таблицы на время транзакции (не актуально для управляемых блокировок). Подробнее в статье ДЛЯ ИЗМЕНЕНИЯ в запросах 1С.

РАЗРЕШЕННЫЕ — выбирает только записи из таблицы, на которые есть права у текущего пользователя.

РАЗЛИЧНЫЕ — означает, что в результат не попадет дублирующих строк.

ПЕРВЫЕ n — в языке 1С 8.3 запрос отберет только n первых записей.

ВЫБОР (CASE)

Очень часто данная конструкция недооценивается программистами. Пример её использования:

ВЫБРАТЬ

ТекущийСправочник.Наименование,

ВЫБОР

КОГДА ТекущийСправочник.Услуга ТОГДА

«Услуга»

ИНАЧЕ

«Товар»

КОНЕЦ КАК ВидНоменклатуры

ИЗ

Справочник.Номенклатура КАК ТекущийСправочник

Пример возвратит в поле «ВидНоменклатуры» текстовое значение — «Товар» или «Услуга».

ГДЕ (WHERE)

Конструкция языка запросов 1C, позволяющая наложить отбор на получаемые данные. Учтите, что от сервера система получает все данные, а только потом они отбираются по данному параметру.

Пример:

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

ГДЕ ТекущийСправочник.Услуга = ИСТИНА

В примере мы отбираем записи, у которых значение реквизита «Услуга» установлено в положение «Истина».  В данном примере можно было бы обойтись и таким условием:

«ГДЕ Услуга»

По сути, мы отбираем строки, у которых выражение после ключевого слова равно «Истина».

В выражениях можно использовать прямые условия:

ГДЕ Код = «005215»

С помощью оператора «ЗНАЧЕНИЕ()» в условиях использовать обращение к предопределенным элементам и перечислениям в запросе 1С:

ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Товар)

Значения времени могут указываться следующим образом:

ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01):

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

Получите 267 видеоуроков по 1С бесплатно:

ГДЕ НоменклатурнаяГруппа= &НоменклатурнаяГруппа

Условие можно наложить на тип реквизита, если он составного типа:

ГДЕ РегистрНакопления.Регистратор ССЫЛКА Документ.ПоступленияТоваров

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

ГДЕ РегистрНакопления.Регистратор В (&СписокДокументовДляОтбора)

Условие может быть и сложное, состоящее из нескольких условий:

ГДЕ ДатаПоступления > ДАТАВРЕМЯ(2012,01,01)  И НоменклатурнаяГруппа= &НоменклатурнаяГруппа И НЕ Услуга

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

Конструкция языка запросов 1С 8.2, используемая для группировки результата.

Например:

ВЫБРАТЬПоступлениеТоваровУслугTовары.Товар,СУММА(ПоступлениеТоваровУслугTовары.Количество) КАК Количество,СУММА(ПоступлениеТоваровУслугTовары.Сумма) КАК СуммаИЗ

Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугTовары

СГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугTовары.Товар

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

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

ИМЕЮЩИЕ (HAVING)

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

Пример использования ИМЕЮЩИЕ в запросе 1С:

ВЫБРАТЬПоступлениеТоваровУслугТовары.Товар,СУММА(ПоступлениеТоваровУслугТовары.Количество) КАК Количество,СУММА(ПоступлениеТоваровУслугТовары.Сумма) КАК СуммаИЗ

Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТоваровУслугТовары

СГРУППИРОВАТЬ ПО
ПоступлениеТоваровУслугТовары.товар

ИМЕЮЩИЕ

СУММА(ПоступлениеТоваровУслугТовары.Количество) > 5

Так мы отберем количество товаров, которых поступило более 5 штук.

ЗНАЧЕНИЕ()

В запросе 1C ЗНАЧЕНИЕ ()  можно использовать для указания предопределенных значений, таких как значения перечисления, пустую ссылку или значение предопределенного элемента (справочника, плана счетов, ПВХ  и т.д).

Например:

ГДЕ  Банк = Значение(Справочник.Банки.ПустаяСсылка)

или

ГДЕ ВидНоменклатуры = Значение(Справочник.ВидыНоменклатуры.Товар)

или

ГДЕ ТипНоменклатуры= Значение(Перечисление.ТипыНоменклатуры.Услуга)

ТИП в запросе

Тип данных можно проверить следующим образом: с помощью функций ТИП() и ТИПЗНАЧЕНИЯ() или с помощью  логического оператора ССЫЛКА.

Пример:

ГДЕ ТИПЗНАЧЕНИЯ(Проект.Ссылка) ТИП(Справочник.Проект)

или

ГДЕ Проект.Ссылка ССЫЛКА Справочник.Проект

ВЫРАЗИТЬ()

Оператор Выразить в запросах 1С служит для преобразования типов данных.

Синтаксис: ВЫРАЗИТЬ( КАК ) 

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

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

Пример:

ВЫБРАТЬКонтактнаяИнформация.Объект,ВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)) КАК ПредставлениеИЗ

РегистрСведений.КонтактнаяИнформация КАК КонтактнаяИнформация

СГРУППИРОВАТЬ ПОВЫРАЗИТЬ(КонтактнаяИнформация.Представление КАК СТРОКА(150)),

КонтактнаяИнформация.Объект

ЕСТЬNULL (ISNULL)

Достаточно полезная функция языка запросов 1С, которая проверяет значение в записи, и если оно равно NULL, то позволяет заменить на своё значение. Чаще всего используется при получении виртуальных таблиц остатков и оборотов, чтобы скрыть NULL  и поставить понятный 0 (ноль).

Пример:

ЕСТЬNULL(НалогиПредМесяца.ПримененнаяЛьготаФСС, 0)

Такая функция языка запросов 1С ЕСТЬNULL при отсутствии значения вернет ноль, что позволит избежать ошибки.

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

Соединения бывают 4 типов: ЛЕВОЕ, ПРАВОЕ, ПОЛНОЕ, ВНУТРЕННЕЕ.

Левое и правое соединение

Соединения используются для связи двух таблиц по определенному условию. Особенность при ЛЕВОМ СОЕДИНЕНИИ в том, что мы берём первую указанную таблицу полностью и привязываем по условию вторую таблицу. Поля второй таблицы, которые не удалось привязать по условию, заполняются значением NULL.

Пример левого соединения в запросе 1С:

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

Клиенты.Наименование = Банки.Наименование

Вернет всю таблицу Контрагентов и заполнит поле «Банк» лишь в тех местах, где будет соблюдаться условие «Контрагенты.Наименование = Банки.Наименование». Если условие не соблюдается, в поле Банк будет установлено NULL.

ПРАВОЕ СОЕДИНЕНИЕ в языке 1С 8.3 абсолютно аналогично ЛЕВОМУ соединению, за исключением одного отличия: в ПРАВОМ СОЕДИНЕНИИ «главная» таблица — вторая, а не первая.

Полное соединение

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

Например:

ВЫБРАТЬКлиенты.Ссылка КАК Клиенты,Банки.Ссылка КАК БанкИЗ

Справочник.Контрагенты КАК Клиенты

Полное соединение
Справочник.Банки КАК Банки

ПО
Клиенты.Наименование = Банки.Наименование

Язык запросов вернет обе таблицы полностью лишь по выполненному условию Соединить записи. В отличие от левого/правого соединения возможно появления NULL в двух полях.

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ отличается от полного тем, что выводит только те записи, которые смогли соединить по заданному условию.

Например:

ВЫБРАТЬКлиенты.Ссылка КАК Клиенты,

Банки.Ссылка КАК Банк

ИЗ
Справочник.Контрагенты КАК Клиенты

ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Справочник.Банки КАК Банки

ПО
Клиенты.Наименование = Банки.Наименование

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

Заключение

Это лишь небольшая часть синтаксиса из языка запросов 1С 8, в дальнейшем я попробую рассмотреть более подробно некоторые моменты, показать способы оптимизации запросов и многое другое!

курс по 1С

Если вы начинаете изучать 1С программирование, рекомендуем наш бесплатный курс (не забудьте подписаться на — регулярно выходят новые виде):

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Источник: https://programmist1s.ru/yazyik-zaprosa-1s/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

Начать тест

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

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

1, 2, 3, 4, 5, 6

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

1, 6

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

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

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

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

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

Источник: https://helpme1c.ru/yazyk-zaprosov-1s-8-3-dlya-nachinayushhix-programmistov-gruppirovka

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

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

; Вложенные запросы (в разработке).

Поступление товаров и услуг.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сравнительно с предыдущей публикацией текста много, но по-другому совсем никак.

1.Полная форма использования оператора В.

Помимо формы, указанной в предыдущей публикации:

Поле В (&СписокЗначений),

Также в запросах языка 1С допустимы следующие формы использования этого оператора:

1.Поле В (&Массив)

2.Поле В (&Значение1,….&ЗначениеN),

3.Поле В (Выбрать …..) или для нескольких полей:

(Поле1, Поле2) В (Выбрать Т.Поле5, Т.Поле6 Из Таблица Т), где Т — таблица (физическая, виртуальная или временная)

4.Поле В (&ТаблицаЗначений) или для нескольких полей:

(Поле1, Поле2) В (&ТаблицаЗначений)

Вариант записи №3 называют подзапросом.

У варианта №4 есть особенность: количество полей в Таблице значений должно точно совпадать с количеством полей, по которым производится проверка. Кроме того следует учесть что при сравнении по нескольким полям условие будет принимать значение Истина только при совпадении по всем полям одновременно.

2.Оператор В Иерархии.

Этот оператор может быть использован только для иерархических справочников (и других иерархических объектов, например План Счетов, План Видов Характеристик). По сути этот оператор очень схож с оператором В, но имеет совсем другой смысл: оператор производит проверку, что элемент справочника принадлежит указанной группе (или группам вложенным в указанную группу).

Пример использования: отбираем в запросе все элементы справочника номенклатура, которые находятся в группе товаров «Мебель» (для простоты элемент справочника «Номенклатура» Мебель — предопределённый).

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

Запрос.УстановитьПараметр(«Группа1»,Справочники.Номенклатура.Мебель);

Примечание1: если в качестве аргумента оператора В Иерархии указать пустой элемент — будут отобраны все элементы справочника (т.е.

оператор в Иерархии будет возвращать значение Истина для любого элемента справочника, т.к. у элементов и групп самого верхнего уровня значение реквизита Родитель пустое), например: Запрос.

УстановитьПараметр(«Группа1»,Справочники.Номенклатура.ПустаяСсылка());

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

3.Использование конструктора запроса.

В среде программистов 1С не редко возникают споры о использовании конструктора запросов или отказе от использования конструктора и написании запроса вручную.

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

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

Основные преимущества использования конструктора запросов:

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

2.Точное указание имён полей и реквизитов (т.к. они не набираются вручную а выбираются из списка).

3.Упрощенное отображение взаимосвязей между объектами запроса и условий.

4.Относительно изолированная работа над каждой частью запроса (каждым запросом в пакетном запросе или отдельном запросе при объединении запросов).

5.Абсолютно достоверные данные по «существующим» в данной конфигурации Виртуальным таблицам.

4.Работа с датами в запросах (дата, момент времени, граница)

В средствах разработки 1С, начиная с версии 8.0, тип данных Дата стал составным из Даты и Времени. И всё-бы было хорошо, но и тут есть свои особенности.

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

Док

Док

Док = Документы.Приход.СоздатьДокумент();Док.Дата = ТекущаяДата(); Док.Записать();

Док = Документы.Расход.СоздатьДокумент();Док.Дата = ТекущаяДата(); Док.Записать();

А потом выполнить следующий запрос:

ОБЪЕДИНИТЬ ВСЕ

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

То получим странный результат:

Документы не упорядочены даже по порядку их создания! Если заменим реквизит документа Дата на реквизит МоментВремени в запросе получим уже более лучший результат:

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

Примечание: МоментВремени содержит дату, время и ссылку на объект базы данных. А т.к. ссылка содержит код типа объекта и уникальный номер объекта — то сортировка в пределах секунды по МоментуВремени в качестве результата выдаст группы разнотипных объектов внутри групп упорядоченных, но между собой не «перемешанных».

Как решить эту задачу:

1вариант: если номера самих документов не принципиальны — можно создать нумератор документов и тогда номер документа будет ответствовать его порядковому номеру при создании. Это решение очень плохо в виду отказа от сквозной нумерации документов.

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

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

Запрос.УстановитьПараметр(» КонецПериода», КонецМесяца(ТекущаяДата()));

Но на самом деле запрос получения остатков будет выполнен по состоянию на начало указанной даты(которая есть дата+время). Если учесть, что КонецМесяца(Дата(2012,10,29)) = 31.10.0012 23:59:59

То получается, что у нас выпадают обороты формируемые движениями (документами) за последнюю секунду периода и мы получим остатки за секунду до конца месяца а не по состоянию на конец месяца! Особенно часто эта особенность приводит к ошибке получения остатков при импорте нечальных остатков (когда в последную секунду может поместиться огромное количество документов) и в организациях работающих в режиме 7*24.

Как решить эту проблему:

1 вариант: прибавить к концу периода 1 секунду:

Запрос.УстановитьПараметр(» КонецПериода», КонецМесяца(ТекущаяДата())+1);

2 вариант: воспользоваться специально созданным для этого объектом Граница:

ГраницаПериода = Новый Граница(КонецМесяца(ТекущаяДата())+1, ВидГраницы.Исключая);

Запрос.УстановитьПараметр(«КонецПериода»,ГраницаПериода);

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

ГраницаПериода = Новый Граница(Документ.МоментВремени, ВидГраницы.Исключая);

или если движения и этого документа должны попасть:

ГраницаПериода = Новый Граница(Документ.МоментВремени, ВидГраницы.Включая);

Примечание 2:

Источник: https://nasvai.ru/1s-8-maksimum-v-zaprose-agregatnye-funkcii-sistemy-komponovki-dannyh.html

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