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