При разработке сложных отчетов, особенно в сфере ЖКХ или при расчете заработной платы, программисты часто сталкиваются с задачей сопоставления данных разной степени детализации. Типичный пример: у нас есть начальное сальдо (общая сумма задолженности на текущий момент) и расшифровка начислений по месяцам. Нам необходимо получить отчет, где напротив каждой строки начисления будет стоять актуальное сальдо, либо «размножить» общую задолженность по всем периодам для корректного отображения в печатной форме.
В этой статье мы подробно разберем, как правильно связать временные таблицы, если в одной из них отсутствует поле Период, проанализируем ошибки использования простого объединения и рассмотрим наиболее эффективные способы решения задачи через соединения запросов.
Рассмотрим структуру наших данных на примере трех временных таблиц (ВТ):
ЛицевойСчет, ВидРасчета и НСальдо (сумма). Поля периода здесь нет, так как это итоговая сумма.ЛицевойСчет, ВидРасчета, Начислено и Период (например, начало месяца).Период.Основная сложность заключается в том, что при попытке использовать оператор ОБЪЕДИНИТЬ ВСЕ, данные из ВТ1 попадут в итоговую таблицу с пустым периодом (NULL), а данные из ВТ2 и ВТ3 — со своими датами. В итоге вместо красивой таблицы мы получим разрозненные строки, которые невозможно адекватно сгруппировать по периоду, не потеряв при этом сумму сальдо.
Если мы просто напишем ОБЪЕДИНИТЬ, указав во второй и третьей таблицах поле Период, а в первой подставив NULL или ДАТАВРЕМЯ(1, 1, 1), система создаст отдельные записи. Группировка по периоду «схлопнет» только те записи, где периоды совпадают. Запись с сальдо останется «висеть» в отдельной строке с пустым периодом.
Как справедливо отмечено в обсуждении, если мы хотим видеть сальдо в каждой строке начисления, нам нужно использовать соединение (JOIN), а не объединение (UNION).
Этот способ подходит, если нам нужно «приклеить» общую сумму задолженности к каждой строке начисления. Мы берем таблицу с начислениями за основу и присоединяем к ней сальдо по тем полям, которые у них общие (ЛицевойСчет и ВидРасчета).
Рассмотрим пример кода такого запроса:
ВЫБРАТЬ
ВТ_Начисления.ЛицевойСчет,
ВТ_Начисления.ВидРасчета,
ВТ_Начисления.Период КАК ПериодЗадолженности,
ВТ_Начисления.Начислено,
ЕСТЬNULL(ВТ_Сальдо.НСальдо, 0) КАК НСальдо
ИЗ
ВТ2 КАК ВТ_Начисления
ЛЕВОЕ СОЕДИНЕНИЕ ВТ1 КАК ВТ_Сальдо
ПО ВТ_Начисления.ЛицевойСчет = ВТ_Сальдо.ЛицевойСчет
И ВТ_Начисления.ВидРасчета = ВТ_Сальдо.ВидРасчета
В данном случае сумма НСальдо будет продублирована для каждого периода, который есть в таблице начислений. Это именно то «размножение» данных, о котором шла речь в задаче.
Иногда ситуация сложнее: сальдо может существовать по услуге, по которой в конкретном месяце не было начислений. Если мы используем только левое соединение от таблицы начислений, мы потеряем информацию о сальдо в те месяцы, где начислено 0. Чтобы этого избежать, нужно сначала подготовить «сетку» всех возможных периодов.
Разберем этот алгоритм по шагам:
Период из всех таблиц (ВТ2, ВТ3).Посмотрим на пример формирования такой сетки:
// Сначала соберем все периоды
ВЫБРАТЬ РАЗЛИЧНЫЕ
Период
ПОМЕСТИТЬ ВТ_Периоды
ИЗ (
ВЫБРАТЬ Период ИЗ ВТ2
ОБЪЕДИНИТЬ
ВЫБРАТЬ Период ИЗ ВТ3
) КАК Объединение;
// Теперь "размножаем" аналитику по периодам
ВЫБРАТЬ
ВТ1.ЛицевойСчет,
ВТ1.ВидРасчета,
ВТ_Периоды.Период
ПОМЕСТИТЬ ВТ_АналитикаПоПериодам
ИЗ
ВТ1 КАК ВТ1
СОЕДИНЕНИЕ ВТ_Периоды КАК ВТ_Периоды
ПО (ИСТИНА); // Декартово произведение (Cross Join)
После создания такой структуры мы можем левым соединением подтянуть суммы начислений и сальдо. В итоге мы получим записи по каждому виду расчета в каждом периоде, даже если там не было движений.
При работе с подобными запросами крайне важно учитывать несколько моментов, которые влияют на производительность и корректность данных:
1. Использование функции ЕСТЬNULL. При левом соединении, если в одной из таблиц нет соответствующей записи, запрос вернет NULL. Для числовых полей это критично, так как 100 + NULL = NULL. Всегда оборачивайте суммируемые поля: ЕСТЬNULL(Сумма, 0).
2. Индексирование временных таблиц. Если в ваших таблицах тысячи лицевых счетов, соединения будут работать медленно. Обязательно добавляйте индексацию по полям связи:
ПОМЕСТИТЬ ВТ1
ИЗ ...
ИНДЕКСИРОВАТЬ ПО
ЛицевойСчет,
ВидРасчета
3. Проблема задвоения итогов в СКД. Помните, что «размножив» сальдо по периодам, вы рискуете получить неверные общие итоги в отчете. Например, если сальдо 7 000 руб. вывести в три строки по разным месяцам, стандартный итог в подвале отчета покажет 21 000 руб. Для решения этой проблемы в Системе Компоновки Данных (СКД) необходимо правильно настроить Роли полей. Для поля сальдо нужно указать роль «Остаток» и привязать его к полю периода. Тогда СКД будет суммировать начисления, но брать последнее или первое значение сальдо, не допуская арифметических ошибок.
Если ваша задача подразумевает, что задолженность должна меняться от месяца к месяцу (как упоминалось в сообщении 16: «задолженность увеличится в следующем периоде»), вам потребуется расчет нарастающего итога. В языке запросов 1С это реализуется через соединение таблицы с самой собой по условию Т1.Период <= Т2.Период.
Проанализируем пример логики:
ВЫБРАТЬ
Т1.ЛицевойСчет,
Т1.Период КАК Период,
СУММА(Т2.Начислено) КАК НачисленоНарастающимИтогом
ИЗ
ВТ_Данные КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
ПО Т1.ЛицевойСчет = Т2.ЛицевойСчет
И Т1.Период >= Т2.Период
СГРУППИРОВАТЬ ПО
Т1.ЛицевойСчет,
Т1.Период
Добавив к этому результату начальное сальдо из ВТ1, вы получите корректную картину изменения задолженности в динамике по каждому месяцу.
Для правильного объединения таблиц с периодом и без него мы выяснили следующее:
ОБЪЕДИНИТЬ не подходит для «размножения» данных — оно лишь добавляет новые строки.ЛЕВОЕ СОЕДИНЕНИЕ, если нужно привязать общие данные к существующим периодам.NULL с помощью ЕСТЬNULL().ЛицевойСчет и ВидРасчета для ускорения работы запроса.Применяя эти подходы, вы сможете строить сложные аналитические отчеты, корректно сопоставляя остатки и обороты в любых разрезах аналитики.