Запросы

 

Когда необходимо получить какую-либо сложную выборку данных из информационной базы проще всего использовать так называемые «запросы».

 

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

 

• Источники данных (табличная модель данных)

• Структура запроса (описание запроса)

• Обработка результата запроса

 

Источники данных

 

Можно сказать что система «редприятие 8.0» предоставляет две модели доступа к данным:

 

• Объектная модель (доступ идет через свойства, методы объектов)

• Табличная модель (база данных представляется набором таблиц).

Таблицы, поля базы данных

Таблицы в «редприятии 8.0» подразделяются на два основных класса: реальные и виртуальные.

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

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

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

 

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

 

• «Обычное поле» (содержащее какое-либо значение, либо значение типа «Null»)

• вложенная таблица


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

Можно проиллюстрировать данное понятие следующим образом:

Если продолжить разговор о полях, содержащих какие-либо значения, можно отметить: поле может содержать значение одного типа, может содержать значения нескольких типов (иметь составной тип), при этом для конкретной записи поле содержит значение одного типа.

 

Структура запроса (описание запроса)

 

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

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

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

 

Можно сказать, что текст запроса состоит из следующих секций:

 

• Описание запроса

• Объединение запросов

• Упорядочивание результатов или Автоупорядочивание

• Описание итогов

 

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

 

Выбрать [Различные] [Первые<Количество>]

<Список полей выборки>

[Из <Список источников>


[Где <Уcловие отбора>]

[Сгруппировать По <Поля группировки>]

[Имеющие <Условия отбора>]

[Для изменения [[Of]<Список таблиц верхнего уровня>]]

 

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

 

Выполнение и работа с запросами во встроенном языке

 

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

Рассмотрим в качестве первого примера отчет «Знакомство с Запросом».

 

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

 

НачПериода тип «Дата» состав «Дата+Время»

КонПериода тип «Дата» состав «Дата+Время»

ЗначениеСсылка тип <СправочникСсылка»

ЗначениеЧисло тип «Число»

ЗначениеСтрока тип «Строка»

ЗначениеБулево тип «Булево»

 

Определена табличная часть «ТЧ»:

 

• Реквизит табличной части «ТЧСсылка», тип «СправочникСсылка»

 

 

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

 

 

У поля текстового документа в свойстве «Расширение» проставить значение «Язык запросов». У табличного поля определить тип значения «Таблица значений».

 

 

 

Текст процедуры, которая выполняется при нажатии на кнопку

«Сформировать» приводится ниже:

 

Таблицы базы данных

 

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

 

Таблица констант

 

Таблица констант имеет имя «Константы». Имена полей таблицы соответствуют именам констант, заданным в конфигураторе.

 

Запишем в окне текстового документа следующий текст:

 

 

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

 

Указание источников в запросе

 

Рассмотрим следующий запрос:

 

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

Следующий запрос показывает содержимое всех полей в таблице констант

 

 

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

 

Таблица справочников

 

Каждый справочник представлен таблицей, обладающей следующим

набором полей:

 

Для просмотра состава не виртуальных полей справочника «Физические лица» можно воспользоваться следующим запросом:

 

Указание вложенной таблицы как источника, конструкции «Различные», Первые N.

 

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

запрос:

 

Зададимся целью получить перечень единиц измерения. используемых при описании номенклатурных позиций.

 

 

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

 

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

 

Фильтрация результатов запроса

 

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

 

Как пример рассмотрим следующие запросы:

 

 

В данных примерах знак равно (в условии) не обязателен, так как поле «ЭтоГруппа» содержит значения типа «Булево».

 

 

В условиях (это не обязательно может быть конструкция «Где») помимо обычных операций сравнения могут использоваться «В», «В ИЕРАРХИИ», «Между И», «Подобно», «Есть Null».

 

Переход в другую таблицу «по точке»

 

В языке запросов «релприятие 8.0» существует возможность получения данных из нескольких таблиц, хотя при этом в разделе «ИЗ» указывается только одна таблица — источник данных.

 

Рассмотрим следующий запрос:

 

Указание нескольких источников, соединения, псевдонимы

 

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

 

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

Связывать таблицы можно с помощью конструкции языка запросов «Где» (как показано в следующем примере).

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

 

то время выполнения данного запроса снизится почти в два раза.

 

Другим способом указания взаимосвязи таблиц является использование «Соединений».

 

Соединения бывают нескольких видов:

 

• Внутреннее соединение

• Левое внешнее соединение

• Правое внешнее соединение

• Полное внешнее соединение

 

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

Рассмотрим эти варианты на следующем примере:

Есть две таблицы:

Условием соединения будет:

 

Таблица1.Номер1=Таблица2.Номер2

 

В качестве полей запроса определим две колонки: «Номер» из первой таблицы и «ЕдИзм» из второй таблицы.

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

Записи, неудовлетворяющие условию соединения:

Теперь рассмотрим варианты соединения:

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

Левое внешнее соединение: в результат выполнения запроса войдут данные из записей, для которых выполняется условие соединения и «не вошедшие» из Таблицы №1. Можно сказать, что в результат запроса войдут все данные из Таблицы №1, и для тех записей результата запроса, для которых выполнялось условие соединения в полях, куда помещаются данные из таблицы №2, будут стоять значения, для которых условие не выполняется, будет стоять Null.

Правое внешнее соединение обратно левому.

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

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

В качестве примера использования соединений рассмотрим нижеследующий текст запроса:

 

Практикум №13

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

 

Таблица документов

 

Любому документу соответствует следующая таблица:

 

Практикум №14

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

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

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


Упорядочивание результатов запроса

 

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

 

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

В случае, если вариант упорядочивания не указан (и не используется «автоупорядочивание»), то упорядочивание производиться не будет).

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

 

Группировки результатов запроса

 

При просмотре предыдущих результатов выполнения запросов данные получались в том виде, как они вводились в документы. Но если требуется получить ответ на вопрос: сколько какого-то товара закупалось (вообще), то либо придется складывать все при обходе результата запроса, либо использовать группировку.

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

Рассмотрим текст запроса:


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

 

В качестве агрегатных функций можно использовать:

 

• Сумма (Выражение)

• Среднее (Выражение)

• Минимум (Выражение)

• Максимум (Выражение)

• Количество ([Различные] Выражение)

 

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

 

Дополнительно рассмотрите следующий текст запроса:

 

 

Условия на значения агрегатных функций

 

Рассмотрим следующий текст запроса:

 

 

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

- то это необходимо сделать в первую очередь.

 

Итоги в запросе

 

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

 

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

 

Посмотрите результат, если вместо ключевого слова «Иерархия» указать «Только Иерархия».

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

 

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

 

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

 

Практикум .№15

Напишите текст запроса, по результатам которого можно будет увидеть суммы закупок по группам справочника «Контрагенты» и по отдельным поставщикам (элементам справочника «Контрагенты»). В результате запроса данные об определенном поставщике должны присутствовать в качестве одной записи.

 

Встроенные функции в языке запросов, операция выбора

 

• Подстрока (Выражение, Начальный символ, Сколько отрезать)

• Год (выражение)

• Квартал (выражение)

• Месяц (выражение)

ДеньГода (выражение)

• День (выражение)

• Неделя (выражение)

ДеньНедели (выражение)

• Час (выражение)

• Минута (выражение)

• Секунда (выражение)

НачалоПериода (Дата, Период)

КонецПериода (Дата, Период)

 

Источники-запросы

 

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

 

Следует отметить, что вложенный запрос можно использовать и в конструкции «В ()», «В Иерархии ()».

Например

……..

Где Номенклатура В (

Выбрать Номенклатура Как Товар Из………

………..

 

Таблица журнала документов

 

 Данная таблица является виртуальной.

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

соответствующем поле будет находиться значение Null.

Обратиться к таблице можно, используя текст запроса:

 

 

Практикум №16

Напишите текст запроса, который выдаст перечень проведенных и не помеченных на удаление документов.

 

Передача параметров в запрос

 

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

 

 

Таблицы регистра сведений

 

Основная таблица

Практикум № 17

1. Напишите текст запросов, позволяющих просматривать наполнение регистра сведений «Валюты».

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

 

Таблица получения среза последних

Виртуальная таблица. Она предназначена для получения наиболее близких в «прошлое» записей регистра сведений на указанную дату (включительно). Включает только активные записи.

 

Таблица получения среза первых

Виртуальная таблица. Предназначена для получения наиболее близких в «будущее» записей регистра сведений на указанную дату (включительно). Включает только активные записи.

 

Практикум №18

1. Сформируйте текст запроса, выполнение которого приведет к получению данных о курсах валют на выбранную дату.

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

 

Таблицы регистра накопления

 

Основная таблица

 

Предназначена для получения записей регистра накопления

 

Таблица получения оборотов

 

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

Параметры «Начало», «Окончание» задают период, за который получаются обороты.

 

В качестве периодичности может задаваться один из следующих вариантов:

 

• Период (не разворачивать)

• Регистратор

• день

• Неделя

• Месяц

• Квартал

• Год

 

Посмотреть таблицу можно с помощью запроса:

 

Таблица получения остатков

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

 

Таблица получения остатков и оборотов

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

 

Возможные значения метода дополнения:

• Движения (выдаются те периоды, в которых были движения).

ДвиженияИГраницыПериода (выбираются периоды по которым были движения и сушествовали – остатки на начало и конец)

 

Практикум №19

1. Напишите текст запроса, который при выполнении позволил бы получать остаток выбранной номенклатурной позиции на интересующую дату.

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

 3. Напишите текст запроса, который при выполнении выдаст данные о продажах номенклатуры по выбранному покупателю.


Таблицы регистра бухгалтерии (с поддержкой корреспонденции)

 

Основная таблица

Хранятся записи регистра бухгалтерии

 

Значения субконто

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


 

 

Движения с субконто

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


 

Остатки

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

Обороты

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

 

 

Остатки и обороты

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

 

Пример запроса с установленной периодичностью по неделе

 

Обороты Дт Кт

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