Как в 1С получить запросом количество повторяющихся подряд записей?

← На главную

В практике разработки на платформе 1С:Предприятие нередко возникает задача классического анализа непрерывных последовательностей (известная в реляционных базах данных как Gaps and Islands — «пропуски и острова»). Например, требуется подсчитать количество выходов на работу или явок сотрудника подряд, определить непрерывный стаж, серию непрерывных оплат или количество последовательных срабатываний определенного статуса.

В обычном коде на встроенном языке такая задача легко решается простым обходом выборки в цикле. Однако часто требуется получить готовый результат непосредственно на уровне СУБД в одном запросе. Разберем, как реализовать подобную логику средствами языка запросов 1С, выясним особенности алгоритма и рассмотрим альтернативные подходы.

Постановка задачи и логика решения

Представим, что у нас есть упорядоченная по датам таблица с признаком состояния (например, явка сотрудника: ИСТИНА или ЛОЖЬ):

01.04 — Явка (1 раз подряд)
02.04 — Явка (2 раза подряд)
03.04 — Явка (3 раза подряд)
04.04 — Неявка (1 раз подряд)
05.04 — Явка (1 раз подряд)
06.04 — Явка (2 раза подряд)

Поскольку в языке запросов 1С отсутствуют стандартные оконные функции SQL (такие как ROW_NUMBER() OVER (...) или LAG()), определение начала текущей непрерывной серии строится на поиске даты последней смены состояния, предшествующей текущей строке. Для каждой текущей даты мы находим максимальную дату из прошлого, когда признак состояния отличался от текущего, и вычисляем разницу дней между текущей датой и этой точкой перелома.

Реализация с помощью одного запроса (самосоединение)

Рассмотрим наиболее компактный и элегантный вариант запроса, который выполняет самосоединение таблицы по условию «прошлых дат» и группировку:


ВЫБРАТЬ
    ДАТАВРЕМЯ(2017, 4, 1) КАК Дата, ИСТИНА КАК Явка
ПОМЕСТИТЬ Дано
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 2), ИСТИНА
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 3), ИСТИНА
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 4), ЛОЖЬ
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 5), ИСТИНА
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 6), ИСТИНА
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 7), ЛОЖЬ
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 8), ЛОЖЬ
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ДАТАВРЕМЯ(2017, 4, 9), ИСТИНА
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Дано.Дата КАК Дата,
    МИНИМУМ(Дано.Явка) КАК Явка,
    РАЗНОСТЬДАТ(
        ЕСТЬNULL(
            МАКСИМУМ(
                ВЫБОР
                    КОГДА Раньше.Явка <> Дано.Явка
                        ТОГДА Раньше.Дата
                КОНЕЦ
            ),
            ДОБАВИТЬКДАТЕ(МИНИМУМ(Раньше.Дата), ДЕНЬ, -1)
        ),
        Дано.Дата,
        ДЕНЬ
    ) КАК РазПодряд
ИЗ
    Дано КАК Дано
        ЛЕВОЕ СОЕДИНЕНИЕ Дано КАК Раньше
        ПО (Раньше.Дата <= Дано.Дата)
СГРУППИРОВАТЬ ПО
    Дано.Дата
УПОРЯДОЧИТЬ ПО
    Дано.Дата

Разберем по шагам, как работает это выражение:

Левое соединение: Для каждой записи из таблицы Дано присоединяются все записи Раньше с меньшей или равной датой.
Поиск границы островка: В конструкции МАКСИМУМ(ВЫБОР КОГДА Раньше.Явка <> Дано.Явка ТОГДА Раньше.Дата КОНЕЦ) мы находим самую свежую дату, когда состояние было противоположным.
Обработка первого периода: Если смены состояния еще не было (для самой первой серии записей), функция МАКСИМУМ() вернет NULL. С помощью ЕСТЬNULL() мы подставляем день, предшествующий самой первой дате выборки: ДОБАВИТЬКДАТЕ(МИНИМУМ(Раньше.Дата), ДЕНЬ, -1).
Вычисление счетчика: Функция РАЗНОСТЬДАТ считает разницу в днях между найденной границей и текущей датой, возвращая точный порядковый номер события внутри непрерывной цепочки.

Важные ограничения и краевые случаи

При использовании данного алгоритма в реальных проектах необходимо учитывать следующие технические нюансы:

Календарные разрывы: Расчет через РАЗНОСТЬДАТ(..., ДЕНЬ) корректен только в том случае, если в исходной таблице даты идут строго подряд без пропусков (сплошной календарь). Если между сменами есть пропущенные дни (выходные, праздники), РАЗНОСТЬДАТ вернет разницу в днях по календарю, а не количество фактических записей. В таких случаях перед расчетом необходимо пронумеровать строки непрерывным целочисленным рядом либо соединять данные с производственным календарем.
Аналитические разрезы (многомерность): Если расчет ведется одновременно по нескольким сотрудникам, подразделениям или договорам, в условие соединения ПО и в секцию СГРУППИРОВАТЬ ПО обязательно должно входить равенство по этим разрезам (например, Дано.Сотрудник = Раньше.Сотрудник), иначе данные разных объектов перемешаются.
Производительность: Соединение вида ПО Раньше.Дата <= Дано.Дата имеет квадратичную сложность O(N²). На выборках в десятки и сотни тысяч записей такой запрос вызовет высокую нагрузку на дисковую подсистему и базу tempdb.

Альтернативные способы решения

В зависимости от архитектуры задачи можно использовать более производительные альтернативы:

Обработка на встроенном языке 1С: Если данные все равно передаются в коллекцию или выводятся на форму, оптимальнее выполнить плоский упорядоченный запрос, а счетчик непрерывности рассчитать при линейном обходе ВыборкаИзРезультатаЗапроса со сложностью O(N).
Вычисляемые поля в СКД: При формировании отчетов расчет можно перенести на уровень Системы компоновки данных с помощью функции ВычислитьВыражение(), обращаясь к предыдущим строкам группировки без утяжеления исходного SQL-запроса.