Главная › Общие вопросы › Агрегатные функции системы компоновки данных. Агрегатные функции системы компоновки данных Приведение типа в языке запросов

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

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

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: Как справедливо заметили в комментариях ещё 1 плюс работы с типом данных Граница - не надо задумываться что выбираешь остатки или обороты и нет необходимости передавать в сложный запрос в котором выбираются и остатки и обороты 2 параметра описывающих момент на который проиводится выборка (один для оборотов, второй для остатков).

5.Выборка по группировкам, итоги.

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

Инструменты

Напильник

То обычная выборка запроса вида:

Выборка = Запрос. Выполнить(). Выбрать();

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

Запрос = Новый Запрос;

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

ИТОГИ ПО Номенклатура ИЕРАРХИЯ";

Выборка = Запрос.Выполнить(). Выбрать(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией, "Номенклатура" );

Сообщить("Элемент=" + ВыборкаДетали. Номенклатура);

КонецЦикла;

Кроме того есть платформа позволяет при выполнении выборки производить расчёт итогов на каждом уровне, пусть для простоты запроса итоги берём из справочника (а не из регистров накопления) :

Запрос = Новый Запрос;

Запрос. Текст = "ВЫБРАТЬ Номенклатура.Ссылка КАК Номенклатура, Номенклатура.Продано КАК Продано

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

ИТОГИ СУММА(Продано)

ПО Номенклатура ИЕРАРХИЯ";

Выборка = Запрос. Выполнить(). Выбрать(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией, "Номенклатура" );

Пока Выборка. Следующий() Цикл

Сообщить("Группа=" + Выборка. Номенклатура+ " Продано=" + Выборка. Продано);

ВыборкаДетали = Выборка.Выбрать(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией, "Номенклатура" );

Пока ВыборкаДетали. Следующий() Цикл

Сообщить("Элемент=" + ВыборкаДетали. Номенклатура+ " Продано=" + ВыборкаДетали. Продано);

КонецЦикла;

Примечание1: есть ещё один вариант обход «ПоГруппировкам», отличается от иерархического обхода тем, что элементы выборки с иерархическими итогами будут в нем как детальные записи а не узловые.

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

……………………..

Выборка = Запрос.Выполнить(). Выбрать(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией);
ВыбратьРекурсивно(Выборка);

………….
КонецПроцедуры

Процедура ВыбратьРекурсивно(Выборка)

Пока Выборка.Следующий() Цикл

Сообщить(Выборка. Номенклатура+ " колво=" + Выборка. Продано);

// Попытка получить дочерние записи

ВыбратьРекурсивно(Выборка. Выбрать(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией);
КонецЦикла;
КонецПроцедуры

Примечание3: ещё одно применение иерархической выборки - выгрузка результатов запроса в объект типа Дерево Значений, например так:

ЗначениеВРеквизитФормы(Результат. Выгрузить(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией, "Дерево" );

Дерево = Результат. Выгрузить(ОбходРезультатаЗапроса. ПоГруппировкамСИерархией);

6.Временные таблицы, пакетные запросы .

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

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

////////////////////////////////////////////////////////////

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

Схема пакетного запроса:

в 1 запросе получаем полный перечень продаваемой номенклатуры и максимальную дату продажи для каждой из номенклатур,

во 2 запросе отбираем для каждой номенклатуры из первого запроса документ реализации с датой равной для этой номенклатуры максимальной

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

ПОМЕСТИТЬ Даты

ИЗ Документ.Реализация.Товары КАК РеализацияТовары

СГРУППИРОВАТЬ ПО РеализацияТовары.Номенклатура

////////////////////////////////////////////////////////////

ВЫБРАТЬ Даты.Номенклатура, Даты.Дата, МАКСИМУМ(Реализация.Ссылка) КАК Ссылка

ИЗ Даты КАК Даты

ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Реализация КАК Реализация

ПО Даты.Дата = Реализация.Дата

СГРУППИРОВАТЬ ПО Реализация.Ссылка

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

7.Последовательность чисел, дат.

Задача: генерация числовой последовательности от 0 до 959 в запросе. При использовании пакетного запроса задача решается легко:

ВЫБРАТЬ Цифры.Поле1 КАК Цифра

ПОМЕСТИТЬ Цифры

ИЗ (ВЫБРАТЬ 1 КАК Поле1

ОБЪЕДИНИТЬ ВЫБРАТЬ 2

ОБЪЕДИНИТЬ ВЫБРАТЬ 3

ОБЪЕДИНИТЬ ВЫБРАТЬ 4

ОБЪЕДИНИТЬ ВЫБРАТЬ 5

ОБЪЕДИНИТЬ ВЫБРАТЬ 6

ОБЪЕДИНИТЬ ВЫБРАТЬ 7

ОБЪЕДИНИТЬ ВЫБРАТЬ 8

ОБЪЕДИНИТЬ ВЫБРАТЬ 9

ОБЪЕДИНИТЬ ВЫБРАТЬ 10) КАК Цифры

ВЫБРАТЬ (Цифры.Цифра- 1) + (Цифры1.Цифра - 1)* 10+ (Цифры2.Цифра - 1)* 100 КАК Число

ГДЕ (Цифры.Цифра- 1) + (Цифры1.Цифра - 1)* 10+ (Цифры2.Цифра - 1)* 100 <= 959

УПОРЯДОЧИТЬ ПО Число

Задача : генерация последовательности дат от Дата1 до Дата2 . Решается аналогично, можно как выполнить в 3 шага (первый и второй как в приведённом примере, но результат не выбрать а поместить во временную таблицу) или в 2 шага изменив второй запрос пакета на вот такой:

ВЫБРАТЬ ДОБАВИТЬКДАТЕ (&Дата1 , ДЕНЬ , (Цифры.Цифра- 1) + (Цифры1.Цифра - 1)* 10+ (Цифры2.Цифра - 1)* 100) КАК Дата

ИЗ Цифры КАК Цифры, Цифры КАК Цифры1, Цифры КАК Цифры2

ГДЕ ДОБАВИТЬКДАТЕ (&Дата1 , ДЕНЬ , (Цифры.Цифра- 1) + (Цифры1.Цифра - 1)* 10+ (Цифры2.Цифра - 1)* 100)

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

Примечание: естественно этот запрос будет правильно работать только если между датами Дата1 и Дата2 не более 999 дней.

8.Использование данных из таблицы значений в запросе.

Если в первом разделе (оператор В ) мы производим сравнение набора полей с данными из Таблицы значений, то иногда этого мало. Например, из стороннего источника передаются наборы данных, например такие: штрих-код и количество единиц проданной продукции. Можно конечно в запросе обойти переданный набор значений выполняя на каждой итерации цикла запрос, но это наихудший вариант решения этой задачи. Правильнее сделать так:

1.Создать типизированную Таблицу значений (т.е. у которой указан тип для каждого поля).

2.Заполнить Таблицу значений полученными данными о продажах.

3.Произвести поиск номенклатуры в запросе.

Пример кода для 1 и 3 этапа:

Создание типизированной Таблицы значений:

// Создание описателей типов для таблицы значений

КЧК = Новый КвалификаторыЧисла(14, 3);

КЧШК = Новый КвалификаторыЧисла(13, 0);

Массив = Новый Массив;

Массив. Добавить(Тип("Число" ));

ОписаниеТиповЧК = Новый ОписаниеТипов(Массив, КЧК);

Массив. Очистить();

Массив. Добавить(Тип(" Число"));

ОписаниеТиповЧШ = Новый ОписаниеТипов(Массив, КЧШК);

// Создание таблицы значений

ТаблицаЗначений = Новый ТаблицаЗначений;

// добавим в таблицу значений две колонки

ТаблицаЗначений. Колонки.Добавить("ШтрихКод" , ОписаниеТиповЧШ, "ШтрихКод" , 13); ТаблицаЗначений. Колонки. Добавить("Продано" , ОписаниеТиповЧК, "Продано" , 14);

Поиск Номенклатуры в запросе:

Запрос = Новый Запрос;

Запрос.Текст = "ВЫБРАТЬ ТЗ.ШтрихКод, ТЗ.Количество

ПОМЕСТИТЬ ТЗ

ИЗ &ТЗ КАК ТЗ

////////////////////////////////////////////////////////////////////////////////

ВЫБРАТЬ ТЗ.Количество КАК ОбъемПродаж, Номенклатура.Ссылка КАК Ссылка

ИЗ ТЗ КАК ТЗ

ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура

ПО ТЗ.ШтрихКод = Номенклатура.ШтрихКод";

Запрос. УстановитьПараметр("ТЗ" , ТаблицаЗначений);

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

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

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

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

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

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

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

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

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

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

Задача 1.

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

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

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

Задача 2.

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

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

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

Задача 3.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Результат:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ГДЕ
Проведен

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

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

Организация

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В этой статье рассмотрим группировку в запросах 1С 8 и
агрегатные функции с помощью которых эта группировка и делается.

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

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

Здесь то нам и поможет группировка.

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

ВЫБРАТЬ "Центральный" КАК Склад, "Карандаш" КАК Товар, 45 КАК Количество ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Центральный", "Ручка", 30 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Карандаш", 15 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Ручка", 25

А теперь поместим нашу исходную таблицу во временную и выберем из нее данные с
группировкой по полю Товар

ВЫБРАТЬ "Центральный" КАК Склад, "Карандаш" КАК Товар, 45 КАК Количество ПОМЕСТИТЬ ВТ_Количество ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Центральный", "Ручка", 30 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Карандаш", 15 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ "Офис", "Ручка", 25 ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ВТ_Количество.Товар, СУММА(ВТ_Количество.Количество) КАК Количество ИЗ ВТ_Количество КАК ВТ_Количество СГРУППИРОВАТЬ ПО ВТ_Количество.Товар

Жирным выделен код, который непосредственно учавствует в группировке.
СУММА() - это агрегатная функция.
СГРУППИРОВАТЬ ПО - это оператор после которого идет список
полей через запятую по которым производится группировка. По сути здесь должны быть
перечислены все поля из раздела ВЫБРАТЬ за исключением тех к которым
применены агрегатные функции. В противном случае выполнение запроса завершится с
ошибкой.

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

СУММА() - суммирует значения поля;
МИНИМУМ() - выбирает из всех значений поля минимальное;
МАКСИМУМ() - выбирает из всех значений поля максимальное;
СРЕДНЕЕ() - рассчитывает среднее значение (Сумма/Количество);
КОЛИЧЕСТВО() - выводит количество записей;
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) - выводит количество записей у которых различаются значения;
Продемонстрируем их работу на нашем примере. Добавим в наш запрос другие агрегатные
функции кроме СУММЫ .

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

В результате выполнения запроса получим следующую таблицу:

Здесь мы в поле Количество получили общее количество записей в таблице,
а в поле КоличествоРазличные - количество записей, у которых в поле
Товар значения различаются. Применительно к нашей таблице это Карандаш
и Ручка .

Как видно из последнего примера, если ко всем полям в запросе применяются
агрегатные функции, то использовать СГРУППИРОВАТЬ ПО не нужно.