При работе с периодическими регистрами сведений, такими как ЦеныНоменклатуры, разработчики часто сталкиваются с ситуацией, когда стандартная агрегатная функция МАКСИМУМ в запросе ведет себя «непредсказуемо». Кажется, что платформа игнорирует инструкцию и возвращает все записи регистра вместо одной последней. В этой статье мы подробно разберем, почему так происходит, как работает механизм группировки SQL и какими способами можно гарантированно получить актуальные данные на нужную дату.
Рассмотрим ситуацию: нам необходимо получить максимально свежую цену для каждой номенклатуры. Часто начинающий разработчик пишет запрос следующим образом:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.Характеристика КАК Характеристика,
ЕСТЬNULL(ЦеныНоменклатуры.Цена, 0) КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.ВидЦены = &ВидЦены
СГРУППИРОВАТЬ ПО
ЦеныНоменклатуры.Номенклатура,
ЦеныНоменклатуры.Характеристика,
ЕСТЬNULL(ЦеныНоменклатуры.Цена, 0)
Почему этот запрос не работает? Давайте выясним причину. В языке SQL (и в языке запросов 1С) секция СГРУППИРОВАТЬ ПО определяет уникальный ключ строки в итоговой выборке. Если вы включаете поле Цена в группировку, система будет искать максимальный период для каждой уникальной комбинации номенклатуры, характеристики и цены. Если цена менялась трижды в разные периоды, вы получите три строки, потому что каждая цена образует свою собственную группу.
Проанализируем ситуацию: функция МАКСИМУМ(Период) отрабатывает честно, но только внутри группы, где цена уже зафиксирована. Чтобы получить одну строку с последней ценой, поле Цена не должно участвовать в группировке, однако оно нам нужно в результате. Это классическая дилемма, которая решается несколькими способами.
Для периодических регистров сведений платформа 1С предоставляет встроенный механизм — виртуальную таблицу СрезПоследних. Это самый эффективный и рекомендуемый способ получения актуальных данных.
Разберем по шагам, как это работает. Вместо того чтобы обращаться к физической таблице регистра, мы обращаемся к виртуальной:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура,
ЦеныНоменклатурыСрезПоследних.Характеристика,
ЦеныНоменклатурыСрезПоследних.Цена,
ЦеныНоменклатурыСрезПоследних.Период
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ВидЦены = &ВидЦены) КАК ЦеныНоменклатурыСрезПоследних
Преимущества этого метода:
&Период любую дату (например, конец прошлого года), и система сама найдет цены, актуальные именно на тот момент.Иногда возникают задачи, когда СрезПоследних не подходит. Например, когда нужно получить срезы данных на разные даты для каждой строки в одном запросе. В этом случае мы реализуем логику «среза» вручную с помощью временных таблиц.
Рассмотрим алгоритм действий:
Шаг 1. Подготовка данных. Поместим все подходящие записи во временную таблицу, чтобы не обращаться к тяжелому регистру несколько раз.
ВЫБРАТЬ
Цены.Период КАК Период,
Цены.Номенклатура КАК Номенклатура,
Цены.Характеристика КАК Характеристика,
Цены.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ВсеЦены
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.ВидЦены = &ВидЦены;
Шаг 2. Определение максимального периода. Создаем вторую временную таблицу, где для каждой ключевой пары (Номенклатура + Характеристика) вычисляем только МАКСИМУМ(Период). Здесь мы не включаем цену в группировку!
ВЫБРАТЬ
ВТ.Номенклатура КАК Номенклатура,
ВТ.Характеристика КАК Характеристика,
МАКСИМУМ(ВТ.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_ВсеЦены КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Номенклатура,
ВТ.Характеристика;
Шаг 3. Получение ресурсов. Соединяем таблицу с максимальными датами обратно с таблицей всех цен по всем ключевым полям (Номенклатура, Характеристика и Период).
ВЫБРАТЬ
Цены.Номенклатура,
Цены.Характеристика,
Цены.Период,
Цены.Цена
ИЗ
ВТ_ВсеЦены КАК Цены
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МаксимальныеДаты КАК Даты
ПО Цены.Номенклатура = Даты.Номенклатура
И Цены.Характеристика = Даты.Характеристика
И Цены.Период = Даты.МаксПериод
Проанализируем важные особенности работы агрегатных функций, которые могут повлиять на ваш результат:
МАКСИМУМ полностью игнорирует значения NULL. Если в выборке по какой-то группе окажутся только значения NULL, результатом функции также будет NULL. Это важно учитывать при использовании ЛЕВОЕ СОЕДИНЕНИЕ.МАКСИМУМ можно применять не только к числам и датам, но и к строкам. В этом случае результат будет определяться лексикографическим порядком (по алфавиту).МАКСИМУМ(Период) работал быстро на больших объемах данных, убедитесь, что в регистре стоит флаг «Основной отбор по периоду». Это позволяет СУБД использовать индекс периода для быстрого нахождения последней записи без полного сканирования таблицы.МАКСИМУМ(Период) выдаст две строки. В таких случаях для однозначности в соединение добавляют дополнительные поля, такие как НомерСтроки или регистратор.Посмотрим на ситуацию со стороны производительности. При использовании платформы 8.3.14 и выше, механизмы работы с временными таблицами были значительно оптимизированы. Однако стоит помнить: если вы используете метод с временными таблицами, обязательно индексируйте их перед соединением.
Добавление индекса во временную таблицу ВТ_МаксимальныеДаты по полям Номенклатура и МаксПериод позволит СУБД выполнить соединение мгновенно, даже если в справочнике сотни тысяч товаров. Без индекса система может прибегнуть к методу Nested Loops, что существенно замедлит выполнение запроса.
Подводя итог, можно сказать: функция МАКСИМУМ всегда работает корректно согласно законам логики SQL. Ошибка «неработающего максимума» — это почти всегда сигнал о том, что в секцию СГРУППИРОВАТЬ ПО попало лишнее поле, которое дробит выборку на мелкие группы. Используйте СрезПоследних для стандартных задач и многоуровневые запросы с временными таблицами для сложных аналитических отчетов.