В практике разработчика 1С часто возникает задача анализа последовательностей. Типичный пример: есть набор записей (например, в регистре сведений), где некоторое булево поле (флаг) принимает значение Истина для определенных строк. Эти строки могут идти как поодиночке, так и группами. Нам же требуется получить таблицу интервалов, где указаны номер первой и номер последней строки каждой непрерывной группы. В теории баз данных эта задача известна как Gaps and Islands («Разрывы и острова»).
Разберем подробно, как можно решить эту задачу исключительно средствами языка запросов 1С, не прибегая к медленной постобработке выборки в цикле.
Проанализируем первый подход. Его суть заключается в определении «соседей» для каждой строки, где флаг установлен в значение Истина. Если у строки нет соседа «слева» (предыдущий номер), значит, это начало интервала. Если нет соседа «справа» (следующий номер) — это конец интервала.
Рассмотрим по шагам логику построения такого запроса:
Истина, во временную таблицу.НомерСтроки = НомерСтроки + 1 и НомерСтроки = НомерСтроки - 1.Пример реализации такого алгоритма:
// Шаг 1: Выбираем только нужные строки
ВЫБРАТЬ
Регистр.НомерСтроки КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ТолькоИстина
ИЗ
РегистрСведений.Данные КАК Регистр
ГДЕ
Регистр.Флаг = ИСТИНА;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Определяем границы
ВЫБРАТЬ
ВТ.НомерСтроки КАК НомерСтроки,
ЕСТЬNULL(ВТ_Пред.НомерСтроки, 0) КАК ПредНомер,
ЕСТЬNULL(ВТ_След.НомерСтроки, 0) КАК СледНомер
ПОМЕСТИТЬ ВТ_АнализГраниц
ИЗ
ВТ_ТолькоИстина КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТолькоИстина КАК ВТ_Пред ПО ВТ.НомерСтроки = ВТ_Пред.НомерСтроки + 1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТолькоИстина КАК ВТ_След ПО ВТ.НомерСтроки = ВТ_След.НомерСтроки - 1;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Группируем интервалы
ВЫБРАТЬ
Начала.НомерСтроки КАК НачалоИнтервала,
МИНИМУМ(Концы.НомерСтроки) КАК КонецИнтервала
ИЗ
(ВЫБРАТЬ НомерСтроки ИЗ ВТ_АнализГраниц ГДЕ ПредНомер = 0) КАК Начала
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ НомерСтроки ИЗ ВТ_АнализГраниц ГДЕ СледНомер = 0) КАК Концы
ПО Начала.НомерСтроки <= Концы.НомерСтроки
СГРУППИРОВАТЬ ПО
Начала.НомерСтроки;
Важный нюанс: Этот метод использует так называемое «Тэта-соединение» (соединение по неравенству) на финальном этапе. На очень больших объемах данных (десятки тысяч строк в интервалах) это может привести к снижению производительности, так как СУБД вынуждена сравнивать каждую точку начала с каждой точкой конца.
Рассмотрим более производительный метод, который часто называют методом поиска идентификатора группы. Если мы для каждой строки с флагом Истина найдем максимальный номер строки, которая меньше текущей и имеет флаг Ложь, то этот номер будет одинаковым для всех строк одного непрерывного интервала.
Разберем алгоритм:
тмпВыборкаИнтервалов со всеми записями и индексируем её.Истина ищем МАКСИМУМ(НомерСтроки) среди записей, где флаг Ложь и номер меньше текущего.ID_Группы) используем для группировки.Посмотрим на пример кода:
ВЫБРАТЬ
Т1.НомерСтроки КАК НомерСтроки,
МАКСИМУМ(Т2.НомерСтроки) КАК НомерСтрокиДоИнтервала
ПОМЕСТИТЬ ВТ_СтрокиСГруппировкой
ИЗ
РегистрСведений.ВашРегистр КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ВашРегистр КАК Т2
ПО Т2.Флаг = ЛОЖЬ И Т1.НомерСтроки > Т2.НомерСтроки
ГДЕ
Т1.Флаг = ИСТИНА
СГРУППИРОВАТЬ ПО
Т1.НомерСтроки;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МИНИМУМ(ВТ.НомерСтроки) КАК НачалоИнтервала,
МАКСИМУМ(ВТ.НомерСтроки) КАК КонецИнтервала
ИЗ
ВТ_СтрокиСГруппировкой КАК ВТ
ИТОГИ ПО
НомерСтрокиДоИнтервала;
Этот способ элегантен тем, что использует стандартную агрегацию. Поле НомерСтрокиДоИнтервала выступает уникальным ключом для каждого «острова» истинных значений.
Если ваша конфигурация работает на современной версии платформы (8.3.20 и выше), мы можем применить наиболее эффективный метод — разность порядковых номеров (метод Tabibitosan).
Проанализируем математическую логику: если мы пронумеруем все строки подряд (1, 2, 3...) и отдельно пронумеруем только «истинные» строки, то разность между общим номером и специфическим номером будет постоянной внутри одного интервала.
Выясним причину на примере:
Как видим, группы 9 и 16 четко разделяют наши интервалы.
ВЫБРАТЬ
Т.НомерСтроки,
Т.НомерСтроки - ПОРЯДКОВЫЙ_НОМЕР() OVER (УПОРЯДОЧИТЬ ПО Т.НомерСтроки) КАК ГруппаID
ПОМЕСТИТЬ ВТ_Острова
ИЗ
РегистрСведений.ВашРегистр КАК Т
ГДЕ
Т.Флаг = ИСТИНА;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МИНИМУМ(НомерСтроки) КАК Начало,
МАКСИМУМ(НомерСтроки) КАК Конец
ИЗ
ВТ_Острова
СГРУППИРОВАТЬ ПО
ГруппаID;
Ситуация значительно усложняется, если вместо простого номера строки мы имеем дело с техническими параметрами, такими как PCR (Program Clock Reference) в видеопотоках. Особенность PCR в том, что это 33-битное число, которое при достижении максимума сбрасывается в ноль.
Для корректной обработки таких интервалов в запросе нужно учитывать «переход через ноль». Мы можем это сделать, проверяя разницу между текущим и предыдущим значением. Если разница отрицательная и велика по модулю, значит, произошел сброс счетчика.
Разберем пример обработки такого перехода:
ВЫБРАТЬ
Т1.НомерСтроки,
ВЫБОР
КОГДА Т2.PCR - Т1.PCR > 100000000 ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК ПереходЧерезНоль
ПОМЕСТИТЬ ВТ_Переходы
ИЗ
тмпРС КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ тмпРС КАК Т2 ПО Т1.НомерСтроки = Т2.НомерСтроки - 1;
В дальнейшем количество накопленных переходов (через функцию СУММА или КОЛИЧЕСТВО) добавляется к логике формирования интервалов, чтобы «склеить» последовательность, разорванную обнулением регистра.
Подведем итог нашему анализу:
Используя эти подходы, вы сможете эффективно решать задачи поиска последовательностей и интервалов, обеспечивая высокую скорость работы ваших отчетов и обработок.