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

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

Работа в рамках окна (группы строк). Аналитические функции работают в определённом окне, которое определяется с помощью операторов 

PARTITION BY (разделение на группы) и 

ORDER BY (сортировка). 

Сохранение строк. В отличие от агрегатных функций, которые сводят строки к одной, аналитические функции возвращают одно значение для каждой строки в наборе. 

Порядок обработки. Аналитические функции — это последний набор операций, выполняемых в запросе, за исключением последнего предложения  ORDER BY.  Все соединения, WHERE, GROUP BY и HAVING обрабатываются до них. 

Синтаксис. Аналитические функции обычно включают ключевое слово  OVER с определением окна. 

Виды аналитических функций

Агрегатные. Выполняют арифметические вычисления и возвращают итоговое значение. К ним относятся SUM, AVG, MIN, MAX, COUNT. 

 Ранжирующие. Определяют ранги строк на основе заданного условия. Например, RANK, DENSE_RANK, ROW_NUMBER. 

Функции смещения (сдвига). Позволяют получать доступ к данным из других строк в пределах окна вычисления. Например, LAG (значение из предыдущей строки), LEAD(значение из следующей строки), FIRST_VALUE, LAST_VALUE. 

Статистические. Дают информацию о вариативности и распределении данных в окне. Например, 

STDDEV, VARIANCE, PERCENTILE_CONT, PERCENTILE_DISC. 

Функции распределения. Помогают понять относительное положение значения в общем наборе данных. Например, PERCENT_RANK(относительный ранг строки), CUME_DIST (кумулятивное распределение).

Применение: 

• Отчёты и аналитика. Удобны для создания отчётов с детализацией по категориям или группам. 
• Финансовые расчёты. Позволяют вычислять кумулятивные суммы или средние значения по временным периодам. 
•  Ранжирование данных. Используются для ранжирования элементов по определённым критериям. 
• Анализ трендов и сравнение данных. Например, функции смещения помогают сравнивать текущую строку с соседними. 

Важно: аналитические функции не поддерживаются в материализованных представлениях. Их использование может влиять на производительность запросов, поэтому стоит оптимизировать запросы при работе с большими объёмами данных.

Некоторые преимущества использования аналитических функций:

• Скорость и эффективность. Аналитические функции выполняются непосредственно в базе данных, что снижает нагрузку на сервер и ускоряет обработку запросов.
• Уменьшение объёма кода. С использованием аналитических функций можно существенно уменьшить объём SQL-кода, делая его более читаемым и поддерживаемым.
• Возможность выполнять сложные операции. Аналитические функции позволяют выполнять такие операции, как расчёты накопительных сумм, анализ трендов и вычисление отклонений.
• Гибкость. Аналитические функции предоставляют возможность настраивать аналитические запросы под конкретные потребности бизнеса.

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

1С не поддерживает аналитические функции, хотя на них в SQL сформировался свой стандарт.

Аналог аналитических функций в 1С

В 1С СКД есть свои аналитические функции: ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив, ВычислитьВыражениеСГруппировкойТаблицаЗначений. Данные функции реализованы только в СКД и выполняются только после основного запроса. Фактически обработка данных производится на стороне сервера 1С, а не сервера баз данных.

Синтаксис функции «ВычислитьВыражениеСГруппировкойМассив»: 

ВычислитьВыражениеСГруппировкойМассив (Выражение, ВыраженияПолейГруппировки, ОтборЗаписей, ОтборГруппировок).

Параметры:

• Выражение - выражение, которое нужно вычислить. Тип Строка. Например, "Сумма(СуммаОборот)"; 
• ВыраженияПолейГруппировки - выражения полей группировки, перечисленные через запятую. Например, "Контрагент, Партия"; 
• ОтборЗаписей - выражение, применяемое к детальным записям. Например, "ПометкаУдаления = Ложь". Если в данном параметре используется агрегатная функция, то при выполнении компоновки данных возникнет ошибка; 
• ОтборГруппировок - отбор, применяемый к групповым записям. Например: "Сумма(СуммаОборот) > &Параметр1".

Как видим, синтаксис похож на синтаксис аналитической функции SQL.

Функция ВычислитьВыражениеСГруппировкойМассив позволят возвращать массив, каждый элемент которого содержит результат вычисления выражения для группировки по указанному полю. То есть массив вычисляемых полей.

ВЫБРАТЬ
	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

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

ВЫБРАТЬ
	ВременнаяТаблица.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