Как получить максимальную цену номенклатуры по покупателю и поставщику из прошлого документа в запросе 1С?

← На главную

Приветствуем вас, коллеги! Мы часто сталкиваемся с задачами, требующими извлечения агрегированных данных, таких как максимальные значения, из исторических документов и их сопоставления с текущими данными. Наш коллега столкнулся именно с такой проблемой: как выбрать данные из текущего документа и одновременно получить максимальную цену по конкретной номенклатуре, покупателю и поставщику из аналогичного документа, сформированного в прошлом периоде. Разберем этот кейс подробно и выясним, как правильно построить запрос в 1С.

Исходная проблема: почему МАКСИМУМ() не работает так, как ожидалось?

Наш коллега столкнулся с трудностью при попытке получить одно максимальное значение цены для каждой номенклатуры. Вместо этого запрос возвращал несколько значений или неправильные максимальные значения. Рассмотрим его первоначальную попытку запроса:


ВЫБРАТЬ
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Ссылка КАК Ссылка,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Номенклатура КАК Номенклатура,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Количество КАК Количество,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.ЦенаВход КАК ЦенаВход
ИЗ
    Документ.ОбщаяЗаявкаПоставщику.СписокЗаказов КАК ОбщаяЗаявкаПоставщикуСписокЗаказов
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            ЗаявкаПрМес.Ссылка КАК Ссылка,
            ЗаявкаПрМес.Номенклатура КАК Номенклатура,
            ЗаявкаПрМес.Покупатель КАК Покупатель,
            МАКСИМУМ(ЗаявкаПрМес.ЦенаВход) КАК ЦенаВход
        ИЗ
            Документ.ОбщаяЗаявкаПоставщику.СписокЗаказов КАК ЗаявкаПрМес
        СГРУППИРОВАТЬ ПО
            ЗаявкаПрМес.Номенклатура,
            ЗаявкаПрМес.Ссылка,
            ЗаявкаПрМес.Покупатель) КАК ВложенныйЗапрос
        ПО (ОбщаяЗаявкаПоставщикуСписокЗаказов.Ссылка = ВложенныйЗапрос.Ссылка)
            И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Номенклатура = ВложенныйЗапрос.Номенклатура)
            И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Покупатель = ВложенныйЗапрос.Покупатель)
ГДЕ
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Ссылка = &Ссылка
    И ОбщаяЗаявкаПоставщикуСписокЗаказов.Покупатель = &Покупатель

Причина такого поведения кроется в некорректной группировке во вложенном запросе. Когда мы используем агрегатную функцию МАКСИМУМ(), необходимо группировать результаты по всем полям, которые определяют уникальность группы, для которой мы ищем максимум. Если в секцию СГРУППИРОВАТЬ ПО включить поле Ссылка (на документ), то группировка будет происходить в рамках каждой строки документа, а не по уникальной комбинации Номенклатуры, Покупателя и Поставщика в целом. Это приводит к тому, что функция МАКСИМУМ() возвращает максимум для каждой конкретной строки документа, а не для всей совокупности записей по данной номенклатуре и покупателю, что не решает нашу задачу.

Ключ к решению: правильная группировка, фильтрация по периоду и временные таблицы

Для того чтобы функция МАКСИМУМ() работала корректно и мы получали одно максимальное значение для каждой уникальной комбинации номенклатуры, покупателя и поставщика из прошлого документа, мы должны применить несколько важных принципов:

Правильная группировка: В секции СГРУППИРОВАТЬ ПО должны быть только те поля, по которым мы хотим получить уникальный максимум. В нашем случае это Номенклатура, Покупатель и Поставщик.
Фильтрация по периоду: Необходимо четко определить, какие документы считать "прошлыми". В нашем случае, это документ, созданный в прошлом месяце.
Использование временных таблиц: Для повышения читаемости и производительности запроса, особенно на больших объемах данных, целесообразно использовать временные таблицы (конструкция ПОМЕСТИТЬ). Это позволяет сначала вычислить агрегированные значения, а затем присоединить их к основным данным.

Давайте рассмотрим решение, предложенное одним из участников обсуждения, и дополним его с учетом требования о поставщике.

Шаг 1: Формируем временную таблицу с максимальными ценами за предыдущий период

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


ВЫБРАТЬ
    ЗаявкаПрМес.Номенклатура КАК Номенклатура,
    ЗаявкаПрМес.Покупатель КАК Покупатель,
    ЗаявкаПрМес.Поставщик КАК Поставщик, // Добавляем Поставщика для корректной группировки
    МАКСИМУМ(ЗаявкаПрМес.ЦенаВход) КАК ЦенаВход
ПОМЕСТИТЬ ТабличкаЦенЗаПрошлыйМесяц
ИЗ
    Документ.ОбщаяЗаявкаПоставщику.СписокЗаказов КАК ЗаявкаПрМес
ГДЕ
    ЗаявкаПрМес.Ссылка = &СсылкаНаДокументЗаПрошлыйМесяц // Фильтруем по конкретному документу прошлого месяца
СГРУППИРОВАТЬ ПО
    ЗаявкаПрМес.Номенклатура,
    ЗаявкаПрМес.Покупатель,
    ЗаявкаПрМес.Поставщик // Группируем по всем определяющим полям

Разберем этот фрагмент запроса по частям:

ПОМЕСТИТЬ ТабличкаЦенЗаПрошлыйМесяц: Мы создаем временную таблицу с именем ТабличкаЦенЗаПрошлыйМесяц. Все данные, выбранные в этом запросе, будут помещены в эту временную таблицу, которую мы сможем использовать далее в основном запросе. Это очень удобно для работы со сложными агрегациями.
Фильтрация по периоду (ГДЕ ЗаявкаПрМес.Ссылка = &СсылкаНаДокументЗаПрошлыйМесяц): Крайне важно ограничить выборку только теми данными, которые относятся к "прошлому документу". В данном случае предполагается, что мы уже знаем ссылку на документ, который был создан в прошлом месяце, и передаем её в запрос как параметр &СсылкаНаДокументЗаПрошлыйМесяц. Это обеспечивает точность данных и повышает производительность, так как СУБД не будет обрабатывать лишние записи.
Корректная группировка (СГРУППИРОВАТЬ ПО ЗаявкаПрМес.Номенклатура, ЗаявкаПрМес.Покупатель, ЗаявкаПрМес.Поставщик): Именно здесь кроется главное отличие от первоначальной попытки. Мы группируем данные по Номенклатуре, Покупателю и Поставщику. Это означает, что для каждой уникальной комбинации этих трех полей будет вычислено одно агрегированное значение. Важно, что поле Ссылка из исходного документа здесь не участвует в группировке, так как мы хотим получить максимальную цену по уникальным комбинациям номенклатуры, покупателя и поставщика в целом, а не в рамках одной записи документа.
Агрегатная функция (МАКСИМУМ(ЗаявкаПрМес.ЦенаВход)): Эта функция вычисляет максимальное значение поля ЦенаВход для каждой группы, определенной в секции СГРУППИРОВАТЬ ПО. В результате мы получаем одну строку для каждой комбинации Номенклатуры, Покупателя и Поставщика с соответствующей максимальной ценой.

Шаг 2: Присоединяем максимальные цены к данным текущего документа

После того как у нас есть готовая временная таблица с максимальными ценами из прошлого документа, мы можем легко присоединить её к данным текущего документа. Мы используем для этого ЛЕВОЕ СОЕДИНЕНИЕ, чтобы гарантировать, что все строки текущего документа попадут в результат, даже если для какой-то номенклатуры, покупателя и поставщика не найдется прошлой цены.


ВЫБРАТЬ
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Номенклатура КАК Номенклатура,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Покупатель КАК Покупатель,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Поставщик КАК Поставщик,
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Количество КАК Количество,
    ЕСТьNULL(ТабличкаЦенЗаПрошлыйМесяц.ЦенаВход, ОбщаяЗаявкаПоставщикуСписокЗаказов.ЦенаВход) КАК ЦенаВходИзПрошлогоИлиТекущая
ИЗ
    Документ.ОбщаяЗаявкаПоставщику.СписокЗаказов КАК ОбщаяЗаявкаПоставщикуСписокЗаказов
        ЛЕВОЕ СОЕДИНЕНИЕ ТабличкаЦенЗаПрошлыйМесяц КАК ТабличкаЦенЗаПрошлыйМесяц
        ПО (ОбщаяЗаявкаПоставщикуСписокЗаказов.Номенклатура = ТабличкаЦенЗаПрошлыйМесяц.Номенклатура)
            И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Покупатель = ТабличкаЦенЗаПрошлыйМесяц.Покупатель)
            И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Поставщик = ТабличкаЦенЗаПрошлыйМесяц.Поставщик) // Добавляем Поставщика в условие соединения
ГДЕ
    ОбщаяЗаявкаПоставщикуСписокЗаказов.Ссылка = &СсылкаНаДокументЗаЭтотМесяц // Фильтруем по конкретному документу этого месяца

Проанализируем вторую часть запроса:

Основная выборка (ИЗ Документ.ОбщаяЗаявкаПоставщику.СписокЗаказов КАК ОбщаяЗаявкаПоставщикуСписокЗаказов): Здесь мы выбираем все необходимые поля из табличной части текущего документа, который был создан в этом месяце.
Левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ ТабличкаЦенЗаПрошлыйМесяц КАК ТабличкаЦенЗаПрошлыйМесяц ПО ...): Мы используем левое соединение для того, чтобы получить все строки из текущего документа (левая таблица) и добавить к ним соответствующие данные из нашей временной таблицы с максимальными ценами (правая таблица). Если соответствия не будет найдено, поля из правой таблицы будут содержать NULL.
Условие соединения (ПО (ОбщаяЗаявкаПоставщикуСписокЗаказов.Номенклатура = ТабличкаЦенЗаПрошлыйМесяц.Номенклатура) И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Покупатель = ТабличкаЦенЗаПрошлыйМесяц.Покупатель) И (ОбщаяЗаявкаПоставщикуСписокЗаказов.Поставщик = ТабличкаЦенЗаПрошлыйМесяц.Поставщик)): Соединение происходит по всем ключевым полям: Номенклатура, Покупатель и Поставщик. Это гарантирует, что мы сопоставляем цены для идентичных комбинаций этих параметров.
Обработка отсутствия данных (ЕСТьNULL(ТабличкаЦенЗаПрошлыйМесяц.ЦенаВход, ОбщаяЗаявкаПоставщикуСписокЗаказов.ЦенаВход)): Это крайне важная часть запроса. Если для какой-либо строки текущего документа не нашлось соответствующей записи во временной таблице (то есть не было такой номенклатуры у данного покупателя и поставщика в прошлом месяце), то ТабличкаЦенЗаПрошлыйМесяц.ЦенаВход будет равна NULL. Функция ЕСТЬNULL() позволяет нам в этом случае использовать ЦенаВход из текущего документа, что соответствует логике задачи. Таким образом, мы всегда получаем какую-то цену: либо максимальную прошлую, либо текущую, если прошлой нет.
Фильтрация по текущему документу (ГДЕ ОбщаяЗаявкаПоставщикуСписокЗаказов.Ссылка = &СсылкаНаДокументЗаЭтотМесяц): Аналогично первому шагу, мы фильтруем данные, чтобы выбрать строки только из конкретного документа текущего месяца, ссылку на который мы передаем как параметр &СсылкаНаДокументЗаЭтотМесяц.

Дополнительные рекомендации и расширение решения

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

Выбор ссылки на документ предыдущего/текущего периода

В нашем решении мы используем параметры &СсылкаНаДокументЗаПрошлыйМесяц и &СсылкаНаДокументЗаЭтотМесяц. Эти ссылки необходимо получить до выполнения основного запроса. Мы можем сделать это, например, с помощью отдельных запросов или программно:

Чтобы получить ссылку на документ за текущий месяц (например, самый последний), мы можем использовать следующий запрос:


ВЫБРАТЬ ПЕРВЫЕ 1
    ДокументОбщаяЗаявка.Ссылка
ИЗ
    Документ.ОбщаяЗаявкаПоставщику КАК ДокументОбщаяЗаявка
ГДЕ
    НАЧАЛОПЕРИОДА(ДокументОбщаяЗаявка.Дата, МЕСЯЦ) = НАЧАЛОПЕРИОДА(&ТекущаяДата, МЕСЯЦ)
УПОРЯДОЧИТЬ ПО
    ДокументОбщаяЗаявка.Дата УБЫВ

Аналогично для прошлого месяца:


ВЫБРАТЬ ПЕРВЫЕ 1
    ДокументОбщаяЗаявка.Ссылка
ИЗ
    Документ.ОбщаяЗаявкаПоставщику КАК ДокументОбщаяЗаявка
ГДЕ
    НАЧАЛОПЕРИОДА(ДокументОбщаяЗаявка.Дата, МЕСЯЦ) = НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&ТекущаяДата, МЕСЯЦ, -1), МЕСЯЦ)
УПОРЯДОЧИТЬ ПО
    ДокументОбщаяЗаявка.Дата УБЫВ

Переменная &ТекущаяДата должна содержать любую дату текущего месяца. Если же у вас есть конкретный документ, из которого вы хотите получить данные, его ссылку можно передать напрямую.

Учет Поставщика в условиях группировки

Изначально в постановке задачи было указано, что максимальная цена должна быть по номенклатуре, покупателю и поставщику. В представленном решении мы уже учли это, добавив поле Поставщик как в секцию ВЫБРАТЬ и СГРУППИРОВАТЬ ПО первой временной таблицы, так и в условия соединения во втором запросе. Это критически важно, поскольку без учета поставщика, максимальная цена могла бы быть взята от другого поставщика, что исказило бы смысл задачи.

Оптимизация производительности на больших объемах данных

При работе с очень большими базами данных или при выполнении таких запросов в высоконагруженных системах, всегда стоит думать об оптимизации:

Индексы: Убедитесь, что поля, используемые в условиях соединения (Номенклатура, Покупатель, Поставщик) и фильтрации (Ссылка, Дата), проиндексированы. Это значительно ускорит выполнение запросов.
Параметры запроса: Всегда используйте параметры запроса (например, &СсылкаНаДокументЗаПрошлыйМесяц) вместо строковой конкатенации значений. Это предотвращает SQL-инъекции и позволяет СУБД кэшировать планы выполнения запросов.
Разбиение сложных запросов: Если логика становится еще сложнее, не бойтесь разбивать один большой запрос на несколько более мелких с использованием промежуточных временных таблиц. Это часто делает код более читаемым и управляемым, а иногда и более производительным, позволяя СУБД эффективно обрабатывать каждый шаг.
Анализ плана запроса: В случае возникновения проблем с производительностью, всегда анализируйте план выполнения запроса через консоль запросов или инструменты СУБД (например, SQL Server Management Studio). Это позволит точно определить "узкие места".

Заключение

Таким образом, мы видим, что для корректного извлечения агрегированных данных, таких как максимальная цена по сложным критериям (номенклатура, покупатель, поставщик), из документов предыдущих периодов, нам необходимо тщательно продумать структуру запроса. Использование временных таблиц в сочетании с правильной группировкой и фильтрацией по дате/ссылке на документ является мощным и эффективным инструментом в арсенале разработчика 1С. Этот подход не только позволяет получить точный результат, но и делает сам запрос более понятным и поддерживаемым. Применяя эти принципы, вы сможете успешно решать подобные задачи и строить надежные и производительные отчеты и обработки в 1С.