В практике разработки на платформе 1С:Предприятие часто возникает классическая задача: сопоставить записи из одной таблицы с наиболее актуальными данными из другой таблицы на определенную дату. Эту задачу также называют получением «среза последних на каждую дату».
Типичный пример: у нас есть таблица с начислениями сотрудников (где указаны даты начислений) и таблица с историей изменения коэффициентов или ставок сотрудников. Нам нужно определить, какой коэффициент действовал для каждого сотрудника на момент каждого начисления. Давайте подробно разберем, как решить эту задачу правильно, проанализируем частые ошибки разработчиков и рассмотрим альтернативные варианты решения.
Давайте посмотрим на пример исходных данных, с которыми столкнулся автор темы на форуме.
Таблица 1 (Начисления сотрудников):
Таблица 2 (История изменения коэффициентов):
Если мы попытаемся сделать простое ЛЕВОЕ СОЕДИНЕНИЕ по условию Таблица1.Дата >= Таблица2.Дата, то СУБД сопоставит строке от 28.02.2018 сразу обе записи из Таблицы 2, так как обе даты (01.01.2018 и 01.02.2018) меньше, чем 28.02.2018. В результате запрос «задвоит» строки, и мы получим лишние данные:
Чтобы избежать этой проблемы, нам нужно соединять таблицы не просто по условию «меньше или равно», а находить максимальную (самую свежую) дату в Таблице 2 для каждого события из Таблицы 1.
В Сообщении 6 на форуме был предложен вариант решения через временную таблицу, однако в нем содержатся две серьезные логические ошибки, из-за которых код либо вернет неверные данные, либо вообще не будет работать в реальной базе данных.
Ошибка №1: Неверное направление знака сравнения дат
В тексте запроса написано условие: ПО Т1.Сотр = Т2.Сотр И Т1.Дата <= Т2.Период. Если нам нужен коэффициент, действующий на момент проведения документа или события, то дата установки коэффициента должна быть меньше или равна дате события. С условием <= запрос будет выбирать будущие коэффициенты, которые еще не вступили в силу на указанную дату.
Ошибка №2: Потеря аналитики дат при группировке (схлопывание данных)
В запросе используется конструкция: СГРУППИРОВАТЬ ПО Т1.Сотр. Давайте проанализируем, к чему это приведет на наших тестовых данных. Если мы сгруппируем временную таблицу только по сотруднику, то для Иванова система найдет одну-единственную максимальную дату коэффициента на всю историю — 01.02.2018. При последующем соединении обе записи Иванова (и за январь, и за февраль) получат один и тот же коэффициент Коэф2. Таким образом, для записи от 31.01.2018 будет ошибочно установлен коэффициент, который начал действовать только с 01.02.2018.
Вывод: Группировку во временной таблице необходимо обязательно выполнять в разрезе двух полей: уникального идентификатора сотрудника (или другой аналитики) и конкретной даты из Таблицы 1.
Для гарантированно точного и быстрого получения результатов мы применим проверенный двухэтапный алгоритм с использованием временных таблиц.
Шаг 1. Создаем временную таблицу для поиска актуальных дат
Мы соединяем Таблицу 1 с Таблицей 2 по сотруднику и по условию, что дата в Таблице 2 меньше либо равна дате из Таблицы 1. С помощью функции МАКСИМУМ мы определяем точную дату последней модификации коэффициента для каждого конкретного события:
ВЫБРАТЬ
Т1.Сотр КАК Сотрудник,
Т1.Дата КАК ДатаСобытия,
МАКСИМУМ(Т2.Период) КАК АктуальныйПериод
ПОМЕСТИТЬ ВТ_АктуальныеДаты
ИЗ
Таб1 КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Таб2 КАК Т2
ПО Т1.Сотр = Т2.Сотр
И Т2.Период <= Т1.Дата
СГРУППИРОВАТЬ ПО
Т1.Сотр,
Т1.Дата
;
Шаг 2. Получаем итоговые данные с коэффициентом
Теперь мы соединяем нашу исходную Таблицу 1 с временной таблицей ВТ_АктуальныеДаты, а затем левым соединением подключаем Таблицу 2, чтобы вытащить сам коэффициент по найденной точной паре «Сотрудник + Актуальная дата»:
ВЫБРАТЬ
Т1.Сотр КАК Сотрудник,
Т1.Дата КАК ДатаСобытия,
ЕСТЬNULL(Т2.Коэф, 0) КАК Коэффициент
ИЗ
Таб1 КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_АктуальныеДаты КАК ВТ_Даты
ПО Т1.Сотр = ВТ_Даты.Сотрудник
И Т1.Дата = ВТ_Даты.ДатаСобытия
ЛЕВОЕ СОЕДИНЕНИЕ Таб2 КАК Т2
ПО ВТ_Даты.Сотрудник = Т2.Сотр
И ВТ_Даты.АктуальныйПериод = Т2.Период
Данный подход является наиболее производительным в СУБД, исключает появление дублей и корректно обрабатывает пустые значения (если для сотрудника на какую-то раннюю дату еще вообще не было заведено коэффициентов).
В ходе обсуждения участники пытались использовать функцию КОНЕЦПЕРИОДА для округления дат до месяца, но столкнулись с ошибкой компилятора запросов. Давайте разберем причины этой ошибки:
КОНЕЦПЕРИОДА должен выступать предопределенный тип периода (например, ДЕНЬ, МЕСЯЦ, КВАРТАЛ, ГОД). Этот параметр пишется прямо в коде запроса без кавычек. Ошибка часто возникает, если разработчик передает его как строковый литерал (например, "МЕСЯЦ" или 'МЕСЯЦ').Месяц или Период имеет строковый тип (например, "Январь 2018") или числовой тип, то функция КОНЕЦПЕРИОДА вызовет ошибку выполнения запроса. Данные должны быть предварительно приведены к типу Дата.Если ваша задача решается в рамках построения отчета на Системе компоновки данных (СКД), то писать сложные запросы с временными таблицами вовсе не обязательно. Платформа 1С умеет автоматически решать задачу получения среза последних на каждую дату с помощью механизмов связей наборов данных.
Давайте разберем, как это настроить по шагам:
&ПериодСвязи. Пример текста запроса для Набора 2:
ВЫБРАТЬ
РегистрКоэффициентыСрезПоследних.Сотрудник,
РегистрКоэффициентыСрезПоследних.Коэффициент
ИЗ
РегистрСведений.РегистрКоэффициенты.СрезПоследних(&ПериодСвязи, Сотрудник = &Сотрудник) КАК РегистрКоэффициентыСрезПоследних
Сотрудник = СотрудникПериодСвязи, Выражение источника: Дата (поле даты из первого набора).При такой схеме компоновщик данных самостоятельно и оптимальным образом передаст дату из каждой строки первого набора в параметры получения среза последних второго набора данных. Это убережет базу от лишних перегрузок и избавит вас от написания громоздкого кода.
В классических СУБД (MS SQL Server, PostgreSQL, Oracle) подобные задачи элегантно решаются в один проход с помощью оконных функций разметки строк, таких как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC).
Несмотря на то, что начиная с версии платформы 8.3.20 фирма «1С» значительно расширила возможности языка запросов (добавив новые математические, строковые функции и расширенную работу с типами), полноценная поддержка оконных функций в языке запросов 1С на данный момент отсутствует. Именно поэтому использование классического метода с временными таблицами и двойным левым соединением по сей день остается стандартом разработки в среде 1С, обеспечивающим максимальную производительность системы.