В практике разработки на платформе 1С:Предприятие 8.3 часто возникает классическая задача: необходимо построить отчет по продажам, где для каждой строки реализации нужно подтянуть цену, действовавшую именно в момент этой продажи. Сложность заключается в том, что стандартная виртуальная таблица СрезПоследних принимает в качестве параметра только одну дату на весь запрос, а нам требуется "срез" на разные даты для каждой строки.
В данной статье мы подробно разберем, как правильно реализовать такой запрос, проанализируем различные подходы — от простых подзапросов до высокопроизводительных методов с использованием временных таблиц и современных оконных функций.
Для обеспечения максимальной производительности, особенно на больших объемах данных, мы рекомендуем использовать методику трех шагов с применением временных таблиц. Этот подход позволяет оптимизатору СУБД (SQL Server, PostgreSQL) эффективно использовать индексы.
Шаг 1. Сбор данных о продажах во временную таблицу
Сначала мы выберем все необходимые продажи. Нам важно зафиксировать не только номенклатуру, но и точный момент времени (период) каждой продажи.
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Период КАК ПериодПродажи,
Продажи.Количество КАК Количество
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода И &КонецПериода
;
Шаг 2. Определение дат установки цен
Теперь нам нужно найти для каждой продажи самую позднюю дату в регистре цен, которая была бы меньше или равна дате продажи. Мы соединяем нашу временную таблицу ВТ_Продажи с физической таблицей регистра сведений Цены.
ВЫБРАТЬ
ВТ_Продажи.Номенклатура КАК Номенклатура,
ВТ_Продажи.ПериодПродажи КАК ПериодПродажи,
МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены
ПО ВТ_Продажи.Номенклатура = Цены.Номенклатура
И ВТ_Продажи.ПериодПродажи >= Цены.Период
ГРУППИРОВАТЬ ПО
ВТ_Продажи.Номенклатура,
ВТ_Продажи.ПериодПродажи
;
Шаг 3. Получение итоговых данных с ценой
На финальном этапе мы объединяем все данные, получаем числовое значение цены и рассчитываем сумму.
ВЫБРАТЬ
ВТ_Продажи.ПериодПродажи КАК ПериодПродажи,
ВТ_Продажи.Номенклатура КАК Номенклатура,
ВТ_Продажи.Количество КАК Количество,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена,
ВТ_Продажи.Количество * ЕСТЬNULL(Цены.Цена, 0) КАК Сумма
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
ПО ВТ_Продажи.Номенклатура = ВТ_МаксимальныеДаты.Номенклатура
И ВТ_Продажи.ПериодПродажи = ВТ_МаксимальныеДаты.ПериодПродажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены
ПО ВТ_МаксимальныеДаты.Номенклатура = Цены.Номенклатура
И ВТ_МаксимальныеДаты.ПериодЦены = Цены.Период
;
Если объем данных невелик (например, небольшая обработка), можно использовать коррелированный подзапрос. Рассмотрим, как это выглядит в языке запросов 1С. Основная идея — для каждой строки продаж вычислить максимальный период цены прямо в условии соединения.
ВЫБРАТЬ
Продажи.Период КАК ДатаПродажи,
Продажи.Номенклатура КАК Номенклатура,
Продажи.Количество КАК Количество,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
РегистрНакопления.Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Цены КАК Цены
ПО Продажи.Номенклатура = Цены.Номенклатура
И Цены.Период = (
ВЫБРАТЬ
МАКСИМУМ(ПодЦены.Период)
ИЗ
РегистрСведений.Цены КАК ПодЦены
ГДЕ
ПодЦены.Номенклатура = Продажи.Номенклатура
И ПодЦены.Период <= Продажи.Период
)
Проанализируем этот подход: он компактен, но имеет серьезный недостаток. СУБД будет выполнять подзапрос для каждой строки таблицы продаж, что при большом количестве записей приведет к значительным задержкам.
При реализации подобных запросов необходимо учитывать специфику платформы 1С:
Периодичность установлена в значение ПозицияРегистратора, то теоретически может существовать две записи с одинаковой секундой. В этом случае МАКСИМУМ(Период) вернет одну дату, но соединение выдаст две строки. Для "непотопляемости" запроса в реальных системах к периоду добавляют сравнение по Регистратору или НомеруСтроки.ТипЦен, Характеристика и, если требуется, Упаковка. Без этого вы получите неверные данные или дублирование строк.ЕСТЬNULL. Если цена на товар никогда не устанавливалась до даты продажи, поле цены будет содержать NULL. Умножение количества на NULL даст NULL, и ваша сумма "исчезнет" из итогов.Если ваша база работает на современной версии платформы (начиная с 8.3.24), мы можем использовать мощь оконных функций. Это позволяет избежать многократных соединений и решить задачу за один проход по данным.
Рассмотрим логику использования функции ROW_NUMBER(). Мы можем пронумеровать все записи цен для каждой номенклатуры, отсортировав их по убыванию даты внутри "окна", ограниченного датой продажи. Однако в текущем языке запросов 1С оконные функции наиболее эффективно применяются для постобработки уже соединенных данных.
Выясним причину, почему запросы к ценам могут работать медленно. Основная причина — отсутствие подходящих индексов. Для эффективной работы описанных выше запросов, регистр сведений Цены должен иметь индекс, где первыми полями идут измерения (Номенклатура, ТипЦен), а затем Период. Платформа 1С создает такие индексы автоматически, но важно следить, чтобы порядок измерений в запросе совпадал с порядком в индексе для оптимального поиска (Index Seek вместо Index Scan).
Заключение: Для большинства задач в 1С оптимальным является Метод 1 (Временные таблицы). Он предсказуем, хорошо масштабируется и понятен другим разработчикам. Разберем его по шагам при внедрении в ваш проект, и вы получите быстрый и надежный отчет.