Неуникальные значения в нарастающем итоге SUM + ORDER BY

В предыдущей серии: "Но если внутри over написать ORDER BY, то функция будет вычисляться не по всем строкам, а от первой строки и до текущей, включительно (не совсем так конечно, но об этом в следующих заданиях)."

Разберем, что значит "не совсем так". Посмотри на запрос и его результат:

SELECT pi.purchase_id,
       pi.count,
       sum(pi.count) over (ORDER BY pi.purchase_id, pi.count) AS count_current,
       sum(pi.count) over () AS count_total
  FROM purchase_item pi
 ORDER BY pi.purchase_id,
          pi.count
# purchase_id count count_current count_total
1 1 1 1 54
2 1 30 31 54
3 2 1 32 54
4 3 1 34 54
5 3 1 34 54
6 4 2 36 54
7 5 1 37 54
8 6 1 40 54
9 6 1 40 54
10 6 1 40 54
11 7 3 43 54
12 8 1 44 54
13 9 1 45 54
14 10 1 47 54
15 10 1 47 54
16 10 5 52 54
17 11 1 54 54
18 11 1 54 54

Особо интересуют строки

# purchase_id count count_current count_total
... ... ... ... ...
3 2 1 32 54
4 3 1 34 54
5 3 1 34 54
6 4 2 36 54
... ... ... ... ...

На 3-ей строке count_current был равен 32. На 4-й count равен 1, и казалось бы count_current должен быть равен 33 (32 + 1). Но он равен 34.

Причины становятся ясны, если взглянуть на ORDER BY в оконной функции и на 5-ю строку.

(ORDER BY pi.purchase_id, pi.count)
# purchase_id count count_current count_total
... ... ... ... ...
3 2 1 . .
4 3 1 . .
5 3 1 . .
6 4 2 . .
... ... ... ... ...

В 4-й и 5-й строке оба значения purchase_id и count совпадают. Поэтому с точки зрения сортировки они не различаются.

Агрегатные оконные функции для вычисления значения для очередной строки включают в набор все строки с совпадающими значениями из ORDER BY. Именно по этому в 4-й и 5-й строках count_current равен 34 (32 + 1 + 1).

Для вычисления значения count_current в 4-й и 5-й строке брались строки

# purchase_id count count_current count_total
1 1 1 1 54
2 1 30 31 54
3 2 1 32 54
4 3 1 34 54
5 3 1 34 54

Для строк с 8 по 10 агрегатная функция так же принимает одинаковое значение:

# purchase_id count count_current count_total
... ... ... ... ...
7 5 1 37 54
8 6 1 40 54
9 6 1 40 54
10 6 1 40 54
11 7 3 43 54
... ... ... ... ...

40 = 37 + 1 + 1 + 1

P.S. В оконных функциях можно явно задавать размер окна (группу строк) для вычисления значения. Возможностей задания окна достаточно много и они заслуживают отдельной темы.

11.8 Нарастающий итог SUM + ORDER BY

11.10 Собираем строки через разделитель - STRING_AGG