При разработке отчетов и механизмов анализа данных в 1С часто возникает задача преобразования плоской таблицы записей в таблицу интервалов. Например, когда у нас есть ежедневные записи значения какого-либо параметра, и нам нужно получить таблицу вида «Дата начала | Дата окончания | Значение», объединив идущие подряд дни с одинаковым значением. Сложность заключается в том, что обычная группировка по значению параметра здесь не подходит: один и тот же параметр может принимать одно и то же значение в разные, не связанные между собой периоды времени. В SQL эта задача известна как Gaps and Islands («Разрывы и острова»).
Поскольку язык запросов 1С не поддерживает стандартные для SQL оконные функции (ROW_NUMBER, LAG, LEAD), мы разберем, как решить эту задачу с помощью временных таблиц и соединений.
Рассмотрим ситуацию: параметр имел значение "1" с 1 по 4 января, затем значение "2" с 5 по 7 января, и снова значение "1" с 8 по 9 января. Если мы просто сгруппируем данные по значению, то для единицы мы получим общий период с 1 по 9 января, что неверно, так как внутри был «остров» другого значения.
Для решения нам необходимо реализовать следующий алгоритм:
Проанализируем пример запроса, который позволяет эффективно выделить границы периодов. Сначала мы помещаем наши исходные данные во временную таблицу ВТ_Исходная.
// Предположим, у нас уже есть ВТ_Исходная с полями Дата и Значение
// Выделяем даты начала интервалов
ВЫБРАТЬ
Т1.Дата КАК ДатаНачала,
Т1.Значение КАК Значение
ПОМЕСТИТЬ ВТ_Начала
ИЗ
ВТ_Исходная КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исходная КАК Т2
ПО Т1.Значение = Т2.Значение
И (ДОБАВИТЬКДАТЕ(Т1.Дата, ДЕНЬ, -1) = Т2.Дата)
ГДЕ
Т2.Дата ЕСТЬ NULL;
////////////////////////////////////////////////////////////////////////////////
// Выделяем даты окончания интервалов
ВЫБРАТЬ
Т1.Дата КАК ДатаОкончания,
Т1.Значение КАК Значение
ПОМЕСТИТЬ ВТ_Окончания
ИЗ
ВТ_Исходная КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исходная КАК Т2
ПО Т1.Значение = Т2.Значение
И (ДОБАВИТЬКДАТЕ(Т1.Дата, ДЕНЬ, 1) = Т2.Дата)
ГДЕ
Т2.Дата ЕСТЬ NULL;
В этом блоке кода мы используем ЛЕВОЕ СОЕДИНЕНИЕ таблицы самой с собой со смещением в один день. Если соединение не находит «соседа» (Т2.Дата ЕСТЬ NULL), значит, мы нашли границу интервала.
Теперь нам нужно соединить эти две временные таблицы так, чтобы для каждой даты начала нашлась ее дата окончания. Мы используем условие, что дата окончания должна быть больше или равна дате начала, и при этом выбираем самую раннюю из возможных дат окончания для конкретного значения.
ВЫБРАТЬ
ВТ_Начала.Значение КАК ЗначениеПараметра,
ВТ_Начала.ДатаНачала КАК ДатаНачала,
МИНИМУМ(ВТ_Окончания.ДатаОкончания) КАК ДатаОкончания
ИЗ
ВТ_Начала КАК ВТ_Начала
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Окончания КАК ВТ_Окончания
ПО ВТ_Начала.Значение = ВТ_Окончания.Значение
И ВТ_Начала.ДатаНачала <= ВТ_Окончания.ДатаОкончания
СГРУППИРОВАТЬ ПО
ВТ_Начала.Значение,
ВТ_Начала.ДатаНачала
УПОРЯДОЧИТЬ ПО
ДатаНачала
При работе с реальными данными на больших периодах (например, 2 года и более) следует учитывать несколько важных моментов:
Дата и Значение. Без индексов время выполнения запроса будет расти экспоненциально из-за вложенных соединений.ДОБАВИТЬКДАТЕ(..., ДЕНЬ, 1) предполагает, что записи в таблице идут строго каждый день без пропусков. Если в вашей таблице могут быть «дыры» (например, отсутствуют выходные дни), то условие «дата + 1 день» не сработает. В таком случае вместо прибавления дня нужно искать «следующую по порядку запись» через вложенный запрос ВЫБРАТЬ МИНИМУМ(Дата)... ГДЕ Дата > ТекущаяДата.NULL, обязательно используйте функцию ЕСТЬNULL(), иначе условия соединения Т1.Значение = Т2.Значение не сработают для пустых значений, и логика интервалов будет нарушена.НАЧАЛОПЕРИОДА(Дата, ДЕНЬ), чтобы избежать ошибок при сравнении «соседних» дней.Рассмотрим ситуацию, когда данных очень много. Иногда выгоднее выполнить простой запрос, упорядоченный по дате, и обработать его программно в цикле на встроенном языке 1С. Алгоритмическая сложность такого обхода — O(n), в то время как сложный запрос с соединениями может выполняться значительно дольше.
// Пример программной обработки таблицы значений
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("ДатаНачала");
ТаблицаРезультат.Колонки.Добавить("ДатаОкончания");
ТаблицаРезультат.Колонки.Добавить("Значение");
ТекущийИнтервал = Неопределено;
Для Каждого Строка Из ИсходныеДанные Цикл
Если ТекущийИнтервал = Неопределено Тогда
// Первый проход
ТекущийИнтервал = ТаблицаРезультат.Добавить();
ТекущийИнтервал.ДатаНачала = Строка.Дата;
ТекущийИнтервал.ДатаОкончания = Строка.Дата;
ТекущийИнтервал.Значение = Строка.Значение;
ИначеЕсли Строка.Значение = ТекущийИнтервал.Значение
И Строка.Дата = ДобавитьКДате(ТекущийИнтервал.ДатаОкончания, "День", 1) Тогда
// Продолжаем текущий интервал
ТекущийИнтервал.ДатаОкончания = Строка.Дата;
Иначе
// Значение изменилось или случился разрыв в датах - создаем новый интервал
ТекущийИнтервал = ТаблицаРезультат.Добавить();
ТекущийИнтервал.ДатаНачала = Строка.Дата;
ТекущийИнтервал.ДатаОкончания = Строка.Дата;
ТекущийИнтервал.Значение = Строка.Значение;
КонецЕсли;
КонецЦикла;
Выбор между запросом и программным обходом зависит от конкретной задачи: если результат нужен для использования в других частях этого же запроса (например, для соединения с другими таблицами), выбирайте решение на языке запросов. Если же это финальный этап формирования отчета — программный обход может быть эффективнее и проще в поддержке.