Нам часто приходится сталкиваться с задачами, где необходимо корректно определить границы периодов действия определенных записей. В системе 1С такая задача может возникнуть при работе с историческими данными, например, со списком сотрудников и периодами их работы или с любыми другими периодическими данными, где каждая запись имеет дату начала, но дата окончания не всегда явно задана. Рассмотрим типовой сценарий: у нас есть таблица, содержащая даты начала действия определенных записей, но даты окончания для некоторых из них отсутствуют. Наша цель — автоматически заполнить эти даты, ориентируясь на дату начала следующей записи или устанавливая фиксированную конечную дату для последней записи в серии.
Предположим, у нас есть данные о сотрудниках с их датами начала действия записей, как показано в примере ниже. Для ряда записей поле ДатаОкончанияСортировка не заполнено.
Сотрудник | ДатаНачала | ДатаОкончанияСортировка
----------|---------------------|------------------------
Иванов | 01.07.2008 0:00:00 |
Иванов | 07.11.2016 0:00:00 |
Иванов | 01.09.2020 0:01:00 | 01.10.2020 0:00:59
Иванов | 01.10.2020 0:01:00 | 31.12.3999 23:59:59
Петров | 01.09.2020 0:01:00 |
Петров | 01.10.2020 0:01:00 |
Мы видим, что для многих записей поле ДатаОкончанияСортировка является пустым. Нам необходимо заполнить его, применяя следующие правила:
ДатаОкончанияСортировка для текущей записи должна быть равна дате начала этой следующей записи минус одна секунда. Это гарантирует, что периоды не пересекаются, но при этом покрывают весь временной интервал.ДатаОкончанияСортировка должна быть установлена в фиксированное, максимально возможное значение, например, 31.12.3999 23:59:59.Для решения этой задачи в 1С мы будем использовать мощный механизм запросов, основанный на тета-соединении (также известном как перекрестное соединение с условиями) и агрегатных функциях. Этот подход позволяет нам для каждой строки исходной таблицы найти соответствующие ей последующие строки, что является ключом к определению "следующей" даты начала. Такой метод является эффективным и предпочтительным для обработки больших объемов данных на стороне СУБД.
Разберем по шагам, как мы будем строить наш запрос:
На первом шаге мы получаем базовые данные из нашей исходной таблицы. Это будет наша "основная" часть запроса, которая предоставит поля Сотрудник и ДатаНачала для каждой записи, которую мы хотим обработать. Мы будем рассматривать эту выборку как основу для каждой строки, для которой нам нужно определить дату окончания.
Следующий, и, возможно, самый критически важный шаг — это соединение нашей таблицы самой с собой. Этот тип соединения часто называют тета-соединением, поскольку условия соединения могут быть не только на равенство. Мы будем использовать левое соединение. Левое соединение здесь принципиально важно, так как оно гарантирует, что все записи из нашей основной выборки будут включены в результат, даже если для какой-то записи не найдется "следующей". Условие соединения будет состоять из двух частей:
ОсновнаяВыборка.Сотрудник = СоединеннаяВыборка.Сотрудник:
Это условие гарантирует, что мы рассматриваем только записи, относящиеся к одному и тому же сотруднику. Без этого условия мы бы сравнивали даты начала всех сотрудников друг с другом, что не соответствует нашей задаче.
ОсновнаяВыборка.ДатаНачала < СоединеннаяВыборка.ДатаНачала:
Это условие позволяет для каждой записи из ОсновнаяВыборка (текущей записи) найти все записи из СоединеннаяВыборка (потенциально "следующих"), у которых дата начала строго больше. Таким образом, для каждой "текущей" записи мы получаем список всех ее хронологически последующих записей для того же сотрудника.
Результатом такого соединения будет таблица, где для каждой строки из "ОсновнойВыборки" может быть множество строк из "СоединеннойВыборки", соответствующих всем последующим записям того же сотрудника. Для "последних" записей сотрудника, для которых нет последующих дат, соединенная часть будет содержать NULL.
После выполнения самосоединения нам необходимо для каждой записи из ОсновнаяВыборка определить минимальную ДатуНачала среди всех последующих записей, найденных в СоединеннаяВыборка. Именно эта минимальная дата и будет той "следующей датой начала", которая нам нужна для определения даты окончания текущего периода.
Мы достигаем этого, группируя результат запроса по полям нашей ОсновнойВыборки (Сотрудник и ДатаНачала) и применяя агрегатную функцию МИНИМУМ() к полю СоединеннаяВыборка.ДатаНачала. Эта функция агрегирует все найденные "последующие" даты начала для каждой текущей записи и выбирает самую раннюю из них.
ДатаОкончанияСортировка с помощью ВЫБОР КОГДА:
На заключительном этапе, используя конструкцию ВЫБОР КОГДА (аналог CASE WHEN в стандартном SQL), мы определяем итоговое значение для поля ДатаОкончанияСортировка в соответствии с нашими правилами:
Если результат функции МИНИМУМ(СоединеннаяВыборка.ДатаНачала) не является NULL (что означает, что следующая запись была успешно найдена), то мы устанавливаем ДатаОкончанияСортировка равной этой минимальной дате минус одна секунда. Для вычитания одной секунды мы используем функцию 1С ДОБАВИТЬКДАТЕ(<Дата>, СЕКУНДА, -1). Это позволяет нам получить точную дату и время, предшествующие началу следующего периода.
Если же результат МИНИМУМ(СоединеннаяВыборка.ДатаНачала) равен NULL (это происходит, когда текущая запись является последней для данного сотрудника, и самосоединение не нашло ни одной хронологически последующей даты), то мы устанавливаем ДатаОкончанияСортировка в заранее определенное фиксированное значение. В нашем случае это ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59), которое создается функцией ДАТАВРЕМЯ.
Давайте посмотрим, как все эти шаги реализуются в реальном запросе 1С. Предположим, наша исходная таблица, содержащая поля Сотрудник и ДатаНачала, называется МояТаблицаДанных.
ВЫБРАТЬ
Основная.Сотрудник,
Основная.ДатаНачала,
ВЫБОР
КОГДА МИНИМУМ(Следующая.ДатаНачала) ЕСТЬ NULL
ТОГДА ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59)
ИНАЧЕ ДОБАВИТЬКДАТЕ(МИНИМУМ(Следующая.ДатаНачала), СЕКУНДА, -1)
КОНЕЦ КАК ДатаОкончанияСортировка
ИЗ
МояТаблицаДанных КАК Основная
ЛЕВОЕ СОЕДИНЕНИЕ МояТаблицаДанных КАК Следующая
ПО (Основная.Сотрудник = Следующая.Сотрудник)
И (Основная.ДатаНачала < Следующая.ДатаНачала)
СГРУППИРОВАТЬ ПО
Основная.Сотрудник,
Основная.ДатаНачала
УПОРЯДОЧИТЬ ПО
Основная.Сотрудник,
Основная.ДатаНачала
Проанализируем этот запрос подробнее, чтобы понять каждую его часть:
ВЫБРАТЬ Основная.Сотрудник, Основная.ДатаНачала, ...:
Мы выбираем поля Сотрудник и ДатаНачала из нашей основной выборки, которую мы алиасировали как Основная. Эти поля будут представлять собой данные текущей записи, для которой мы вычисляем дату окончания.
ИЗ МояТаблицаДанных КАК Основная:
Здесь мы указываем источник данных — нашу таблицу МояТаблицаДанных и присваиваем ей псевдоним Основная. Это первая "копия" таблицы, с которой мы работаем.
ЛЕВОЕ СОЕДИНЕНИЕ МояТаблицаДанных КАК Следующая:
Выполняем операцию левого самосоединения со второй копией той же таблицы МояТаблицаДанных, которой мы присвоили псевдоним Следующая. Использование левого соединения здесь принципиально, так как оно позволяет нам сохранить все записи из Основная, даже если для них не найдется соответствующих "следующих" записей в Следующая. Это критически важно для корректной обработки "последних" записей каждого сотрудника.
ПО (Основная.Сотрудник = Следующая.Сотрудник) И (Основная.ДатаНачала < Следующая.ДатаНачала):
Это условие тета-соединения, которое мы подробно обсуждали ранее. Оно сопоставляет каждую запись из Основная со всеми записями из Следующая, которые относятся к тому же сотруднику и имеют дату начала, строго большую, чем дата начала текущей записи. Таким образом, мы эффективно находим все "потенциально следующие" периоды для каждого сотрудника.
ВЫБОР КОГДА МИНИМУМ(Следующая.ДатаНачала) ЕСТЬ NULL ТОГДА ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59) ИНАЧЕ ДОБАВИТЬКДАТЕ(МИНИМУМ(Следующая.ДатаНачала), СЕКУНДА, -1) КОНЕЦ КАК ДатаОкончанияСортировка:
Эта сложная, но мощная конструкция реализует всю логику вычисления даты окончания:
МИНИМУМ(Следующая.ДатаНачала): Это агрегатная функция, которая находит минимальную (самую раннюю) дату начала среди всех "следующих" записей, найденных для текущей строки Основная. Если для текущей записи не было найдено ни одной "следующей" записи (что характерно для последней записи сотрудника), эта функция вернет значение NULL.ЕСТЬ NULL: Проверяет, является ли результат МИНИМУМ(Следующая.ДатаНачала) пустым. Если это так, мы имеем дело с последней записью сотрудника.ТОГДА ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59): В случае, если МИНИМУМ(Следующая.ДатаНачала) равен NULL, мы устанавливаем дату окончания в заданное максимальное значение, используя функцию ДАТАВРЕМЯ для создания конкретного значения даты и времени.ИНАЧЕ ДОБАВИТЬКДАТЕ(МИНИМУМ(Следующая.ДатаНачала), СЕКУНДА, -1): Если же МИНИМУМ(Следующая.ДатаНачала) не является NULL (то есть, следующая запись была найдена), мы используем функцию 1С ДОБАВИТЬКДАТЕ. Эта функция позволяет нам манипулировать датами, добавляя или вычитая определенные интервалы. В данном случае, мы вычитаем одну секунду из найденной минимальной "следующей" даты начала. Например, если следующая дата начинается в 01.09.2020 0:01:00, то текущая дата окончания будет установлена как 31.08.2020 23:59:59, что точно соответствует условию "минус секунда до следующей даты начала".СГРУППИРОВАТЬ ПО Основная.Сотрудник, Основная.ДатаНачала:
Эта часть запроса является обязательной, поскольку мы используем агрегатную функцию МИНИМУМ() в секции ВЫБРАТЬ. Группировка гарантирует, что для каждой уникальной комбинации Сотрудник и ДатаНачала из основной таблицы будет вычислено одно значение агрегатной функции МИНИМУМ(Следующая.ДатаНачала).
УПОРЯДОЧИТЬ ПО Основная.Сотрудник, Основная.ДатаНачала:
Это опциональная, но часто полезная часть запроса, которая упорядочивает итоговый результат по сотрудникам и их датам начала. Это помогает нам получать данные в логическом порядке и упрощает их дальнейший анализ или отображение.
Хотя предложенный запрос является весьма эффективным для решения данной задачи, важно учитывать несколько аспектов, особенно при работе с большими объемами данных:
Тета-соединения, особенно при работе с большими таблицами, могут быть ресурсоемкими и привести к длительному выполнению запроса. Если ваша таблица содержит сотни тысяч или миллионы записей, производительность запроса может стать критичной. В таких случаях настоятельно рекомендуется убедиться, что поля Сотрудник и ДатаНачала имеют соответствующие индексы в вашей базе данных. Правильно настроенные индексы значительно ускорят операции соединения и группировки.
Для улучшения читаемости сложного запроса и, в некоторых случаях, для повышения производительности (за счет сокращения объема данных на промежуточных этапах), можно использовать временные таблицы. Это позволяет разбить сложную логику на несколько более простых шагов. Например, вы можете сначала сформировать уникальный список сотрудников, затем получить основные данные в одну временную таблицу, а потом уже выполнять самосоединение. Это особенно полезно, когда запрос становится очень громоздким для отладки.
Пример структуры с временной таблицей:
ВЫБРАТЬ
МояТаблицаДанных.Сотрудник,
МояТаблицаДанных.ДатаНачала
ПОМЕСТИТЬ ОсновныеДанные
ИЗ
МояТаблицаДанных КАК МояТаблицаДанных
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Основная.Сотрудник,
Основная.ДатаНачала,
ВЫБОР
КОГДА МИНИМУМ(Следующая.ДатаНачала) ЕСТЬ NULL
ТОГДА ДАТАВРЕМЯ(3999, 12, 31, 23, 59, 59)
ИНАЧЕ ДОБАВИТЬКДАТЕ(МИНИМУМ(Следующая.ДатаНачала), СЕКУНДА, -1)
КОНЕЦ КАК ДатаОкончанияСортировка
ИЗ
ОсновныеДанные КАК Основная
ЛЕВОЕ СОЕДИНЕНИЕ ОсновныеДанные КАК Следующая
ПО (Основная.Сотрудник = Следующая.Сотрудник)
И (Основная.ДатаНачала < Следующая.ДатаНачала)
СГРУППИРОВАТЬ ПО
Основная.Сотрудник,
Основная.ДатаНачала
УПОРЯДОЧИТЬ ПО
Основная.Сотрудник,
Основная.ДатаНачала
Этот подход делает запрос более модульным и понятным.
Хотя запросы, выполняемые на стороне СУБД, как правило, более производительны для больших объемов данных, в некоторых случаях (например, при относительно небольшом количестве записей или при необходимости выполнения очень сложной бизнес-логики, которую трудно выразить в запросе) можно рассмотреть решение на встроенном языке 1С. Это будет включать итерацию по выборке данных (например, полученной простым запросом), а затем внутри цикла поиск следующей записи или установку конечной даты программным путем. Однако этот метод обычно менее оптимален с точки зрения скорости выполнения для объемных данных по сравнению с чистым запросом.
Всегда убеждайтесь, что поля, с которыми вы работаете (ДатаНачала, а также значение, устанавливаемое для ДатаОкончанияСортировка), имеют корректный тип Дата в 1С. Функции ДАТАВРЕМЯ и ДОБАВИТЬКДАТЕ предназначены для работы с этим типом и обеспечат правильность вычислений.
Мы успешно разобрались, как с помощью запроса 1С и мощных механизмов самосоединения (тета-соединения) и условных выражений ВЫБОР КОГДА можно автоматически заполнять даты окончания периодов. Этот подход является гибким и мощным инструментом для работы с временными рядами данных в вашей системе 1С. При правильной реализации, учете возможных оптимизаций и индексации, он позволяет эффективно решать сложные задачи по управлению данными о периодах, обеспечивая целостность и корректность ваших учетных записей.