В практике разработчика 1С часто возникает задача преобразования дискретных записей регистра (например, ежедневных отметок в табеле учета рабочего времени) в наглядные интервалы «Дата начала — Дата окончания». Такая задача в теории баз данных называется «задачей об островах и промежутках» (Islands and Gaps). В этой статье мы подробно разберем, как объединить идущие подряд даты в одну строку, исключая разрывы, и какие инструменты платформы 1С наиболее эффективны для этого.
Представим ситуацию, описанную в вопросе автора: имеется регистр накопления ДанныеТабельногоУчетаРабочегоВремениСотрудников. Каждая запись в нем соответствует определенному дню (поле Период). Если сотрудник работал с 1 по 2 февраля, а затем с 4 по 5 февраля, в базе будет 4 записи. Наша цель — получить две строки: «01.02 — 02.02» и «04.02 — 05.02».
Проанализируем базовый запрос, с которого начинается работа:
ВЫБРАТЬ
ДанныеТабельногоУчета.Период КАК Период,
ДанныеТабельногоУчета.Регистратор КАК Регистратор,
ДанныеТабельногоУчета.ВидУчетаВремени КАК ВидУчетаВремени
ИЗ
РегистрНакопления.ДанныеТабельногоУчетаРабочегоВремениСотрудников КАК ДанныеТабельногоУчета
ГДЕ
ДанныеТабельногоУчета.Период МЕЖДУ &НачалоПериода И &КонецПериода
И ДанныеТабельногоУчета.ВидУчетаВремени = &ВидУчетаВремени
Важно помнить, что в регистре Период может содержать время. Чтобы алгоритм группировки работал корректно, первым делом необходимо «очистить» дату от времени, используя функцию НАЧАЛОПЕРИОДА(Период, ДЕНЬ).
Рассмотрим алгоритм, который часто называют «методом Ильдаровича». Он основан на выявлении дат, которые являются началом или концом цепочки.
Шаг 1: Определение начал периодов. Началом периода считается такая дата, для которой в исходной таблице не существует записи на предыдущий день (Период - 1).
Шаг 2: Определение концов периодов. Концом периода считается дата, для которой нет записи на следующий день (Период + 1).
Шаг 3: Сопоставление. Для каждого «начала» мы находим ближайшее к нему «конец», которое больше или равно этому началу.
Проанализируем пример реализации такого запроса:
// Помещаем уникальные даты во временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
НАЧАЛОПЕРИОДА(Табель.Период, ДЕНЬ) КАК День
ПОМЕСТИТЬ ВТ_ВсеДаты
ИЗ
РегистрНакопления.ДанныеТабельногоУчетаРабочегоВремениСотрудников КАК Табель
ГДЕ
Табель.Сотрудник = &Сотрудник;
////////////////////////////////////////////////////////////////////////////////
// Находим даты начала "островов"
ВЫБРАТЬ
Даты.День КАК ДатаНачала
ПОМЕСТИТЬ ВТ_Начала
ИЗ
ВТ_ВсеДаты КАК Даты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВсеДаты КАК ДатыМинус1
ПО (ДОБАВИТЬКДАТЕ(Даты.День, ДЕНЬ, -1) = ДатыМинус1.День)
ГДЕ
ДатыМинус1.День ЕСТЬ NULL;
////////////////////////////////////////////////////////////////////////////////
// Находим даты окончания "островов"
ВЫБРАТЬ
Даты.День КАК ДатаОкончания
ПОМЕСТИТЬ ВТ_Концы
ИЗ
ВТ_ВсеДаты КАК Даты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВсеДаты КАК ДатыПлюс1
ПО (ДОБАВИТЬКДАТЕ(Даты.День, ДЕНЬ, 1) = ДатыПлюс1.День)
ГДЕ
ДатыПлюс1.День ЕСТЬ NULL;
////////////////////////////////////////////////////////////////////////////////
// Соединяем начала и концы
ВЫБРАТЬ
Начала.ДатаНачала,
МИНИМУМ(Концы.ДатаОкончания) КАК ДатаОкончания
ИЗ
ВТ_Начала КАК Начала
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Концы КАК Концы
ПО (Начала.ДатаНачала <= Концы.ДатаОкончания)
СГРУППИРОВАТЬ ПО
Начала.ДатаНачала
Если вы работаете на современной версии платформы 1С, задачу можно решить значительно проще. Выясним причину: оконные функции позволяют пронумеровать строки внутри выборки. Если вычесть из даты её порядковый номер, то для всех дат, идущих без разрывов, результат будет одинаковым. Это значение мы и используем для группировки.
Разберем этот элегантный метод по шагам:
ПОРЯДКОВЫЙ_НОМЕР() с упорядочиванием по дате.ДОБАВИТЬКДАТЕ(Период, ДЕНЬ, - ПОРЯДКОВЫЙ_НОМЕР()).
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Табель.Период, ДЕНЬ) КАК День
ПОМЕСТИТЬ ВТ_Дни
ИЗ
РегистрНакопления.ДанныеТабельногоУчетаРабочегоВремениСотрудников КАК Табель;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МИНИМУМ(ВложенныйЗапрос.День) КАК ДатаНачала,
МАКСИМУМ(ВложенныйЗапрос.День) КАК ДатаОкончания
ИЗ
(ВЫБРАТЬ
День,
ДОБАВИТЬКДАТЕ(День, ДЕНЬ, - ПОРЯДКОВЫЙ_НОМЕР() OVER (УПОРЯДОЧИТЬ ПО День)) КАК Группа
ИЗ
ВТ_Дни) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Группа
Иногда запрос становится слишком сложным, особенно если нужно учитывать дополнительные аналитики (сотрудник, подразделение, вид времени). В таких случаях эффективнее выполнить простой запрос и обработать результат в коде на языке 1С. Рассмотрим, как это реализовать:
// Получаем данные, отсортированные по сотруднику и дате
Запрос = Новый Запрос("ВЫБРАТЬ ... УПОРЯДОЧИТЬ ПО Сотрудник, Период");
Выборка = Запрос.Выполнить().Выбрать();
ТаблицаИнтервалов = Новый ТаблицаЗначений;
ТаблицаИнтервалов.Колонки.Добавить("Сотрудник");
ТаблицаИнтервалов.Колонки.Добавить("ДатаНачала");
ТаблицаИнтервалов.Колонки.Добавить("ДатаОкончания");
ТекущийИнтервал = Неопределено;
Пока Выборка.Следующий() Цикл
// Если новый сотрудник или разрыв в датах более 1 дня
Если ТекущийИнтервал = Неопределено
ИЛИ ТекущийИнтервал.Сотрудник <> Выборка.Сотрудник
ИЛИ Выборка.Период <> ДобавитьКДате(ТекущийИнтервал.ДатаОкончания, "День", 1) Тогда
ТекущийИнтервал = ТаблицаИнтервалов.Добавить();
ТекущийИнтервал.Сотрудник = Выборка.Сотрудник;
ТекущийИнтервал.ДатаНачала = Выборка.Период;
ТекущийИнтервал.ДатаОкончания = Выборка.Период;
Иначе
// Продолжаем текущий интервал
ТекущийИнтервал.ДатаОкончания = Выборка.Период;
КонецЕсли;
КонецЦикла;
При реализации любого из этих методов следует учитывать специфику учета времени в 1С:
+1 день, а отсутствие рабочих дней по производственному календарю.МИНИМУМ(Концы.ДатаОкончания) может работать медленно на очень больших объемах данных (десятки тысяч записей), так как выполняется неэффективное соединение. В таких случаях предпочтительнее оконные функции или программный обход.СГРУППИРОВАТЬ ПО в запросе. Если в один день по сотруднику есть две записи (например, явка и переработка), это может нарушить логику поиска следующего дня.Мы проанализировали ситуацию и выяснили, что наиболее универсальным и производительным решением для современных систем является использование оконных функций. Однако классический метод соединений остается актуальным для старых версий платформы и позволяет решать задачу исключительно средствами SQL-движка без привлечения ресурсов сервера приложений для постобработки.