что такое виртуальная таблица в 1с
Профессия — 1С
рубрики: Виртуальные таблицы | Дата: 2 февраля, 2017
Как известно при чтении данных платформа 1С обращается к таблицам базы данных. Но для регистров платформа 1С на основе реальных таблиц умеет формировать виртуальные таблицы, которые физически в базе данных не храняться. Это позволяет разработчику вместо того, чтобы делать сложный запрос к реальной таблице, сразу получить данные из виртуальной простейшим запросом. А также исключает возможные ошибки. Поэтому использовать виртуальные таблицы нужно всегда, когда есть такая возможность. Особенно при сдаче экзамена 1С:Специалист. Рассмотрим разные типы регистров и посмотрим какие виртуальные таблицы предоставляет платформа для каждого типа регистров.
Регистры сведений
Платформа формирует виртуальные таблицы только для периодических регистров сведений. Доступны следиющие их типы
Регистры накопления
Для регистров накопления набор предоставляемых виртуальных таблиц также зависит от вида регистра. Как известно есть два вида регистров накопления: Остатки и Обороты
Регистр накопления остатков
Доступны следующие виртуальные таблицы
Регистр накопления оборотов
Доступна только одна виртуальная таблица
Регистры расчета
Здесь также в зависимости от настроек доступны следующие виртуальные таблицы
В свою очередь есть три регистра расчета: Начисления, НачисленияДополнительные и Удержания. И регистры Начисления и НачисленияДополнительные включены в план видов расчета Начисления, а регистр Удержания соответственно в план видов расчета Удержания. 
Так вот, для регистра расчета Удержания у нас тогда будут доступны две виртуальные таблицы с базой
БазаНачисления
БазаНачисленияДополнительные
Регистры бухгалтерии
Самым большим набором виртуальных таблиц обладают регистры бухгалтерии
Но скорость обращения к регистрам бухгалтерии самая низкая. Поэтому если есть возможность получить те же остатки или обороты с использованием регистров накопления, то их и надо использовать.
Использование виртуальных таблиц 1С в запросах
1. Получение данных — создание запросов к базе данных.
Получение информации из регистров происходит посредством создания запросов к базе данных, и оттого насколько правильно сделан запрос будет зависеть скорость получения данных и как следствие оптимальность работы системы. В данной статье мы рассмотрим некоторые особенности получения данных посредством запросов данных в 1С.
2. Структура регистров.
Регистры состоят из двух таблиц, которые хранятся в базе данных, это таблица движений и таблица итогов. Так же в структуру регистров входят виртуальные таблицы, расчет которых производится на основе таблиц движений и итогов, в момент обращения в базе данных.
(Более подробно данная тема рассматривалась в статье. Следует заметить, что указанная статья не дает полного описания механизма регистров, для более углубленного изучения данного вопроса следует использовать дополнительные материалы).
Расчет таблицы итогов будет зависеть от вида регистра 1С, структура имеет следующий вид:
3. Доступ к виртуальным таблицам 1С.
Посредством запроса 1С можно обратиться к движениям и к виртуальным таблицам 1С, доступ к итогам напрямую невозможен.
Использование таблицы движений является крайне нежелательной, т.к. она содержит максимальное количество записей, обработка которых будет сильно нагружать систему.
Если обратится к базе данных посредством 1С конструктора запроса, то таблицы будут выглядеть следующим образом.
При написании запроса следует ориентироваться на использование виртуальных таблиц 1Срассчитываемых на основе итогов. В этом случае запрос будет наиболее оптимальным.
Исходя из того что виртуальная таблица 1С является результатом расчета, то в первую очередь необходимо исключить расчет ненужных данных. Для этих целей существуют параметры.
4. Параметры виртуальной таблицы 1С остатков.
Период – Дата, либо момент времени на который необходимо получить остатки.
В качестве примера рассмотрим следующую таблицу движений и параметры виртуальной таблицы 1С:
Остатки рассчитываются на начало секунды!
Если к примеру мы укажем дату остатков 20.01.2019 12:00:00 то движения за период 20.01.2019 12:00:00 учтены не будут, и остаток будет равен 8.
Период возможно указать с точностью до секунды, возможно ситуация когда движения могут быть в пределах одной секунды, и если использовать дату в качестве периода можно получить некорректные данные.
Движения формируются документом, для того чтобы документы располагались в хронологическом порядке в пределах секунды, платформа 1С использует объект Момент времени.
Он представляет собой поле Дата+Ссылка
Момент времени создается автоматически, и изменить его в пределах одной секунды невозможно. Изменить хронологию документов можно, только переместив дату одного издокументов на следующую секунду.
Допустим, что 20.01.2019 12:00:00 был проведен еще один документ, таблица движений примет следующий вид:
На временной оси два последних документа будут расположены следующим образом
Для того чтобы получить остатки с учетом его движений, необходимо использовать объект Граница.
В зависимости от свойства Вид Границы, можно получить момент непосредственно перед или после позиции документа.
Условие – параметр позволяющий произвести отбор данных по измерениям регистра.
В нашем примере у регистра остатков только одно измерение «Номенклатура».
Условия можно описывать как простой конструкцией, например
Так и при помощи подзапроса
Номенклатура В (ВЫБРАТЬ
ВтНоменклатура КАК ВтНоменклатура)
При обращении к виртуальной таблице следует передавать в условия наиболее простые конструкции. Не рекомендуется использовать подзапросы и соединения в параметрах виртуальной таблицы, так как это приводит к медленной работе запроса.
Если все же подзапрос необходим, то необходимо соблюдать следующие условия:
· в подзапросе только одна таблица, нет соединений с другими таблицами.
· если в подзапросе таблица табличной части (например, Документ.Накладная.СписокТоваров), то не должно быть обращения к реквизитам таблицы-шапки (Накладная.Проведен).
· если в подзапросе таблица, у которой могут быть табличные части (например, Документ.Накладная), то не должно быть обращений к табличным частям (например, ГДЕ Документ.Накладная.СписокТоваров.Номенклатура = «1»).
· если в подзапросе временная таблица, то не должно быть условий (раздела ГДЕ).
· если в подзапросе постоянная таблица, то условие (раздел ГДЕ) может быть допустимо, только если условие выполняется для 80% (или более) случаев, отсутствие условия означает выполнение для 100% случаев
· если в подзапросе постоянная таблица, то в ограничениях доступа к данным (RLS) не должно содержатся подзапросов и соединений (допускаются только простые условия вида «ГДЕ Реквизит = Значение», «ГДЕ Истина»).
5. Основные параметры таблицы Оборотов.
Как видим в качестве периода здесь два параметра, это НачалоПериода и КонецПериода.
Обороты в отличие от остатков можно получить только за определенный период. К примеру, если нам требуется узнать остаток товаров, то мы говорим : «Сколько товаров осталось на сегодня на 12:00? а вчера на 18:00». В случае оборотов вопрос строится так: «Сколько товаров было продано за сегодня? А за этот месяц?».
НачалоПериода – в случае если указывается дата, то берется начало секунды; если параметр не указан, то берется начало секунды периода первого движения.
КонецПериода – в случае если указывается дата, то берется конец секунды; если параметр не указан, то берется конец секунды периода последнего движения.
Если оба основных параметра таблицы указать одинаковыми, то период будет равен 1 секунде.
В случае если в качестве параметра НачалоПериода или КонецПериода, берется МоментВремени документа, то движения самого документа будут входить в выбираемый период!
Для того чтобы получить момент непосредственно перед или после позиции документа, следует воспользоваться объектом Граница.
Следующим параметром таблицы оборотов является Периодичность таблицы.
Рассмотрим использование данного параметра на примере следующей таблицы движений:
Выберем данные из таблицы оборотов, в качестве периодичности выберем месяц.
Т.к. все движения формировались в одном месяце, то получим всего одну запись:
Изменим периодичность на секунду, два последних движения сформированы в одну секунду, поэтому они будут представлены одной записью:
Для того чтобы детализировать записи в пределах секунды, в качестве периодичности выберем Регистратор, в результате появится дополнительная колонка Регистратор:
Если в качестве периодичности выбрать авто то возможно привести данные к нескольким периодам:
Параметр условие аналогичен условию в виртуальной таблице остатков.
При использовании в запросах виртуальных таблиц, в первую очередь необходимо выбирать таблицы, расчет которых производится на основе таблиц итогов.
Для регистра с видом остатки это таблица остатки, для оборотного обороты.
Далее необходимо максимально точно описать условия виртуальных таблиц.
При соблюдении этих условий, возможно получить данные наиболее оптимальным способом.
Специалист компании ООО «Кодерлайн»
Александр Гармышев
Виртуальные таблицы регистра бухгалтерии
Выборка из регистра бухгалтерии и работа с виртуальными таблицами мало чем отличается от выборки из регистра накопления. Поэтому перед чтением данной статьи рекомендую ознакомиться со статьей про работу с виртуальными таблица регистра накопления. В данной статье подробно будет рассмотрена только та функциональность, которая есть только у регистра бухгалтерии.
Выборка из регистра бухгалтерии
Помимо выборки из основной таблицы регистра бухгалтерии в запросе можно выбрать данные из таблицы субконто.
Выборка значений субконто:
Виртуальные таблицы регистра бухгалтерии
У регистра бухгалтерии немного больше виртуальных таблиц, чем у регистра накопления:
При этом у регистра бухгалтерии без поддержки корреспонденции не будет виртуальной таблицы ОборотыДтКт.
ДвиженияССубконто
Виртуальная таблица ДвиженияССубконто содержит в себе поля как основной таблицы регистра бухгалтерии, так и таблицы значений субконто:
Данная таблица получается соединением основной таблицы и таблицы значений субконто. Из всех виртуальных таблиц только данная таблица может содержать неактивные записи (поле Активность = Ложь), но при этом в самой таблице есть поле Активность, поэтому неактивные записи можно будет исключить в запросе.
Таблица ДвиженияССубконто имеет следующие параметры:
Данный запрос выберет первые 10 записей за период между &НачДата и &КонДата, с отбором по счету дебета = &СчетДт, выборка будет отсортирована по полю Организация.
Все отборы, сортировку и ограничение количества записей можно указать не в параметрах виртуальной таблицы, а в тексте запроса. Преимущество использования параметров в том, что они будут использованы в процессе формирования виртуальной таблицы, что положительно скажется на производительности запроса.
Как правило данную таблицу используют для получения проводок вместе с субконто.
Виртуальная таблица остатки
Отличия от виртуальной таблицы регистра накопления
Виртуальная таблица остатков регистра бухгалтерии очень сильно похожа на виртуальную таблицу остатков регистра накопления, поэтому рассмотрим только основные отличия.
Во-первых помимо измерений регистра бухгалтерии в таблице остатков присутствуют поля Счет и субконто. Количество субконто равно максимальному количеству субконто в плане счетов.
В нашем примере в настройках плана счетов было указано максимум два субконто, поэтому в таблице есть два поля для субконто: Субконто1 и Субконто2. Если на каком-то счете присутствует только одно субконто, то в поле Субконто2 будет NULL.
Во-вторых, для каждого ресурса создается по пять полей:
В поле Остаток будет остаток, как он хранится в таблице итогов. Дебетовый остаток будет больше нуля, кредитовый меньше.
Поля ОстатокДт и ОстатокКт рассчитываются по разному, в зависимости от вида счета:
В данном примере счете 41.01 активный, счет 60 — пассивный, а счета 76.01 и 76.02 — активно-пассивные.
Даже если у активного счета остаток будет меньше нуля, то он будет отображаться как дебетовый остаток, но с минусом. Аналогично с пассивным счетом: если остаток больше нуля, то он будет по кредиту, но с минусом:
Признак Балансовый у измерений и ресурсов никак не влияет на количество полей.
В-третьих, есть отличие при формировании виртуальной таблицы. Если в таблице итогов последние рассчитанные итоги были за июль 2021 года (то есть в таблице итогов есть запись с датой 01.07.2021 с остатком на конец июня и оборотом за июль), то при получении остатка на 20 июля для регистра накопления будет получен остаток из текущих итогов и по записям таблицы движений за период 20.07.2021 — 01.11.3999 будет досчитан остаток на 20 июля.
У регистра бухгалтерии в одной строке с остатком хранится оборот за текущий месяц, поэтому к остатку на конец июня будет прибавлен дебетовый оборот за июль, вычтен кредитовый оборот за июль, а потом от этого остатка по движениям за 20.07.2021 — 31.07.2021 будет досчитан остаток на 20 июля 2021.
В-четвертых, есть два новых параметра: УсловиеСчета и Субконто:
УсловиеСчета позволяет задать отбор по счетам учета, можно указать несколько счетов.
В параметре Условие можно задать отбор по измерениям и субконто, данный параметр аналогичен параметру для виртуальной таблицы регистра накопления.
Регистры накопления. Виртуальные таблицы. Часть №1: Обороты
О регистрах накопления
В нескольких статьях представлены основные сведения о внутреннем устройстве регистров накопления, о SQL-запросах платформы при работе с ними и их изменение в зависимости от настроек регистра. Подробно описана работа платформы с разными типами регистров (остатков и накопления), а также принцип действия агрегатов.
Материалы созданы во времена платформы 8.2, поэтому некоторые моменты могут быть уже не актуальными, но основные принципы работы остались неизменными.
Больше года назад сайт был закрыт. Некоторые из его материалов будут реанимированы на Инфостарт.
Конкретно в этой статье речь идет о виртуальной таблице «Обороты» регистров накопления в базе данных. Все примеры из публикации Вы можете найти на GitHub.
Виртуальные таблицы
В статье «Регистры накопления. Структура хранения в базе данных» мы рассматривали таблицы, которые использует платформа для хранения движений в регистрах накопления, а также его итоговых оборотов или остатков в зависимости от вида регистра («Остатки» или «Обороты»). Также были подробно рассмотрены действия платформы с таблицами остатков и оборотов при записи движений в регистр.
Сегодня в статье проанализируем SQL-запросы, формируемые платформой, при обращении к виртуальным таблицам регистра. Напомню, что у регистров накопления существует всего три виртуальных таблицы:
Как мы видим, кроме физической таблицы движений, для которой в базе данных создается отдельная таблица, также имеются виртуальные таблицы. Всего их три:
Последние две становятся доступными только если вид регистра установлен как «Остатки».
Главное отличие виртуальных таблиц от физических: виртуальные таблицы не хранятся непосредственно в базе данных. При выборке данных из виртуальной таблицы платформа формирует некоторый запрос в зависимости от переданных параметров, который может получать записи из двух и более таблиц для формирования конечного результата.
Далее в статье проанализируем SQL-запросы платформы 1С:Предприятие 8.2 при обращении к виртуальной таблицам. При этом будем выполнять запросы при различных комбинациях параметров.
Сторона СУБД
Начнем анализ с виртуальной таблицы «Обороты», так как она присутствует вне зависимости от вида регистра.
Виртуальная таблица «Обороты»
Виртуальная таблица «Обороты» есть, как у регистра накопления с видом «Обороты», так и с видом «Остатки». Рассмотрим оба варианта. Начнем с последнего.
Вид регистра «Остатки»
Посмотрим состав полей таблицы оборотов на примере регистра «ОстаткиНоменклатуры».
В нем содержатся поля каждого из измерений, а также поля «Приход», «Расход» и «Оборот» для каждого из ресурсов в регистре. В нашем случае у нас два измерения («Номенклатура» и «Склад»), а также три поля «КоличествоПриход», «КоличествоРасход» и «КоличестоОборот».
В зависимости от установленного параметра «Периодичность» в состав доступных полей вирт. таблицы будут добавляться соответствующие периоды («ПериодДень», «ПериодМесяц» и т.д.).
Теперь напишем простой запрос для получения оборотов по номенклатуре за период. В параметрах виртуальной таблицы установим поля «НачалоПериода» и «КонецПериода», а в условия добавим отбор по складу «Склад №1». При выполнении запроса платформа сформирует два SQL-запроса к базе данных. Первый запрос получает настройки регистра накопления:
Используя эти настройки, платформа формирует SQL-запрос непосредственно на получение оборотов. Вот так выглядит SQL-запрос платформы для получения оборотов:
Старался подробно разобрать весь запрос. Если будут непонятные моменты, то прошу в комментарии. Исходный текст запроса на языке 1С:Предприятия выглядит следующим образом:
В случае, если для виртуальной таблицы также устанавливается параметр «Периодичность», например, в значение «Месяц», то SQL-запрос немного видоизменится:
Если в виртуальной таблице периодичность установлена «Авто», то в SQL-запросе будут содержаться поля периода для каждой из получаемой в запросе периодичности («День», «Месяц», «Год» и т.д.). Причина, по которой платформа хранит значения периода с увеличением части даты «Год» на 2000 лет мне не известна. Если кто из читателей подскажет, буду благодарен.
Мы с Вами рассмотрели SQL-запросы платформы при работе с виртуальной таблицей «Обороты» регистра накопления с видом «Остатки». Как мы видим, виртуальная таблица «Обороты» в этом случае берет данные из таблицы движений регистра. Даже, если обороты в запросе получаются за несколько месяцев, необходимые данные будут также формироваться по таблице движений без использования каких-либо сохраненных ранее итогов. И это понятно, регистр накопления с видом «Остатки» предназначен для учета остатков, а не оборотов. Далее мы увидим, почему для решения задач учета оборотов лучше использовать соответствующий вид регистра накопления.
Вид регистра «Обороты»
Виртуальная таблица «Обороты» включает в себя поля для каждого из измерений регистра, а также по одному полю на каждый ресурс. Для оборотного регистра «ДвиженияНоменклатуры» из тестовой конфигурации состав полей таблицы следующий:
Как и в предыдущем случае, в зависимости от параметра «Периодичность» в составе доступных полей появятся соответствующие периоды.
И так, выполним несколько запросов к таблице «Обороты» и проанализируем SQL-запросы платформы. Первый запрос на языке запросов платформы:
Первым делом платформа 1С:Предприятие получит настройки регистра накопления, к которому выполняется запрос. Запрос будет идентичный рассматриваемому ранее примеру, пойдем дальше. Сформированный платформой SQL-запрос тогда будет такой:
К комментариям в приведенном тексте добавлю, что вне зависимости от значений параметров «НачалоПериода» и «КонецПериода» запрос пытается получить данные и из итоговых таблиц, и из таблицы движений регистра.
Если в запросе на языке платформы мы добавим использование параметра «Периодичность» (например, поставим значение «Месяц»), то SQL-запрос платформы изменится аналогично рассмотренному примеру для регистра накопления с видом остатки. Будут добавлены поля выбранных периодов («ПериодДень», «ПериодМесяц» и т.д.) в секции запроса «SELECT» и «GROUP BY». Для нашего примера это месяц. Поля и группировки будут добавлены для всех вложенных запросов и, конечно, содержаться в результатирующей выборке. В нашем примере, выражения в поле запроса для получения периода будет таким:
Принцип получения значений периода был описан выше для регистра с видом «Обороты».
Заключение
Если представить действия SQL-запросов схематично, то выглядеть это будет примерно так:
По схеме видно, что наиболее оптимальным образом платформа работает с виртуальной таблицей «Обороты» для регистра накопления с видом «Обороты», поскольку использует рассчитанных итоги по оборотам в разрезе месяцев. И лишь в тех случаях, когда рассчитанных итогов для периода нет, тогда использует таблицу движений. Для регистра вида «Остатки» всегда используется таблица движений вне зависимости от настроек хранения итоговых записей. Именно поэтому следует внимательно отнестись к настройке вида регистра накопления при проектировании структуры метаданных конфигурации.
Интересен тот факт, что если для регистра накопления отключить использование итогов, то тогда запрос к виртуальной таблице «Остатки» станет невозможным. Будет появляться такая ошибка:
Причем, данная ошибка будет не смотря на то, что виртуальная таблица «Обороты» для регистра накопления с видом «Остатки» использует только таблицу движений.
Что дальше
В следующих статьях будет рассмотрена работа платформы с виртуальными таблицами «Остатки» и «Остатки и обороты». Также коснемся работы агрегатов регистра накопления.