+7(499)-938-42-58 Москва
+7(800)-333-37-98 Горячая линия

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

Содержание

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

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

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

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

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

Язык запросов 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, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6

1, 2, 3, 4, 5, 6

1, 6

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

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

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

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

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

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

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

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

Закажите бесплатный расчет стоимости вашей задачи по 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/

Система компоновки данных (СКД). Часть 3

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

Приветствую вас, дорогие мои читатели! Давайте продолжим рассматривать основы системы компоновки. Во второй части этой серии статей вы узнали различия набора данных — запрос от обычного запроса. Также вы рассмотрели параметры виртуальных таблиц и и познакомились с расширением языка запросов.  А теперь посмотрите в содержании, что вы узнаете здесь. Успехов!

Функции языка выражений СКД

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

НачалоПериода(ДатаВремя(2002, 10, 12, 10, 15, 34), “Месяц”)

Эта функция отличается от функции языка запросов вторым параметром. Здесь используется строка «Месяц», тогда как в функции языка запросов литерал МЕСЯЦ.

Описание функций можно найти, открыв Конфигуратор: Справка -> -> Система компоновки данных -> Язык выражений системы компоновки данных -> Функции языка выражений системы компоновки данных.

Где можно применять функции языка выражений СКД, если в языке запросов их использовать нельзя?

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

Особенности работы СКД

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

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

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

Основные настройки полей СКД

На закладке «Наборы данных» при создании набора данных типа запрос происходит автозаполнение полей компоновки. Как видим, поля содержат большое количество настроек.

Колонка «Поле» содержит название поля, которое получается из информационной базы (ИБ).
Неизменяемое значение, которое жёстко связано с запросом. Оно соответствует псевдониму запроса.

Но есть одна особенность.

Если мы поменяем псевдоним, например «Номенклатура», в Конструкторе запроса на вкладке «Объединения/Псевдонимы» в колонке «Имя поля», на «Товар», тогда на закладке «Наборы данных» компоновки появиться новое поле «Товар», но при этом осталось и поле «Номенклатура». Это связано с тем, что установлен флаг «Автозаполнение». При такой настройке система не может понять, что поле «Товар» то же самое, что и поле «Номенклатура», поэтому, на всякий случай, поле добавляется дважды.

Может ли такая конструкция повредить работе пользователя? Да. Ведь в настройках варианта отчета появится два поля «Товар» и «Номенклатура». Для пользователя будет непонятно, чем они отличаются, хотя эти отборы идентичны.

Хотя можно исключить появление поля «Номенклатура», поставив галочки в колонках «Ограничение поля»-«Условие» и «Ограничение реквизитов»-«Условие» на закладке «Наборы данных» в настройках полей.

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

Колонка «Путь» указывает, как мы будем обращаться к полю на всех закладках компоновки данных. Значение, которое можно изменить по своему усмотрению.

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

Лучше изменить заголовок, поставив галочку в колонке «Заголовок».

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

Можно применять следующий способ для работы с колонкой «Путь». С помощью указания пути можно создавать вложенные виртуальные реквизиты элемента.

Например, поместим реквизит «Качество» внутрь реквизита «Товар». Изменим поле «Путь» реквизита «Качество» на «Товар.Качество».

Теперь на закладке «Настройки» во вкладке «Выбранные поля» мы не найдём поле «Качество», но оно появится в внутри поля «Товар».

Рассмотрим подробнее настройки ограничения доступности. Как видим, существуют две строки для ограничения доступности в колонках «Ограничение поля» и «Ограничение реквизитов».

Колонки ограничения полей СКД

Колонка «Ограничение поля» означает, что ограничения будут наложены на само поле. Колонка «Ограничение реквизитов» означает, что ограничения будут наложены на вложенные реквизиты этого поля, если поле относится к сложному типу данных, такому как справочник или документ.

Каждая строка разбита ещё на четыре колонки. Если для поля или реквизитов этого поля установлен флаг:

  • Поле, то они не будут доступны для выбора на закладке «Настройки» во вкладке «Выбранные поля».
  • Условие, то они не будут доступны как условие на закладке «Настройки» во вкладке «Отбор».
  • Группа, то на закладке «Настройки» при создании новой группировки не будет доступна группировка по ним.
  • Упорядочивание, то они не будут доступны для упорядочивания на закладке «Настройки» во вкладке «Сортировка».

Колонка «Выражение представления» нужна для изменения представление поля в результате отчёта. Например, нужно выводить не только наименование товара, но и его артикул. Поэтому в этой колонке можно написать следующее:

Товар.Артикул + “-” + Товар.Наименование

В этом поле мы можем использовать все возможности языка СКД и обращаться к функциям общих модулей.

Если в конфигурации существует общий модуль с названием «ДляСКД» и в этом модуле определена общая (с ключевым словом «Экспорт») функция «ПолучитьПредставление» с параметром «Ссылка», тогда в поле «Выражение представления» мы запишем:

ДляСКД.ПолучитьПредставление(Товар)

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

Колонка «Выражение упорядочивания» нужна для определения нестандартной сортировки. Здесь можно использовать такие же конструкции, что и в поле «Выражение представления».

Источник: http://www.softmaker.kz/skd/sistema-komponovki-dannyx-skd-chast-3.html

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

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

Группировки в запросах 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с

17.06.2017

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

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

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

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

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

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

Выбрать (select)

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

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

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

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

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

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

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

Для изменения

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

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

В таком случае полученный остаток будет уже не верен.

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

Пример:

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

Для изменения

Где (where)

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

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

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

КонтактныеЛица.Должность = &ИмяПараметра

Выбор (case)

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

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

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

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

Соединение (join)

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

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

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

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

Пример:

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

ПО ПоступлениеТиУ.Номенклатура = Цены.Номенклатура

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

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

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

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

Сгруппировать по (group by)

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

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

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

Цены.Номенклатура.ВидНоменклатуры

ИТОГИ

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

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

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

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

ВидНоменклатуры

Имеющие (having)

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

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

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

МАКСИМУМ(Цены.Цена) > 1000

Упорядочить по

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

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

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

АВТОУПОРЯДОЧИВАНИЕ

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

  • ОБЪЕДИНИТЬ – результаты двух запросов в один.
  • ОБЪЕДИНИТЬ ВСЕ – аналог ОБЪЕДИНИТЬ, но без группировки одинаковых строк.

  • ПУСТАЯ ТАБЛИЦА – иногда используется при объединении запросов для указания пустой вложенной таблицы.

  • ПОМЕСТИТЬ – создает временную таблицу для оптимизации сложных запросов 1С. Такие запросы называются пакетными.

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

  • ПОДСТРОКА обрезает строку с определенной позиции на указанное количество символов.
  • ГОД…СЕКУНДА позволяют получить выбранное значение числового типа. Входным параметром является дата.
  • НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА используются при работе с датами.

    В качестве дополнительного параметра указывается тип периода (ДЕНЬ, МЕСЯЦ, ГОД и т. п.).

  • ДОБАВИТЬКДАТЕ позволяет прибавить или отнять от даты указанное время определенного типа (СЕКУНДА, МИНУТА, ДЕНЬ и т. п.).

  • РАЗНОСТЬДАТ определяет разницу между двумя датами с указанием типа выходного значения (ДЕНЬ, ГОД, МЕСЯЦ и т. п.).
  • ЕСТЬNULL заменяет отсутствующее значение на указанное выражение.

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

  • ССЫЛКА является логическим оператором сравнения для типа значения реквизита.
  • ВЫРАЗИТЬ используется для преобразования значения к нужному типу.
  • ДАТАВРЕМЯ получает значение типа «Дата» из числовых значений (Год, Месяц, День, Час, Минута, Секунда).

  • ЗНАЧЕНИЕ в запросе 1С используется для указания предопределенных значений — справочников, перечислений, планов видов характеристик. Пример использования: «Где ЮрФизЛицо = Значение(Перечисление.ЮрФизЛица.ФизЛицо)«.

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

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

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

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

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

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

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

Источник: https://1s83.info/programmirovanie/yazyik-zaprosov-1s.html

Поделиться:
Нет комментариев

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

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