Как упорядочить результат запроса по агрегатной функции в итогах 1С?

← На главную

Рассмотрим частую задачу при написании запросов в 1С: необходимость отсортировать результат по количеству элементов (или другой агрегатной функции), когда в запросе используется секция ИТОГИ. Проанализируем типичную ошибку и разберем способы получения правильного результата.

Суть проблемы и типичная ошибка

Допустим, мы хотим получить список заказов и количество вхождений ссылок по каждому из них, а затем отсортировать результат от большего количества к меньшему. Часто разработчики пытаются написать конструкцию вида:


ВЫБРАТЬ
	ПриобретениеТоваровУслуг.НомерИнтернетЗаказа КАК НомерИнтернетЗаказа,
	ПриобретениеТоваровУслуг.Ссылка КАК КоличествоВхождений
ИЗ
	Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг
ГДЕ
	ПриобретениеТоваровУслуг.НомерИнтернетЗаказа В (&НомерИнтернетЗаказа)
УПОРЯДОЧИТЬ ПО
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ КоличествоВхождений) УБЫВ
ИТОГИ
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ КоличествоВхождений)
ПО
	НомерИнтернетЗаказа

При попытке выполнить такой запрос платформа 1С выдаст синтаксическую ошибку.

Почему возникает ошибка и как работает платформа?

Выясним причину такого поведения. В языке запросов 1С существуют жесткие ограничения на использование агрегатных функций в секции УПОРЯДОЧИТЬ ПО:

Ограничение на агрегатные функции без группировки: Использование агрегатных функций (включая КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...)) в предложении УПОРЯДОЧИТЬ ПО допускается платформой только в том случае, если в запросе используется предложение классической группировки СГРУППИРОВАТЬ ПО. Если запрос содержит только секцию ИТОГИ, платформа не позволяет сортировать по агрегатам, так как итоги формируются на отдельном этапе поверх основной выборки.
Иллюзия правильной сортировки по псевдониму: Если мы исправим синтаксическую ошибку и напишем просто УПОРЯДОЧИТЬ ПО КоличествоВхождений УБЫВ (где КоличествоВхождений — это псевдоним поля Ссылка), мы получим не тот результат, который ожидаем. В этом случае отсортируются исключительно детальные строки (документы) внутри каждой группы, причем сортировка произойдет не по количеству, а по внутреннему идентификатору ссылки (GUID). Сами же группировочные записи (уровни итогов по номеру заказа) останутся в стандартном порядке (по алфавиту или по возрастанию самого поля).

Важно понимать: В языке запросов 1С принципиально отсутствует возможность напрямую упорядочить итоговые уровни ИТОГИ ... ПО ... по значению рассчитанного для них агрегатного итога.

Способы решения задачи

Разберем по шагам альтернативные варианты решения этой задачи в зависимости от того, какой именно результат нам нужен на выходе.

Способ 1. Замена ИТОГИ на СГРУППИРОВАТЬ ПО

Если нам не требуются детальные записи (сами документы), а нужен лишь свернутый список номеров заказов с их количеством, следует отказаться от секции ИТОГИ в пользу классической группировки. В запросах с СГРУППИРОВАТЬ ПО сортировка по имени агрегатного поля работает абсолютно штатно.

Посмотрим на исправленный пример кода:


ВЫБРАТЬ
    ПриобретениеТоваровУслуг.НомерИнтернетЗаказа КАК НомерИнтернетЗаказа,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПриобретениеТоваровУслуг.Ссылка) КАК КоличествоВхождений
ИЗ
    Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг
ГДЕ
    ПриобретениеТоваровУслуг.НомерИнтернетЗаказа В (&НомерИнтернетЗаказа)
СГРУППИРОВАТЬ ПО
    ПриобретениеТоваровУслуг.НомерИнтернетЗаказа
УПОРЯДОЧИТЬ ПО
    КоличествоВхождений УБЫВ

Способ 2. Использование временной таблицы

Если необходимо получить и детальные записи документов (иерархию), и при этом чтобы группы (номера заказов) были упорядочены по количеству вхождений, используем пакет запросов и временную таблицу.

Первым запросом пакета группируем данные по полю НомерИнтернетЗаказа, вычисляем КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) и помещаем этот результат во временную таблицу.
Во втором запросе соединяем полученную временную таблицу с исходной таблицей документов.
В секции ИТОГИ ... ПО второй выборки делаем первой группировкой вычисленное числовое поле количества. Платформа отсортирует верхний уровень итогов по этому числовому значению.

Способ 3. Программная сортировка в коллекции

Если результат запроса в дальнейшем выгружается в коллекцию значений для обработки кодом, мы можем произвести сортировку программно:

При выгрузке плоской выборки в ТаблицаЗначений можно применить метод: ТаблицаЗначений.Сортировать("КоличествоВхождений Убыв");
При выгрузке результата запроса с итогами в ДеревоЗначений, строки верхнего уровня (наши группировки) можно отсортировать методом: Дерево.Строки.Сортировать("КоличествоВхождений Убыв");

Способ 4. Использование Системы компоновки данных (СКД)

Если данная задача решается в рамках разработки отчета, то лучше всего использовать СКД. В Системе компоновки данных описанное ограничение языка запросов отсутствует. В настройках структуры отчета, на уровне группировки по полю НомерИнтернетЗаказа, достаточно добавить сортировку по ресурсу КоличествоВхождений (по убыванию). СКД сама корректно рассчитает итоги и отсортирует ветви дерева по агрегированному значению при выводе.