При работе с регистрами сведений, особенно такими как История использования штатного расписания в «ЗУП 3.1», часто возникает необходимость проанализировать не только текущую запись, но и те, что стоят непосредственно перед ней или после неё. В стандартном SQL для этого существуют оконные функции LEAD (следующая) и LAG (предыдущая). В системе «1С:Предприятие» долгое время прямой поддержки этих функций не было, что вынуждало разработчиков искать обходные пути. Рассмотрим подробнее, как реализовать этот функционал различными методами в зависимости от версии платформы и требований к производительности.
Этот метод считается базовым и работает на всех версиях платформы 1С. Его суть заключается в том, что мы сначала определяем даты соседних записей, а затем по этим датам получаем сами данные. Разберем этот процесс по шагам.
Проанализируем ситуацию: нам нужно для каждой записи найти максимальную дату среди всех дат, которые меньше текущей (это будет дата предыдущей записи), и минимальную дату среди всех, что больше текущей (дата следующей записи).
Пример реализации в одном запросе:
ВЫБРАТЬ
ТекущаяЗапись.Дата КАК Дата,
МАКСИМУМ(ПредыдущаяЗапись.Дата) КАК ПредыдущаяДата,
МИНИМУМ(СледующаяЗапись.Дата) КАК СледующаяДата
ПОМЕСТИТЬ ВТ_Даты
ИЗ
РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК ТекущаяЗапись
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК ПредыдущаяЗапись
ПО ТекущаяЗапись.Дата > ПредыдущаяЗапись.Дата
И ТекущаяЗапись.Организация = ПредыдущаяЗапись.Организация
// Добавьте другие измерения для фильтрации
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК СледующаяЗапись
ПО ТекущаяЗапись.Дата < СледующаяЗапись.Дата
И ТекущаяЗапись.Организация = СледующаяЗапись.Организация
СГРУППИРОВАТЬ ПО
ТекущаяЗапись.Дата;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Даты.Дата КАК Дата,
РегистрТекущий.Значение КАК ТекущееЗначение,
РегистрПредыдущий.Значение КАК ПредыдущееЗначение,
РегистрСледующий.Значение КАК СледующееЗначение
ИЗ
ВТ_Даты КАК ВТ_Даты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК РегистрТекущий
ПО ВТ_Даты.Дата = РегистрТекущий.Дата
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК РегистрПредыдущий
ПО ВТ_Даты.ПредыдущаяДата = РегистрПредыдущий.Дата
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК РегистрСледующий
ПО ВТ_Даты.СледующаяДата = РегистрСледующий.Дата
Важный нюанс: Если в регистре на одну и ту же секунду может быть записано несколько документов (например, в «ЗУП 3.1» это случается редко, но технически возможно), соединение только по полю Дата может привести к дублированию строк. В таких случаях необходимо использовать дополнительные уникальные идентификаторы, такие как НомерСтроки или Регистратор.
Если данных в регистре очень много, предыдущий метод может работать медленно из-за условий сравнения «больше/меньше». Выясним причину: СУБД приходится выполнять огромный объем сравнений для каждой строки. Более эффективный способ — пронумеровать записи по порядку и соединять их по индексу НомерСтроки = НомерСтроки + 1.
Рассмотрим алгоритм действий:
Пример создания нумерованной таблицы:
ВЫБРАТЬ
Т1.Дата КАК Дата,
КОЛИЧЕСТВО(Т2.Дата) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Нумерация
ИЗ
ВТ_ИсходныеДанные КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
ПО Т1.Дата >= Т2.Дата
СГРУППИРОВАТЬ ПО
Т1.Дата;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т_Тек.Дата КАК ТекущаяДата,
Т_Пред.Дата КАК ПредыдущаяДата
ИЗ
ВТ_Нумерация КАК Т_Тек
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Нумерация КАК Т_Пред
ПО Т_Тек.НомерСтроки = Т_Пред.НомерСтроки + 1
Этот метод значительно ускоряет получение именно «соседней» записи, так как соединение по равенству целых чисел (индексов) работает быстрее, чем поиск максимума по диапазону дат.
Начиная с версии платформы 8.3.24, в языке запросов 1С появилась официальная поддержка оконных функций. Это кардинально упрощает задачу. Теперь нам не нужно строить сложные соединения. Мы можем использовать конструкцию ROW_NUMBER() или напрямую искать значения в «окне».
Хотя функции LEAD и LAG могут быть не представлены отдельными ключевыми словами в первых релизах, их легко заменить функцией ROW_NUMBER() в рамках одного запроса. Посмотрим на пример концепции:
ВЫБРАТЬ
История.Дата,
История.Значение,
ПОРЯДКОВЫЙ_НОМЕР() ОБРАБОТКА ПОСЛЕДОВАТЕЛЬНОСТИ(УПОРЯДОЧИТЬ ПО История.Дата) КАК Индекс
ПОМЕСТИТЬ ВТ_СИндексом
ИЗ
РегистрСведений.ИсторияИспользованияШтатногоРасписания КАК История
Далее работа строится аналогично методу с нумерацией, но сама нумерация выполняется движком БД мгновенно, без тяжелых соединений.
При работе с конкретным регистром в «ЗУП 3.1» нужно учитывать несколько критических факторов:
Дата входит в индекс. Если вы фильтруете историю по подразделению или должности, эти измерения также должны участвовать в соединениях, чтобы СУБД использовала Index Seek вместо сканирования всей таблицы.NULL, а для последней — NULL будет в «следующей». Используйте функцию ЕСТЬNULL(), чтобы корректно обрабатывать эти ситуации в отчетах.Разберем, как сделать запрос максимально быстрым:
Дата, ключевые измерения и те значения, которые нужны для анализа.ИНДЕКСИРОВАТЬ ПО. Без этого при больших объемах штатного расписания (тысячи позиций) запрос может уйти в таймаут.ВЫБРАТЬ ПЕРВЫЕ 1... УПОРЯДОЧИТЬ ПО Дата УБЫВ. Но для массового анализа списка записей этот подход противопоказан.Таким образом, выбор метода зависит от вашей инфраструктуры. Для старых систем оптимальным будет использование временных таблиц с индексированной нумерацией, а для современных — использование новых возможностей платформы 8.3.24.