В практике разработки на платформе 1С:Предприятие регулярно возникает задача свертки дублирующихся записей, когда среди нескольких строк с одинаковыми ключевыми полями необходимо выбрать одну-единственную, обладающую наивысшим приоритетом. Классический пример — выбор строки с заполненным аналитическим разрезом (например, Подразделение) вместо строки, где это поле содержит пустую ссылку.
Когда речь заходит о небольших таблицах (до нескольких тысяч строк), разработчики редко задумываются о производительности. Однако, если объем обрабатываемых данных превышает 2 миллиона записей, неоптимальный алгоритм группировки и соединения может привести к критической нагрузке на СУБД, переполнению базы tempdb и зависанию сеанса. В этой статье мы подробно разберем несколько подходов к решению этой задачи, проанализируем их плюсы и минусы, а также выясним, почему популярные «простые» решения могут оказаться опасной ловушкой на больших объемах данных.
Представим, что у нас есть исходная таблица со следующей структурой:
В таблице присутствуют дубли по ключевым полям Товар и Магазин. При этом для одного и того же товара в одном и том же магазине может существовать две строки: в одной подразделение указано (заполнено), а во второй — нет (пустая ссылка). Наша цель — свернуть таблицу по полям Товар и Магазин таким образом, чтобы:
ТипЦен) должны соответствовать выбранной приоритетной строке.---
Рассмотрим самый простой способ, который часто предлагают на форумах. Если нам нужно избавиться от пустых ссылок при наличии заполненных, мы можем использовать группировку и агрегатную функцию МАКСИМУМ по полю Подразделение. Давайте разберем следующий код запроса:
ВЫБРАТЬ
Источник.Товар КАК Товар,
Источник.Магазин КАК Магазин,
МАКСИМУМ(Источник.Подразделение) КАК Подразделение
ИЗ
Источник КАК Источник
СГРУППИРОВАТЬ ПО
Источник.Товар,
Источник.Магазин
Почему этот механизм работает в СУБД?
На уровне базах данных (MS SQL Server, PostgreSQL) пустая ссылка любого типа в 1С хранится как набор бинарных нулей (например, 0x00000000000000000000000000000000 или 00000000-0000-0000-0000-000000000000). Любая реальная, созданная в системе ссылка (GUID) при двоичном сравнении всегда будет больше, чем это нулевое значение. Соответственно, функция МАКСИМУМ гарантированно вернет заполненное подразделение, если в группе есть хотя бы одна непустая ссылка.
Важное ограничение (Ловушка составного типа):
Этот изящный способ работает только в том случае, если поле Подразделение имеет строго одиночный ссылочный тип. Если бы это поле имело составной тип (например, Справочник.Подразделения и Справочник.Склады), СУБД при вычислении максимума ориентировалась бы сначала на внутренний идентификатор типа данных, присвоенный платформой 1С. В этом случае пустая ссылка одного типа могла бы оказаться "больше" реальной ссылки другого типа. Поэтому для полей составного типа данный метод применять нельзя.
---
Основная проблема базовой группировки из Варианта 1 заключается в том, что при использовании агрегатных функций мы теряем другие связанные поля (в нашем примере — ТипЦен). Если мы попытаемся добавить ТипЦен в секцию СГРУППИРОВАТЬ ПО, СУБД снова размножит строки, и свертка не произойдет. Если же мы применим к нему функцию МАКСИМУМ(ТипЦен), то получим некорректные данные: система может взять подразделение из одной строки, а тип цен — из другой.
Чтобы решить эту проблему, рассмотрим классический двухэтапный алгоритм с использованием временной таблицы:
ВЫБРАТЬ
Источник.Товар КАК Товар,
Источник.Магазин КАК Магазин,
МАКСИМУМ(Источник.Подразделение) КАК Подразделение
ПОМЕСТИТЬ ВТ_СгруппированныеДанные
ИЗ
Источник КАК Источник
СГРУППИРОВАТЬ ПО
Источник.Товар,
Источник.Магазин
ИНДЕКСИРОВАТЬ ПО
Товар,
Магазин,
Подразделение
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Товар КАК Товар,
ВТ.Магазин КАК Магазин,
ВТ.Подразделение КАК Подразделение,
Источник.ТипЦен КАК ТипЦен
ИЗ
ВТ_СгруппированныеДанные КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ Источник КАК Источник
ПО ВТ.Товар = Источник.Товар
И ВТ.Магазин = Источник.Магазин
И ВТ.Подразделение = Источник.Подразделение
Разберем этот алгоритм по шагам:
МАКСИМУМ. Результат помещается во временную таблицу ВТ_СгруппированныеДанные.Товар, Магазин, Подразделение). Это жизненно необходимо на объемах в миллионы строк, чтобы СУБД могла выполнить быстрое соединение типа Index Seek вместо тяжелого сканирования всей таблицы.ТипЦен).---
В ходе обсуждения автор темы предложил альтернативный вариант без использования временных таблиц, мотивируя это нежеланием тратить ресурсы на запись 2 миллионов строк в базу tempdb. Рассмотрим его логику:
ВЫБРАТЬ
Продажи.Товар КАК Товар,
Продажи.Магазин КАК Магазин,
ЕСТЬNULL(ТабСПодразделением.Подразделение, ТабБезПодразделения.Подразделение) КАК Подразделение,
ЕСТЬNULL(ТабСПодразделением.ТипЦен, ТабБезПодразделения.ТипЦен) КАК ТипЦен
ИЗ
ИсходныеПродажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Магазины КАК ТабСПодразделением
ПО Продажи.Магазин = ТабСПодразделением.Ссылка
И ТабСПодразделением.Подразделение <> ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Магазины КАК ТабБезПодразделения
ПО Продажи.Магазин = ТабБезПодразделения.Ссылка
И ТабБезПодразделения.Подразделение = ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
Проанализируем этот подход:
Этот метод основан на разделении условий связи на две ветки. Сначала мы пытаемся соединиться по строкам, где подразделение заполнено. Если такое соединение не вернуло результат (значение NULL), функция ЕСТЬNULL перенаправляет запрос на второе соединение, которое ищет запись с пустым подразделением.
В чем кроется опасность такого подхода на больших объемах?
Несмотря на то, что автор избежал создания временной таблицы, этот запрос заставляет СУБД выполнять два независимых левых соединения с одной и той же таблицей. На таблицах объемом более 2 миллионов записей это приводит к колоссальной нагрузке:
Поэтому решение автора из Сообщения 22 является рабочим, но крайне не рекомендуется для высоконагруженных систем и больших массивов данных.
---
Существует профессиональный разработческий прием, который позволяет решить задачу выбора приоритетной записи вообще за один проход СУБД, без повторных соединений и без использования ресурсоемких временных таблиц. Этот метод основан на лексикографическом сравнении строк.
Если сопутствующее поле (например, ТипЦен) имеет строковое представление (или мы можем получить его уникальный идентификатор в виде строки), мы можем искусственно сконструировать "взвешенное" строковое поле прямо в запросе. Рассмотрим пример:
ВЫБРАТЬ
Источник.Товар КАК Товар,
Источник.Магазин КАК Магазин,
// Вычисляем приоритетное подразделение
МАКСИМУМ(Источник.Подразделение) КАК Подразделение,
// Конструируем строку: префикс приоритета + представление Типа Цен
МИНИМУМ(
ВЫБОР
КОГДА Источник.Подразделение <> ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
ТОГДА "1_" + Источник.ТипЦен.Код
ИНАЧЕ "2_" + Источник.ТипЦен.Код
КОНЕЦ
) КАК СклеенныйТипЦен
ПОМЕСТИТЬ ВТ_ПредварительныйРезультат
ИЗ
Источник КАК Источник
СГРУППИРОВАТЬ ПО
Источник.Товар,
Источник.Магазин
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Товар КАК Товар,
ВТ.Магазин КАК Магазин,
ВТ.Подразделение КАК Подразделение,
// Отсекаем префикс приоритета и получаем чистый код типа цен
ПОДСТРОКА(ВТ.СклеенныйТипЦен, 3, 10) КАК КодТипаЦен
ИЗ
ВТ_ПредварительныйРезультат КАК ВТ
Как это работает?
В секции конструирования строки мы добавляем префикс "1_" для строк с заполненным подразделением (высший приоритет) и префикс "2_" для строк с пустой ссылкой. При использовании агрегатной функции МИНИМУМ СУБД сравнивает строки посимвольно. Поскольку символ "1" лексически меньше, чем "2", система гарантированно выберет значение, соответствующее строке с заполненным подразделением.
Затем, во втором запросе пакета, нам остается лишь воспользоваться функцией ПОДСТРОКА, чтобы отсечь первые два сервисных символа и вернуть оригинальный код (или GUID) типа цен. Этот метод работает невероятно быстро, поскольку СУБД сканирует исходную таблицу ровно один раз.
---
Если ваша база работает на современной версии платформы 1С:Предприятие (начиная с релиза 8.3.13), у вас появляется возможность использовать встроенную функцию нумерации строк. Это аналог оконной функции ROW_NUMBER() OVER(...) из классического SQL.
С помощью этой функции мы можем пронумеровать записи в разрезе каждой группы товаров и магазинов, отсортировав их так, чтобы строки с заполненными подразделениями шли первыми:
ВЫБРАТЬ
Источник.Товар КАК Товар,
Источник.Магазин КАК Магазин,
Источник.Подразделение КАК Подразделение,
Источник.ТипЦен КАК ТипЦен,
// Задаем сортировку: заполненные подразделения будут первыми
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Пронумерованная
ИЗ
Источник КАК Источник
УПОРЯДОЧИТЬ ПО
Товар,
Магазин,
ВЫБОР
КОГДА Источник.Подразделение <> ЗНАЧЕНИЕ(Справочник.Подразделения.ПустаяСсылка)
ТОГДА 1
ИНАЧЕ 2
КОНЕЦ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Товар КАК Товар,
ВТ.Магазин КАК Магазин,
МИНИМУМ(ВТ.НомерСтроки) КАК МинимальныйНомер
ПОМЕСТИТЬ ВТ_МинимальныеНомера
ИЗ
ВТ_Пронумерованная КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Товар,
ВТ.Магазин
ИНДЕКСИРОВАТЬ ПО
МинимальныйНомер
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Данные.Товар КАК Товар,
ВТ_Данные.Магазин КАК Магазин,
ВТ_Данные.Подразделение КАК Подразделение,
ВТ_Данные.ТипЦен КАК ТипЦен
ИЗ
ВТ_МинимальныеНомера КАК ВТ_Номера
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Пронумерованная КАК ВТ_Данные
ПО ВТ_Номера.МинимальныйНомер = ВТ_Данные.НомерСтроки
Важная задокументированная ловушка функции АВТОНОМЕРЗАПИСИ():
Многие начинающие разработчики совершают ошибку, накладывая жесткое условие ГДЕ НомерСтроки = 1. Помните: в СУБД сквозная нумерация при использовании функции АВТОНОМЕРЗАПИСИ() может начаться не с единицы, а продолжиться дальше, если в рамках одной сессии выполняются несколько подобных операций. Поэтому всегда следует сначала получить минимальный номер строки в разрезе вашей группировки с помощью МИНИМУМ(НомерСтроки), а затем соединить таблицы обратно по этому номеру.
---
Выбор оптимального метода напрямую зависит от архитектуры вашей конфигурации и объема данных:
МАКСИМУМ во временную таблицу с последующим левым соединением). Он понятен, легко читается и сопровождается другими разработчиками.