При разработке отчетов и механизмов расчета в системе 1С:Предприятие программисты регулярно сталкиваются с необходимостью сопоставить ежедневные данные (например, фактически отработанные часы, продажи или перемещения) с периодическими сведениями, у которых известна только дата изменения (начисления, цены, ответственные менеджеры). Типичный пример — определение того, какой тариф или оклад действовал у сотрудника в конкретный рабочий день, если история начислений хранится в виде записей с датами окончания или начала действия.
В этой статье мы подробно разберем, как решить эту задачу с помощью языка запросов 1С. Мы рассмотрим классический и наиболее надежный метод с использованием временных таблиц и группировок, проанализируем альтернативный подход с формированием интервалов, а также изучим современный и высокопроизводительный способ решения через оконные функции, доступные в новых версиях платформы 1С.
Представим ситуацию, которая часто возникает при расчете переработок или анализе эффективности персонала. У нас есть две таблицы.
Первая таблица — ЧасыРаботы. В ней фиксируется количество отработанных часов по дням:
Вторая таблица — Начисления. Здесь хранится история изменения плановых начислений сотрудника. Для каждой записи указана только дата окончания ее действия (для последнего актуального начисления устанавливается максимальная дата, например, 31.12.3999):
Наша цель — получить результирующую таблицу, где для каждого рабочего дня будет указано начисление, действовавшее на эту дату. То есть для 1, 2 и 3 января должен определиться «Оклад», а для 4 и 5 января — «Тариф».
Сложность заключается в том, что прямое левое соединение по равенству дат здесь использовать невозможно, так как даты в таблицах не совпадают. Использование условий сравнения наподобие «больше или равно» в простом соединении приведет к дублированию строк, поскольку для одной рабочей даты найдется сразу несколько подходящих записей из таблицы начислений. Нам необходимо выбрать именно ту запись, которая является ближайшей (актуальной) на конкретный день.
Давайте пошагово разберем классический алгоритм решения этой задачи, который работает на любых версиях платформы 1С v8. Этот метод основан на двухэтапном запросе с использованием временной таблицы.
Суть метода заключается в следующем. Сначала мы сопоставляем каждую рабочую дату со всеми возможными датами окончания начислений, которые больше или равны этой рабочей дате. Затем с помощью группировки мы находим самую первую (минимальную) из этих дат окончания. Это и будет дата актуальности начисления для данного дня. На втором этапе мы соединяем полученный результат обратно с таблицей начислений по этой найденной дате.
Рассмотрим подробнее логику выбора функции группировки. Почему именно МИНИМУМ? Давайте проанализируем ситуацию для рабочей даты 02.01.2019:
РабочаяДата <= ДатаОкончания соответствуют обе записи начислений: и «Оклад» (окончание 03.01.2019), и «Тариф» (окончание 31.12.3999).Для рабочей даты 04.01.2019 запись с «Окладом» уже не подходит (так как 04.01.2019 больше, чем 03.01.2019). Единственным подходящим вариантом остается «Тариф» с окончанием 31.12.3999. Минимальное значение из одного этого варианта — 31.12.3999. Логика работает безупречно.
Теперь напишем готовый текст запроса, реализующий этот алгоритм:
ВЫБРАТЬ
ЧасыРаботы.Сотрудник КАК Сотрудник,
ЧасыРаботы.Дата КАК Дата,
ЧасыРаботы.Часы КАК Часы,
МИНИМУМ(Начисления.ДатаОкончания) КАК ДатаАктуальности
ПОМЕСТИТЬ ВТ_РабочиеДниСДатойАктуальности
ИЗ
ВТ_ЧасыРаботы КАК ЧасыРаботы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Начисления КАК Начисления
ПО ЧасыРаботы.Сотрудник = Начисления.Сотрудник
И ЧасыРаботы.Дата <= Начисления.ДатаОкончания
СГРУППИРОВАТЬ ПО
ЧасыРаботы.Sотрудник,
ЧасыРаботы.Дата,
ЧасыРаботы.Часы
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
РабочиеДни.Сотрудник КАК Сотрудник,
РабочиеДни.Дата КАК Дата,
РабочиеДни.Часы КАК Часы,
ЕСТЬNULL(Начисления.Начисление, "Начисление не назначено") КАК Начисление
ИЗ
ВТ_РабочиеДниСДатойАктуальности КАК РабочиеДни
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Начисления КАК Начисления
ПО РабочиеДни.Сотрудник = Начисления.Сотрудник
И РабочиеДни.ДатаАктуальности = Начисления.ДатаОкончания
Давайте разберем по шагам, что происходит в этом запросе:
ЧасыРаботы и соединяем ее левым соединением с таблицей Начисления по сотруднику и условию ЧасыРаботы.Дата <= Начисления.ДатаОкончания.МИНИМУМ(Начисления.ДатаОкончания). Результат сохраняем во временную таблицу ВТ_РабочиеДниСДатойАктуальности.Начисления. На этот раз условие связи строгое: совпадение сотрудника и точное равенство рассчитанной ДатаАктуальности и ДатаОкончания. Это позволяет однозначно вытащить наименование начисления для каждого дня.ЕСТЬNULL, чтобы корректно обработать ситуации, когда на какую-то дату у сотрудника вообще не было назначено никаких плановых начислений.Если вы работаете на современной версии платформы «1С:Предприятие» (начиная с версии 8.3.21), у вас есть возможность использовать мощный инструмент — оконные функции. Этот подход работает гораздо быстрее на уровне СУБД, так как избавляет базу данных от необходимости выполнять повторное соединение таблиц и использовать временные таблицы для промежуточного хранения данных.
Мы можем использовать оконную функцию ROW_NUMBER() (номер строки в окне). Идея состоит в том, чтобы сопоставить рабочие даты с начислениями по условию «меньше или равно», пронумеровать полученные строки для каждого рабочего дня в порядке возрастания даты окончания начисления и затем отобрать только строки с номером 1.
Давайте посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
СвязанныеДанные.Сотрудник КАК Сотрудник,
СвязанныеДанные.Дата КАК Дата,
СвязанныеДанные.Часы КАК Часы,
СвязанныеДанные.Начисление КАК Начисление
ИЗ
(
ВТ_ЧасыРаботы КАК ЧасыРаботы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Начисления КАК Начисления
ПО ЧасыРаботы.Сотрудник = Начисления.Сотрудник
И ЧасыРаботы.Дата <= Начисления.ДатаОкончания
) КАК СвязанныеДанные
// Здесь мы применяем оконную функцию для нумерации записей внутри группы
// для каждого сотрудника и каждой конкретной рабочей даты
АВТОНОМНЫЙ_НОМЕР_СТРОКИ() КАК НомерСтроки
ПОЛУЧИТЬ ИЗ СвязанныеДанные
РАСПРЕДЕЛИТЬ ПО СвязанныеДанные.Сотрудник, СвязанныеДанные.Дата
УПОРЯДОЧИТЬ ПО СвязанныеДанные.ДатаОкончания ВОЗР
ГДЕ
НомерСтроки = 1
Использование оконных функций позволяет СУБД построить оптимальный план выполнения запроса. Вместо записи промежуточных данных во временные таблицы базы данных tempdb и последующего повторного сканирования, СУБД выполняет вычисления «на лету» в рамках одного прохода по индексам. Это дает колоссальный прирост производительности на больших объемах данных.
Еще один профессиональный способ решения подобных задач — это предварительное преобразование таблицы начислений. Если в исходной таблице у нас есть только дата окончания, мы можем программно или в запросе рассчитать для каждой записи «Дату начала».
Дата начала текущего начисления — это дата окончания предыдущего начисления плюс 1 секунда (или 1 день, в зависимости от периодичности сведений). Если мы преобразуем таблицу начислений к такому виду, то итоговое соединение станет тривиальным и максимально быстрым:
ВЫБРАТЬ
ЧасыРаботы.Сотрудник КАК Сотрудник,
ЧасыРаботы.Дата КАК Дата,
ЧасыРаботы.Часы КАК Часы,
Начисления.Начисление КАК Начисление
ИЗ
ВТ_ЧасыРаботы КАК ЧасыРаботы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НачисленияСИнтервалами КАК Начисления
ПО ЧасыРаботы.Сотрудник = Начисления.Сотрудник
И ЧасыРаботы.Дата МЕЖДУ Начисления.ДатаНачала И Начисления.ДатаОкончания
Этот метод считается наиболее красивым с точки зрения реляционной теории баз данных. Условие МЕЖДУ (в терминах SQL — BETWEEN) прекрасно оптимизируется оптимизаторами СУБД, особенно если по полям дат построены соответствующие индексы. Если вы проектируете собственные регистры сведений для хранения периодических данных, настоятельно рекомендуется хранить именно интервалы (и дату начала, и дату окончания), а не ограничиваться одной границей.
При реализации ручного среза последних на реальных базах данных с сотнями тысяч записей необходимо учитывать важные нюансы оптимизации производительности:
ВТ_Начисления должна иметь индексы по полям Сотрудник и ДатаОкончания. Для этого в конструкторе запроса на вкладке «Индексы» нужно указать эти поля либо написать в тексте запроса: ИНДЕКСИРОВАТЬ ПО Сотрудник, ДатаОкончания.СрезПоследних возвращает данные только на одну конкретную дату. Если вам нужно получить срез на каждый день периода, использование виртуальной таблицы в цикле или соединение с ней будет крайне неоптимальным. В таких случаях ручной срез последних по описанной выше методике является единственно верным решением.Мы подробно разобрали три различных подхода к решению задачи соединения таблиц по меньшей или ближайшей дате в 1С. Выбор конкретного метода зависит от версии используемой платформы, архитектуры ваших метаданных и требований к производительности системы. Применяйте полученные знания на практике для создания быстрых и надежных отчетов!