В практике разработки на платформе 1С нередко возникают нестандартные задачи по анализу временных последовательностей. Одной из таких задач является контроль симметрии вещательной сетки в периодическом регистре сведений. Представьте ситуацию: эфирные сутки разбиты на два полусуточных блока по 12 часов (первый блок — с 06:00 до 17:59, второй — с 18:00 до 05:59 следующего дня календарно). Передачи во втором блоке должны с точностью до секунды повторять первый блок, за исключением определенных временных отрезков (например, последней четверти каждого часа) и периодов проведения технических работ (профилактики).
В рамках данной статьи мы подробно разберем, как решить эту задачу двумя принципиально разными способами: с помощью построения сложного пакетного запроса и с помощью эффективного программного алгоритма «конечного автомата» в оперативной памяти. Также мы проанализируем неочевидные проблемы производительности СУБД (SARGability) и методы борьбы с ними.
В нашей системе присутствует независимый периодический регистр сведений ТВ_Программа со следующей структурой:
Дата, точность до 1 секунды) — время выхода передачи в эфир.СправочникСсылка.ТВ_Передачи) — ссылка на транслируемый медиаресурс.Бизнес-требования к проверке симметрии формулируются следующим образом:
Рассмотрим детально оба подхода к реализации данной проверки.
Разберем логику построения оптимального пакетного запроса. Нам необходимо не просто сопоставить записи, но и динамически рассчитать границы блоков для каждой строки регистра, определить периоды профилактики и выполнить левое соединение со смещением во времени.
Шаг 1. Получение анализируемых передач и расчет границ блоков
В первом запросе пакета мы выбираем передачи за указанный период, сразу отсекаем профилактические записи и записи, приходящиеся на конец часа (минуты >= 45). Также мы динамически вычисляем виртуальные границы текущего блока вещания:
ВЫБРАТЬ
ТВ_Программа.ТВ_Передача КАК ТВ_Передача,
ТВ_Программа.Период КАК Период,
ВЫБОР
КОГДА ЧАС(ТВ_Программа.Период) >= 6 И ЧАС(ТВ_Программа.Период) < 18
ТОГДА ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ТВ_Программа.Период, ДЕНЬ), ЧАС, 6)
ИНАЧЕ ВЫБОР
КОГДА ЧАС(ТВ_Программа.Период) >= 18
ТОГДА ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ТВ_Программа.Период, ДЕНЬ), ЧАС, 18)
ИНАЧЕ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(ТВ_Программа.Период, ДЕНЬ, -1), ДЕНЬ), ЧАС, 18)
КОНЕЦ
КОНЕЦ КАК НачалоБлока,
ВЫБОР
КОГДА ЧАС(ТВ_Программа.Период) >= 6 И ЧАС(ТВ_Программа.Период) < 18
ТОГДА ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ТВ_Программа.Период, ДЕНЬ), ЧАС, 18)
ИНАЧЕ ВЫБОР
КОГДА ЧАС(ТВ_Программа.Период) >= 18
ТОГДА ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(ТВ_Программа.Период, ДЕНЬ, 1), ДЕНЬ), ЧАС, 6)
ИНАЧЕ ДОБАВИТЬКДАТЕ(НАЧАЛОПЕРИОДА(ТВ_Программа.Период, ДЕНЬ), ЧАС, 6)
КОНЕЦ
КОНЕЦ КАК КонецБлока
ПОМЕСТИТЬ ПередачиАнализируемогоПериода
ИЗ
РегистрСведений.ТВ_Программа КАК ТВ_Программа
ГДЕ
ТВ_Программа.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И МИНУТА(ТВ_Программа.Период) < 45
И ТВ_Программа.ТВ_Передача <> &ПередачаПрофилактика
;
Шаг 2. Расчет ожидаемых смещений для предыдущего и следующего блоков
Во втором временном запросе мы рассчитываем границы сопоставляемых блоков со смещением в 12 часов назад и вперед:
ВЫБРАТЬ
Анализ.ТВ_Передача КАК ТВ_Передача,
Анализ.Период КАК Период,
ДОБАВИТЬКДАТЕ(Анализ.НачалоБлока, ЧАС, -12) КАК ПредыдущийБлок_Начало,
ДОБАВИТЬКДАТЕ(Анализ.КонецБлока, ЧАС, -12) КАК ПредыдущийБлок_Конец,
ДОБАВИТЬКДАТЕ(Анализ.НачалоБлока, ЧАС, 12) КАК СледующийБлок_Начало,
ДОБАВИТЬКДАТЕ(Анализ.КонецБлока, ЧАС, 12) КАК СледующийБлок_Конец
ПОМЕСТИТЬ ПередачиСГраницамиСмещения
ИЗ
ПередачиАнализируемогоПериода КАК Анализ
;
Шаг 3. Расчет непрерывных интервалов профилактики
Профилактика может длиться долго. Чтобы определить ее точные временные границы в базе данных, выполним левое соединение таблицы профилактик с самой собой для поиска времени окончания:
ВЫБРАТЬ
ТВ_Программа.ТВ_Передача КАК ТВ_Передача,
ТВ_Программа.Период КАК ДатаНачалаПрофилактики,
МИНИМУМ(ЕСТЬNULL(ТВ_ПрограммаСлед.Период, ДОБАВИТЬКДАТЕ(&ДатаОкончания, ЧАС, 12))) КАК ДатаОкончанияПрофилактики
ПОМЕСТИТЬ ПериодыПрофилактики
ИЗ
РегистрСведений.ТВ_Программа КАК ТВ_Программа
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ТВ_Программа КАК ТВ_ПрограммаСлед
ПО ТВ_Программа.Период < ТВ_ПрограммаСлед.Период
ГДЕ
ТВ_Программа.ТВ_Передача = &ПередачаПрофилактика
И ТВ_Программа.Период МЕЖДУ ДОБАВИТЬКДАТЕ(&ДатаНачала, ЧАС, -12) И ДОБАВИТЬКДАТЕ(&ДатаОкончания, ЧАС, 12)
ГРУППИРОВАТЬ ПО
ТВ_Программа.ТВ_Передача,
ТВ_Программа.Период
;
Шаг 4. Финальное сопоставление и поиск отклонений
Теперь объединим наши подготовленные данные с помощью левых соединений. Мы ищем случаи, когда оригинальная передача не имеет пары в сопоставимом периоде, и при этом в сопоставимый момент времени не велась трансляция профилактических работ:
ВЫБРАТЬ
Смещение.ТВ_Передача КАК ТВ_Передача,
Смещение.Период КАК Период,
ВЫБОР
КОГДА ТВ_ПрограммаПред.ТВ_Передача ЕСТЬ NULL
И ПрофилактикаПред.ТВ_Передача ЕСТЬ NULL ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ОтсутствуетВПредыдущемБлоке,
ВЫБОР
КОГДА ТВ_ПрограммаСлед.ТВ_Передача ЕСТЬ NULL
И ПрофилактикаСлед.ТВ_Передача ЕСТЬ NULL ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ОтсутствуетВСледующемБлоке
ПОМЕСТИТЬ РезультатАнализа
ИЗ
ПередачиСГраницамиСмещения КАК Смещение
// Соединение с предыдущим блоком вещания
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ТВ_Программа КАК ТВ_ПрограммаПред
ПО Смещение.ТВ_Передача = ТВ_ПрограммаПред.ТВ_Передача
И ТВ_ПрограммаПред.Период МЕЖДУ Смещение.ПредыдущийБлок_Начало И Смещение.ПредыдущийБлок_Конец
// Соединение со следующим блоком вещания
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ТВ_Программа КАК ТВ_ПрограммаСлед
ПО Смещение.ТВ_Передача = ТВ_ПрограммаСлед.ТВ_Передача
И ТВ_ПрограммаСлед.Период МЕЖДУ Смещение.СледующийБлок_Начало И Смещение.СледующийБлок_Конец
// Проверка профилактики для предыдущего блока
ЛЕВОЕ СОЕДИНЕНИЕ ПериодыПрофилактики КАК ПрофилактикаПред
ПО ДОБАВИТЬКДАТЕ(Смещение.Период, ЧАС, -12) МЕЖДУ ПрофилактикаПред.ДатаНачалаПрофилактики И ПрофилактикаПред.ДатаОкончанияПрофилактики
// Проверка профилактики для следующего блока
ЛЕВОЕ СОЕДИНЕНИЕ ПериодыПрофилактики КАК ПрофилактикаСлед
ПО ДОБАВИТЬКДАТЕ(Смещение.Период, ЧАС, 12) МЕЖДУ ПрофилактикаСлед.ДатаНачалаПрофилактики И ПрофилактикаСлед.ДатаОкончанияПрофилактики
;
// Выводим только те передачи, у которых нарушена симметрия
ВЫБРАТЬ
Результат.ТВ_Передача КАК ТВ_Передача,
Результат.Период КАК Период,
Результат.ОтсутствуетВПредыдущемБлоке КАК ОтсутствуетВПредыдущемБлоке,
Результат.ОтсутствуетВСледующемБлоке КАК ОтсутствуетВСледующемБлоке
ИЗ
РезультатАнализа КАК Результат
ГДЕ
Результат.ОтсутствуетВПредыдущемБлоке = ИСТИНА
ИЛИ Результат.ОтсутствуетВСледующемБлоке = ИСТИНА
УПОРЯДОЧИТЬ ПО
Период
;
Представленный выше запрос логичен, но таит в себе серьезную опасность для высоконагруженных баз данных. Обратим внимание на условия связи:
ПО ДОБАВИТЬКДАТЕ(Смещение.Период, ЧАС, -12) МЕЖДУ ПрофилактикаПред.ДатаНачалаПрофилактики...
Использование функций платформы ДОБАВИТЬКДАТЕ(), ЧАС(), МИНУТА() непосредственно внутри условий ПО и ГДЕ приводит к тому, что СУБД (будь то MS SQL, PostgreSQL или файловый движок) не может использовать индексы по полю Период. Вместо быстрого поиска по индексу (Index Seek) СУБД будет вынуждена выполнять полное сканирование всей таблицы (Table Scan), вычисляя значение функции для каждой строки.
Как это оптимизировать?
Поскольку в требованиях заявлено строгое совпадение до секунд, мы можем рассчитать точные значения сопоставляемых дат заранее на этапе формирования временных таблиц, проиндексировать их и выполнять соединение строго по равенству простых проиндексированных полей:
ОжидаемыйПериодПовтора = ДОБАВИТЬКДАТЕ(Период, ЧАС, 12).ИНДЕКСИРОВАТЬ ПО для полей ТВ_Передача и ОжидаемыйПериодПовтора.ВТ.ОжидаемыйПериодПовтора = Регистр.Период. В этом случае СУБД выполнит мгновенный поиск по индексу регистра, и запрос отработает за доли секунды.
Если анализируемый период небольшой (например, мы проверяем симметрию посуточно или за пару дней), то объем данных составляет всего несколько сотен или тысяч записей. В этом случае сложный реляционный запрос с обилием левых соединений может оказаться избыточным, сложным в отладке и медленным.
Альтернативный подход — выгрузить данные простым запросом и выполнить проверку в оперативной памяти сервера с использованием коллекции Соответствие (хэш-таблица с доступом к элементам за константное время O(1)).
Разберем по шагам алгоритмическую реализацию этой логики:
// Шаг 1. Получаем все передачи за анализируемые сутки + 12 часов запаса
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТВ_Программа.Период КАК Период,
| ТВ_Программа.ТВ_Передача КАК ТВ_Передача
|ИЗ
| РегистрСведений.ТВ_Программа КАК ТВ_Программа
|ГДЕ
| ТВ_Программа.Период МЕЖДУ &ДатаНачала И &ДатаОкончанияСЗапасом
|УПОРЯДОЧИТЬ ПО
| Период";
Запрос.УстановитьПараметр("ДатаНачала", ДатаНачала);
// Берем запас времени, чтобы проверить передачи конца дня во втором блоке следующего дня
Запрос.УстановитьПараметр("ДатаОкончанияСЗапасом", КонецДня(ДатаОкончания) + 43200); // +12 часов
ТаблицаПередач = Запрос.Выполнить().Выгрузить();
// Шаг 2. Подготавливаем коллекции в памяти
ПередачиБлока1 = Новый Соответствие; // Ключ: УникальныйКлючВремени, Значение: СтрокаТаблицы
ОшибкиСимметрии = Новый ТаблицаЗначений;
ОшибкиСимметрии.Колонки.Добавить("ТВ_Передача", Новый ОписаниеТипов("СправочникСсылка.ТВ_Передачи"));
ОшибкиСимметрии.Колонки.Добавить("Период", Новый ОписаниеТипов("Дата"));
ОшибкиСимметрии.Колонки.Добавить("ОписаниеОшибки", Новый ОписаниеТипов("Строка"));
// Шаг 3. Заполняем карту периодов профилактики
// В реальной системе это может быть чтение из Производственного Календаря или Справочника
ТаблицаПрофилактик = ПолучитьПериодыПрофилактики(ДатаНачала, ДатаОкончания + 43200);
// Шаг 4. Проход по выборке (принцип конечного автомата)
Для Каждого СтрокаПередачи Из ТаблицаПередач Цикл
// Исключаем 45-ю минуту вещания
Если Минута(СтрокаПередачи.Период) >= 45 Тогда
Продолжить;
КонецЕсли;
// Исключаем саму запись профилактики из анализа
Если СтрокаПередачи.ТВ_Передача = ПередачаПрофилактика Тогда
Продолжить;
КонецЕсли;
ВремяЭфира = СтрокаПередачи.Период;
ЧасЭфира = Час(ВремяЭфира);
// Определяем принадлежность к первому или второму блоку вещательных суток
Если ЧасЭфира >= 6 И ЧасЭфира < 18 Тогда
// БЛОК 1. Запоминаем передачу для последующего сравнения
// Ключом будет смещение времени относительно начала суток и идентификатор передачи
КлючВремени = ПолучитьКлючВремени(ВремяЭфира);
УникальныйИдентификатор = Строка(СтрокаПередачи.ТВ_Передача.УникальныйИдентификатор()) + "_" + КлючВремени;
ПередачиБлока1.Вставить(УникальныйИдентификатор, СтрокаПередачи);
Иначе
// БЛОК 2. Ищем аналогичную запись в Блоке 1 (минус 12 часов)
ОжидаемоеВремяБлока1 = ДобавитьКДате(ВремяЭфира, ЧастьДаты.Час, -12);
КлючВремениБлока1 = ПолучитьКлючВремени(ОжидаемоеВремяБлока1);
УникальныйИдентификаторБлока1 = Строка(СтрокаПередачи.ТВ_Передача.УникальныйИдентификатор()) + "_" + КлючВремениБлока1;
ЗаписьБлока1 = ПередачиБлока1[УникальныйИдентификаторБлока1];
Если ЗаписьБлока1 = Неопределено Тогда
// Пары нет. Проверяем, не было ли в это время профилактики в первом блоке
Если Не ВремяПопадаетВПрофилактику(ОжидаемоеВремяБлока1, ТаблицаПрофилактик) Тогда
НоваяОшибка = ОшибкиСимметрии.Добавить();
НоваяОшибка.ТВ_Передача = СтрокаПередачи.ТВ_Передача;
НоваяОшибка.Период = ВремяЭфира;
НоваяОшибка.ОписаниеОшибки = "Отсутствует оригинальная передача в первом блоке вещания (в " + ОжидаемоеВремяБлока1 + ")";
КонецЕсли;
Иначе
// Нашли соответствие. Удаляем из карты, чтобы в конце остались только непарные записи Блока 1
ПередачиБлока1.Удалить(УникальныйИдентификаторБлока1);
КонецЕсли;
КонецЕсли;
КонецЦикла;
// Шаг 5. Все записи, оставшиеся в карте ПередачиБлока1 — это непарные записи первого блока
Для Каждого ЭлементКарты Из ПередачиБлока1 Цикл
ЗаписьБлока1 = ЭлементКарты.Значение;
ОжидаемоеВремяБлока2 = ДобавитьКДате(ЗаписьБлока1.Период, ЧастьДаты.Час, 12);
// Проверяем, не перекрыто ли ожидаемое время повтора профилактикой во втором блоке
Если Не ВремяПопадаетВПрофилактику(ОжидаемоеВремяБлока2, ТаблицаПрофилактик) Тогда
НоваяОшибка = ОшибкиСимметрии.Добавить();
НоваяОшибка.ТВ_Передача = ЗаписьБлока1.ТВ_Передача;
НоваяОшибка.Период = ЗаписьБлока1.Период;
НоваяОшибка.ОписаниеОшибки = "Передача не повторилась во втором блоке вещания (в " + ОжидаемоеВремяБлока2 + ")";
КонецЕсли;
КонецЦикла;
Вспомогательные функции, обеспечивающие работу алгоритма:
Функция ПолучитьКлючВремени(Знач ДатаВремя) Экспорт
// Возвращает строковое выражение смещения времени в секундах от начала вещательных суток (с 06:00:00)
НачалоДня = НачалоПериода(ДатаВремя, День);
СекундСНачалаДня = ДатаВремя - НачалоДня;
Возврат Строка(СекундСНачалаДня % 43200); // 43200 секунд в 12 часах
КонецФункции
Функция ВремяПопадаетВПрофилактику(ПроверяемоеВремя, ТаблицаПрофилактик)
Для Каждого Интервал Из ТаблицаПрофилактик Цикл
Если ПроверяемоеВремя >= Интервал.ДатаНачала И ПроверяемоеВремя <= Интервал.ДатаОкончания Тогда
Возврат ИСТИНА;
КонецЕсли;
КонецЦикла;
Возврат ЛОЖЬ;
КонецФункции
При выборе метода реализации важно проанализировать несколько жизненных ситуаций, с которыми вы обязательно столкнетесь при эксплуатации системы в реальном эфире.
1. Проблема миллисекундного люфта времени
В реальном мире выдача передач в эфир может происходить с небольшим аппаратным сдвигом в несколько секунд (например, передача началась в 11:00:02, а повтор запустился в 23:00:05). Если мы используем реляционный запрос со строгим сопоставлением по времени, СУБД посчитает это ошибкой симметрии.
МЕЖДУ ДОБАВИТЬКДАТЕ(ОжидаемоеВремя, СЕКУНДА, -10) И ДОБАВИТЬКДАТЕ(ОжидаемоеВремя, СЕКУНДА, 10)), что моментально снизит производительность из-за невозможности нормального использования индексов.Соответствие, выполнив при отсутствии точного совпадения быстрый перебор ключей в небольшом диапазоне (задавая допуск +/- 10 секунд). Для этого в качестве ключа хэш-карты можно использовать целочисленное смещение в секундах.2. Проблема декартового задвоения
Если короткая передача (например, «Прогноз погоды») выходит в рамках одного и того же блока дважды (в 08:15 и в 08:45), то при левом соединении таблиц в запросе без строгой привязки к секундам произойдет «размножение» записей (декартово произведение). Программный алгоритм с использованием хэш-карт полностью застрахован от этой коллизии, так как каждая запись сопоставляется индивидуально и удаляется из коллекции по мере нахождения пары.
Для создания отказоустойчивой системы контроля симметрии вещания мы рекомендуем совмещать оба подхода: