При работе в системе «1С:Предприятие» разработчики регулярно сталкиваются с необходимостью получить не одну последнюю запись (для чего отлично подходит стандартный срез последних), а несколько (например, 3 последних продажи, 5 последних изменений цен или несколько предыдущих документов движения) по каждой номенклатурной позиции. В классическом SQL для этого существуют удобные оконные функции вроде ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). Однако в языке запросов 1С оконные функции на текущий момент отсутствуют. Из-за этого программистам приходится искать обходные пути.
В этой статье мы подробно разберем несколько практических способов решения данной задачи. Мы проанализируем как классические методы на уровне СУБД, так и варианты с постобработкой данных на сервере приложений 1С, выясним их сильные и слабые стороны, а также разберем готовые примеры кода.
Рассмотрим наиболее элегантный способ решения задачи в рамках одного запроса. Этот метод не требует создания бесконечных цепочек временных таблиц и легко масштабируется, если вам нужно выбрать не 3, а, например, 5 или 10 записей.
Идея заключается в том, чтобы соединить таблицу саму с собой по совпадению номенклатуры. При этом условие соединения по дате (периоду) задается как «меньше или равно». В результате группировки мы сможем подсчитать количество таких соединений и отсечь лишние строки с помощью конструкции ИМЕЮЩИЕ.
Давайте разберем этот алгоритм на примере следующего запроса:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Период КАК Период,
Т1.Количество КАК Количество
ПОМЕСТИТЬ ВТ_ИтоговыйРезультат
ИЗ
ВТ_Выборка КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Выборка КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.Period <= Т2.Период
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.Период,
Т1.Количество
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) <= 3
УПОРЯДОЧИТЬ ПО
Номенклатура,
Период УБЫВ
Выясним причину, почему этот запрос работает именно так:
Т2 найдется только одна запись, удовлетворяющая условию Т1.Период <= Т2.Период — это она сама. Количество различных периодов будет равно 1.ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) <= 3 просто отсечет их при группировке.Важная рекомендация по оптимизации: такое нелинейное соединение «каждый с каждым» внутри одной номенклатуры может сильно нагрузить СУБД на больших объемах данных. Чтобы запрос выполнялся максимально быстро, предварительно отберите нужные данные во временную таблицу ВТ_Выборка и обязательно создайте индекс по полям Номенклатура и Период.
Этот способ был предложен и успешно протестирован разработчиками на форуме. Он отлично подходит, если вам жестко и гарантированно требуется получить ровно 3 последние записи и объем исходных данных велик. Метод строится на последовательном вычислении максимальной даты, затем предыдущей максимальной и так далее.
Проанализируем ситуацию и разберем структуру этого запроса по шагам:
ВЫБРАТЬ
ВТ_Выборка.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВТ_Выборка.Период) КАК Период
ПОМЕСТИТЬ ВТ_Максимум1
ИЗ
ВТ_Выборка КАК ВТ_Выборка
СГРУППИРОВАТЬ ПО
ВТ_Выборка.Номенклатура
;
ВЫБРАТЬ
ВТ_Выборка.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВТ_Выборка.Период) КАК Период
ПОМЕСТИТЬ ВТ_Максимум2
ИЗ
ВТ_Выборка КАК ВТ_Выборка
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Максимум1 КАК ВТ_Максимум1
ПО ВТ_Выборка.Номенклатура = ВТ_Максимум1.Номенклатура
ГДЕ
ВТ_Выборка.Период < ВТ_Максимум1.Период
СГРУППИРОВАТЬ ПО
ВТ_Выборка.Номенклатура
;
ВЫБРАТЬ
ВТ_Выборка.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВТ_Выборка.Период) КАК Период
ПОМЕСТИТЬ ВТ_Максимум3
ИЗ
ВТ_Выборка КАК ВТ_Выборка
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Максимум2 КАК ВТ_Максимум2
ПО ВТ_Выборка.Номенклатура = ВТ_Максимум2.Номенклатура
ГДЕ
ВТ_Выборка.Период < ВТ_Максимум2.Период
СГРУППИРОВАТЬ ПО
ВТ_Выборка.Номенклатура
;
ВТ_Выборка со всеми тремя таблицами максимумов по совпадению номенклатуры и периода:
ВЫБРАТЬ
ВТ_Выборка.Номенклатура КАК Номенклатура,
ВТ_Выборка.Период КАК Период,
ВТ_Выборка.Количество КАК Количество
ИЗ
ВТ_Выборка КАК ВТ_Выборка
// Соединения гарантируют, что мы выберем только те строки,
// периоды которых совпали с одним из трех вычисленных максимумов
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ Номенклатура, Период ИЗ ВТ_Максимум1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Номенклатура, Период ИЗ ВТ_Максимум2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Номенклатура, Период ИЗ ВТ_Максимум3
) КАК ВТ_ВсеМаксимумы
ПО ВТ_Выборка.Номенклатура = ВТ_ВсеМаксимумы.Номенклатура
И ВТ_Выборка.Период = ВТ_ВсеМаксимумы.Период
Этот подход работает надежно и предсказуемо на СУБД, однако имеет очевидный минус — громоздкость. Если вам вдруг понадобится получать не 3, а 10 последних записей, текст запроса увеличится в три раза, что крайне неудобно для поддержки и разработки.
Попробуем реализовать задачу через вложенный запрос с ограничением количества строк. Этот метод часто интуитивно выбирают начинающие разработчики. Смысл в том, чтобы прямо в условии отбора ГДЕ проверять вхождение текущего периода в подзапрос, выбирающий первые 3 записи.
Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Период КАК Период
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты
ГДЕ
ПродажиОбороты.Период В
(ВЫБРАТЬ ПЕРВЫЕ 3
Т.Период КАК Период
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Т
ГДЕ
Т.Номенклатура = ПродажиОбороты.Номенклатура
УПОРЯДОЧИТЬ ПО
Период УБЫВ)
УПОРЯДОЧИТЬ ПО
Номенклатура,
Период УБЫВ
Предупреждение о производительности: Хранилище данных SQL при выполнении такого запроса вынуждено для каждой строчки внешнего запроса производить запуск вложенного подзапроса (так называемый correlated subquery). Это приводит к колоссальному падению производительности и диким тормозам на реальных базах данных. Использовать данный способ на больших таблицах крайне не рекомендуется.
Иногда лучшим решением в плане производительности и расхода оперативной памяти СУБД является перенос логики фильтрации на сторону сервера приложений 1С. Мы можем получить из базы данных простую плоскую таблицу, упорядоченную по номенклатуре и убыванию даты, а затем обойти ее в легком цикле, оставив только первые 3 записи по каждому товару.
Давайте разберем по шагам, как это реализовать на встроенном языке:
// 1. Получаем упорядоченные данные простейшим быстрым запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Т.Номенклатура КАК Номенклатура,
| Т.Период КАК Период,
| Т.Количество КАК Количество
|ИЗ
| РегистрНакопления.Продажи.Обороты(,, День, ) КАК Т
|УПОРЯДОЧИТЬ ПО
| Номенклатура,
| Период УБЫВ";
Выборка = Запрос.Выполнить().Выбрать();
// 2. Создаем результирующую таблицу значений
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Номенклатура");
ТаблицаРезультат.Колонки.Добавить("Период");
ТаблицаРезультат.Колонки.Добавить("Количество");
ТекущаяНоменклатура = Неопределено;
Счетчик = 0;
// 3. Обходим выборку и отсекаем лишнее
Пока Выборка.Следующий() Цикл
Если Выборка.Номенклатура <> ТекущаяНоменклатура Тогда
// Наткнулись на новую номенклатуру - сбрасываем счетчик
ТекущаяНоменклатура = Выборка.Номенклатура;
Счетчик = 1;
Иначе
// Продолжаем обход текущей номенклатуры
Счетчик = Счетчик + 1;
КонецЕсли;
// Добавляем в результат только первые 3 строки для каждого товара
Если Счетчик <= 3 Тогда
НоваяСтрока = ТаблицаРезультат.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, Выборка);
КонецЕсли;
КонецЦикла;
Этот алгоритм работает молниеносно. СУБД отдает данные практически мгновенно, так как ей не приходится делать тяжелые группировки и вложенные циклы, а упорядочивание часто происходит по уже существующим индексам базы данных. Сервер приложений 1С выполняет линейный обход коллекции за время O(N), расходуя минимум памяти.
Если ваша задача состоит в выводе этих данных в отчет, то вам вообще не нужно писать сложные запросы или циклы обработки. Система компоновки данных умеет ограничивать количество строк в группировках штатными средствами.
Проанализируем, как настроить это в схеме компоновки данных:
Номенклатура.Детальные записи (или группировку по полю Период).3.СКД автоматически применит это ограничение при выводе отчета, и вы получите ровно по 3 последние детальные строки для каждого товара.
ИМЕЮЩИЕ). Не забудьте предварительно проиндексировать временную таблицу!