При решении аналитических и интеграционных задач нередко возникает потребность укрупнить данные и разбить временной диапазон на равные интервалы (например, по 4 часа — ровно 6 интервалов или «тиков» в сутках). Типовые виртуальные таблицы регистров накопления поддерживают только фиксированную периодичность (Час, День, Месяц и т. д.), поэтому группировку по нестандартным интервалам приходится реализовывать в тексте запроса.
Разберем, почему стандартные функции работы с датами могут давать сбой при переходе через границу календарного года, и рассмотрим правильные варианты реализации интервального деления в языке запросов 1С.
Выясним причину типичной ошибки. Часто для нумерации интервалов разработчики используют функцию ДЕНЬГОДА(Период). Формула вида ДЕНЬГОДА(Период) * 6 + НомерИнтервалаВСутках корректно работает только в пределах одного календарного года (с 1 января по 31 декабря).
Если же интервал отчета смещается (например, с 1 февраля 2016 года по 31 января 2017 года), то 1 января 2017 года функция ДЕНЬГОДА возвращает значение 1. В результате сквозная нумерация «тиков» сбрасывается, и данные за январь следующего года ошибочно встают в начало шкалы вместо её конца.
Проанализируем ситуацию: если перед нами стоит задача только сгруппировать имеющиеся движения по 4-часовым блокам (без необходимости вывода интервалов с нулевыми продажами), создавать вспомогательную таблицу-календарь не требуется. Достаточно рассчитать сквозной номер интервала («тик») относительно точки отсчета &НачалоПериода.
Разберем по шагам формулу расчета:
РАЗНОСТЬДАТ(&НачалоПериода, Движения.Период, СЕКУНДА).FLOOR) вычитаем 0.5 и приводим тип с помощью ВЫРАЗИТЬ(... КАК ЧИСЛО(10, 0)).Посмотрим на пример запроса:
ВЫБРАТЬ
ПродажиПоАкциямОбороты.Номенклатура КАК SKU,
ВЫРАЗИТЬ((РАЗНОСТЬДАТ(&НачалоПериода, ПродажиПоАкциямОбороты.Период, СЕКУНДА) / 14400) - 0.5 КАК ЧИСЛО(10, 0)) КАК Tick,
СУММА(ПродажиПоАкциямОбороты.КоличествоОборот) КАК Consumption
ИЗ
РегистрНакопления.ПродажиПоАкциям.Обороты(
&НачалоПериода,
&КонецПериода,
Час,
Номенклатура = &Номенклатура
И Акция = ЗНАЧЕНИЕ(Документ.МаркетинговаяАкция.ПустаяСсылка)) КАК ПродажиПоАкциямОбороты
СГРУППИРОВАТЬ ПО
ПродажиПоАкциямОбороты.Номенклатура,
ВЫРАЗИТЬ((РАЗНОСТЬДАТ(&НачалоПериода, ПродажиПоАкциямОбороты.Период, СЕКУНДА) / 14400) - 0.5 КАК ЧИСЛО(10, 0))
УПОРЯДОЧИТЬ ПО
Tick
Преимущества этого способа: максимальная производительность, отсутствие временных таблиц в базе tempdb и полная независимость от границ календарных лет.
Если для аналитики или внешней системы обязательно требуется непрерывная последовательность интервалов (включая периоды без оборотов), необходимо сгенерировать временную сетку в запросе.
Частая ошибка при ручной генерации интервалов — использование самосоединений для подсчета номера строки (КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...)). На периоде в один год (2196 интервалов) это приводит к квадратичной сложности $O(N^2)$ и резкому падению скорости выполнения запроса.
Рассмотрим правильный подход: само декартово произведение цифр уже формирует непрерывную последовательность чисел. Умножая этот счетчик на 4, мы сразу получаем точное смещение даты в часах и готовый номер «тика».
ВЫБРАТЬ
Цифры.Счетчик КАК Tick,
ДОБАВИТЬКДАТЕ(&НачалоПериода, ЧАС, Цифры.Счетчик * 4) КАК ПериодНачало,
ДОБАВИТЬКДАТЕ(&НачалоПериода, ЧАС, (Цифры.Счетчик + 1) * 4) КАК ПериодКонец
ПОМЕСТИТЬ ВТ_СеткаИнтервалов
ИЗ
(ВЫБРАТЬ
aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d КАК Счетчик
ИЗ
(ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК aa
КРОСС СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК bb
КРОСС СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК c ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК cc
КРОСС СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК d ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК dd
ГДЕ
(aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) * 4 <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ЧАС)) КАК Цифры
ИНДЕКСИРОВАТЬ ПО
ПериодНачало,
ПериодКонец
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Сетка.Tick КАК Tick,
ЕСТЬNULL(СУММА(Обороты.КоличествоОборот), 0) КАК Consumption
ИЗ
ВТ_СеткаИнтервалов КАК Сетка
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПродажиПоАкциям.Обороты(
&НачалоПериода,
&КонецПериода,
Час,
Номенклатура = &Номенклатура
И Акция = ЗНАЧЕНИЕ(Документ.МаркетинговаяАкция.ПустаяСсылка)) КАК Обороты
ПО Обороты.Период >= Сетка.ПериодНачало
И Обороты.Период < Сетка.ПериодКонец
СГРУППИРОВАТЬ ПО
Сетка.Tick
УПОРЯДОЧИТЬ ПО
Tick
Обратите внимание на важные правила оптимизации:
&НачалоПериода и &КонецПериода передаются строго в первые два параметра виртуальной таблицы Обороты, чтобы платформа 1С задействовала кластерные индексы базы данных по периоду.ЛЕВОЕ СОЕДИНЕНИЕ вместе с ЕСТЬNULL гарантирует, что в результирующую выборку попадут абсолютно все интервалы года (даже те, по которым не было продаж).