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