Оконные функции: использование RANGE и GROUPS mode
14 июня 2025
Ранее в этом блоге мы познакомились с оконными функциями в PostgreSQL. Возможности оконных функций рассматривались на примере ROWS mode, как наиболее простом для понимания. Однако бывают задачи, где ROWS mode не очень-то помогает, и приходится прибегать к RANGE mode или GROUPS mode. Рассмотрим использование данных режимов на простых примерах.
Пусть имеется таблица с временными рядами:
ts TIMESTAMP NOT NULL,
temperature INT NOT NULL);
INSERT INTO temperature (ts, temperature) VALUES
('2020-01-01 01:00:00', 10),
('2020-01-01 02:10:00', 21),
('2020-01-01 02:20:00', 22),
('2020-01-01 02:30:00', 23),
('2020-01-01 06:00:00', 60),
('2020-01-01 07:00:00', 70),
('2020-01-01 07:30:00', 73),
('2020-01-01 08:50:00', 85);
Временные метки не округлены до ближайшего часа или дня. Именно так обычно и бывает на практике. Необходимо посчитать некий агрегат по временным рядам, вроде скользящей средней. ROWS mode здесь довольно бесполезен.
Попробуем воспользоваться RANGE mode и посмотрим, что будет:
FROM temperature AS t
WINDOW w AS (
ORDER BY ts
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND
INTERVAL '1 hour' FOLLOWING
);
Здесь и далее в качестве агрегатной функции я использую array_agg(). Сделано это для наглядности. Так мы видим все аргументы, которые принимала функция, а не какое-то производное значение от аргументов.
Получаем результат:
---------------------+---------------
2020-01-01 01:00:00 | {10}
2020-01-01 02:10:00 | {21,22,23}
2020-01-01 02:20:00 | {21,22,23}
2020-01-01 02:30:00 | {21,22,23}
2020-01-01 06:00:00 | {60,70}
2020-01-01 07:00:00 | {60,70,73}
2020-01-01 07:30:00 | {70,73}
2020-01-01 08:50:00 | {85}
Качественно RANGE mode работает так же, как и ROWS mode. Однако он смотрит не на номера строк, а на значения, по которым делается ORDER BY. В данном примере мы смотрим на 1 час вперед и на 1 час назад относительно ts.
Заметьте, что во фрейм для шестого часа попала строка, которая относится к седьмому часу. То есть, здесь используются замкнутые интервалы. Если нужен открытый интервал, замените 1 hour на 59 min 59 sec 999999 usec.
Разумеется, для типов, отличных от TIMESTAMP, RANGE mode работает ничем не хуже. Например, для целых чисел вместо INTERVAL '1 hour' вы будете писать просто 1. Запомнить просто. С чем вы обычно складываете или вычитаете тип, то и указываете для BETWEEN.
Fun fact! Ранее отмечалось, что синтаксис OVER () эквивалентен OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Так как в этом случае не указан никакой ORDER BY, то строки считаются равными в рамках порядка сортировки. Именно потому все они попадают в один фрейм.
GROUPS mode работает не намного сложнее:
FROM temperature AS t
WINDOW w AS (
ORDER BY date_trunc('hour', ts)
GROUPS CURRENT ROW
);
Результат:
---------------------+------------
2020-01-01 01:00:00 | {10}
2020-01-01 02:10:00 | {21,22,23}
2020-01-01 02:20:00 | {21,22,23}
2020-01-01 02:30:00 | {21,22,23}
2020-01-01 06:00:00 | {60}
2020-01-01 07:00:00 | {70,73}
2020-01-01 07:30:00 | {70,73}
2020-01-01 08:50:00 | {85}
Здесь временные метки были округлены до часа при помощи date_trunc(). При этом в каждый фрейм попали строки, относящиеся к одному часу. Именно для этого и придуман GROUPS mode.
Попробуем объединить строки за текущий час и за предыдущий в одном фрейме:
FROM temperature AS t
WINDOW w AS (
ORDER BY date_trunc('hour', ts)
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
);
Результат:
---------------------+---------------
2020-01-01 01:00:00 | {10}
2020-01-01 02:10:00 | {10,21,22,23}
2020-01-01 02:20:00 | {10,21,22,23}
2020-01-01 02:30:00 | {10,21,22,23}
2020-01-01 06:00:00 | {21,22,23,60}
2020-01-01 07:00:00 | {60,70,73}
2020-01-01 07:30:00 | {60,70,73}
2020-01-01 08:50:00 | {70,73,85}
Заметьте, что во фрейм для шестого часа попали строки, относящиеся ко второму часу. Связано это с тем, что GROUPS mode, в отличие RANGE mode, значения не интересуют. Просили данные из предыдущей группы? Пожалуйста, вот данные из предыдущей группы. Тот факт, что между группами имеется дыра в несколько часов — это не забота GROUPS mode.
В силу названных обстоятельств для чего-то вроде скользящей средней больше подходит RANGE mode:
SELECT date_trunc('hour', ts) AS h,
temperature
FROM temperature
)
SELECT DISTINCT ON (h)
h, array_agg(temperature) OVER w
FROM hourly
WINDOW w AS (
ORDER BY h
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
);
Результат почти правильный:
---------------------+---------------
2020-01-01 01:00:00 | {10}
2020-01-01 02:00:00 | {10,21,22,23}
2020-01-01 06:00:00 | {60}
2020-01-01 07:00:00 | {60,70,73}
2020-01-01 08:00:00 | {70,73,85}
«Почти», потому что на практике часто требуется получить результат и для тех интервалов, по которым исходных данных нет. Вот исправленный запрос:
SELECT date_trunc('hour', ts) AS h, temperature
FROM temperature
),
all_hours AS (
SELECT generate_series(min(h), max(h), INTERVAL '1 hour') AS h
FROM hourly
)
SELECT DISTINCT ON (all_hours.h)
all_hours.h, COALESCE(
array_agg(hourly.temperature)
FILTER (WHERE hourly.temperature IS NOT NULL)
OVER w,
'{}') AS agg
FROM all_hours
LEFT JOIN hourly ON all_hours.h = hourly.h
WINDOW w AS (
ORDER BY all_hours.h
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
);
… и его результат:
---------------------+---------------
2020-01-01 01:00:00 | {10}
2020-01-01 02:00:00 | {10,21,22,23}
2020-01-01 03:00:00 | {21,22,23}
2020-01-01 04:00:00 | {}
2020-01-01 05:00:00 | {}
2020-01-01 06:00:00 | {60}
2020-01-01 07:00:00 | {60,70,73}
2020-01-01 08:00:00 | {70,73,85}
Заметьте, что во фрейм для третьего часа попали данные для второго часа, хотя по третьему часу исходные данные отсутствовали. Как правило, это желаемое поведение. Заинтересованным читателям предлагается переписать запрос так, чтобы по третьему часу агрегат был пустым, в качестве упражнения.
Подведем итоги. ROWS mode используется, когда мы работаем с конкретными строками. RANGE mode — когда работаем с диапазонами значений. GROUPS mode — когда данные объединяются в логические группы. Если держать в уме эту эвристику, то затруднений с использованием оконных функций не возникает.
Метки: PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.