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