Как реализовать сопоставление плана и факта отгрузок одним запросом по методу FIFO

В практике разработки на 1С и работы с SQL часто возникает задача сопоставления двух потоков данных. Например, когда у нас есть график плановых отгрузок и таблица фактических отгрузок, и нам нужно понять, какая фактическая поставка закрыла конкретную плановую позицию. Ситуация осложняется тем, что одна плановая отгрузка может закрываться несколькими фактическими, а одна фактическая — покрывать остаток предыдущего плана и начало следующего. Разберем, как решить эту задачу изящно, используя один запрос и математику накопленных итогов.

Математическая суть решения: метод интервалов

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

1. Для каждой строки плана мы вычисляем «границы»: от какого количества (сумма предыдущих строк) до какого количества (сумма текущей и предыдущих строк) она действует.
2. Аналогичные границы мы рассчитываем для каждой строки факта.
3. Затем мы соединяем эти таблицы. Условием соединения будет пересечение интервалов.
4. Количество, которое относится к конкретной паре «план-факт», вычисляется как разница между минимальной верхней границей и максимальной нижней границей этой пары.

Такой подход гарантирует, что мы распределим весь факт по плановым строкам строго в хронологическом порядке, соблюдая принцип FIFO (First In, First Out).

Реализация задачи на языке SQL

Посмотрим на пример реализации, предложенный в обсуждении. Для решения используются обобщенные табличные выражения (CTE), которые позволяют подготовить данные перед основным сопоставлением. Проанализируем структуру запроса, где мы сначала формируем временные таблицы для плана и факта:

WITH vt_plan as (
    SELECT 1 as tov, 1 as doc, DATETIME('2023-01-15') as DateX, 10 as kol 
    UNION ALL SELECT 1, 1, DATETIME('2023-01-30'), 15
),
vt_fakt as (
    SELECT 1 as tov, 1 as doc, DATETIME('2023-01-16') as DateX, 4 as kol 
    UNION ALL SELECT 1, 1, DATETIME('2023-01-20'), 7 
    UNION ALL SELECT 1, 1, DATETIME('2023-01-22'), 2
)

В данном примере vt_plan и vt_fakt — это наши исходные данные. В реальной задаче 1С это будут временные таблицы или выборки из регистров. Основная магия происходит при расчете нарастающих итогов. В современных СУБД (и в 1С, начиная с версии 8.3.20) для этого используются оконные функции.

Особенности реализации в 1С: оконные функции против самосоединений

Выясним причину, почему раньше такие запросы называли «километровыми». До появления полноценной поддержки оконных функций в 1С (функция СУММА(...) ОБЪЕДИНИТЬ УПОРЯДОЧИТЬ ПО...), программистам приходилось считать нарастающий итог через соединение таблицы самой с собой по условию Т1.МоментВремени <= Т2.МоментВремени. Это создавало огромную нагрузку на сервер (сложность $O(n^2)$).

Разберем, как выглядит современный подход в 1С. Мы используем объект Запрос, где для получения границ интервалов применяем оконные функции:

ВЫБРАТЬ
    Т.Товар,
    Т.Количество,
    СУММА(Т.Количество) НАД(ПОРЯДОК ПО Т.Дата) КАК КонецИнтервала,
    СУММА(Т.Количество) НАД(ПОРЯДОК ПО Т.Дата) - Т.Количество КАК НачалоИнтервала
ИЗ
    ВТ_План КАК Т

Аналогично подготавливаем таблицу факта. После этого выполняем ВНУТРЕННЕЕ СОЕДИНЕНИЕ (или ПОЛНОЕ СОЕДИНЕНИЕ, если нужно видеть отклонения) по условию пересечения интервалов:

План.НачалоИнтервала < Факт.КонецИнтервала И План.КонецИнтервала > Факт.НачалоИнтервала

Обработка «подлянок» и краевых случаев

В процессе обсуждения была справедливо замечена проблема: что делать, если факт превышает план? Если мы просто соединим таблицы, «лишний» факт может просто исчезнуть из отчета, что недопустимо для аналитики.

Рассмотрим решение проблемы превышения:

1. Виртуальная строка плана: Мы можем искусственно добавить к таблице плана строку с «бесконечным» количеством и очень большой датой (например, 31.12.3999). В этом случае весь факт, который не нашел своего реального плана, «приклеится» к этой технической строке. Это позволит увидеть сверхплановые отгрузки в отчете.
2. Использование FULL JOIN: Если мы используем полное соединение, то строки факта, не попавшие в интервалы плана, останутся в выборке с NULL в полях плана. Это отличный маркер для управленца: «отгрузили то, что не планировали».
3. Проверка на пустой факт: Если план есть, а отгрузки не было, LEFT JOIN оставит строку плана, заполнив поля факта значениями NULL. В поле КоличествоКРаспределению мы можем использовать конструкцию ЕСТЬNULL(Факт.Количество, 0).

Алгоритм «Двух указателей» как альтернатива

Проанализируем ситуацию, когда запрос может быть неэффективен. Если объем данных по одному заказу или товару исчисляется десятками тысяч строк, даже оконные функции могут замедлить систему. В этом случае мы рекомендуем рассмотреть программную постобработку. Мы выгружаем отсортированные план и факт в таблицу значений и проходим по ним одним циклом, используя два указателя (индекса). Это классический алгоритм с линейной сложностью $O(n)$, который в 1С часто работает быстрее сложных соединений в SQL.

Однако, для большинства задач отчетности, решение «одним запросом» является наиболее предпочтительным, так как оно позволяет использовать всю мощь движка компоновки данных (СКД) для дальнейшей группировки и фильтрации полученных сопоставленных строк.

Резюме

Мы выяснили, что задача сопоставления плана и факта успешно решается через расчет накопительных итогов. Главное — помнить о правильном расчете границ интервалов и всегда обрабатывать случаи, когда фактические данные выходят за рамки запланированных. Использование современных возможностей платформы 1С 8.3.20+ значительно упрощает написание таких запросов, делая их читаемыми и производительными.