IE 2016

Полезные сведения о языке запросов 1С 8.2

Опубликовал fpat в раздел Программирование - Практика программирования

В статье приведены полезные приемы при работе с запросами 1С v.8.2, а также сведения, которые не так хорошо известны о языке запросов. Я не стремлюсь дать полное описание языка запросов, а хочу остановиться лишь на некоторых моментах, которые для кого-то могут быть полезны.

Итак, начнем. Запрос - это специальный объект в 1С 8.2, который используется для формирования и выполнения запросов к таблицам базы данных в системе. Для выполнения запроса необходимо составить текст запроса, в котором описывается какие таблицы будут использоваться в качестве источников данных запроса, какие нужно выбрать поля, какие применить сортировки и группировки и т.д. Подробнее о запросах можно прочитать в книге "1С 8.2 Руководстве разработчика". Язык запросов 1С 8.2 очень похож синтаксисом на другие SQL языки запросов баз данных, но есть и отличия. Из основных преимуществ встроенного языка запросов стоит отметить разыменование полей, наличие виртуальных таблиц, удобная работа с итогами и нетипизированные поля в запросах. Из недостатков – в качестве выходного поля нельзя использовать запрос, нельзя использовать хранимые процедуры, нельзя преобразовать строку в число.

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

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

Примеры:

 

ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.Москва)

ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.ПустаяСсылка)

ГДЕ ТипТовара = ЗНАЧЕНИЕ(Перечисление.ВидыТоваров.Услуга)

ГДЕ ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)

ГДЕ ТочкаМаршрута =

ЗНАЧЕНИЕ(БизнесПроцесс.Согласование.ТочкаМаршрута.Согласие)

 

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

2.Автоупорядочивание в запросе может сильно тормозить процесс. Если сортировка не нужна, лучше вообще ее не использовать. Во многих случаях эффективнее записать сортировку через ключевое слово УПОРЯДОЧИТЬ ПО.

3.Нужно следить, чтобы при использовании псевдонимов не появилось неоднозначное поле. Иначе система не поймет к какому объекту надо обращаться.

Пример запроса с неоднозначным полем:

     ВЫБРАТЬ

            Номенклатура.Ссылка,

            ОстаткиТоваровОстатки.КоличествоОстаток

ИЗ

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

                        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки КАК ОстаткиТоваровОстатки

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

Нужно исправить псевдоним таблицы, например, так: «Справочник.Номенклатура КАК Номенклатура1», а «Номенклатура.Ссылка»  соответственно исправить на «Номенклатура1.Ссылка».

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

Пример:

ВЫБРАТЬ

ПРЕДСТАВЛЕНИЕ(Документ.Контрагент) КАК Получатель,

ПРЕДСТАВЛЕНИЕ(Документ.Основание)

ИЗ

Документ.РасходнаяНакладная КАК Документ

5.Использование в запросе ВЫРАЗИТЬ(Поле КАК Тип) позволяет убрать лишние таблицы из соединения с полем составного типа данных. Тем самым ускорить выполнение запроса.

Пример (регистратор - поле с составным типом для физической таблицы регистранакопления ОстаткиТоваров, в запросе выбираются Дата и Номер  документов ПоступлениеТоваров, при этом при обращении к реквизитам документа Дата и Номер через  Регистратор не происходит множественного соединения таблицы регистра с таблицами документов, являющихся регистраторами для регистра ОстаткиТоваров):

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

ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров).Дата КАК ДАТАПОСТУПЛЕНИЯ

ИЗ
 РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ГДЕ
 (ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров) ЕСТЬ НЕ NULL)

6.Когда в конфигурации 1С есть пользователи, у которых права ограничены на определенные объекты конфигурации, в запросе к таким объектам необходимо использовать ключевое слово РАЗРЕШЕННЫЕ, чтобы запрос выполнился без ошибки (Выбрать Разрешенные ...)

7.При объединении таблиц, содержащих вложенные таблицы (например, Документ с табличной частью) бывает полезно ключевое слово ПУСТАЯТАБЛИЦА, когда, например, в одном из документов нет табличной части.

Пример:

ВЫБРАТЬ Ссылка.Номер, ПУСТАЯТАБЛИЦА.(Ном, Тов, Кол) КАК Состав

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

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

ВЫБРАТЬ Ссылка.Номер, Состав.(НомерСтроки, Номенклатура, Количество)

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

8.При работе с соединениями таблиц, содержащих по одной строке, бывает нужно склеить  строки таблиц (при этом в обеих таблицах нет такого поля, по которому их можно было соединить). Этого можно добиться, применив конструкцию «ПОЛНОЕ СОЕДИНЕНИЕ Таблица По ИСТИНА». Если в таблицах больше, чем одна  строка, то в результате будет количество строк, равное произведению количества строк обеих таблиц. Если в одной таблице О строк, то в результирующей таблице количество строк будет равно количеству строк второй таблицы. Также для соединения таких таблиц можно применять декартово произведение таблиц , при котором в результирующей таблице будут встречаться все комбинации строк из обеих таблиц. Надо помнить, что если в одной из таблиц 0 строк, тогда и декартово произведение будет 0, поэтому полное соединение будет  лучше. Вообще вместо полного соединения ПО ИСТИНА можно использовать  и любой другой тип соединения, но в таком случае тоже возможна ситуация, когда в результирующей таблице будет 0 строк, даже если в одной из таблиц будет ненулевое количество строк. В случае полного соединения такая ситуация будет только в одном случае, если количество строк в обеих таблицах равно 0. Если знать, что в таблице есть точно хотя бы одна строка, тогда можно использовать  и ЛЕВОЕ СОЕДИНЕНИЕ с другой таблицей с условием ПО ИСТИНА.

Пример (правда надуманный, для Полного соединения):

ВЫБРАТЬ

    Первые 1

            Пол.Ссылка,

            К.Контрагент

ИЗ

            Перечисление.Пол КАК Пол

                        ПОЛНОЕ СОЕДИНЕНИЕ (Выбрать Первые 1 Д.Контрагент ИЗ Документ.РеализацияТоваров КАК Д Упорядочить По Д.МоментВремени ) КАК К

                        ПО (ИСТИНА)

9. Для того чтобы получить уникальные записи по какому-то полю, правильней  вместо группировки пользоваться ключевым словом РАЗЛИЧНЫЕ в запросе, потому что такая конструкция намного наглядней и ключевое слово СГРУППИРОВАТЬ ПО имеет более широкое применение и часто используется, если дополнительно надо рассчитать агрегатные функции по группировкам. В некоторых случаях необходимо вывести ограниченное количество строк. Для этого в описании запроса в описании запроса следует указать ключевое слово ПЕРВЫЕ и после него – требуемое количество строк.

Пример для ПЕРВЫЕ:

Выбрать Первые 5

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

Справочник.Номенклатура.ЗакупочнаяЦена

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

Справочник.Номенклатура.ЗакупочнаяЦена Убыв

 

Пример для РАЗЛИЧНЫЕ:

 

Выбрать Различные

Документ.Расходная.Контрагент

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

Пример:

Выбрать

Сумма(Накладная.Сумма) Как Сумма

Из

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

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

Пример:

ВЫБРАТЬ
 ПоступлениеТоваровИУслугТовары.Номенклатура,
 ПоступлениеТоваровИУслугТовары.Номенклатура.Код,
 ПоступлениеТоваровИУслугТовары.Количество КАК Количество,
 ПоступлениеТоваровИУслугТовары.Ссылка.Контрагент
ИЗ
 Документ.ПоступлениеТоваровИУслуг.Товары КАК ПоступлениеТоваровИУслугТовары
ГДЕ
 ПоступлениеТоваровИУслугТовары.Ссылка = &Ссылка

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

Пример:

  ВЫБРАТЬ

 ПоступлениеТоваровИУслугТовары.Номенклатура,

 ПоступлениеТоваровИУслугТовары.Номенклатура.Код,

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

 ПоступлениеТоваровИУслугТовары.Ссылка.Контрагент,

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

ИЗ

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

ГДЕ

 ПоступлениеТоваровИУслугТовары.Ссылка = &Ссылка

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

 ПоступлениеТоваровИУслугТовары.Номенклатура,

 ПоступлениеТоваровИУслугТовары.Ссылка

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

Пример:

ВЫБРАТЬ

            ПоступлениеТоваровИУслуг.Товары.(СУММА(Количество),Номенклатура),

            ПоступлениеТоваровИУслуг.Ссылка,

            ПоступлениеТоваровИУслуг.Контрагент

ИЗ

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

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

            ПоступлениеТоваровИУслуг.Товары.(Номенклатура)

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

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

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

А затем установить параметр в тексте запроса:

Запрос.УстановитьПараметр(«&Контрагент», ВыбКонтрагент);

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

13. В универсальных запросах  параметры можно использовать в описании источников данных запроса, в условиях ГДЕ, в условиях соединения таблиц и параметрах виртуальных таблиц. Существует два приема для создания универсальных запросов:

А) с помощью механизма конкатенации строк, добавляя в текст запроса переменные;

Пример1:

 

ТипУпорядочивания = ?(НЕКАЯПЕРЕМЕННАЯ,"","УБЫВ");

Запрос.Текст= "Выбрать  ... Упорядочить ПО Поле1 " + ТипУпорядочивания  + "...";

Пример2:

Запрос.Текст = "Выбрать Поле1...";

 

Если  НЕКАЯПЕРЕМЕННАЯ = 1 Тогда

Запрос.Текст = Запрос.Текст + ",Поле2 ...";

КонецЕсли;

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

Пример:

Выбрать

ДокТЧ.Номенклатура,

...

ИЗ

&НекийДокТЧ КАК ДокТЧ

А затем установить параметр  в тексте запроса

 Запрос.Текст = СтрЗаменить(Запрос.Текст, "&НекийДокТЧ", "Документ."+Ссылка.Метаданные().Имя+".Товары");

 

 Параметры можно использовать в условиях запроса, чтобы включить опциональное условие &Параметр ИЛИ НЕ КакоеТоСвойство:

 Запрос.УстановитьПараметр(“&Параметр”, “Контрагент.Наименование=””Иванов”””);

С помощью литерала ИСТИНА можно убирать определенные фильтры в запросе

Запрос.УстановитьПараметр(«&Параметр»,Истина);

14.Очень полезными в конструкторе запросов является команда контекстного меню таблицы - "Переименовать таблицу...", с помощью которого можно придумать некоторое обобщенное имя для источника данных. Для создания запросов к однотипным таблицам, похожим по структуре, бывает полезным для второй таблицы скопировать текст запроса первой таблицы, зайти в окно конструктора запросов и в контекстном меню таблицы выбрать пункт - Заменить таблицу... и выбрать вторую талицу.

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

Пример вложенного запроса:

 Товар В ( Выбрать Номенклатура ...)

16. При проектировании отчетов СКД в запросах к регистрам остатков - в качестве параметра Период удобнее и правильнее использовать выражение ДобавитьКДате(КонецПериода(Период,ДЕНЬ),СЕКУДА,1), так как остатки в виртуальных получаются на начало периода, не включая последнюю секунду. Прием +1 секунда не может быть применен с документами:  по новой методике проведения документов остатки по регистру надо получать на Период, заданный объектом Граница с моментом времени документа включая (а не на дату документа  +1 секунда!), а по старой методике проведения  - на момент времени документа (а не на дату документа !). При анализе оборотов или данных за период удобно добавлять параметр с типом СтандартныйПериод (в этом случае не надо приводить последнюю дату интервала на конец дня). У стандартного поля «НачалоПериода» в поле «Выражение» надо прописать «&Период.ДатаНачала». А у стандартного поля «КонецПериода» в поле «Выражение» прописать «&Период.ДатаОкончания». Очень много полезной информации по языку запросов можно найти не в синтакс-помощнике, а в полной справке конфигуратора 1С 8.2 (кнопка F1)

17.Функция запроса ЕстьNull (удобнее писать англоязычный вариант IsNull) обычно используется для избавления от значений типа Null для числовых полей запроса. В ряде случаев, например полного соединения двух таблиц функция IsNull (Параметр1,Параметр2) может с успехом заменить конструкцию ВЫБОР КОГДА ... ТОГДА ..ИНАЧЕ ….КОНЕЦ, когда для какого-либо поля значения NULL могут быть как в первой таблице, так и во второй (такая конструкция позволяет получать не Null значение для поля). Но надо помнить, что в отличие от условного оператора ВЫБОР функция ЕстьNull приводит тип второго аргумента к типу первого аргумента, что нужно учитывать, если типы аргументов отличаются!

Пример:

IsNull(Рег.Остаток,0)

IsNull(Док.Товар,Док1.Номенклатура)

18. У условной конструкции ВЫБОР   есть альтернативный синтаксис для простого случая проверки равенства определенному значению, но, правда, он недокументированный:

 Выбор Выражение Когда 1 Тогда «Высший» Когда 2 Тогда «Средний» Иначе «Низший» Конец

19.Оператор проверки значения на NULL Eсть Null (Можно рекомендовать использовать англоязычный вариант Is Null). Такая конструкция появилась потому, что любая операция сравнения двух величин, хотя бы одно из которых Null, всегда ложь. Написать Где Наименование = Null неправильно. Интересна также форма отрицания данного оператора Не Есть Null - неправильно, а правильно Есть Не Null или форма Не (Поле1 Есть Null) - это существенное отличие от всех операторов, использующихся совместно с оператором Не.

20. Иногда полезна форма оператора В для проверки совпадения с одним из перечисленных значений.

Пример:

 ...Где Товар.Наименование В ("Бытовая техника","Компьютеры")

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

Пример:

...Где Номенклатура В ИЕРАРХИИ (&Группа)

Оператор В часто используется для проверки вхождения значения в результат вложенного запроса.

Пример:

...Где Номенклатура.Ссылка В (Выбрать Номенклатура.Ссылка ...).

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

Пример:

// Выбрать названия товаров, которые присутствовали

// в расходных накладных

ВЫБРАТЬ

Товары.Наименование

ИЗ

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

ГДЕ

Товары.Ссылка В

(ВЫБРАТЬ

РасходнаяНакладнаяСостав.Номенклатура

ИЗ

Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав

ГДЕ

РасходнаяНакладнаяСостав.Номенклатура = Товары.Ссылка)

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

Синтаксис для вложенного запроса

(выражение1, выражение2,...,выражениеN) В (Выбрать выражение1, выражение2,...,выражениеN ...)

Синтаксис для таблицы значений

(выражение1, выражение2,...,выражениеN) В (&ТЗ), где в таблице значений ТЗ используются N первых колонок

20. В интернете есть шутка по поводу того, как конструктор запроса постоянно делает ЛЕВОЕ соединение таблиц (и меняет их местами), как бы мы не указывали ПРАВОЕ:

1С:Предприятие любит «налево».

21. Сложные запросы удобно отлаживать в консоли запросов. Существует их в интернете много. После отладки запроса его можно скопировать и в конструкторе запроса есть замечательная кнопка «Запрос», куда можно вставить его в том же виде и сохранить (раньше была только возможность скопировать в конфигураторе и сделать форматирование запроса посредством символа переноса строки). В окне, которое открывается при нажатии кнопки «Запрос», можно редактировать запрос и смотреть результат выполнения, что довольно удобно.

22.При проектировании отчетов  СКД нужно помнить, что если нужно обеспечить фильтрацию по некоторому полю, необязательно добавлять параметр в текст запроса. У конструктора запросов есть вкладка «Компоновка данных», где можно добавлять параметры в условия. Кроме того, на уровне отчета СКД есть закладка условия, где можно добавлять произвольные условия и сохранять в быстрых настройках. В таком случае условия будут универсальными (равенство, неравенство, принадлежность, вхождение в список и т.д.).

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

Пример неправильно работающего запроса, получающего последний проведенный документ по указанному контрагенту (вернее, табличную часть документа):

 

ВЫБРАТЬ

РасходнаяТовары.Ссылка,

РасходнаяТовары.НомерСтроки,

РасходнаяТовары.Товар,

РасходнаяТовары.Количество,

РасходнаяТовары.Цена,

РасходнаяТовары.Сумма

ИЗ

Документ.Расходная.Товары КАК РасходнаяТовары

ГДЕ

РасходнаяТовары.Ссылка В

(ВЫБРАТЬ ПЕРВЫЕ 1

 Д.Ссылка

 ИЗ

 Документ.Расходная КАК Д

 ГДЕ

  Д.Ссылка.Проведен

 И Д.Контрагент = &Контрагент

 УПОРЯДОЧИТЬ ПО Д.Ссылка.МоментВремени УБЫВ)

 

Возможные решения:

A)    Заменить на УПОРЯДОЧИТЬ ПО  на

УПОРЯДОЧИТЬ ПО Д.Дата УБЫВ

УПОРЯДОЧИТЬ ПО Д.Ссылка УБЫВ

 

      Б) Можно перенести вложенный запрос во временную таблицу:

ВЫБРАТЬ ПЕРВЫЕ 1

        Д.Ссылка

ПОМЕСТИТЬ ТЗСсылка

ИЗ

        Документ.Расходная КАК Д

ГДЕ

        Д.Ссылка.Проведен

        И Д.Контрагент = &Контрагент

 

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

        Д.Ссылка.МоментВремени УБЫВ

;

 

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

ВЫБРАТЬ

        РасходнаяТовары.Ссылка,

        РасходнаяТовары.НомерСтроки,

        РасходнаяТовары.Товар,

        РасходнаяТовары.Количество,

        РасходнаяТовары.Цена,

        РасходнаяТовары.Сумма

ИЗ

        Документ.Расходная.Товары КАК РасходнаяТовары

ГДЕ

        РасходнаяТовары.Ссылка В

                       (ВЫБРАТЬ

                               Т.Ссылка

                       ИЗ

                        ТЗСсылка КАК Т)

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

ВЫБРАТЬ ПЕРВЫЕ 1

        Расходная.Ссылка,

        Расходная.Товары.(

               Ссылка,

               НомерСтроки,

               Товар,

               Количество,

               Цена,

               Сумма

        )

ИЗ

        Документ.Расходная КАК Расходная

ГДЕ

        Расходная.Контрагент = &Контрагент

        И Расходная.Проведен

 

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

        Расходная.МоментВремени УБЫВ

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

Пример:

Выбрать Приходная.Ссылка ИЗ  Документ.Приходная Где Приходная.Товары.Номенклатура = &Номенклатура.

 

Преимущество этого запроса перед запросом ко вложенной таблице Приходная.Товары в том, что если есть дубли в документах , результат запроса вернет только уникальные документы без использования ключевого слова РАЗЛИЧНЫЕ.

Сравните:

Выбрать Различные Товары.Ссылка ИЗ Документ.Приходная.Товары как Товары Где Товары.Номенклатура = &Номенклатура.

На этом месте, пожалуй, всё. Понятно, что в языке запросов ещё много неосвещенных мной вопросов.  Для написания статьи была использована информация, полученная мной после прохождения базового курса 1С 8.2 spec8.ru, а также из книги «1С 8.2 Руководство разработчика» и просторов интернета.

Всем спасибо!

См. также

Лучшие комментарии

29. KulSer 25.12.2012 08:56
(27) Hany. Вот как я это использую.
Допустим, у нас есть справочник Склады. Иерархический. Надо сделать отчёт по остаткам ТМЦ в разрезе по складам. В отчёте есть реквизит Склад типа СправочникСсылка.Склады. Я ставлю на форму поле ввода, привязанное к реквизиту Склад (Данные = Склад).
В запросе в условии я использую конструкцию
 ГДЕ Склад В ИЕРАРХИИ(&Склад) 
или в условии виртуальной таблицы
 Остатки(&Период, Склад В ИЕРАРХИИ(&Склад) 
.
Что при этом получается? Если в поле ввода Склад выбран конкретный элемент справочника, то запрос выберет остатки по этому одному складу. Если выбрана группа складов, то запрос выберет остатки по всем складам, входящим в эту группу. Если же в поле ввода Склад не выбрано ничего (ПустаяСсылка), то запрос выберет остатки по вообще всем складам (по умолчанию считается, что если пользователь ничего не ввёл в поле Склад, то ему как раз и надо выбрать по всем складам).
Не знаю, как Вам, а мне такой способ записи условий нравится. Коротко и удобно.
Ответили: (31)
# Ответить
20. KulSer 19.12.2012 09:44
Для начинающих в самый раз. Я бы добавил к примеру "Где Номенклатура В ИЕРАРХИИ (&Группа)" оговорочку, что если параметру &Группа присвоить значение ПустаяСсылка(), то запрос вернёт все значения справочника. Сам этим пользуюсь.
Ответили: (22) (27) (44) (46)
− 1 [ AlexO; ]
# Ответить
3. Rebelx 10.12.2012 15:14
такая возможность не упомянута (пример):

... И (ВЫРАЗИТЬ(&Номенклатура КАК Справочник.Номенклатура).ЛогистическаяГруппа = ЛогистическиеИздержки.ЛогистическаяГруппа)
# Ответить

Комментарии

1. Поручик 10.12.2012 10:48
Для новичков пойдёт.
# Ответить
2. DrAku1a 10.12.2012 10:58
Плюсую. Есть несколько хитрых приемов, правда, уже описанных на ИС.
(0) Автор, проверьте статью на грамматические ошибки. И еще - по поводу оформления. Читать не очень удобно. Сравните с http://infostart.ru/public/71130/.
# Ответить
3. Rebelx 10.12.2012 15:14
такая возможность не упомянута (пример):

... И (ВЫРАЗИТЬ(&Номенклатура КАК Справочник.Номенклатура).ЛогистическаяГруппа = ЛогистическиеИздержки.ЛогистическаяГруппа)
# Ответить
4. fpat 10.12.2012 15:41
Спасибо за ссылку! Ошибки, какие найду, исправлю (очень спешил, поэтому местами кратко и описки были). По ходу комментариев дополню статью и постараюсь поработать с оформлением.
# Ответить
5. platinum 11.12.2012 22:15
Неплохо, но если уж писать, то делать это с примерами использования конкретной конструкции.
Ответили: (10)
# Ответить
6. maxx 11.12.2012 22:17
Спасибо за п.16 в плане выделения текста запроса в условии или параметров виртуальным таблице в конструкции В (..) с вызовом конструктора.


Насчет п.11 еще одна фишка. Если есть агрегатные функции в запросе к пустой таблице (нет данных), а группировочных полей нет (т.е. без конструкции СГРУППИРОВАТЬ ПО), то будет возвращать одна запись где все колонки равны NULL, а если применить к этой же таблице СГРУППИРОВАТЬ ПО КОНТРАГЕНТ, то запрос не вернёт ни одной записи. Самое главное - что это правильное поведении согласно стандарту SQL ANSI 92.

Насчет п.27, если я не ошибаюсь также нельзя применять агрегатныt функции к МоментВремени, т.е. нельзя написать МАКСИМУМ(МоментВремени) , т.е. найти максимальный момент времени

ОТ СЕБЯ также бы добавил, что не всегда написанный "красиво" запрос выполняется быстрее написанной менее компактно конструкции, например, конструкцию В лучше применять где действительно есть списки , а не один или 2 элемента, к которым лучше применять равенство = .
+ 2 [ sulfur17; illUMI; ]
# Ответить
7. romansun 11.12.2012 23:34
7. В запросах может использоваться конструкция «ДЛЯ ИЗМЕНЕНИЯ» чтобы установить автоматическую блокировку и на запись и на чтение (аналог режима Исключительный в управляемых блокировках).


Насколько я понимаю, это устарело, ибо имеет смысл только для режима автоматических блокировок.
# Ответить
8. adhocprog 12.12.2012 09:14
(0) спасибо за инфу :)
В п.28. чем первый запрос лучше второго?
# Ответить
9. fpat 12.12.2012 11:10
По результату запроса они идентичны. Просто первый вариант запроса менее очевиден, не так часто используется. И по-видимому он производительнее, в случае, если надо получать уникальные документы (ключевое слово Различные требует дополнительных ресурсов). Везде пишут, что делайте обращение сразу ко вложенной таблице документа (ТЧ). Во многих задач (в частности задача - получить последний документ со всей его табличной частью сразу, по конкретному контрагенту есть довольно простое решение сделать запрос через основную таблицу без вложенного запроса в условии и без использования временной таблицы). Часто вообще игнорируют способ обращения к основной таблице документа, из которой также можно обращаться к табличной части.
# Ответить
10. fpat 12.12.2012 11:11
(5) platinum, Примеры в ближайшее время добавлю ко всем пунктам. Какие-то по видимому уберу
# Ответить
11. fpat 12.12.2012 11:23
Если кто-то знает какие-то интересные приемы в языке запросов, пожалуйста, пишите. Всем будет интересно почитать.
# Ответить
12. iceflash 12.12.2012 18:06
Замечание - уячитесь мыслить sql запросами не запросами 1ц!!! Изучайте именно реляционные базы (в 1ц нет объектных) и именно со знанием их, вы поймете всю мощь, и не будет возникать "лишних скрытых возможностей" языка запросов 1ц.
Язык запросов 1ц - это "прослойка" между платформой и СУБД (Вот он, ключевой момент) в 8.х наконец пришли к тому что инпретатор стал более лоялен - вся его суть сводится к замене читаемых "Справочник.Номенклатура" - к _ref12312312
Важно понять как это работает. Большая часть функций из языка 1ц и есть реализация алиасов из стандарта SQL.

По статье:
Оформлено не очень читабельно, не видно "ясного" разделения + примеры наглядные (статья же для новичков(?)).
Самый главный вопрос:
- Прежде чем советовать выбор между выбрать различные и группировкой вы проводили замеры и анализ? DISTINCT vs GROUP BY (?) суть в том, что все зависит от данных+индексов+выбираемых данных(полей)+наложенных условий. В большинстве случаев, если вы посмотрите Execution plan запроса, то увидете то GROUP BY выигрывает.

2. Примеры бы нужны в студию, раз уж для новичков.
3. Большая часть моментов является в принципе весьма логичными и их акцентирования не имеет смысла.
4. Лично для меня было ново ПУСТАЯТАБЛИЦА но по большому счету все решается и без нее
Ответили: (16)
# Ответить
13. iceflash 12.12.2012 18:07
Потому ощущение статья любителя для любителя, после которызх приходится много чего анализировать и изменять. Пишите профессиональную статью - пишите профессионально, пожалуйста.
Ответили: (15)
# Ответить
14. ilov_boris 12.12.2012 20:55
Для начинающих отличная дока.
# Ответить
15. SeiOkami 12.12.2012 22:33
(13) iceflash, а почему бы тебе не написать?)
я не на понт беру, а реально интересуюсь. часто встречаю статьи, в которых говорится о, якобы, истинно-верных подходов, а потом в комментах их громят в крах =)
# Ответить
16. fpat 12.12.2012 23:02
(12) iceflash, Статья действительно для новичков. Над оформлением ещё поработаю. Вы забыли упомянуть, что на скорость выполнения также влияет выбор СУБД (одно дело - если это MSQL, а другое дело - PostGreSql или ORACLE). Говорить, что такая-то конструкция выполняется 100% быстрее, чем другая неправильно. Можно лишь рекомендовать, что в большинстве случаев такая конструкция будет оптимальнее, чем другая (полное соединение всегда будет проигрывать объединению с опцей Все в том же PostgreSql). Говорят тоже, что вложенные запросы - это зло и лучше использовать временные таблицы или соединения. Но в ряде случаев эффективнее оказывается могут выполняться запросы со вложенными запросами. На скорость выполнения, как вы заметили правильно, влияет план запроса, который формирует СУБД (в профайлере можно посмотреть) на основании таблиц, связей между таблицами, индексами,размером таблиц,данными, своей статистике запросов и обращений и т.д. И для одного и того же запроса может быть сколько угодно различных планов выполнения запроса, в который можно вмешиваться на низком уровне, изучая конкретную реализацию СУБД. Что касается Различные - это ключевое слово намного читабельнее, чем СГРУППИРОВАТЬ ПО и в простых случаях именно всегда его и рекомендуют использовать, при этом разницы большой в скорости выполнения между ними нет. При добавлении индекса по полю данные запрсы будут выполняться одинаково быстро.По поводу 1С программисту необязательно вникать в планы запросов,которые весьма различаются в разных СУБД. 1С сделала всё, чтобы упростить написание запросов для программистов 1С. 1С программисту важно понимать, что даже если запрос маленький и записан одной секцией выбрать - это не значит, что система будет выполнять обращение к одной таблице и для самой СУБД данный запрос может быть интерпретирован как 7-этажный запрос. В случае составных реквизитов или при наличии разыменования полей 1С программист должен понимать, что фактически в запрос добавляются множественные соединения с количеством, соответствующим количеству типов составного поля + соединения через разыменования полей;поэтому где только возможно нужно ограничивать число таких таблиц с помощью того же ключевого слова ВЫРАЗИТЬ; в случае строк неограниченной длины - знать, что нужно ограничивать их длину в самом запросе иначе может быть вылет из-за нехватки памяти; понимать, что временные таблицы надо своевременно удалять из памяти и не использовать их там где не надо. 1С -это ещё и объектная среда, поэтому отличия между 1С и чисто реляционными СУБД разительные. Язык SQL намного гибче 1С, но отчасти, из-за того, что 1С присущи два подхода - как табличный, так и объектный.
Ответили: (17)
+ 2 [ teflon; abev; ]
# Ответить
17. iceflash 13.12.2012 09:20
(16)

Вы забыли упомянуть, что на скорость выполнения также влияет выбор СУБД (одно дело - если это MSQL, а другое дело - PostGreSql или ORACLE). Говорить, что такая-то конструкция выполняется 100% быстрее, чем другая неправильно. Можно лишь рекомендовать, что в большинстве случаев такая конструкция будет оптимальнее, чем другая


Вообще-то, не забыл ;) :
- Прежде чем советовать выбор между выбрать различные и группировкой вы проводили замеры и анализ? DISTINCT vs GROUP BY (?) суть в том, что все зависит от данных+индексов+выбираемых данных(полей)+наложенных условий. В большинстве случаев, если вы посмотрите Execution plan запроса, то увидете то GROUP BY выигрывает.


Потому я и указал на то, что нужно написать в вашем материале=)
А конструкции join будут выполнятся на разных субд по-разному, и тем более что в постгрес, можно настроить какие будут использоваться алгоритмы, тем самым более тонко настраивая СУБД, чем тот же MS SQL(с ораклом не работал) тем самым можно добиться более лучших результатов.

Таким образом, я лишь говорю о том, что материал требует определенных правок.
Про то что программист 1ц должен понимать что запрос обработанный платформой будет совсем другим, и с дополнительными джойнами:
"Выбрать Док.Товар.Артикул" - (приведет к тому, что будет приджойнина таблица справочника ради получения реквизита Артикул)
И это как раз и становится очевидным, если понимать как работают sql запросы.
# Ответить
18. ManyakRus 18.12.2012 11:49
"11.В любых запросах с группировками в списках полей запроса можно свободно обращаться к реквизитам группировочных полей."

Все реквизиты к которым обращаешься через точку к группировочным полям неявно(незаметно) добавятся в секцию СГРУППИРОВАТЬ, а группировка это главный тормоз при расчете запросов, чем меньше группировок тем быстрее считается. В том числе ПРЕДСТАВЛЕНИЕ(..) тоже неявно добавляется в секцию СГРУППИРОВАТЬ и тормозит из-за этого.
# Ответить
19. fpat 18.12.2012 12:10
Конструктор запроса добавляет в группировку и реквизит группировочного поля, но если запрос писать вручную или убрать поле реквизит из группировки - запрос отрабатывается без ошибки. Данный пункт я нашел в книге Руководство разработчика, они его конечно особо не выделяли. Но вот интересно происходит ли в обоих случаях одинаковый запрос к базе данных или есть всё-таки оптимизация. Опять есть некоторое отличие от обработки запроса к табличной части документа и обращению к некоторому полю основной таблицы документа (в обоих случаях присутсвует левое соединение с другой таблицей: в первом случае - например, справочником номенклатура; во втором случае - таблицей основного документа), но если удалить из группировки данное поле во втором случае, то запрос не выполнится, так как возникнет ошибка; система потребует добавить в группировку. (например, ДокТЧ.Ссылка.Контрагент)
Ответили: (33)
# Ответить
20. KulSer 19.12.2012 09:44
Для начинающих в самый раз. Я бы добавил к примеру "Где Номенклатура В ИЕРАРХИИ (&Группа)" оговорочку, что если параметру &Группа присвоить значение ПустаяСсылка(), то запрос вернёт все значения справочника. Сам этим пользуюсь.
Ответили: (22) (27) (44) (46)
− 1 [ AlexO; ]
# Ответить
21. Zas1402 20.12.2012 17:21
Норм.
# Ответить
22. MoshkovEV 21.12.2012 01:14
(20) KulSer,
если параметру &Группа присвоить значение ПустаяСсылка(), то запрос вернёт все значения справочника

Спасибо за подсказку, не знал, а штука полезная порой.
# Ответить
23. GreenFox 21.12.2012 19:00
[12] Змечание более чем справедливо, что б писать эфективные запросы, нужно понимать механизмы sql сервера хотя бы в плане как выбираются данные из таблиц и делаются соединения.
Ответили: (24)
# Ответить
24. headMade 23.12.2012 10:58
(23) GreenFox,
ну так может посоветуете конкретных авторов, где можно поизучать ?
# Ответить
25. agulaev 24.12.2012 16:31
Спасибо! Автору плюс.
# Ответить
26. Raminus 24.12.2012 16:57
Хотелось бы видеть жизненных примеров...
# Ответить
27. Hany 24.12.2012 17:43
(20) KulSer,
а какой практический смысл для случаев пустой ссылки Группа выполнять код "Где Номенклатура В ИЕРАРХИИ (&Группа)", если можно
параметризировать условие вот так:

ГДЕ ВЫБОР КОГДА &ПризнакОтбора ТОГДА 
               НашаТаблица.Номенклатура В ИЕРАРХИИ (&Группа) 
                ИНАЧЕ ИСТИНА 
          КОНЕЦ


или же так, если угодно:

ГДЕ
	(НЕ &ИспользуемОтбор
			ИЛИ НашаТаблица.Номенклатура  В ИЕРАРХИИ (&Группа))
Ответили: (29)
# Ответить
29. KulSer 25.12.2012 08:56
(27) Hany. Вот как я это использую.
Допустим, у нас есть справочник Склады. Иерархический. Надо сделать отчёт по остаткам ТМЦ в разрезе по складам. В отчёте есть реквизит Склад типа СправочникСсылка.Склады. Я ставлю на форму поле ввода, привязанное к реквизиту Склад (Данные = Склад).
В запросе в условии я использую конструкцию
 ГДЕ Склад В ИЕРАРХИИ(&Склад) 
или в условии виртуальной таблицы
 Остатки(&Период, Склад В ИЕРАРХИИ(&Склад) 
.
Что при этом получается? Если в поле ввода Склад выбран конкретный элемент справочника, то запрос выберет остатки по этому одному складу. Если выбрана группа складов, то запрос выберет остатки по всем складам, входящим в эту группу. Если же в поле ввода Склад не выбрано ничего (ПустаяСсылка), то запрос выберет остатки по вообще всем складам (по умолчанию считается, что если пользователь ничего не ввёл в поле Склад, то ему как раз и надо выбрать по всем складам).
Не знаю, как Вам, а мне такой способ записи условий нравится. Коротко и удобно.
Ответили: (31)
# Ответить
30. ooosnika 25.12.2012 10:17
Очень полезно,очень хорошо что такая информация собрано в 1 месте, написана кратко и с примерами
# Ответить
31. Hany 25.12.2012 11:30
(29) KulSer,
а мне такой способ записи условий нравится. Коротко и удобно. 


Да понятно, только не всегда "краткость кода" синоним "оптимальность кода".
Если вам отбор по складу не нужен, то и не нужно в запросе его обрабатывать. Лучше пожертвовать читабельностью и длиной кода и написать проверку на параметр "НЕ &ИспользуемОтбор", который вернет ИСТИНА в случае пустого склада и в дальнейшее условие по отбору В ИЕРАРХИИ даже не полезет.
Ответили: (32)
+ 2 [ the1; Lukich66; ]
# Ответить
32. KulSer 25.12.2012 14:47
(31) Hany,
 не всегда "краткость кода" синоним "оптимальность кода" 

Конечно, не всегда. Я и не утверждал, что моё маленькое замечание годится на все случаи жизни.
Например, у меня работе количество записей даже в самой большой таблице не превышает 100 тысяч, и я могу себе позволить не выбирать между производительностью и читабельностью. А у Вас, возможно, ситуация другая.
# Ответить
33. Lukich66 07.01.2013 12:12
(19) fpat,добрый день. "Запросная" тема для меня не очень любима( ощущения элементов "знахарства"-отталкивает), поэтому если не составит особого труда хочу предложить Вам( ну и всем желающим) заполнить только с помощью 1-го запроса таблицу значений вида
сотрудник,м1,м2,...,мХ где, м1-мХ колонки месяцев из периода запроса Д1-Д2 заполненные значениями сумм основных начислений из соответствущего р-ра ЗиУП. Размер вознаграждения на Ваше усмотрение.
Ответили: (38)
# Ответить
34. Tpakmop 20.01.2013 18:16
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров) КАК Регистратор
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ГДЕ
(ВЫРАЗИТЬ(ОстаткиТоваров.Регистратор КАК Документ.ПоступлениеТоваров) ЕСТЬ НЕ NULL)

В пункте 5.

Для данного примера можно также использовать оператор ССЫЛКА (которого кстати нет в данной публикации):

ГДЕ
(ОстаткиТоваров.Регистратор ССЫЛКА Документ.ПоступлениеТоваров)
# Ответить
35. yuraos 22.01.2013 05:58
плюс автору за "разжевывание" 1с-ного мануала, который:
а. не найдешь легально в свободном доступе;
б. от которого часто нет толка ни-ка-ко-го
;)
+ 1 [ elenko1; ]
# Ответить
36. Kom-off 22.01.2013 10:26
Только мне показалось, что в списке "полезных сведений" два 20-х пункта?
# Ответить
37. kurvik 29.01.2013 18:31
Плюсую за полезные сведения,пригодиться ввиде шпаргалки.
# Ответить
38. Hany 29.01.2013 19:10
(33) Lukich66, странное задание, если у вас ЗиУП, то есть таблица РегламентированныйПроизводственныйКалендарь, оттуда берете список всех месяцев и соединяете с регистром ОсновныеНачисленияРаботниковОрганизаций по периодам...Это не мегазадача для ЗУПа
Ответили: (39)
# Ответить
39. Lukich66 30.01.2013 19:41
(38) Hany,добрый вечер. Уж думал мое предложение так и останется незамеченным. Хочу несколько уточнить задачу.
Производственный календарь не используем. Пишем запрос( !шедевр,если без временных таблиц) на входе Дата1,Дата2. На выходе ТЗ с количеством колонок К1,..Кn, где n=месяцев(Дата2-Дата1+1)+ колонка сотрудник.
В каждой строке= сотрудник,S1,..Sn суммы из р-ра ОсновныеНачисленияСотрудниковОрганизации заданного периода запроса. Т.е.,если сотруднХ в указанном периоде(Д1,Д2) имел суммы только в периодах Д1+х1,Д1+х2 и т.д. то и в соответствующих колонках ТЗ(месяцах Д1+х1,Д1+х2 и т.д.) должны быть эти суммы, остальные колонки этой строки пустые. Т.О. на выходе получаем таблицу вида:
сотр | м1 | м2 | м3 | м4 | м5 | и т.д.
с1 : : хх : хх : : :
с2 : : : хх : хх : хх :
с3 : хх : : : : хх :
возможно ли такое в принципе? Буду оч. признателен( в пределах разумного) за любые предложения.
Ответили: (40)
# Ответить
40. headMade 30.01.2013 23:07
(39) Lukich66,
вам нужен именно сам текст запроса для получения результата в виде таблицы?
или вам нужен просто отчет в таком виде? Если отчета достаточно, то СКД подойдет?
Ответили: (41) (42)
# Ответить
41. Lukich66 31.01.2013 09:41
(40) headMade, лучше тект запроса, но и вариант с СКД тоже рассматривается.
# Ответить
42. Lukich66 01.02.2013 09:49
(40) headMade, приветствую Вас Анатолий, полность удовлетворен Вашим решением. Молодец,так держать!
# Ответить
43. Al-X 07.02.2013 13:44
И я плюсую за данную статью. Для новичков самое то !! Но все же не хватает примеров.
# Ответить
44. AlexO 05.04.2013 12:48
(20) KulSer,
для начинающих - книжки откройте сначала.
А то и читать разучитесь :)
# Ответить
45. LexSeIch 29.04.2013 12:09
Мир этому дому!
Статья полезная - лишней информации не бывает. Тем более, что в комментариях появились дополнительные полезные подсказки! Автору плюс. Критиковать всегда проще.
# Ответить
46. a-novoselov 16.05.2013 11:49
(0) (20) Если статья для новичков, то следует ОБЯЗАТЕЛЬНО упомянуть, что условие В ИЕРАРХИИ(...) сильно снижает производительность запроса, т.к. ни один SQL сервер не понимает иерархию 1С и платформе приходится рекурсивно обходить всех родителей, чтобы проверить выполнение условия. Если не ошибаюсь, то даже в желтых книжках было написано, что использование условия "В ИЕРАРХИИ" не желательно, если можно заменить условием "=" или "В". Правда не объяснено почему так. А т.к. новички эти книжки читают не внимательно, то такое замечание просто необходимо.
Слезы на глаза наворачиваются, когда видишь что по задаче нужно проверить вхождение элемента в список из 20 подобных, передают массив параметром в запрос и по совету (20) вместо "В" пишут "В ИЕРАРХИИ". Особенно для справочников в 100 000 элементов и 15 уровнями иерархии... Потом из-за таких умельцев слышишь "Ваша 1С говно, тормозит ужасно, запрос на 20 строчек полчаса выполняется!"
# Ответить
47. kievgorez 19.05.2013 22:29
Спасибо. ОСвежил некоторые моменты в памяти)
# Ответить
48. krv2k 28.05.2013 16:41
(0)
4.Иногда полезно получать представление ссылочных полей с помощью ключевого слова ПРЕДСТАВЛЕНИЕ наряду со ссылкой для того, чтобы не было повторного обращения к базе данных. Это бывает полезно при выводе результата запроса в таблицу.

Такой трюк работает только при формировании отчета с помощью построителя: http://partners.v8.1c.ru/forum/thread.jsp?id=1142807
Для каждого поля построитель ищет в списке выборки поле, в котором получается поле Представление через точку от иходного поля, или используются функции Представление или ПредставлениеСсылки. Найденные поля считаются полями – представлениями.
# Ответить
49. OksDallas 12.09.2013 04:17
Уважаемые коллеги! Помогите, кто может с запросом.
Имеется регистр сведений, пусть он называется ЦеныНаМеталлы, который имеет Ресурсы: Цена, ЦенаПлан. Плановую цену разнесли до конца года, Цены только до августа. Запрос примерно такой:

Запрос.Текст =
"ВЫБРАТЬ
| ГОД(ЦеныНаМеталл.Период) КАК ГодПериод,
| МЕСЯЦ(ЦеныНаМеталл.Период) КАК МесяцПериод,
| ЦеныНаМеталл.Цена КАК Цена,
| ЦеныНаМеталл.ЦенаПлан КАК ЦенаПлан,
| ЦеныНаМеталл.Металл КАК Металл
|ИЗ
| РегистрСведений.ЦеныНаМеталл КАК ЦеныНаМеталл
|ИТОГИ
| СРЕДНЕЕ(Цена),
| СРЕДНЕЕ(ЦенаПлан)
|ПО
| ГодПериод,
| Металл,
| МесяцПериод";

Одна из целей этого запроса получить среднюю цену за год по металлу.
При формировании запроса для Цены формируются записи с 9 по 12 месяц с 0-ми значениями. Само собой при расчете средней цены складываются все цены (значимых значений 9) и дружно делятся на 12 месяцев. А не на 9, как мне хотелось бы:).
Вне запроса я это, конечно, могу сделать. Но было бы интересно узнать, возможно ли решить данную задачу средствами запроса?
Ответили: (50) (58)
# Ответить
50. registration123 19.09.2013 11:24
(49) OksDallas, Можно, для этого нужно засунуть во временную таблицу расчет количества значащих периодов и делить сумму цены (в следующем запросе, связанным со временной по ключевым полям) на количество значащих периодов.
# Ответить
51. Bukaska 02.10.2013 14:39
Спасибо автору)) Тоже малясь разобралась, а то я в этих командах ещё вечно путаюсь)) Подтягиваться по запросам надо)))
# Ответить
52. KliMich 14.10.2013 19:45
Спасибо! Периодически надо освежать в памяти ...
# Ответить
53. ivanov660 14.10.2013 21:50
Использовать полное соединение плохой совет, с учетом, что в некоторых СУБД такие команды "эмитируются". Поэтому правильнее всего использовать ключевое слово объединить все - да и выполняется она в разы быстрее.
С декартовым соединением вообще лучше не играться - особенно если в средних базах за несколько лет может накопиться порядка 10 тысяч документов и несколько десятков строк и после нажатия кнопки выполнить привет!!!
Ответили: (57)
# Ответить
54. ssa 16.10.2013 10:38
Спасибо за собранную в одном месте нужную и полезную информацию.
# Ответить
55. Raminus 16.10.2013 11:49
Тема развивается, автору респект.
# Ответить
56. VasiL` 16.10.2013 14:13
Про "ВЫРАЗИТЬ" понравилось, спасибо автору! :-)
# Ответить
57. Bukaska 16.10.2013 14:22
(53) ivanov660, Тут вы правы.. Особенно если PostgreSQL
Но примеров точно не хватает.. особенно побегать по темам раздела Программирование 1С82.
так что дай бог автор объявится, а не объявятся - мож я дозрею))) Тем более что язык запросов у меня под рукми.. свежак последний)))
# Ответить
58. ksai 16.10.2013 16:06
(49) OksDallas,
При формировании запроса для Цены формируются записи с 9 по 12 месяц с 0-ми значениями. Само собой при расчете средней цены складываются все цены (значимых значений 9) и дружно делятся на 12 месяцев. А не на 9, как мне хотелось бы:).

Ваше утверждение про "дружно делятся на 12 месяцев" будет верно, только если в регистре по одному металлу делается одна запись в месяц.
Вообще-то при расчете агрегатной функции СРЕДНЕЕ вычисляется среднее значение всех попавших в выборку значений поля. То есть, если бы в регистре было по 2 записи в месяц, то сумма цены за год дружно делилась бы уже на 24, а не на 12.
Отвлеклись.
Возвращаясь к вашему вопросу, уважаемый registration123 подсказал вам совершенно правильное решение

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

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

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

ВЫБРАТЬ
КОЛИЧЕСТВО(ЦеныНаМеталл.Период) КАК КолПериодов,
ЦеныНаМеталл.Период,
ЦеныНаМеталл.Металл
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
РегистрСведений.ЦеныНаМеталл КАК ЦеныНаМеталл
ГДЕ
НЕ ЦеныНаМеталл.Цена = 0

СГРУППИРОВАТЬ ПО
ЦеныНаМеталл.Металл,
ЦеныНаМеталл.Период
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
ГОД(ЦеныНаМеталл.Период) КАК ГодПериод,
ЦеныНаМеталл.Металл КАК Металл,
МЕСЯЦ(ЦеныНаМеталл.Период) КАК МесяцПериод,
ЦеныНаМеталл.ЦенаПлан КАК ЦенаПлан,
ЦеныНаМеталл.Цена КАК Цена,
ВТ_Периоды.КолПериодов
ИЗ
РегистрСведений.ЦеныНаМеталл КАК ЦеныНаМеталл
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Периоды КАК ВТ_Периоды
ПО ЦеныНаМеталл.Металл = ВТ_Периоды.Металл
И ЦеныНаМеталл.Период = ВТ_Периоды.Период
ИТОГИ
СРЕДНЕЕ(ЦенаПлан),
СУММА(ЦеныНаМеталл.Цена) / СУММА(ВТ_Периоды.КолПериодов) КАК Цена
ПО
ГодПериод,
Металл,
МесяцПериод
+ 1 [ RegrZ; ]
# Ответить
59. VZhulanov 18.10.2013 08:36
Добавлю несколько замечаний про оптимизацию (для больших таблиц):
1. Если в запросе одновременно используются слова ПЕРВЫЕ и УПОРЯДОЧИТЬ, то сначала выполняется УПОРЯДОЧИТЬ, а уже затем выбираются ПЕРВЫЕ.
Поэтому использовать эти операторы одновременно стоит только в том случае, когда надо выбрать ПЕРВЫЕ из упорядоченной последовательности.
А если надо выбрать любые несколько записей и показать их в упорядоченном виде, то лучше сначала выбрать эти записи одним запросом, а потом отсортировать другим (временные или вложенные).

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

3. Если делать запрос к виртуальным таблица регистра, то надо быть очень аккуратным к условиям внутри.
Например Выбрать * Из Регистр.ОстаткиТоваров.Остатки(,Товар.Признак1 = &Знач1)
может оказаться в несколько раз медленнее запроса
Выбрать Ссылка Товар Поместить врТов Из Справочник.Товары Где Товар.Признак1 = &Знач1;
Выбрать * Из Регистр.ОстаткиТоваров.Остатки(,Товар В (Выбрать Товар Из врТов))
Т.е. в условиях лучше отказаться от разыменования полей.

4. Щас уже не вспомню конкретных примеров, но опыт показывает что 1Ска не всегда правильно оптимизирует запросы:
4.1. можно написать запрос, который на файловой базе будет работать намного быстрее чем на SQLной
У меня был пример, когда старый Pentium-4 на файловой базе отрабатывал запрос намного быстрее чем I-7 на SQLной (с памятью на втором компе тоже все было намного лучше)
4.2. можно наоборот, причем это будет именно ошибкой оптимизации, а не тем, что SQL быстрее файловой.

Вывод: нужно протестировать запрос и на файловой и на SQLной базе и постараться его оптимизировать

Да и вообще совет всем начинающим и продолжающим - не ленитесь тестировать производительность своих программ и оптимизируйте их.
+ 2 [ wolfsoft; alean; ]
# Ответить
60. Bukaska 18.10.2013 16:34
Хочу так же сказать про левое соединение в запросе.
если мы делаем левое соединение и накладываем условие на правую таблицу, то оно отрабатывает как внутреннее соединение согласно данному условию
Ответили: (61)
# Ответить
61. postovalov 19.10.2013 08:26
(60) Bukaska,

Спасибо за замечание, действительное поведение запроса не очевидное.
В задаче 6 самостоятельной работы, которую я даю слушателям курсов "Основные объекты"
(http://infostart.ru/profile/255868/public/), возникает эта проблема.

Ее можно обойти, если условие на правую таблицу внести в условие связи,
т.е. "ТАБЛИЦА 1 ЛЕВОЕ СОЕДИНЕНИЕ ТАБЛИЦА2 ПО (УСЛОВИЕ_СВЯЗИ И УСЛОВИЕ_НА_ТАБЛИЦУ2)"
Ответили: (62)
# Ответить
62. Bukaska 19.10.2013 11:33
(61) postovalov, да как обойти я знаю)))) Я уже половину Хрусталевой перелопатила Язык Запросов))) Просто специально не сказала)))
# Ответить
63. postovalov 19.10.2013 18:35
Про книгу Хрусталевой не знал, надо будет посмотреть.
# Ответить
64. Сисой 21.10.2013 11:48
Подскажите пожалуйста, почему в примерах не раз встречается выборка табличной части методом ("от шапки")? Лично я (да и пеЙсатели типовых) предпочитаю работать "от табличной части", добавляя поля шапки через "Ссылка.". По-моему, так удобнее обрабатывать данные, и гораздо ближе к нативному SQL.
Именно поэтому ПУСТАЯТАБЛИЦА ни разу использовать не приходилось, для меня это рудимент, легко заменяемый константами ВЫРАЗИТЬ(xxx) с алиасами.

Подобный запрос с обращением к табличным частям можно себе представить только в случае, если этих табличных частей много и мы хотим минимизировать количество обращений к СУБД.
# Ответить
65. svvinks 22.10.2013 11:32
19 пункт - Ложная информация.

В SQL любое выражение, один из операндов которого Null возвращает Null. Такое поведение реализовано в запросах 1С.

Любая операция сравнения, один из операндов которой Null принимает значение UNKNOWN. В запросах при вычислении условий отбора строк UNKNOWN аналогично но вовсе не равно значению False.

В запросах 1С такое поведение реализовано. В справке конфигуратора по встроенному языку есть примечание к статье "Правила сравнения значений" :

Важно! Любая операция сравнения двух значений, в которой участвует хотя бы одно значение NULL, дает результат, аналогичный значению ЛОЖЬ.

Для Null приоритет отрицания не срабатывает. Читаем справку, статья "Логические выражения в языке запросов" :

<Выражение> ЕСТЬ [НЕ] NULL

Оператор ЕСТЬ NULL позволяет проверить значение выражения слева от него на NULL. Если значение равно NULL – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ. Применение НЕ изменяет действие оператора на обратное.

В сухом остатке имеем что при вычислении логических выражений в запросах 1С выражения "Есть НЕ NULL" и "НЕ Есть NULL" эквивалентны !
# Ответить
66. ShantinTD 23.10.2013 17:26
Спасибо.
Для себя вынес полезного:
- про недокументированную форму оператора ВЫБОР
- про ВЫРАЗИТЬ. И тут хочу добавить:
- в конструкторе запроса ВЫРАЗИТЬ позволяет избавиться при дальнейшей обработке от нежелательных типов.
- может сильно сказываться на времени выполнения запроса. См. описание эксперимента ниже.

Эксперимент:
1. Клиент-серверный режим 1С 8.3.4.304, PostgreSQL 9.1.2-1.1C. Выборка на ~580000 записей, допустимые типы ~70. Через ВЫРАЗИТЬ выполняется за ~0.9 секунды, без него за ~1.6 секунд. На меньшей выборке - разница была еще значительнее: ~12500 записей за ~0.1 секунды через ВЫРАЗИТЬ и ~10 секунд без него.
2. Файловая база 1С 8.3.4.304. Выборка ~750000 записей, допустимых типов 5. Через ВЫРАЗИТЬ время составило около 17 секунд, без него - около 19-20 секунд.
3. Второй же запрос на MS SQL отработал так же в пользу ВЫРАЗИТЬ: 1.0-1.1 секунды против 2.3-2.5 секунды без ВЫРАЗИТЬ.
Вывод (по моему очевиден): если нет надобности в том, чтобы оставить поле составного типа, то есть однозначный смысл использовать ВЫРАЗИТЬ. В самом плохом случае - от него не стало ни лучше, ни хуже.
# Ответить
67. mcher 20.11.2013 08:27
Спасибо за статью. Есть что подчеркнуть для себя.
# Ответить
68. MrFlanker 25.12.2013 19:18
Отличная статья. Спасибо автору.
# Ответить
69. Bukaska 26.12.2013 15:55
А расширение статьи планируется?
# Ответить
70. Эсти 19.08.2014 11:01
Спасибо за статью
# Ответить
71. distorshion 29.08.2014 14:55
Полезная статья
# Ответить
72. demon1981 12.09.2014 05:21
Спасибо за статью. Занимаюсь обновлением знаний. Также добавлю ссылку на полезный самоучитель по запросам http://infostart.ru/public/184350/index.php# Позволят обновить знания о методах и способх формирования запросов, в варианте тонкого клиент прикольно тренировать написание запросов без использования конструктора. Тут поднимался вопрос о полезности изучения язфыка запросов SQL есть ссылка на полезный сайт для этого http://sql-ex.ru/
# Ответить
73. TopSergey 28.03.2015 11:26
Не нашёл ещё 1 интересной конструкции:

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

СГРУППИРОВАТЬ ПО
      Контрагенты.Ссылка

ИМЕЮЩИЕ
      КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагенты.ИНН) > 1


"ИМЕЮЩИЕ" - это ключевое слово, аналог "ГДЕ" для вычисляемых полей.
Конструктором запросов переваривается нормально, но самый простой способ - написать ручками в теле запроса.

Данный участок кода Выводит контрагентов, которые имеют дубли по ИНН.
+ 1 [ grachev1c; ]
# Ответить
74. kite2 19.06.2015 13:37
Статья нормальная, но нет информации о ПОДОБНО:

Например:

|ГДЕ
| Товары.Наименование ПОДОБНО ""[Cc]тул%""";
+ 1 [ rubezh; ]
# Ответить
75. Ivan48 15.10.2015 01:20
Подскажи пожалуйста, как сгруппировать по дате (по одному дню)?
# Ответить
76. DEDBAZAR 21.10.2015 16:01
	ЗаписатьОкноВРегистрСведений(ТекущаяФорма.УникальныйИдентификатор);
			ИначеЕсли ТекущаяФорма.Объект.Ссылка.Пустая() И ТипЗнч(ТекущаяФорма.Объект.Ссылка) = Тип("СправочникСсылка.ВнутренниеДокументы")  Тогда
# Ответить
77. RokLI 13.11.2015 08:28
В отчете в скд поправьте

ВЫБОР
		КОГДА ДвиженияСерийныхНомеров.ХозяйственнаяОперация = ЗНАЧЕНИЕ(Перечисление.ХозОперацияСертификатов.Реализация)
			ТОГДА ЕСТЬNULL(ДвижениеСертификатовОстатки.СуммаОстаток, 0)
		ИНАЧЕ 0
КОНЕЦ КАК ВнереализационнаяПрибыль
...Показать Скрыть


вместо
ЕСТЬNULL(ДвижениеСертификатовОстатки.СуммаОстаток, 0)
# Ответить
78. juvv873 22.05.2016 03:34
Как правильно использовать ЗНАЧЕНИЕ?
Пишу запрос, надо проверить входит ли номенклатурная позиция в группу ГАЗЫ
и в случае если это так, заполнить ячейку таблицы текстом "БАЛ"
Никак не могу правильно построить запрос. Со строкой сравниваться не хочет, т.к. слева ссылка.
Как правильно сделать? Через ЗНАЧЕНИЕ?

| ВЫБОР
| КОГДА ТаблицаТовары.Номенклатура.Родитель = ЗНАЧЕНИЕ(Справочник.Номенклатура.ГАЗЫ)
| ТОГДА ""БАЛ""
| ИНАЧЕ ""--""
| КОНЕЦ КАК БАЛ,
# Ответить
Внимание! За постинг в данном форуме $m не начисляются.
Внимание! Для написания сообщения необходимо авторизоваться
Текст сообщения*
Прикрепить файл