Заметки по SQL: Создание аналога аналитической функции, которая находит максимальное (минимальное) значение в пределах окна в группе простым запросом

Постановка задачи.

Необходимо сравнить продажи менеджеров с значением продаж самого эффективного менеджера за месяц.

Исходные данные:   

Результат на выходе:

select emp.name,
       emp.val,
       emp.месяц,
       (select max(val) from employees where месяц = emp.месяц) max_val
from employees emp

К сожалению, 1С не поддерживает подзапросы в разделе SELECT.

Использование аналитической функции:

select emp.name,
       emp.val,
       emp.месяц,
       max(val) over (partition by месяц) max_val
from employees emp

1С не поддерживает такие функции, зато в СКД есть своя – ВычислитьВыражение, но это другая история.

Предлагаемое решение:

ВЫБРАТЬ
	148 КАК id,
	410 КАК val,
	"Dgon" КАК name,
	"1" КАК Month,
	ДАТАВРЕМЯ(2026, 1, 1) КАК Data_val
ПОМЕСТИТЬ ВременнаяТаблица

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	130,
	370,
	"Smit",
	"1",
	ДАТАВРЕМЯ(2026, 1, 14)

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	150,
	170,
	"Dgon",
	"2",
	ДАТАВРЕМЯ(2026, 2, 11)

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	153,
	770,
	"Smit",
	"2",
	ДАТАВРЕМЯ(2026, 2, 16)

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	151,
	405,
	"Dgon",
	"3",
	ДАТАВРЕМЯ(2026, 3, 10)

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	153,
	275,
	"Smit",
	"3",
	ДАТАВРЕМЯ(2026, 3, 8)
;

ВЫБРАТЬ
	ВременнаяТаблица.val КАК max_val,
	ВременнаяТаблица.name КАК max_name,
	ВременнаяТаблица2.val КАК val,
	ВременнаяТаблица2.name КАК name,
	ВременнаяТаблица2.Month КАК Month
ИЗ
	ВременнаяТаблица КАК ВременнаяТаблица2
		ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица
			ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
			ПО ВременнаяТаблица.Month = ВременнаяТаблица1.Month
		ПО ВременнаяТаблица2.Month = ВременнаяТаблица.Month

СГРУППИРОВАТЬ ПО
	ВременнаяТаблица.name,
	ВременнаяТаблица.val,
	ВременнаяТаблица2.Month,
	ВременнаяТаблица2.val,
	ВременнаяТаблица2.name

ИМЕЮЩИЕ
	МАКСИМУМ(ВременнаяТаблица1.val) = ВременнаяТаблица.val

УПОРЯДОЧИТЬ ПО
	Month

Анализ  запроса

ВЫБРАТЬ
	ВременнаяТаблица.val КАК val,
	ВременнаяТаблица.name КАК name,
	ВременнаяТаблица.Month КАК Month
ПОМЕСТИТЬ ВременнаяТаблица1
ИЗ
	ВременнаяТаблица КАК ВременнаяТаблица
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
		ПО ВременнаяТаблица.Month = ВременнаяТаблица1.Month

СГРУППИРОВАТЬ ПО
	ВременнаяТаблица.Month,
	ВременнаяТаблица.name,
	ВременнаяТаблица.val

ИМЕЮЩИЕ
	МАКСИМУМ(ВременнаяТаблица1.val) = ВременнаяТаблица.val

Условие 

ИМЕЮЩИЕ МАКСИМУМ(ВременнаяТаблица1.val) = ВременнаяТаблица. val

ищет максимальное значение val за месяц.

Если заменить это условие на

ИМЕЮЩИЕ  МИНИМУМ (ВременнаяТаблица1.val) = ВременнаяТаблица. val

то получим минимальное значение за месяц.

ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица

ПО ВременнаяТаблица.месяц = ВременнаяТаблица1.месяц

создаёт окно просмотра.

Такая конструкция позволяет не только получить максимальное значение val за месяц и сразу узнать какому сотруднику оно принадлежит.

Усложним условие.

Предположим за третий месяц два сотрудника продали товара на одинаковую сумму, добавим в первую временную таблицу

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	153,
	405,
	"Garii",
	"3",
	ДАТАВРЕМЯ(2026, 3, 4)

 В результате записи по Garii и Dgon удвоятся.

Чтобы это исключить, добавим условие с минимальной датой записи, определим, кто первый достиг максимума продаж

ВЫБРАТЬ
	ВременнаяТаблица.val КАК max_val,
	ВременнаяТаблица.name КАК max_name,
	ВременнаяТаблица.Data_val КАК Data_val,
	ВременнаяТаблица.Month КАК Month,
	ВременнаяТаблица3.name КАК name,
	ВременнаяТаблица3.val
ИЗ
	ВременнаяТаблица КАК ВременнаяТаблица3
		ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
		ПО (ВременнаяТаблица.Month = ВременнаяТаблица1.Month)
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица2
		ПО (ВременнаяТаблица.val = ВременнаяТаблица2.val)
	ПО (ВременнаяТаблица3.Month = ВременнаяТаблица.Month)

СГРУППИРОВАТЬ ПО
	ВременнаяТаблица.name,
	ВременнаяТаблица.val,
	ВременнаяТаблица.Data_val,
	ВременнаяТаблица.Month,
	ВременнаяТаблица3.val,
	ВременнаяТаблица3.name

ИМЕЮЩИЕ
	МАКСИМУМ(ВременнаяТаблица1.val) = ВременнаяТаблица.val И
	МИНИМУМ(ВременнаяТаблица2.Data_val) = ВременнаяТаблица.Data_val

УПОРЯДОЧИТЬ ПО
	Month,
Data_val1

Результат

Примеры применения.

1. Сравнение производительности сотрудников (подразделений).
2. Сравнение прибыли (расходов) по месяцам с прибылью максимально эффективного месяца.
3. Вычисление максимального (минимального) стажа по специальностям
4. Определение контрагента с максимальной задолженностью.
5. …