Приветствуем вас! Сегодня мы разберем одну из самых классических, но от этого не менее сложных задач в разработке на платформе 1С — получение среза последних на каждую дату документа или любой другой динамической таблицы. Эта задача регулярно встречается при расчете себестоимости, получении цен номенклатуры на момент оприходования, курсов валют на дату совершения хозяйственной операции и во многих других сценариях.
Основная сложность заключается в том, что стандартная виртуальная таблица СрезПоследних периодического регистра сведений умеет принимать на вход только фиксированный параметр даты (например, &ДатаКурса). Если у нас в одной таблице запроса есть тысячи документов за разные даты, мы не можем "построчно" передать дату каждого документа внутрь этой виртуальной таблицы. Попытка сделать левое соединение со срезом последних без указания даты вернет данные только на текущий момент (или на дату переданного параметра), что приведет к грубейшим ошибкам в расчетах.
В этой статье мы подробно рассмотрим, как обойти это ограничение, разберем три рабочих метода решения проблемы, проанализируем вопросы производительности и научимся оптимизировать "монструозные" запросы.
Представим типичную ситуацию: у нас есть готовый, отлаженный и сложный запрос для универсального отчета. В нем соединяются регистры накопления, справочники и другие источники. В какой-то момент бизнес требует добавить в отчет колонку с ценой номенклатуры из периодического регистра сведений ЦеныНоменклатуры, причем цена должна быть актуальна строго на момент проведения конкретного документа оприходования (ДокументОприходования.Дата).
Если мы попытаемся сделать прямое левое соединение со срезом последних:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныПрайса
ПО ИсточникДанных.Номенклатура = ЦеныПрайса.Номенклатура
Мы получим срез на текущую дату. Если передать параметр:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ) КАК ЦеныПрайса
ПО ...
То мы получим цены на дату окончания отчета, что тоже неверно, ведь документ мог быть проведен три месяца назад по совершенно другой цене. Нам нужно получить цену "в прошлом" для каждой строки индивидуально. Ниже представлены проверенные и эффективные способы решения этой задачи.
Этот метод основан на работе с физической таблицей регистра сведений. Вместо использования виртуальной таблицы мы будем самостоятельно рассчитывать срез последних с помощью поиска максимальной даты записи регистра, которая меньше или равна дате нашего документа.
Разберем этот алгоритм по шагам. Мы создадим три временные таблицы для изоляции логики и обеспечения максимальной производительности СУБД.
Шаг 1. Подготовка основной таблицы данных
Сначала мы выгружаем во временную таблицу только те поля, которые нам необходимы для связи. Это позволит СУБД работать с минимальным объемом данных и быстро проиндексировать таблицу.
ВЫБРАТЬ
ПартииТоваров.Номенклатура КАК Номенклатура,
ПартииТоваров.ДокументОприходования КАК ДокументОприходования,
ВЫРАЗИТЬ(ПартииТоваров.ДокументОприходования КАК Документ.ПоступлениеТоваровУслуг).Дата КАК ДатаДокумента
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваров
ГДЕ
ПартииТоваров.Склад = &Склад
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ДатаДокумента
;
Шаг 2. Определение максимального периода (даты установки цены)
Теперь мы соединяем наши исходные данные с физической таблицей регистра сведений ЦеныНоменклатуры. Условие связи: период действия цены должен быть меньше или равен дате нашего документа оприходования. С помощью группировки и функции МАКСИМУМ мы находим точную дату последней установки цены для каждой строки.
ВЫБРАТЬ
ВТ_ИсходныеДанные.Номенклатура КАК Номенклатура,
ВТ_ИсходныеДанные.ДатаДокумента КАК ДатаДокумента,
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК ПериодМаксимум
ПОМЕСТИТЬ ВТ_МаксимумыПериодов
ИЗ
ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_ИсходныеДанные.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_ИсходныеДанные.ДатаДокумента >= ЦеныНоменклатуры.Period
СГРУППИРОВАТЬ ПО
ВТ_ИсходныеДанные.Номенклатура,
ВТ_ИсходныеДанные.ДатаДокумента
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ПериодМаксимум
;
Шаг 3. Получение значений ресурсов (цены) на найденную дату
Зная точную дату изменения цены (ПериодМаксимум), мы снова обращаемся к физической таблице регистра сведений, чтобы получить саму цену. Соединение происходит по номенклатуре и по точному совпадению периода.
ВЫБРАТЬ
ВТ_МаксимумыПериодов.Номенклатура КАК Номенклатура,
ВТ_МаксимумыПериодов.ДатаДокумента КАК ДатаДокумента,
ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныНаДату
ИЗ
ВТ_МаксимумыПериодов КАК ВТ_МаксимумыПериодов
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_МаксимумыПериодов.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_МаксимумыПериодов.ПериодМаксимум = ЦеныНоменклатуры.Период
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ДатаДокумента
;
Шаг 4. Финальное объединение с основным запросом
Теперь мы можем легко связать нашу готовую временную таблицу ВТ_ЦеныНаДату с исходным сложным запросом с помощью простого левого соединения. Это полностью решает проблему производительности и дает точный результат.
ВЫБРАТЬ
ВТ_ИсходныеДанные.Номенклатура,
ВТ_ИсходныеДанные.ДокументОприходования,
ЕСТЬNULL(ВТ_ЦеныНаДату.Цена, 0) КАК Цена
ИЗ
ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНаДату КАК ВТ_ЦеныНаДату
ПО ВТ_ИсходныеДанные.Номенклатура = ВТ_ЦеныНаДату.Номенклатура
И ВТ_ИсходныеДанные.ДатаДокумента = ВТ_ЦеныНаДату.ДатаДокумента
Рассмотрим альтернативный способ. Он особенно эффективен, когда записи в регистре сведений меняются не слишком часто, а документов в базе очень много. Идея заключается в том, чтобы превратить точечные записи регистра сведений в непрерывные временные интервалы, в течение которых действует каждая цена.
Мы строим таблицу интервалов вида: Номенклатура | Цена | ДатаНачала | ДатаОкончания.
ВЫБРАТЬ
Цены1.Номенклатура КАК Номенклатура,
Цены1.Период КАК ДатаНачала,
Цены1.Цена КАК Цена,
МИНИМУМ(ЕСТЬNULL(Цены2.Период, ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59))) КАК ДатаОкончания
ПОМЕСТИТЬ ВТ_ИнтервалыЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены2
ПО Цены1.Номенклатура = Цены2.Номенклатура
И Цены1.Период < Цены2.Период
СГРУППИРОВАТЬ ПО
Цены1.Номенклатура,
Цены1.Период,
Цены1.Цена
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ДатаНачала,
ДатаОкончания
;
После формирования интервалов соединение с документами становится тривиальным и очень быстрым для оптимизатора СУБД. Нам достаточно проверить, попадает ли дата документа в интервал действия цены:
ВЫБРАТЬ
Документы.Номенклатура,
Документы.Ссылка КАК Документ,
ЕСТЬNULL(ВТ_ИнтервалыЦен.Цена, 0) КАК Цена
ИЗ
Документы КАК Документы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИнтервалыЦен КАК ВТ_ИнтервалыЦен
ПО Документы.Номенклатура = ВТ_ИнтервалыЦен.Номенклатура
И Документы.Дата МЕЖДУ ВТ_ИнтервалыЦен.ДатаНачала И ВТ_ИнтервалыЦен.ДатаОкончания
Этот метод красив, но будьте аккуратны: на очень больших таблицах регистра сведений левое соединение "само на себя" (Цены1 и Цены2) для расчета даты окончания может занять значительное время. Используйте его, если у вас установлены жесткие фильтры по номенклатуре.
Если ваш монструозный запрос разрабатывается в рамках Системы компоновки данных (СКД), то платформа предоставляет элегантный встроенный механизм получения среза на каждую дату без написания сложных конструкций во временных таблицах.
Проанализируем этот функционал. Мы можем разделить логику на два отдельных набора данных:
ВЫБРАТЬ
Цены.Номенклатура,
Цены.Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодСреза, Номенклатура = &Номенклатура) КАК Цены
Далее мы переходим на вкладку "Связи наборов данных" и настраиваем соединение:
ПериодСреза, а в качестве его значения указываем поле ДатаОприходования из первого набора данных.При выполнении отчета СКД сама в цикле передаст дату каждой строки первого набора в качестве параметра для среза последних второго набора. Однако помните о важном ограничении: этот способ отлично работает на небольших и средних объемах данных, но при выводе десятков тысяч строк производительность может упасть из-за большого количества точечных запросов к базе данных.
При написании подобных запросов очень легко совершить ошибки, которые "уронят" производительность всей системы. Давайте разберем их подробнее.
Опасность разыменования полей (обращения через точку)
В тексте обсуждения авторы не зря обращают внимание на конструкцию ДокументОприходования.Дата. Если поле ДокументОприходования имеет составной тип данных (например, это может быть и "Поступление товаров", и "Оприходование товаров", и "Авансовый отчет"), обращение к реквизиту Дата через точку приведет к тому, что СУБД выполнит неявное левое соединение со всеми физическими таблицами этих документов.
Это порождает огромный SQL-код с массой условий CASE WHEN, что парализует оптимизатор СУБД. Всегда используйте функцию ВЫРАЗИТЬ() для приведения поля к конкретному типу документа перед получением его даты, либо выносите получение дат документов в отдельную временную таблицу на самом первом шаге, как мы сделали в Решении 1.
Особенности работы на PostgreSQL
Если вы работаете под управлением СУБД PostgreSQL, помните, что ее оптимизатор очень чувствителен к сложным вложенным запросам и соединениям физических таблиц без жесткой изоляции. Использование временных таблиц (метод 1) для PostgreSQL является обязательным стандартом. Это позволяет принудительно материализовать промежуточные выборки, рассчитать по ним точную статистику распределения данных и построить оптимальный план выполнения запроса.
Индексация временных таблиц
Никогда не забывайте индексировать временные таблицы по полям связи (таким как Номенклатура, ДатаДокумента). Отсутствие индексов на таблицах объемом более нескольких тысяч строк приведет к сканированию всей таблицы (Table Scan) при каждом соединении, что существенно замедлит выполнение отчета.
Мы детально разобрали, как решить проблему получения среза последних на каждую дату в запросе. Несмотря на то, что штатная виртуальная таблица СрезПоследних не может быть напрямую соединена по динамической дате, использование временных таблиц с группировкой по максимуму периода или построение интервалов дат позволяют решить эту задачу элегантно, быстро и с гарантированно высокой производительностью СУБД. Выбирайте наиболее подходящий для вашей архитектуры метод и успешной вам разработки!