Почему `SUM() OVER (ORDER BY ...)` иногда считает «неправильно»: разбираем оконные фреймы в SQL

[Viktor006Juk]

Чувствуется настоящий ресерч уровня "гуглил три часа, понял на полстраницы". Но зато честно - половина реальной разработки так и выглядит.

[rozhnev]

Если чнстно, цель статьи - повысить осведомлённость разработчиков. Если к оконкам народ уже привык и использует достаточно широко, то о настройках окна кроме стандартных многие (как и я до недавнего времени) даже не подозревают.

[SashkaCosmonaut]

Отличная статья! Как раз хороша тем, что много примеров. А то часто на Хабре пишут, что вот, в справке/документации/манах к {ProductName} всё есть, "открыли Америку" и т.п. Но уверен, что большинство разработчиков, собственно, как и я, вначале читает справку (если вообще читает), а после этого всё равно идёт в Гугл за примерами, разборами, пересказами "простым языком" в статьях, подобных этой.

[Akina]

Есть ещё один способ на реальных данных получить детерминированные накопительные/скользящие итоги, и при этом не прибегать к использованию определения фрейма (для накопительных). Всё, что нужно - просто расширить условие сортировки и сделать ключ сортировки уникальным. Т.е., например, вместо SUM(amount) OVER (ORDER BY amount) пишем SUM(amount) OVER (ORDER BY amount, {id}) , где {id} есть некое уникальное поле либо выражение (ничего подходящего нет в первых мини-примерах статьи, но я не зря оговорился о реальных данных) - и имеем нормальный детерминированный результат.

Причём на сей раз - стопроцентно и гарантированно детерминированный. В отличие от ROWS - там пусть и считаются отдельные записи, но сортировка -то уникальности не обеспечила, а потому порядок записей с равным ключом сортировки в группе не определён, и кто ведает, что нам из той группы отсчитается.

[rozhnev]

Спасибо за хороший пример. Запомню для дальнейшего использования.

[windsurfer69]

Пишите еще, не слушайте недоброжелателей. Статья полезная.

[rozhnev]

Спасибо, обязательно напишу. Кстати я не заметил недоброжелательности. Пока вся критика конструктивная.

[OchirD]

Прохожу курс аналитики, странно что такой инфы там нет)
Статья отличная, все по полочкам, все понятно!
Сохраню ссылку в свой банк знаний)

[anatol8383]

Спасибо за статью. Полезная информация, продробное объяснение.
Также было бы полезно упомянуть очередность выполнения операций WHERE, GROUP BY, HAVING, ORDER BY ... и (самое главное!) каком месте происходит исполнение оконных функций. Тогда было бы понятнее объяснение про "SUM(SUM(amount)) OVER (...) ".

[rozhnev]

Спасибо за комментарий, в этой статье я хотел рассмотреть только один аспект применения оконных функций, другие учебные материалы я публикую на своём сайте

[unfilled]

С rows/range могут быть ещё нюансы с производительностью. В MS SQL, например, rows between unbounded preceding and current row значительно быстрее range between unbounded preceding and current row (который используется по-умолчанию).

На mssql 2019, такие результаты получал, в одном запросе (все строки были уникальны, результат одинаков)

range - CPU time = 1469 ms

rows - CPU time = 235 ms

[rozhnev]

Очень интересный факт. Будет здорово если вы приведете пример, выложив его на sqlize.online

[unfilled]

мне лень куда-то ходить, вот набросал poc

drop table if exists #test;

create table #test (
	id bigint not null primary key
);

insert into #test (
	id
)
select
	[value]
from generate_series (1, 100000)

set statistics time on;
select	
	t = sum(id) over (order by id) 
from #test


select	
	t = sum(id) over (order by id rows between unbounded preceding and current row) 
from #test
set statistics time off;

(100000 rows affected)

(100000 rows affected)

SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 629 ms.

(100000 rows affected)

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 476 ms.

sql server 2022

[unfilled]

Просто для истории - в pg (11, 14, из того, что было под рукой) rows тоже быстрее (не так заметно, как в ms sql, но всё же)

drop table if exists ttest;

create temp table ttest (
	id bigint not null primary key
);

insert into ttest (
	id
)
select
	gs.i
from generate_series (1, 1000000) gs(i);

explain (analyze, costs off)
select	
	sum(id) over (order by id) 
from ttest;
--WindowAgg (actual time=0.042..736.882 rows=1000000 loops=1)

explain (analyze, costs off)
select	
	 sum(id) over (order by id rows between unbounded preceding and current row) 
from ttest;
--WindowAgg (actual time=0.043..638.843 rows=1000000 loops=1)