При работе с данными в 1С часто возникает необходимость в сложных условиях агрегации, когда логика суммирования зависит не только от значений в текущей строке, но и от общего состояния всей выборки данных. Рассмотрим ситуацию, когда нам нужно получить сумму часов из табличной части документа. Условие следующее: если в табличной части ни в одной строке не заполнено поле «Подразделение», тогда мы должны просуммировать часы по всем строкам. Однако, если хотя бы в одной строке поле «Подразделение» заполнено, тогда суммировать нужно только часы тех строк, где «Подразделение» указано.
Такая задача требует особого подхода при построении запроса, так как стандартные агрегатные функции напрямую не позволяют реализовать логику, зависящую от общего наличия данных во всей выборке. Мы разберем два основных подхода к решению этой проблемы: использование пакетного запроса с временными таблицами и условную агрегацию внутри одной функции.
Прежде чем перейти к основному решению, давайте проясним, как правильно проверять заполненность ссылочных полей в запросах 1С. Это критически важный аспект, поскольку некорректная проверка может привести к неправильным результатам.
Для ссылочных типов данных (справочники, документы и т.д.): Если поле Подразделение имеет ссылочный тип, например, Справочник.ПодразделенияОрганизаций, то его незаполненное значение представляет собой так называемую «пустую ссылку» этого справочника. Для проверки на пустоту в запросе мы используем конструкцию ЗНАЧЕНИЕ(Справочник.ИмяСправочника.ПустаяСсылка).
Например, проверка на незаполненное подразделение будет выглядеть так:
ВЫБОР
КОГДА ТЧ.Подразделение = ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка)
ТОГДА Истина
ИНАЧЕ Ложь
КОНЕЦ
Или, для проверки на заполненность:
ВЫБОР
КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка)
ТОГДА Истина
ИНАЧЕ Ложь
КОНЕЦ
Именно этот подход был предложен и использован в ходе обсуждения для определения состояния поля Подразделение.
Для составных типов данных: Если реквизит может хранить значения разных типов (например, ссылка на справочник, число или строка), то незаполненное значение может быть не только пустой ссылкой, но и НЕОПРЕДЕЛЕНО. В таких случаях, помимо проверки на пустую ссылку для каждого возможного ссылочного типа, может потребоваться дополнительная проверка на НЕОПРЕДЕЛЕНО. Однако, для поля Подразделение, как правило, используется конкретный ссылочный тип, что упрощает проверку.
Помните, правильное определение типа поля и соответствующая проверка его заполненности — первый и самый важный шаг к корректному построению запроса.
Наиболее элегантным и производительным способом решения нашей задачи является использование пакетного запроса. Этот метод позволяет нам сначала определить общее условие (есть ли хоть одно заполненное подразделение во всей табличной части), а затем, основываясь на этом условии, выполнить условное суммирование. Мы разобьем задачу на два логических шага, каждый из которых будет реализован отдельным запросом в рамках одного пакета.
На первом шаге мы создадим временную таблицу, которая будет содержать всего одну строку и одно поле — флаг, указывающий, есть ли в нашей табличной части хотя бы одно заполненное подразделение. Мы используем агрегатную функцию МАКСИМУМ() в сочетании с условным оператором ВЫБОР КОГДА для достижения этой цели.
ВЫБРАТЬ
МАКСИМУМ(ВЫБОР КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка) ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК ЕстьЗаполненныеПодразделения
ПОМЕСТИТЬ ВТ_НаличиеЗаполненныхПодразделений
ИЗ
Документ.СводПутевыхЛистов.ПутевыеЛисты КАК ТЧ
;
Давайте подробно разберем, что происходит в этом запросе:
Документ.СводПутевыхЛистов.ПутевыеЛисты КАК ТЧ: Мы обращаемся к табличной части ПутевыеЛисты документа СводПутевыхЛистов. Это наш исходный набор данных.
ВЫБОР КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка) ТОГДА 1 ИНАЧЕ 0 КОНЕЦ: Для каждой строки табличной части мы проверяем, заполнено ли поле Подразделение. Если оно заполнено (т.е., не является пустой ссылкой), то выражение возвращает 1; в противном случае — 0.
МАКСИМУМ(...) КАК ЕстьЗаполненныеПодразделения: Функция МАКСИМУМ() применяется к полученным значениям 0 и 1. Если в любой из строк был хотя бы один 1 (что означает, что хотя бы одно подразделение было заполнено), то МАКСИМУМ() вернет 1. Если все значения были 0 (то есть, ни одно подразделение не было заполнено), то МАКСИМУМ() вернет 0. Таким образом, мы получаем один флаг, который отражает общее состояние всех строк.
ПОМЕСТИТЬ ВТ_НаличиеЗаполненныхПодразделений: Результат этого запроса (одна строка с одним значением 0 или 1) сохраняется во временную таблицу с именем ВТ_НаличиеЗаполненныхПодразделений. Эта временная таблица будет доступна для последующих запросов в рамках того же пакета.
Этот шаг позволяет нам эффективно определить глобальное условие для всей табличной части, что является ключом к решению нашей задачи.
Теперь, когда у нас есть флаг ЕстьЗаполненныеПодразделения во временной таблице, мы можем использовать его для выполнения условного суммирования. Мы будем снова обращаться к нашей исходной табличной части и присоединять к ней временную таблицу с флагом. Важно, что временная таблица содержит всего одну строку, поэтому присоединять ее можно без прямого условия связи, используя ПО ИСТИНА.
ВЫБРАТЬ
СУММА(
ВЫБОР
КОГДА ВТ_НаличиеЗаполненныхПодразделений.ЕстьЗаполненныеПодразделения = 0
ТОГДА ТЧ.Часы // Суммируем все часы, если нет заполненных подразделений
КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка)
ТОГДА ТЧ.Часы // Суммируем часы только по заполненным подразделениям
ИНАЧЕ 0 // Если подразделения есть, но текущее подразделение не заполнено, то 0
КОНЕЦ
) КАК ИтогоЧасы
ИЗ
Документ.СводПутевыхЛистов.ПутевыеЛисты КАК ТЧ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НаличиеЗаполненныхПодразделений КАК ВТ_НаличиеЗаполненныхПодразделений
ПО ИСТИНА
Давайте подробно разберем логику этого запроса:
ИЗ Документ.СводПутевыхЛистов.ПутевыеЛисты КАК ТЧ: Мы снова выбираем данные из нашей исходной табличной части.
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НаличиеЗаполненныхПодразделений КАК ВТ_НаличиеЗаполненныхПодразделений ПО ИСТИНА: Это ключевой момент. Поскольку ВТ_НаличиеЗаполненныхПодразделений содержит всего одну строку, мы можем присоединить ее к каждой строке нашей табличной части ТЧ с помощью условия ПО ИСТИНА. Это гарантирует, что значение флага ЕстьЗаполненныеПодразделения будет доступно для каждой строки ТЧ, позволяя нам использовать его в условном операторе ВЫБОР КОГДА.
СУММА(ВЫБОР ... КОНЕЦ) КАК ИтогоЧасы: Внутри функции СУММА() мы используем сложный условный оператор для определения того, какое значение Часы должно быть включено в общую сумму для каждой строки:
КОГДА ВТ_НаличиеЗаполненныхПодразделений.ЕстьЗаполненныеПодразделения = 0 ТОГДА ТЧ.Часы: Это первое и самое главное условие. Если флаг ЕстьЗаполненныеПодразделения равен 0 (то есть, ни в одной строке табличной части подразделения не заполнены), то для каждой строки ТЧ мы включаем ее ТЧ.Часы в сумму. Это реализует первую часть нашей задачи – суммировать все часы, если подразделения пусты везде.
КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка) ТОГДА ТЧ.Часы: Если первое условие (флаг = 0) не сработало (т.е., флаг = 1, что означает, что в табличной части есть хотя бы одно заполненное подразделение), то мы переходим к этому условию. Оно проверяет, заполнено ли Подразделение для текущей строки ТЧ. Если да, то ТЧ.Часы этой строки включаются в сумму. Это реализует вторую часть нашей задачи – суммировать часы только по заполненным подразделениям, если таковые имеются.
ИНАЧЕ 0: Если ни одно из вышеперечисленных условий не выполнилось (то есть, флаг ЕстьЗаполненныеПодразделения равен 1, и при этом Подразделение текущей строки ТЧ не заполнено), то мы добавляем 0 к сумме. Это гарантирует, что часы незаполненных подразделений не будут учтены, если общая логика требует суммирования только по заполненным.
Таким образом, пакетный запрос позволяет нам сначала определить глобальное состояние, а затем использовать его для выполнения сложной условной агрегации, точно соответствующей поставленной задаче. Это эффективный и легко читаемый способ решения подобных проблем.
На форуме также было предложено решение с использованием "пары временных таблиц". Этот подход хоть и немного отличается по реализации от пакетного запроса, описанного выше, но также основан на концепции временных таблиц и позволяет решить задачу. Давайте рассмотрим его суть.
Идея заключается в следующем:
Создаем первую временную таблицу: В ней мы суммируем все часы без каких-либо условий по подразделениям, просто общую сумму. Это будет "резервная" сумма, которую мы возьмем, если ни одно подразделение не заполнено.
Создаем вторую временную таблицу: В ней мы суммируем часы только для тех строк, где подразделение заполнено. При этом мы также можем сгруппировать по подразделениям, если в дальнейшем это потребуется, но для нашей задачи достаточно просто общей суммы по заполненным.
Основной запрос: В основном запросе мы должны определить, есть ли в табличной части хотя бы одно заполненное подразделение. Для этого можно использовать подзапрос, который проверит наличие непустых значений в поле Подразделение. Затем, с помощью оператора ВЫБОР КОГДА, мы будем выбирать значение из первой временной таблицы (если нет заполненных подразделений) или из второй временной таблицы (если есть заполненные подразделения).
Несмотря на кажущуюся сложность с "восемью таблицами" для нескольких показателей, как опасался автор, для решения нашей конкретной задачи (один показатель, одно условие по подразделению) потребуется: одна временная таблица для всех часов, одна временная таблица для часов по заполненным подразделениям, и один основной запрос, который определит наличие заполненных подразделений и выберет нужную сумму.
Этот подход также жизнеспособен, но пакетный запрос, представленный выше, часто оказывается более лаконичным, так как флаг наличия заполненных подразделений можно получить за один шаг, а затем использовать его для условного включения или исключения значений из агрегации. Это позволяет избежать создания "резервных" сумм, которые могут быть и не использованы.
Как мы уже видели, конструкция ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ является чрезвычайно мощным инструментом в запросах 1С, особенно когда она используется внутри агрегатных функций, таких как СУММА(), МАКСИМУМ(), МИНИМУМ() или КОЛИЧЕСТВО().
Вот основные преимущества такого использования:
Условное включение значений: Вы можете решить, какое значение будет использоваться в агрегации для каждой строки, основываясь на определенных условиях. Например, включать в сумму только положительные числа или только часы, соответствующие определенному подразделению.
СУММА(ВЫБОР КОГДА МойПоказатель > 0 ТОГДА МойПоказатель ИНАЧЕ 0 КОНЕЦ)
Создание флагов: Как в нашем примере с МАКСИМУМ(ВЫБОР КОГДА ... ТОГДА 1 ИНАЧЕ 0 КОНЕЦ), можно генерировать бинарные флаги на основе условий, которые затем используются для дальнейшей логики.
Расчет нескольких показателей за один проход: Можно вычислить несколько условных сумм или счетчиков в одном запросе, используя несколько таких конструкций ВЫБОР КОГДА внутри разных агрегатных функций. Это значительно повышает производительность, поскольку не требуется несколько проходов по одной и той же таблице.
Освоение этой техники является важным шагом для создания сложных и эффективных запросов в 1С.
При разработке запросов в 1С всегда следует уделять внимание производительности. Некоторые принципы оптимизации, применимые к нашей задаче:
Использование временных таблиц: Для сложных запросов, требующих нескольких этапов обработки или вычисления промежуточных значений (как в нашем случае определения флага), временные таблицы часто являются оптимальным решением. Они позволяют разбить сложную задачу на более простые, управляемые части, что улучшает читаемость и производительность запроса. Система управления базами данных (СУБД) может эффективнее оптимизировать каждый отдельный запрос, а не один большой и сложный.
Избегайте ИЛИ в секции ГДЕ: Оператор ИЛИ в секции ГДЕ часто может приводить к проблемам с производительностью, особенно если условия затрагивают неиндексированные поля. В таких случаях СУБД может быть вынуждена выполнять полное сканирование таблицы, игнорируя индексы. В нашей задаче мы избежали этого, перенеся условную логику внутрь функции СУММА() и используя предварительно вычисленный флаг, что гораздо более эффективно.
Левые соединения (ЛЕВОЕ СОЕДИНЕНИЕ): Если мы присоединяем временную таблицу, содержащую один агрегированный результат или флаг, как в нашем случае, то ЛЕВОЕ СОЕДИНЕНИЕ является правильным выбором. Оно гарантирует, что все строки из основной таблицы будут включены в результат, даже если нет совпадений во присоединяемой таблице (хотя в нашем случае с ПО ИСТИНА это не так критично, поскольку одна строка всегда будет присоединена).
Автор запроса выразил озабоченность, что у него таких показателей четыре (Сообщение 7), и это может привести к "восьми таблицам". Предложенный нами пакетный запрос элегантно решает эту проблему.
Если у вас есть несколько показателей (например, Моточасы, РабочиеЧасы, ВремяПростоя и т.д.), которые должны подчиняться той же логике условного суммирования по подразделениям, вы можете использовать тот же флаг ЕстьЗаполненныеПодразделения для всех них. Запрос на первом шаге, который формирует временную таблицу ВТ_НаличиеЗаполненныхПодразделений, останется неизменным, так как он определяет общее состояние только по полю Подразделение.
Во втором запросе пакета вы просто добавите дополнительные агрегатные функции СУММА() для каждого из ваших показателей, используя ту же самую логику ВЫБОР КОГДА:
ВЫБРАТЬ
СУММА(
ВЫБОР
КОГДА ВТ_НаличиеЗаполненныхПодразделений.ЕстьЗаполненныеПодразделения = 0
ТОГДА ТЧ.Моточасы
КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка)
ТОГДА ТЧ.Моточасы
ИНАЧЕ 0
КОНЕЦ
) КАК ИтогоМоточасы,
СУММА(
ВЫБОР
КОГДА ВТ_НаличиеЗаполненныхПодразделений.ЕстьЗаполненныеПодразделения = 0
ТОГДА ТЧ.РабочиеЧасы
КОГДА ТЧ.Подразделение <> ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка)
ТОГДА ТЧ.РабочиеЧасы
ИНАЧЕ 0
КОНЕЦ
) КАК ИтогоРабочиеЧасы,
// и так далее для других показателей
ИЗ
Документ.СводПутевыхЛистов.ПутевыеЛисты КАК ТЧ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НаличиеЗаполненныхПодразделений КАК ВТ_НаличиеЗаполненныхПодразделений
ПО ИСТИНА
Таким образом, для любого количества показателей, подчиняющихся одинаковой логике, вы по-прежнему будете использовать всего две "таблицы" (одну фактическую и одну временную с флагом) в основном запросе. Это значительно упрощает запрос и делает его более эффективным, чем создание отдельных временных таблиц для каждого показателя.
В итоге, решение с использованием пакетного запроса и условной агрегации внутри функции СУММА() является мощным и гибким инструментом для обработки сложных условий суммирования в запросах 1С, позволяя эффективно решать задачи, где логика зависит от общего состояния данных в выборке.