Как объединить таблицы плановых и фактических дат в запросе 1С без дублирования?

← На главную

При разработке отчетов, сопоставляющих плановые и фактические данные (например, график отпусков Т-7, планирование производства или выполнения задач), программисты часто сталкиваются с проблемой связи двух независимых таблиц по одному сотруднику или объекту. Если связать их обычным левым или полным соединением по полю Сотрудник, мы получим декартово произведение. На каждую плановую дату будут выведены абсолютно все фактические даты сотрудника, что приведет к лавинообразному дублированию строк.

Если же использовать ОБЪЕДИНИТЬ ВСЕ, данные выведутся «лесенкой»: сначала строки с плановыми датами и пустыми фактическими, а ниже — строки с фактическими датами и пустыми плановыми. Нам же требуется получить сопоставленные «один к одному» строки плана и факта.

В этой статье мы подробно разберем несколько эффективных способов решения этой задачи на уровне запросов 1С, встроенного языка и системы компоновки данных (СКД).

Причина проблемы: почему стандартные соединения не работают

Рассмотрим ситуацию: у сотрудника С1 есть два плановых отпуска (План1, План2) и два фактических (Факт1, Факт2). Прямой связи между конкретным планом и конкретным фактом в базе данных нет.

При соединении таблиц только по полю Сотрудник СУБД сопоставит каждую запись из левой таблицы с каждой записью из правой. На выходе мы получим 4 строки:

С1 | План1 | Факт1
С1 | План1 | Fact2
С1 | План2 | Факт1
С1 | План2 | Факт2

Это в корне неверно. Нам нужно сопоставить первую запись плана с первой записью факта, а вторую — со второй, получив всего 2 строки.

---

Способ 1. Динамическая нумерация строк в запросе и полное соединение

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

Выясним, как работает этот алгоритм по шагам:

Создаем временную таблицу для плановых данных и нумеруем ее строки для каждого сотрудника.
Создаем временную таблицу для фактических данных и аналогично нумеруем ее.
Связываем полученные таблицы через ПОЛНОЕ СОЕДИНЕНИЕ по полям Сотрудник и НомерСтроки.

Посмотрим на пример реализации эмуляции нумерации строк:


// Нумерация строк для плановой таблицы
ВЫБРАТЬ
    ВТДанныеПлан.Сотрудник КАК Сотрудник,
    ВТДанныеПлан.ДатаНачалаПлан КАК ДатаНачалаПлан,
    ВТДанныеПлан.ДатаОкончанияПлан КАК ДатаОкончанияПлан,
    ВТДанныеПлан.КолвоДнейПлан КАК КолвоДнейПлан,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТДанныеПланДубль.ДатаНачалаПлан) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ПланПронумерованная
ИЗ
    ВТДанныеПлан КАК ВТДанныеПлан
    ЛЕВОЕ СОЕДИНЕНИЕ ВТДанныеПлан КАК ВТДанныеПланДубль
        ПО ВТДанныеПлан.Сотрудник = ВТДанныеПланДубль.Сотрудник
        И ВТДанныеПлан.ДатаНачалаПлан >= ВТДанныеПланДубль.ДатаНачалаПлан
СГРУППИРОВАТЬ ПО
    ВТДанныеПлан.Сотрудник,
    ВТДанныеПлан.ДатаНачалаПлан,
    ВТДанныеПлан.ДатаОкончанияПлан,
    ВТДанныеПлан.КолвоДнейПлан
;

// Аналогично нумеруем фактическую таблицу
ВЫБРАТЬ
    ВТДанныеФакт.Сотрудник КАК Сотрудник,
    ВТДанныеФакт.ДанаНачалаФакт КАК ДанаНачалаФакт,
    ВТДанныеФакт.ДатаОкончанияФакт КАК ДатаОкончанияФакт,
    ВТДанныеФакт.КолвоДнейФакт КАК КолвоДнейФакт,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТДанныеФактДубль.ДанаНачалаФакт) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ФактПронумерованная
ИЗ
    ВТДанныеФакт КАК ВТДанныеФакт
    ЛЕВОЕ СОЕДИНЕНИЕ ВТДанныеФакт КАК ВТДанныеФактДубль
        ПО ВТДанныеФакт.Сотрудник = ВТДанныеФактДубль.Сотрудник
        И ВТДанныеФакт.ДанаНачалаФакт >= ВТДанныеФактДубль.ДанаНачалаФакт
СГРУППИРОВАТЬ ПО
    ВТДанныеФакт.Сотрудник,
    ВТДанныеФакт.ДанаНачалаФакт,
    ВТДанныеФакт.ДатаОкончанияФакт,
    ВТДанныеФакт.КолвоДнейФакт
;

// Соединяем по сотруднику и порядковому номеру
ВЫБРАТЬ
    ЕСТЬNULL(План.Сотрудник, Факт.Сотрудник) КАК Сотрудник,
    План.ДатаНачалаПлан,
    План.ДатаОкончанияПлан,
    План.КолвоДнейПлан,
    Факт.ДанаНачалаФакт,
    Факт.ДатаОкончанияФакт,
    Факт.КолвоДнейФакт
ИЗ
    ВТ_ПланПронумерованная КАК План
    ПОЛНОЕ СОЕДИНЕНИЕ ВТ_ФактПронумерованная КАК Факт
        ПО План.Сотрудник = Факт.Сотрудник
        И План.НомерСтроки = Факт.НомерСтроки

Важное ограничение: данный метод имеет квадратичную сложность $O(N^2)$ при соединении таблицы «самой с собой» для подсчета строк. Если по одному сотруднику в выборку попадает большое число записей (сотни или тысячи), запрос может выполняться медленно. Для отпусков или графиков работы, где у одного человека обычно не более десятка записей за период, этот способ работает отлично.

---

Способ 2. Развертка по дням через производственный календарь

Рассмотрим более детальное решение, которое позволяет избежать проблем с наложением периодов. Мы можем «развернуть» плановые и фактические отпуска по календарным дням, пронумеровать каждый день отпуска по порядку, соединить их по номеру дня и затем свернуть обратно в смежные периоды.

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


// Допустим, у нас есть исходные таблицы План и Факт
// Шаг 1. Разворачиваем плановые периоды по дням
ВЫБРАТЬ
    План.Сотрудник,
    План.Начало,
    Календарь.ДатаКалендаря КАК День
ПОМЕСТИТЬ ПланДни
ИЗ
    План КАК План
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
        ПО Календарь.ДатаКалендаря МЕЖДУ План.Начало И План.Окончание
;

// Шаг 2. Разворачиваем фактические периоды по дням
ВЫБРАТЬ
    Факт.Сотрудник,
    Факт.Начало,
    Календарь.ДатаКалендаря КАК День
ПОМЕСТИТЬ   ФактДни
ИЗ
    Факт КАК Факт
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
        ПО Календарь.ДатаКалендаря МЕЖДУ Факт.Начало И Факт.Окончание
;

// Шаг 3. Нумеруем каждый день отпуска для сотрудника в плане
ВЫБРАТЬ
    План.Сотрудник,
    План.Начало,
    План.День,
    КОЛИЧЕСТВО(Прошлое.День) КАК НомерДня
ПОМЕСТИТЬ ПланПлюс
ИЗ
    ПланДни КАК План
    ЛЕВОЕ СОЕДИНЕНИЕ ПланДни КАК Прошлое
        ПО План.Сотрудник = Прошлое.Сотрудник
        И План.День >= Прошлое.День
СГРУППИРОВАТЬ ПО
    План.Сотрудник,
    План.Начало,
    План.День
;

// Шаг 4. Нумеруем каждый день фактического отпуска
ВЫБРАТЬ
    Факт.Сотрудник,
    Факт.Начало,
    Факт.День,
    КОЛИЧЕСТВО(Прошлое.День) КАК НомерДня
ПОМЕСТИТЬ ФактПлюс
ИЗ
    ФактДни КАК Факт
    ЛЕВОЕ СОЕДИНЕНИЕ  ФактДни КАК Прошлое
        ПО Факт.Сотрудник = Прошлое.Сотрудник
        И  Факт.День >= Прошлое.День
СГРУППИРОВАТЬ ПО
    Факт.Сотрудник,
    Факт.Начало,
    Факт.День
;

// Шаг 5. Объединяем день в день и группируем по периодам обратно
ВЫБРАТЬ
    ПланПлюс.Сотрудник КАК СотрудникПлан,
    МИНИМУМ(ПланПлюс.День) КАК НачалоПлан,
    МАКСИМУМ(ПланПлюс.День) КАК ОкончаниеПлан,
    ФактПлюс.Сотрудник КАК СотрудникФакт,
    МИНИМУМ(ФактПлюс.День) КАК НачалоФакт,
    МАКСИМУМ(ФактПлюс.День) КАК ОкончаниеФакт,
    ЕСТЬNULL(КОЛИЧЕСТВО(ПланПлюс.День), КОЛИЧЕСТВО(ФактПлюс.День)) КАК Длина
ИЗ
    ПланПлюс КАК ПланПлюс
    ПОЛНОЕ СОЕДИНЕНИЕ ФактПлюс КАК ФактПлюс
        ПО ПланПлюс.Сотрудник =   ФактПлюс.Сотрудник
        И ПланПлюс.НомерДня =   ФактПлюс.НомерДня
СГРУППИРОВАТЬ ПО
    ПланПлюс.Сотрудник,
    ФактПлюс.Сотрудник,
    ПланПлюс.Начало,
    ФактПлюс.Начало
УПОРЯДОЧИТЬ ПО
    СотрудникПлан,
    НачалоПлан,
    НачалоФакт

Такой подход гарантирует сопоставление стык-в-стык по количеству дней, корректно отрабатывая ситуации, когда плановые и фактические интервалы частично пересекаются или сдвинуты по календарю.

---

Способ 3. Программная обработка результатов через Таблицу Значений

Если запросы получаются избыточно сложными или база данных испытывает сильные нагрузки при вычислении связей "многие-ко-многим", мы можем перенести логику сопоставления на встроенный язык 1С.

Разберем алгоритм программного сведения:

Выполняем два простых и быстрых запроса: первый получает плановые данные, второй — фактические.
Выгружаем результаты в две таблицы значений (ТЗ_План и ТЗ_Факт).
Добавляем в обе таблицы числовую колонку НомерСтроки.
Обходим таблицы в цикле и заполняем номера строк для каждого сотрудника сбросом счетчика при смене сотрудника.
Помещаем пронумерованные таблицы обратно в новый запрос как параметры и выполняем быстрое и легкое ПОЛНОЕ СОЕДИНЕНИЕ по полям Сотрудник и НомерСтроки.

Посмотрим на пример программного кода:


// Добавляем колонку для нумерации
ТЗ_План.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"));
ТЗ_Факт.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"));

// Сортируем таблицы для последовательного обхода
ТЗ_План.Сортировать("Сотрудник, ДатаНачалаПлан");
ТЗ_Факт.Сортировать("Сотрудник, ДатаНачалаФакт");

// Нумеруем строки плана
ТекущийСотрудник = Неопределено;
Счетчик = 0;
Для Каждого СтрокаПлана Из ТЗ_План Цикл
    Если СтрокаПлана.Сотрудник <> ТекущийСотрудник Тогда
        ТекущийСотрудник = СтрокаПлана.Сотрудник;
        Счетчик = 1;
    Иначе
        Счетчик = Счетчик + 1;
    КонецЕсли;
    СтрокаПлана.НомерСтроки = Счетчик;
КонецЦикла;

// Нумеруем строки факта
ТекущийСотрудник = Неопределено;
Счетчик = 0;
Для Каждого СтрокаФакта Из ТЗ_Факт Цикл
    Если СтрокаФакта.Сотрудник <> ТекущийСотрудник Тогда
        ТекущийСотрудник = СтрокаФакта.Сотрудник;
        Счетчик = 1;
    Иначе
        Счетчик = Счетчик + 1;
    КонецЕсли;
    СтрокаФакта.НомерСтроки = Счетчик;
КонецЦикла;

После этой подготовки таблицы можно передать в качестве параметров в новый запрос и соединить их по простым индексированным полям, что выполнится мгновенно.

---

Способ 4. Связи наборов данных в СКД

Если отчет строится в Системе компоновки данных (СКД), то всю ручную работу по связыванию строк за нас может выполнить сам процессор компоновки.

Сделаем следующие шаги в конструкторе схемы компоновки данных:

Создаем два независимых набора данных-запроса: один выбирает плановые показатели, второй — фактические. В обоих запросах обязательно должно быть поле связи Сотрудник.
Переходим на вкладку Связи наборов данных.
Добавляем новую строку связи: Источник связи — НаборДанныхПлан, Приемник связи — НаборДанныхФакт.
Указываем выражение источника: Сотрудник, выражение приемника: Сотрудник.

При формировании отчета СКД самостоятельно выполнит параллельный обход и выведет данные плана и факта в строки в рамках группировки по сотруднику без лишнего дублирования данных.

---