Оконные функции. Разница между range и rows. Кляуза groups.

Типы кадрирования range и rows в оконных функциях

Будем считать нарастающий итог salary работников в порядке даты найма. Подготовим для начала CTE для таблицы emp* таким образом.

select e.*  
from emp e
cross join generate_series(1,3,1) num
where deptno=10

Будем считать нарастающий итог 3 способами: по старинке - без явного указания окна, и с указанием в явном виде range between unbounded preceding and current row и rows between unbounded preceding and current row. Для учебных целей (наглядности) считать будем в разрезе ename.

with t as (
select e.*  
from emp e
cross join generate_series(1,3,1) num
where deptno=10
)
select t.*
, sum (sal) over (partition by ename order by hiredate) run_total_default
, sum (sal) over (partition by ename
                  order by hiredate
                  range between unbounded preceding and current row) run_total_range
, sum (sal) over (partition by ename
                  order by hiredate
                  rows between unbounded preceding and current row) run_total_rows                  
from t
order by 2

Получаем следующий результат:

Итак, поведением по умолчанию - без указания в явном виде типа кадрирования - используется range. В этом случае мы считаем агрегат по всем строкам, имеющим одинаковые значения в выражении ORDER BY, что и текущая строка. В случае кадрирования rows between unbounded preceding and current row мы считаем сумму с самой 1 строки в окне по текущую.

IRL скорее всего на промышленных данных эти суммы будут совпадать. Но нюанс стоит знать.

Тип кадрирования groups.

Помимо общеизвестных режимов формирования оконного кадра range и rows существует еще режим groups. Далее цитата из книги "PostgreSQL. Профессиональный SQL".

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

Давайте рассмотрим на примере

create table dates ( dt date );

insert into dates values
('2017-07-01'),
('2017-07-01'),
('2017-07-01'),
('2017-07-03'),
('2017-07-03'),
('2017-07-05'),
('2017-07-05'),
('2017-07-06'),
('2017-07-06'),
('2017-07-09'),
('2017-07-09'),
('2017-07-09'),
('2017-07-10');

Посмотрим разницу в режимах формирования оконного кадра на таком запросе

select dt
, count(*) over (order by dt 
    range between interval '2 days' preceding and current row) qt_range_mode
, count(*) over (order by dt 
    rows between 2 preceding and current row) qt_rows_mode
, count(*) over (order by dt 
    groups between 2 preceding and current row) qt_groups_mode
from dates
order by dt;

Проверено на PostgreSQL 15.7, compiled by Visual C++ build 1938, 64-bit

*Скрипты создания таблицы и её наполнения можно взять здесь