1с запрос параметры виртуальной таблицы. Стандартный параметр &Период и проблемы в использовании. Использование конструктора запросов

Статья описывает физическую реализацию виртуальной таблицы остатков конфигурации, работающей в клиент-серверном режиме работы на примере использования СУБД MS SQL Server.

Применимость

В статье рассматривается платформа «1С:Предприятие» редакции 8.3.5.1383. В актуальной версии платформы возможны некоторые изменения в тексте, описанного в материале, запроса T-SQL, выполняемого на стороне сервера СУБД.

Устройство виртуальной таблицы остатков

Рассмотрим, в какой запрос к СУБД трансформируется запрос с использованием виртуальной таблицы остатков регистра накопления. Для примера будет рассматриваться следующий текст запроса:

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

Сначала при помощи метода глобального контекста ПолучитьСтруктуруХраненияБазыДанных() получим список таблиц базы данных, в которых хранятся данные регистра накопления «ТоварныеЗапасы»:

Состав полей основной таблицы регистра накопления и таблицы итогов приведен ниже:

Хранение итогов для данного регистра настроено в режиме «1С:Предприятие 8» следующим образом:

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

Платформа преобразует текст запроса в следующий запрос, который и будет выполнен на сервере СУБД:

SELECT
Q_000_T_001.Fld82 ,
Q_000_T_001.Fld83 ,
Q_000_T_001.Fld84Balance
FROM
(SELECT Fld82 ,
Fld83 ,

FROM
(SELECT Fld82 ,
Fld83 ,
SUM (Fld84 ) AS Fld84Balance
FROM AccumRgT85
WHERE Period = DATETIME (3999 , 11 , 1 )
AND ((Fld83 = ))
AND (Fld84 <> 0 ) AND (Fld84 <> 0 )
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0
UNION ALL
SELECT Fld82 ,
Fld83 ,
SUM (CASE WHEN RecordKind = 0 THEN – Fld84 ELSE Fld84 END ) AS Fld84Balance
FROM AccumRg81
WHERE Period >= DATETIME (2012 , 9 , 1 )
AND Period < DATETIME (3999 , 11 , 1 )
AND Active
AND ((Fld83 = 9:))
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0 ) T
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0 ) Q_000_T_001

Разберем подробнее полученный запрос.

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

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

Во втором запросе объединения используется таблица движений регистра AccumRg81. В зависимости от вида движения (если RecordKind равно 0, то это Приход, в противном случае – Расход) проставляется знак в выражении. Платформа выбирает данные за период с даты, указанной в качестве параметра виртуальной таблицы, по дату хранения текущих итогов (01.11.3999).

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

Если используется СУБД MS SQL Server и для базы данных установлено смещение дат 2000, то все даты будут храниться с указанным смещением, т.е. вместо 01.11.3999 Вы увидите 01.11.5999.

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

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

SELECT
Q_000_T_001.Fld82 ,
Q_000_T_001.Fld83 ,
Q_000_T_001.Fld84Balance
FROM
(SELECT Fld82 ,
Fld83 ,
SUM (Fld84Balance ) AS Fld84Balance
FROM
(SELECT Fld82 ,
Fld83 ,
SUM (Fld84 ) AS Fld84Balance
FROM AccumRgT85
WHERE Period = DATETIME (2012 , 4 , 1 )
AND ((Fld83 = 9:))
AND (Fld84 <> 0 )
AND (Fld84 <> 0 )
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0
UNION ALL
SELECT Fld82 ,
Fld83 ,
SUM (CASE WHEN RecordKind = 0 THEN Fld84 ELSE – Fld84 END ) AS Fld84Balance
FROM AccumRg81
WHERE Period >= DATETIME (2012 , 4 , 1 )
AND Period < DATETIME (2012 , 9 , 1 )
AND Active
AND ((Fld83 = 9:))
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0 ) T
GROUP BY Fld82 , Fld83
HAVING Fld84Balance <> 0 ) Q_000_T_001

Обратите внимание на следующее условие в тексте запроса.

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

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

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

Язык запросов позволяет наложить условие на выборку из виртуальных таблиц двумя способами: в предложении ГДЕ и с помощью параметров виртуальных таблиц. Оба способа приведут к одному результату (за исключением некоторых специфических случаев), но, тем не менее, они далеко не эквиваленты.

Мы уже знаем, что виртуальные таблицы потому и называются виртуальными, что в базе их на самом деле нет. Формируются они только в тот момент, когда к ним обращается запрос. Несмотря на это, нам (то есть, тем, кто составляет запрос) удобно рассматривать виртуальные таблицы именно как реально существующие. Что же произойдёт в системе 1С Предприятие 8, когда составленный нами запрос всё-таки обратится к виртуальной таблице?

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

Таким образом, система проделает не просто бесполезную, а двойную бесполезную работу! Сначала будут затрачены ресурсы на построение виртуальной таблицы на основе лишних данных (на рисунке они помечены как «области данных А и Б»), а потом ещё будет проделана работа по фильтрации этих данных из окончательного результата.

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

В чем заключается различие значений параметра виртуальной таблицы "МетодДополнения"?
Когда МетодДополнения установлен в "движения", то будут выданы только те периоды в которых были движения. Когда установлен "ДвиженияИГраницыПериода", тогда к вышеуказанным движениям добавятся 2 записи: движения на начало и конец заданного в параметрах ВТ периода. Поле "Регистратор" при этом для этих 2-х записей будет пустым.

Информация взята с сайта

Регистры накопления в системе 1С:Предприятие делятся на два вида: регистры накопления остатков и регистры накопления оборотов .

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

Как следует из названия одни предназначены для получения остатков на какую-либо дату, а вторые для получения оборотов за выбранный период. В зависимости от вида регистра накопления платформа 1С:Предприятия формирует разный набор виртуальных таблиц. В этой статье и рассмотрим работу с виртуальными таблицами регистров накопления. Для этого создадим регистр накопления остатков — ТоварыОстатки и регистр накопления оборотов — ТоварыОбороты .

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

Регистр накопления оборотов

Для наглядности откроем и посмотрим какие таблицы доступны для регистра ТоварыОбороты . Это таблица самого регистра — ТоварыОбороты , которая существует физически в базе данных, и одна виртуальная таблица — ТоварыОбороты.Обороты

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

Виртуальная таблица Обороты

Эта таблица позволяет получать оброты ресурсов в разрезе измерений. В нашем случае у нас два измерения: Склад и Товар . И один ресурс — Количество

Пусть в нашем регистре есть следующие записи

Вернемся в конструктор запросов и для начала просто выберем из таблицы ТоварыОбороты.Обороты все поля

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

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

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

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

Для этого исключим из запроса измерение Склад

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

и в результате у нас останется только две строки

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

После этого наш исходный запрос примет следующий вид

ВЫБРАТЬ ТоварыОборотыОбороты.Склад, ТоварыОборотыОбороты.Товар, ТоварыОборотыОбороты.КоличествоОборот ИЗ РегистрНакопления.ТоварыОбороты.Обороты(&НачалоПериода, &КонецПериода, Склад = &Склад) КАК ТоварыОборотыОбороты

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

У тех кто только начинает работать с виртуальными таблицам зачастую возникает соблазн вместо использования параметров установить отбор привычным способом:

ИЗ РегистрНакопления.ТоварыОбороты.Обороты(,) КАК ТоварыОборотыОбороты ГДЕ ТоварыОборотыОбороты.Склад = &Склад

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

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

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

ВЫБРАТЬ ТоварыОборотыОбороты.Период, ТоварыОборотыОбороты.Склад, ТоварыОборотыОбороты.Товар, ТоварыОборотыОбороты.КоличествоОборот ИЗ РегистрНакопления.ТоварыОбороты.Обороты(, Месяц,) КАК ТоварыОборотыОбороты

Выполняем запрос:

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

Регистр накопления остатков

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

Как видим для регистра накопления остатков доступны три виртуальные таблицы: Обороты , Остатки , ОстаткиИОбороты . Рассмотрим каждую из них в отдельности.

Виртуальная таблица Обороты

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

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

Виртуальная таблица Остатки

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

Рассмотрим небольшой пример. Имеем следующие записи регистра:

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

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

А после его выполнения получим вот такой результат

Виртуальная таблица ОстаткиИОбороты

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

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

На первом шаге, система построит виртуальную таблицу. На втором шаге из полученной таблицы будут выбраны записи, удовлетворяющие условию, заданному в предложении ГДЕ:

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

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

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

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

Перед построением виртуальных таблиц, данные можно отобрать по параметрам. Набор параметров различен для разных таблиц. Для таблицы Остатков это период остатков и произвольное условие:

Для таблицы Оборотов период разбивается на два (начало и конец), и добавляется настройка Периодичности:

Для таблица Остатков и оборотов добавляется настройка Метода дополнения:

Рассмотрим подробнее все эти параметры.

Параметр Период (Начало периода, Конец периода) могут принимать значения типа Дата:

а также типа Момент времени и Граница:

Важно, что в случае использования типа Дата либо Момент времени (комбинации Даты и Ссылки) сама секунда времени , указанная в поле выборки, не учитывается в выборке данных! Она уже "за пределами" нее, выборка строится до/после заданного момента.

В случае использования типа Граница, этот нюанс настраивается, так как можно указать тип границы: показывать данные включая или исключая заданную секунду:

Параметр Периодичность - позволяет определить период, к которому будут приведены записи. Например, если периодичность Год, то все записи будут показаны как на 01 января.

Отдельно интересен вариант периодичности Авто, он позволяет в одном запросе привести данные к нескольким периодам. В конструкторе запроса при этом становятся доступны все варианты периода, можно выбрать нужные:

Результат:

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

Параметр Условие - позволяет наложить произвольное условие на измерение регистра:

Вопрос 07.46 экзамена 1С:Профессионал по платформе. При определении в виртуальной таблице "ОстаткиИОбороты" варианта периодичности "Авто":

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

Правильный ответ третий, разбор выше.

Понравилась статья? Поделитесь ей

Распечатать статью