При разработке и поддержке конфигураций на платформе 1С:Предприятие программисты регулярно сталкиваются с необходимостью анализа закрытых показателей. Это могут быть полностью оплаченные заказы покупателей, полностью отгруженные товары со складов, закрытые договоры взаиморасчетов или выполненные спецификации на производство. С точки зрения базы данных, «закрытое» состояние означает, что по данному объекту (измерению регистра) на определенный момент времени отсутствует остаток в соответствующем регистре накопления. Иными словами, остаток равен нулю.
В этой статье мы подробно разберем, как правильно, эффективно и быстро получить такие записи с помощью языка запросов 1С. Мы рассмотрим несколько подходов — от простых решений до продвинутых методик оптимизации для высоконагруженных систем, а также разберем неочевидные подводные камни, с которыми можно столкнуться на практике.
Довольно часто начинающие разработчики пытаются решить задачу «от обратного», используя конструкцию ГДЕ НЕ Измерение В (ВЫБРАТЬ...). Однако с точки зрения производительности СУБД (будь то MS SQL Server или PostgreSQL), использование подзапросов с отрицанием NOT IN работает крайне медленно, так как оптимизатор СУБД не всегда может эффективно применить индексы. Это приводит к полному сканированию таблиц (Table Scan / Index Scan), что на больших базах вызывает колоссальные задержки.
Наиболее эффективным и технически правильным решением является использование операции ЛЕВОГО СОЕДИНЕНИЯ исходной таблицы с виртуальной таблицей остатков регистра накопления с последующей фильтрацией по значению NULL.
Давайте разберем этот паттерн на примере поиска полностью оплаченных заказов покупателей из регистра накопления РасчетыСКонтрагентами:
ВЫБРАТЬ
ЗаказыПокупателей.Ссылка КАК Заказ,
ЗаказыПокупателей.Контрагент КАК Контрагент
ИЗ
Документ.ЗаказПокупателя КАК ЗаказыПокупателей
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКонтрагентами.Остатки(&Период, ) КАК РасчетыОстатки
ПО ЗаказыПокупателей.Ссылка = РасчетыОстатки.Заказ
ГДЕ
РасчетыОстатки.Заказ ЕСТЬ NULL
И ЗаказыПокупателей.Проведен = ИСТИНА
Как работает этот запрос? Мы берем все проведенные заказы покупателей из базы данных и пытаемся сопоставить их с записями остатков в регистре взаиморасчетов. Если заказ полностью оплачен и закрыт, то в виртуальной таблице Остатки записи по нему не будет. В результате левого соединения поля из таблицы остатков для такого заказа примут значение NULL. Условие ГДЕ РасчетыОстатки.Заказ ЕСТЬ NULL отсекает все незакрытые заказы, оставляя нам только те, по которым остаток равен нулю.
Предыдущий метод отлично подходит для небольших баз данных. Но что делать, если в вашей базе хранятся миллионы заказов и документов, а регистр накопления содержит огромный объем данных? Прямое соединение физической таблицы документов с виртуальной таблицей остатков без предварительной фильтрации заставит СУБД выполнять тяжелые вычисления для всей базы данных.
Давайте оптимизируем наше решение, разбив его на логические шаги с использованием механизма временных таблиц:
ВТ_Заказы.Давайте посмотрим на практический пример реализации этого алгоритма:
// Шаг 1: Выбираем интересующие нас заказы во временную таблицу
ВЫБРАТЬ
ЗаказыПокупателей.Ссылка КАК Заказ,
ЗаказыПокупателей.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
Документ.ЗаказПокупателя КАК ЗаказыПокупателей
ГДЕ
ЗаказыПокупателей.Проведен = ИСТИНА
И ЗаказыПокупателей.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем остатки исключительно по выбранным заказам
ВЫБРАТЬ
РасчетыОстатки.Заказ КАК Заказ,
РасчетыОстатки.СуммаОстаток КАК СуммаОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.РасчетыСКонтрагентами.Остатки(
&ПериодОстатков,
Заказ В
(ВЫБРАТЬ
ВТ_Заказы.Заказ
ИЗ
ВТ_Заказы)) КАК РасчетыОстатки
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем временные таблицы и получаем закрытые заказы
ВЫБРАТЬ
ВТ_Заказы.Заказ КАК Заказ,
ВТ_Заказы.Контрагент КАК Контрагент
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
ПО ВТ_Заказы.Заказ = ВТ_Остатки.Заказ
ГДЕ
ВТ_Остатки.Заказ ЕСТЬ NULL
Этот подход работает в разы быстрее на крупных корпоративных базах данных, поскольку СУБД оперирует уже отфильтрованными индексированными наборами данных, а виртуальная таблица остатков не рассчитывается под всю базу «на лету».
Иногда бизнес-логика задачи требует найти не просто «объекты без остатков на текущий момент», а объекты, по которым были движения (обороты) за период, но на конец этого периода остаток стал равен нулю. Типичный пример: нужно узнать, какие товары полностью распроданы со склада в течение месяца.
В этом случае незаменимым инструментом становится виртуальная таблица ОстаткиИОбороты. Давайте проанализируем пример запроса для поиска таких товаров на складе:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.КоличествоПриход КАК КоличествоПриход,
ТоварыНаСкладахОстаткиИОбороты.КоличествоРасход КАК КоличествоРасход
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания, , , ) КАК ТоварыНаСкладахОстаткиИОбороты
ГДЕ
ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток = 0
Преимущество этого метода: мы сразу получаем агрегированные данные. Запрос выведет только те позиции номенклатуры, по которым в указанном периоде были приходы или расходы (обороты), но на конец периода остаток равен 0 (все распродано или списано).
Минус метода: если по номенклатуре вообще не было никаких движений за выбранный период и остаток изначально был равен нулю, то такая номенклатура не попадет в выборку. Если вам нужны абсолютно все позиции справочника без остатков (даже те, по которым не было движений), используйте первый или второй методы.
Реальные базы данных 1С (особенно на базе ERP, Комплексной автоматизации или Управления торговлей) таят в себе множество подводных камней. Простое математическое сравнение остатка с нулем не всегда гарантирует корректный бизнес-результат. Давайте разберем основные нюансы:
0.01 или -0.02 рубля). С точки зрения математики остаток есть, и наш запрос с условием ЕСТЬ NULL пропустит этот заказ. Однако физически и управленчески этот заказ считается закрытым.
ЕСТЬ NULL можно соединять таблицы и проверять, что остаток находится в пределах допустимой погрешности округления:
ГДЕ
ЕСТЬNULL(ВТ_Остатки.СуммаОстаток, 0) МЕЖДУ -0.05 И 0.05
Активность = Истина). Если в вашей системе выполнялись ручные корректировки записей регистров, переносы данных или специфические обмены, в базе могут присутствовать «зависшие» неактивные движения. Они не влияют на остатки в виртуальных таблицах, но могут присутствовать в физических таблицах движений. При проектировании сложных запросов напрямую к физическим таблицам всегда накладывайте фильтр ГДЕ Активность = ИСТИНА.
ЗаказыКлиентов).РасчетыСКлиентами).Мы подробно разобрали, как с помощью запросов 1С получить записи без остатков. Подводя итог, сформулируем основные правила успешной разработки таких запросов:
Применяя эти паттерны оптимизации на практике, вы обеспечите высокую скорость работы ваших отчетов и обработок даже под высокой нагрузкой на СУБД.