При разработке отчетов и получении данных в 1С программисты часто сталкиваются с классической проблемой СУБД — задвоением (дублированием) сумм при соединении таблиц «один ко многим». Типичный пример: у нас есть таблица доходов, детализированная по сотрудникам (ФИО) и КПП, и таблица расходов, где данные ведутся только в разрезе сотрудников (ФИО), без указания КПП.
Если попытаться соединить эти таблицы простым левым соединением по полю ФИО, то сумма расходов привяжется к каждой строке доходов сотрудника. В итоге общие расходы по человеку увеличатся пропорционально количеству его КПП. В этой статье мы подробно разберем, почему это происходит, и рассмотрим три варианта решения этой задачи в языке запросов 1С, включая обработку сложных случаев и пропорциональное распределение.
Рассмотрим исходные данные, которые предоставил автор темы.
Таблица «Доходы»:
Таблица «Расходы»:
Автор пытается выполнить левое соединение по полю ФИО. Проанализируем, к какому результату это приводит для Иванова. Поскольку у Иванова две записи в доходах (КПП1 и КПП2), СУБД для каждой из этих строк находит соответствие в таблице расходов (где у Иванова расход 150 руб.). В результате формируются две строки:
Если мы просуммируем расходы по Иванову в итоговом отчете, то получим 300 рублей вместо фактических 150 рублей. Это грубейшая ошибка в расчетах.
Наша цель — получить следующую результирующую таблицу:
---
Рассмотрим и разберем по шагам первое рабочее решение, предложенное на форуме. Идея заключается в том, чтобы искусственно выбрать для каждого сотрудника только один основной КПП, на который мы «повесим» всю сумму его расходов. Для этого отлично подойдет агрегатная функция МАКСИМУМ.
Разберем алгоритм по шагам:
ВТ1, в которой группируем таблицу доходов по сотрудникам и находим для каждого максимальный КПП.ВТ2, в которой соединяем наш «основной» КПП с доходами и расходами. В эту таблицу попадут только те строки доходов, которые соответствуют максимальному КПП, и к ним корректно привяжется вся сумма расходов.ОБЪЕДИНИТЬ ВСЕ) данные из ВТ2 (где лежат доходы по первому КПП с расходами) и строки из исходной таблицы доходов, КПП которых не вошли в ВТ2 (у этих строк расход будет равен нулю).Посмотрим на пример реализации этого запроса:
// Шаг 1: Определяем один "основной" КПП для каждого сотрудника
ВЫБРАТЬ
Доходы.ФИО КАК ФИО,
МАКСИМУМ(Доходы.КПП) КАК КПП
ПОМЕСТИТЬ ВТ1
ИЗ
Доходы КАК Доходы
СГРУППИРОВАТЬ ПО
Доходы.ФИО
;
// Шаг 2: Связываем основной КПП с доходами и расходами
ВЫБРАТЬ
ВТ1.ФИО КАК ФИО,
ВТ1.КПП КАК КПП,
ЕСТЬNULL(Доходы.Сумма, 0) КАК Доход,
ЕСТЬNULL(Расходы.Сумма, 0) КАК Расход
ПОМЕСТИТЬ ВТ2
ИЗ
ВТ1 КАК ВТ1
ЛЕВОЕ СОЕДИНЕНИЕ Доходы КАК Доходы
ПО ВТ1.ФИО = Доходы.ФИО
И ВТ1.КПП = Доходы.КПП
ЛЕВОЕ СОЕДИНЕНИЕ Расходы КАК Расходы
ПО ВТ1.ФИО = Расходы.ФИО
;
// Шаг 3: Объединяем полученный результат с остальными строками доходов
ВЫБРАТЬ
ВТ2.ФИО КАК ФИО,
ВТ2.КПП КАК КПП,
ВТ2.Доход КАК Доход,
ВТ2.Расход КАК Расход
ИЗ
ВТ2 КАК ВТ2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Доходы.ФИО,
Доходы.КПП,
Доходы.Сумма,
0 // Расход для неосновных КПП равен нулю
ИЗ
Доходы КАК Доходы
ГДЕ
НЕ (Доходы.ФИО, Доходы.КПП) В (ВЫБРАТЬ ВТ2.ФИО, ВТ2.КПП ИЗ ВТ2 КАК ВТ2)
И НЕ Доходы.Сумма = 0
Важное примечание по синтаксису: Обратите внимание на условие в последней части запроса: НЕ (Доходы.ФИО, Доходы.КПП) В (...). Платформа 1С поддерживает сравнение кортежей (нескольких полей одновременно), что позволяет эффективно отсечь те строки, которые мы уже обработали во второй временной таблице.
---
В конце обсуждения на форуме прозвучал очень важный вопрос: «А если в доходах человека нет (или его доход равен 0, и мы его отсекли), а в расходах он есть?». Например, наш Петров имеет доход 0 рублей (строка отсекается фильтром), но у него есть расход в размере 200 рублей.
Если мы воспользуемся Решением №1, то Петров просто исчезнет из финального отчета, так как первая временная таблица ВТ1 строится строго по таблице «Доходы». Чтобы избежать потери данных, нам необходимо применить механизм ПОЛНОЕ СОЕДИНЕНИЕ (FULL JOIN).
Проанализируем универсальный алгоритм запроса:
ВТ_Доходы.ВТ_ЦелевойКПП.КПП примет значение NULL (или пустой ссылки/строки).ФИО и КПП.Посмотрим на практическую реализацию этого универсального запроса:
// Шаг 1: Исключаем записи с нулевыми доходами
ВЫБРАТЬ
Доходы.ФИО КАК ФИО,
Доходы.КПП КАК КПП,
Доходы.Сумма КАК Сумма
ПОМЕСТИТЬ ВТ_Доходы
ИЗ
Доходы КАК Доходы
ГДЕ
НЕ Доходы.Сумма = 0
;
// Шаг 2: Находим основной КПП для распределения расходов
ВЫБРАТЬ
ВТ_Доходы.ФИО КАК ФИО,
МАКСИМУМ(ВТ_Доходы.КПП) КАК КПП
ПОМЕСТИТЬ ВТ_ЦелевойКПП
ИЗ
ВТ_Доходы КАК ВТ_Доходы
СГРУППИРОВАТЬ ПО
ВТ_Доходы.ФИО
;
// Шаг 3: Связываем расходы с целевыми КПП.
// Использование ПОЛНОГО СОЕДИНЕНИЯ гарантирует, что Петров (у которого нет доходов) сохранится в выборке.
ВЫБРАТЬ
ЕСТЬNULL(ВТ_ЦелевойКПП.ФИО, Расходы.ФИО) КАК ФИО,
ВТ_ЦелевойКПП.КПП КАК КПП,
ЕСТЬNULL(Расходы.Сумма, 0) КАК Расход
ПОМЕСТИТЬ ВТ_ЦелевойКПП_СРасходами
ИЗ
ВТ_ЦелевойКПП КАК ВТ_ЦелевойКПП
ПОЛНОЕ СОЕДИНЕНИЕ Расходы КАК Расходы
ПО ВТ_ЦелевойКПП.ФИО = Расходы.ФИО
;
// Шаг 4: Сборка итоговой картины
ВЫБРАТЬ
ЕСТЬNULL(ВТ_Доходы.ФИО, ВТ_ЦелевойКПП_СРасходами.ФИО) КАК ФИО,
ЕСТЬNULL(ВТ_Доходы.КПП, ВТ_ЦелевойКПП_СРасходами.КПП) КАК КПП,
ЕСТЬNULL(ВТ_Доходы.Сумма, 0) КАК Доход,
ЕСТЬNULL(ВТ_ЦелевойКПП_СРасходами.Расход, 0) КАК Расход
ИЗ
ВТ_Доходы КАК ВТ_Доходы
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_ЦелевойКПП_СРасходами КАК ВТ_ЦелевойКПП_СРасходами
ПО ВТ_Доходы.ФИО = ВТ_ЦелевойКПП_СРасходами.ФИО
И ВТ_Доходы.КПП = ВТ_ЦелевойКПП_СРасходами.КПП
Этот запрос выдаст абсолютно корректный результат:
NULL, доход будет равен 0, а расход составит 200.---
Рассмотрим ситуацию с точки зрения реального финансового или управленческого учета. Относить всю сумму расхода на один случайный (или максимальный) КПП методически не всегда верно. Бизнес-пользователи часто требуют распределить сумму расходов пропорционально полученным доходам по каждому КПП.
Выясним, как должна распределиться сумма расходов для Иванова при таком подходе:
Для реализации этой логики нам потребуется рассчитать общую сумму доходов по каждому человеку во временной таблице, а затем использовать эту сумму как делитель при расчете расхода. Проанализируем пример такого запроса:
// Шаг 1: Считаем общий доход каждого сотрудника
ВЫБРАТЬ
Доходы.ФИО КАК ФИО,
СУММА(Доходы.Сумма) КАК ВсегоДоход
ПОМЕСТИТЬ ВТ_ОбщийДоход
ИЗ
Доходы КАК Доходы
СГРУППИРОВАТЬ ПО
Доходы.ФИО
;
// Шаг 2: Распределяем расходы пропорционально долям доходов
ВЫБРАТЬ
Доходы.ФИО КАК ФИО,
Доходы.КПП КАК КПП,
Доходы.Сумма КАК Доход,
ВЫБОР
КОГДА ЕСТЬNULL(ВТ_ОбщийДоход.ВсегоДоход, 0) = 0 ТОГДА 0
ИНАЧЕ ВЫРАЗИТЬ(ЕСТЬNULL(Расходы.Сумма, 0) * Доходы.Сумма / ВТ_ОбщийДоход.ВсегоДоход КАК ЧИСЛО(15, 2))
КОНЕЦ КАК Расход
ИЗ
Доходы КАК Доходы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОбщийДоход КАК ВТ_ОбщийДоход
ПО Доходы.ФИО = ВТ_ОбщийДоход.ФИО
ЛЕВОЕ СОЕДИНЕНИЕ Расходы КАК Расходы
ПО Доходы.ФИО = Расходы.ФИО
Важные детали реализации:
ВЫБОР КОГДА ... ТОГДА ... страхует СУБД от критической ошибки деления на ноль, если общий доход сотрудника окажется равным 0.ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 2)) используется для округления результата деления до копеек.ТаблицаЗначений.Мы подробно разобрали три различных способа сопоставления разнородных данных. Выбирайте подходящий вариант исходя из специфики ведения учета и требований заказчика к вашему отчету!