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