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